Results 1 to 5 of 5

Thread: SQL statement help?? listingsdbelements_field_value

  1. #1
    Join Date
    Oct 2007
    Location
    Bradenton, FL USA
    Posts
    94

    Post SQL statement help?? listingsdbelements_field_value

    can anyone help me expand the sql query below? I would like the statement to pull 6 values from the listingsdbelements_field_value where I am getting only one(property_type) right now. Looking for property_type,state,county,city,zip,neighborhood to be available as:
    $property_type
    $state
    $county
    $city
    $zip
    $neighborhood

    Thanks in advance for your time!!

    Code:
               // SQL to pull price or another field with alterations for display in link
    Code:
               $sql1 = "SELECT listingsdbelements_field_value from ".$config[table_prefix]. "listingsdbelements WHERE listingsdbelements_field_name = 'property_type' AND listingsdb_id = '$ID'";
               $recordSet1 = $conn->Execute($sql1);
               if ($recordSet1 === false) {
                   $misc->log_error($sql1);
               }
               while (!$recordSet1->EOF) {
                   $property_type = $misc->make_db_unsafe ($recordSet1->fields['listingsdbelements_field_value']);
                   $recordSet1->MoveNext();
               }
    Lee Forbes PA

    REALTOR / Designer

    FloridaPropertyOnline.com

    GulfsideLand.com

  2. #2
    Join Date
    Jul 2005
    Location
    Sao Paulo, BRAZIL
    Posts
    3,763

    Default Re: SQL statement help?? listingsdbelements_field_value

    I don't see a reason to "expand" your query. Just repeat it 6 times.

    But here it goes:
    Code:
    <?php
    global $config, $conn;
    require_once $config['basepath'] . '/include/misc.inc.php';
    $misc = new misc();
    $display = '';
    if(isset($_GET['listingID'])) {
    	$listingID = $_GET['listingID'];
    	$sql = 'SELECT listingsdbelements_field_name, listingsdbelements_field_value
     FROM ' . $config['table_prefix'] . 'listingsdbelements
     WHERE (listingsdbelements_field_name = "property_type"
     OR listingsdbelements_field_name = "state"
     OR listingsdbelements_field_name = "country"
     OR listingsdbelements_field_name = "city"
     OR listingsdbelements_field_name = "zip"
     OR listingsdbelements_field_name = "neighborhood")
     AND listingsdb_id = ' . $listingID . ' ORDER BY listingsdbelements_field_name ASC';
    	$recordSet = $conn->Execute($sql);
    	if(!$recordSet) {
    		$misc->log_error($sql);
    		}
    	while(!$recordSet->EOF) {
    		$field_name = $recordSet->fields['listingsdbelements_field_name'];
    		$field_value = $recordSet->fields['listingsdbelements_field_value'];
    		$display .= '<p>' . $field_name . ': ' . $field_value . '</p>';
    		$recordSet->MoveNext();
    		}
    	}
    return $display;
    ?>
    Eduardo

    Open-Realty® DOCs
    We can easily forgive a child who is afraid of the dark; the real tragedy of life is when men are afraid of the light. (Author Unknown)

  3. #3
    Join Date
    Oct 2007
    Location
    Bradenton, FL USA
    Posts
    94

    Default Re: SQL statement help?? listingsdbelements_field_value

    thanks for your help Eduardo. Below is the solution I finished after reading your post. This is for the sitemap pagination addon to use custom fields in the URL for OR 3.1.2
    check it out!!
    http://www.floridapropertyonline.com/sitemap-1.html


    Code:
    $sql = "SELECT listingsdb_id, listing_seotitle FROM " . $config[table_prefix] . "listingsdb WHERE listingsdb_active ='yes' ORDER BY listingsdb_id DESC $limit";
            $recordSet = $conn->Execute($sql);
            if ($recordSet === false) {
                $misc->log_error($sql);
            }
            while (!$recordSet->EOF) {
                $ID = $recordSet->fields[listingsdb_id];
                $Title = $misc->make_db_unsafe ($recordSet->fields[listing_seotitle]);
                // SQL to pull price or another field with alterations for display in link
                $sql1 = "SELECT listingsdbelements_field_value from ".$config[table_prefix]. "listingsdbelements WHERE listingsdbelements_field_name = 'property_type' AND listingsdb_id = '$ID'";
                $recordSet1 = $conn->Execute($sql1);
                if ($recordSet1 === false) {
                    $misc->log_error($sql1);
                }
       while (!$recordSet1->EOF) {
                    $property_type = $misc->make_db_unsafe ($recordSet1->fields['listingsdbelements_field_value']);
        $property_type_clean = str_replace(" ","-",$property_type);
                    $recordSet1->MoveNext();
                }
       $sql2 = "SELECT listingsdbelements_field_value from ".$config[table_prefix]. "listingsdbelements WHERE listingsdbelements_field_name = 'state' AND listingsdb_id = '$ID'";
                $recordSet2 = $conn->Execute($sql2);
                if ($recordSet2 === false) {
                    $misc->log_error($sql2);
                }
       while (!$recordSet2->EOF) {
                    $state = $misc->make_db_unsafe ($recordSet2->fields['listingsdbelements_field_value']);
                    $recordSet2->MoveNext();
                }
       $sql3 = "SELECT listingsdbelements_field_value from ".$config[table_prefix]. "listingsdbelements WHERE listingsdbelements_field_name = 'county' AND listingsdb_id = '$ID'";
                $recordSet3 = $conn->Execute($sql3);
                if ($recordSet3 === false) {
                    $misc->log_error($sql3);
                }
               while (!$recordSet3->EOF) {
                    $county = $misc->make_db_unsafe ($recordSet3->fields['listingsdbelements_field_value']);
                    $recordSet3->MoveNext();
                }
       $sql4 = "SELECT listingsdbelements_field_value from ".$config[table_prefix]. "listingsdbelements WHERE listingsdbelements_field_name = 'city' AND listingsdb_id = '$ID'";
                $recordSet4 = $conn->Execute($sql4);
                if ($recordSet4 === false) {
                    $misc->log_error($sql4);
                }
               while (!$recordSet4->EOF) {
                    $city = $misc->make_db_unsafe ($recordSet4->fields['listingsdbelements_field_value']);
        $city_clean = str_replace(" ","-",$city);
                    $recordSet4->MoveNext();
                }
       $sql5 = "SELECT listingsdbelements_field_value from ".$config[table_prefix]. "listingsdbelements WHERE listingsdbelements_field_name = 'zip' AND listingsdb_id = '$ID'";
                $recordSet5 = $conn->Execute($sql5);
                if ($recordSet5 === false) {
                    $misc->log_error($sql5);
                }
               while (!$recordSet5->EOF) {
                    $zip = $misc->make_db_unsafe ($recordSet5->fields['listingsdbelements_field_value']);
                    $recordSet5->MoveNext();
                }
       $sql6 = "SELECT listingsdbelements_field_value from ".$config[table_prefix]. "listingsdbelements WHERE listingsdbelements_field_name = 'neighborhood' AND listingsdb_id = '$ID'";
                $recordSet6 = $conn->Execute($sql6);
                if ($recordSet6 === false) {
                    $misc->log_error($sql6);
                }
               while (!$recordSet6->EOF) {
                    $neighborhood = $misc->make_db_unsafe ($recordSet6->fields['listingsdbelements_field_value']);
              $neighborhood_clean = str_replace(" ","-",$neighborhood);
        $neighborhood_clean = str_replace("&", "and", $neighborhood_clean);
        $neighborhood_clean = str_replace("/", "-", $neighborhood_clean);
                    $recordSet6->MoveNext();
                }
     
     
                // for SEF URL
                if ($var_reset == 0) {
                    $display .= "<tr>";
                }
                $display .= "<td>$img <a class=\"sitemap\" href=\"listing\\$property_type_clean\\$state\\$county\\$city_clean\\$zip\\$neighborhood_clean\\$Title.html\">$property_type_clean\\$state\\$county\\$city_clean\\$zip\\$neighborhood_clean\\$Title</a></td>";
                if ($var_reset == $col_max) {
                    $display .= "</tr>";
                    $var_reset = 1;
                } else {
                    $var_reset++;
                }
                $recordSet->MoveNext();
            }
    Lee Forbes PA

    REALTOR / Designer

    FloridaPropertyOnline.com

    GulfsideLand.com

  4. #4
    Join Date
    Jul 2005
    Location
    Sao Paulo, BRAZIL
    Posts
    3,763

    Default Re: SQL statement help?? listingsdbelements_field_value

    At your 6 queries ($sql1, $sql2,... $sql6) there is no need to have:
    Code:
    while (!$recordSet#->EOF) {
    ...
    $recordSet#->MoveNext();
    }
    Because you are searching for a unique value, not multiple.

    For Websites that have a huge number of listings I don't recommend to use the sitemap add-on as it is. For those Websites (in my opinion your is one of them) that add-on should write a file with all those links hardcoded - this way the page will read that file and will load A LOT FASTER - and no database queries are going to be executed, every time the page is called.
    Yeah... the add-on should be modified for that.

    Eduardo

    Open-Realty® DOCs
    We can easily forgive a child who is afraid of the dark; the real tragedy of life is when men are afraid of the light. (Author Unknown)

  5. #5
    the_sandking's Avatar
    the_sandking is offline hadron remnant - Moderation Fachmann
    Join Date
    Apr 2003
    Location
    Nullspace
    Posts
    5,289

    Default Re: SQL statement help?? listingsdbelements_field_value

    Or just make an add-on that reads and displays the sitemap.org compliant sitemap files OR can already can create for you.
    "Much of what looks like rudeness in hacker circles is not intended to give offense. Rather, it's the product of the direct, cut-through-the-BS communications style that is natural to people who are more concerned about solving problems than making others feel warm and fuzzy."

    "We gotta' go to the crappy town where I'm a hero!"
    -Hoban 'Wash' Washburne 2485-2519


    "When you’re born you get a ticket to the freak show. When you’re born in America, you get a front-row seat.."
    -George Carlin 1937-2008

    New to Open-Realty® and need help? Check the:
    -OR DOCUMENTATION -

    Important: Read this at least once in your lifetime
    How To Ask Questions The Smart Way

Similar Threads

  1. Add an IF statement
    By monk707 in forum General Help (CLOSED)
    Replies: 6
    Last Post: 03-20-2008, 08:59 AM
  2. Help with IF ELSE Statement
    By jcran in forum General Help (CLOSED)
    Replies: 12
    Last Post: 11-13-2007, 01:33 AM
  3. sql statement help
    By mschiltz in forum Addon help, ideas and discussion (CLOSED)
    Replies: 2
    Last Post: 09-17-2005, 03:09 PM
  4. Help with an SQL statement
    By digires in forum General Discussion
    Replies: 0
    Last Post: 01-29-2004, 06:37 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •