PHP code is not searching the correct results.

usman07 · April 14, 2012

i basically have a php search form on a property website, and depending on what area/type of property/number of bedrooms and price range the search form should bring up results based on that. at the moment it just bring up everything. how would i make it bring up the correct results depending on the search made?

here my php code:


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">

<head>
<title>Mumtaz Properties</title>
	<link rel="stylesheet" href="cutouts/style.css"/>
</head>

<body>
<!--Main Div Tag-->
	<div id="wrapper">

		<div id="header">
			<div id="logo"><a href="index.html"><img src="cutouts/Homepage/logo.png" alt=""/></a></div>
		</div>

<div id="navigation">
<a id="Home" href="index.html" title="home"><span>home</span></a>
<a id="Sale" href="forsale.html" title="for sale"><span>for sale</span></a>
<a id="Rent" href="forrent.html" title="for rent"><span>for rent</span></a>
<a id="Contact" href="contact.html" title="contact us"><span>contact us</span></a>
</div>

<div id="main">
<div id="results"><img src="cutouts/Homepage/results.png"></div>
<?php

$server = "";      // Enter your MYSQL server name/address between quotes
$username = "";    // Your MYSQL username between quotes
$password = "";    // Your MYSQL password between quotes
$database = "";    // Your MYSQL database between quotes

$con = mysql_connect($server, $username, $password);       // Connect to the database
if(!$con) { die('Could not connect: ' . mysql_error()); }  // If connection failed, stop and display error
mysql_select_db($database, $con);  // Select database to use
// Query database
$result = mysql_query("SELECT * FROM Properties");

if (!$result)
{
    echo "Error running query:<br>";
    trigger_error(mysql_error());
}
elseif(!mysql_num_rows($result))
{
    // no records found by query.
    echo "No records found";
}
else
{
    $i = 0;
    echo '<div style="font-family:helvetica; font-size:14px; padding-left:15px; padding-top:20px;">';
    while($row = mysql_fetch_array($result)) {     // Loop through results
        $i++;
         echo '<img class="image1" src="'. $row['images'] .'" />';   //image
        echo "<span style=\"color:#63be21;\">Displaying record $i<br>\n<br></span>";
        echo "<b>Location:</b> ".  $row['Location'] . "<br>\n";            // Where 'location' is the column/field title in the database
        echo "<b>Property Type:</b> ".  $row['Property_type'] . "<br>\n";       // as above
        echo "<b>Bedrooms:</b> ".  $row['Number_of_bedrooms'] . "<br>\n";  // ..
        echo "<b>Purchase Type:</b> ".  $row['Purchase_type'] . "<br>\n";       // ..
        echo "<b>Price:</b> ".  $row['Price_range'] . "<br>\n";         // ..
    }
echo '</div>';
}

mysql_close($con);  // Close the connection to the database after results, not before.
?>
</div>

</div>

</body>
</html>

Would like to thank you in advance.

QuickOldCar · April 14, 2012

There is no search whatsoever in the code you provided.

If you have nothing, I suggest looking at some tutorials on how to make a search using LIKE or even mysql fulltext

http://www.phpfreaks.com/tutorial/simple-sql-search

http://www.mysqltutorial.org/sql-like-mysql.aspx

usman07 · April 16, 2012

So im trying to get a search form working, so depending on the search the correct result will appear from the MYSQL database.

This is my php code at the moment:

<?php

$server = "";      // Enter your MYSQL server name/address between quotes
$username = "";    // Your MYSQL username between quotes
$password = "";    // Your MYSQL password between quotes
$database = "";    // Your MYSQL database between quotes

$con = mysql_connect($server, $username, $password);       // Connect to the database
if(!$con) { die('Could not connect: ' . mysql_error()); }  // If connection failed, stop and display error
mysql_select_db($database, $con);  // Select database to use
// Query database
$result = mysql_query("SELECT * FROM Properties");

if (!$result)
{
    echo "Error running query:<br>";
    trigger_error(mysql_error());
}
elseif(!mysql_num_rows($result))
{
    // no records found by query.
    echo "No records found";
}
else
{
    $i = 0;
    echo '<div class="container" style="float:left;">';
  while($row = mysql_fetch_array($result)) {     // Loop through results
        $i++;
echo '<div class="imageholder" style="float:left;">';
        echo '<img class="image1" src="'. $row['images'] .'" />';   //image
echo '</div>';		
echo '<div class="textholder" style="font-family:helvetica; font-size:13px; float:left; padding-top:10px;">';
        echo "<span style=\"color:green;\"><b>Displaying record $i<br>\n</b><br></span>";
        echo "<b>" . $row['id'] . "</b><br>\n";      // Where 'id' is the column/field title in the database
        echo "Location: ". $row['Location'] . "<br>\n";            // Where 'location' is the column/field title in the database
        echo "Property Type: ". $row['Property_type'] . "<br>\n";       // as above
        echo "Bedrooms: ". $row['Number_of_bedrooms'] . "<br>\n";  // ..
        echo "Purchase Type: ". $row['Purchase_type'] . "<br>\n";       // ..
        echo "Price: ". $row['Price_range'] . "<br>\n";         // ..
echo '</div>';
echo '<div style="clear:both"></div>';				
    }
echo '</div>';	}

mysql_close($con);  // Close the connection to the database after results, not before.
?>

This doesn't actually make no search as I have been told there is no search in the code.

Iv then looked at a tutorial and do I add this code in my php code above, will this make the search happen?

here the code:


<?php
// Set up our error check and result check array
$error = array();
$results = array();

// First check if a form was submitted. 
// Since this is a search we will use $_GET
if (isset($_GET['search'])) {
   $searchTerms = trim($_GET['search']);
   $searchTerms = strip_tags($searchTerms); // remove any html/javascript.
   
   if (strlen($searchTerms) < 3) {
      $error[] = "Search terms must be longer than 3 characters.";
   }else {
      $searchTermDB = mysql_real_escape_string($searchTerms); // prevent sql injection.
   }
   
   // If there are no errors, lets get the search going.
   if (count($error) < 1) {
      $searchSQL = "SELECT sid, sbody, stitle, sdescription FROM simple_search WHERE ";
      
      // grab the search types.
      $types = array();
      $types[] = isset($_GET['body'])?"`sbody` LIKE '%{$searchTermDB}%'":'';
      $types[] = isset($_GET['title'])?"`stitle` LIKE '%{$searchTermDB}%'":'';
      $types[] = isset($_GET['desc'])?"`sdescription` LIKE '%{$searchTermDB}%'":'';
      
      $types = array_filter($types, "removeEmpty"); // removes any item that was empty (not checked)
      
      if (count($types) < 1)
         $types[] = "`sbody` LIKE '%{$searchTermDB}%'"; // use the body as a default search if none are checked
      
          $andOr = isset($_GET['matchall'])?'AND':'OR';
      $searchSQL .= implode(" {$andOr} ", $types) . " ORDER BY `stitle`"; // order by title.

      $searchResult = mysql_query($searchSQL) or trigger_error("There was an error.<br/>" . mysql_error() . "<br />SQL Was: {$searchSQL}");
      
      if (mysql_num_rows($searchResult) < 1) {
         $error[] = "The search term provided {$searchTerms} yielded no results.";
      }else {
         $results = array(); // the result array
         $i = 1;
         while ($row = mysql_fetch_assoc($searchResult)) {
            $results[] = "{$i}: {$row['stitle']}<br />{$row['sdescription']}<br />{$row['sbody']}<br /><br />";
            $i++;
         }
      }
   }
}

QuickOldCar · April 16, 2012

It does look like something that can now search and get results, did you try running this?

be sure the use of mysql_real_escape_string comes after the mysql connection

Your result is now an array, you should just add the actual values into the array and no html, add the html to each value on displaying it.

Wondering why are making this an array when you can echo the values right there in the while loop

This:

$i = 1;
         while ($row = mysql_fetch_assoc($searchResult)) {
            $results[] = "{$i}: {$row['stitle']}<br />{$row['sdescription']}<br />{$row['sbody']}<br /><br />";
            $i++;
         }

Could easily become this:

This:

         while ($row = mysql_fetch_assoc($searchResult)) {
            echo $row['stitle']."<br />".$row['sdescription']."<br />".$row['sbody']."<br />";
         }

QuickOldCar · April 16, 2012

I would also make this:

if (isset($_GET['search'])) {

To this:

if (isset($_GET['search']) && $_GET['search'] !='') {

is no reason to execute the search query if search terms are empty

usman07 · April 17, 2012

Hi thanx for ur reply, yeah Iv ran it but no error messages come up but the search doesn't work correctly as it doesn't bring up the right results.

Heres the website: http://www.mumtazproperties.hostei.com/

Heres the PHP code:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">

<head>
<title>Mumtaz Properties</title>
	<link rel="stylesheet" href="cutouts/style.css"/>
</head>

<body>
<!--Main Div Tag-->
	<div id="wrapper">

		<div id="header">
			<div id="logo"><a href="index.html"><img src="cutouts/Homepage/logo.png" alt=""/></a></div>
		</div>

<div id="navigation">
<a id="Home" href="index.html" title="home"><span>home</span></a>
<a id="Sale" href="forsale.html" title="for sale"><span>for sale</span></a>
<a id="Rent" href="forrent.html" title="for rent"><span>for rent</span></a>
<a id="Contact" href="contact.html" title="contact us"><span>contact us</span></a>
</div>

<div id="main">
<div id="results"><img src="cutouts/Homepage/results.png"></div>
<?php
if ($debug) { // a variable I set at script top for debugging ;-)
   echo "<!-- $sql -->";
} 
$server = "";      // Enter your MYSQL server name/address between quotes
$username = "";    // Your MYSQL username between quotes
$password = "";    // Your MYSQL password between quotes
$database = "";    // Your MYSQL database between quotes

$con = mysql_connect($server, $username, $password);       // Connect to the database
if(!$con) { die('Could not connect: ' . mysql_error()); }  // If connection failed, stop and display error
mysql_select_db($database, $con);  // Select database to use
// Query database
$result = mysql_query("SELECT * FROM Properties");

// Set up our error check and result check array
$error = array();
$results = array();

// First check if a form was submitted. 
// Since this is a search we will use $_GET
if (isset($_GET['search'])) {
   $searchTerms = trim($_GET['search']);
   $searchTerms = strip_tags($searchTerms); // remove any html/javascript.
   
   if (strlen($searchTerms) < 10) {
      $error[] = "Search terms must be longer than 10 characters.";
   }else {
      $searchTermDB = mysql_real_escape_string($searchTerms); // prevent sql injection.
   }
   
   // If there are no errors, lets get the search going.
   if (count($error) < 1) {
      $searchSQL = "SELECT simages, sLocation, sNumberofbedrooms, sdescription FROM Properties WHERE ";
      
      // grab the search types.
      $types = array();
      $types[] = isset($_GET['images'])?"`simages` LIKE '%{$searchTermDB}%'":'';
      $types[] = isset($_GET['Location'])?"`sLocation` LIKE '%{$searchTermDB}%'":'';
      $types[] = isset($_GET['Number of bedrooms'])?"`snumberofbedrooms` LIKE '%{$searchTermDB}%'":'';
      
      $types = array_filter($types, "removeEmpty"); // removes any item that was empty (not checked)
      
      if (count($types) < 1)
         $types[] = "`simages` LIKE '%{$searchTermDB}%'"; // use the body as a default search if none are checked
      
          $andOr = isset($_GET['matchall'])?'AND':'OR';
      $searchSQL .= implode(" {$andOr} ", $types) . " ORDER BY `sLocation`"; // order by title.

      $searchResult = mysql_query($searchSQL) or trigger_error("There was an error.<br/>" . mysql_error() . "<br />SQL Was: {$searchSQL}");
      
      if (mysql_num_rows($searchResult) < 1) {
         $error[] = "The search term provided {$searchTerms} yielded no results.";
      }else {
         $results = array(); // the result array
         $i = 1;
         while ($row = mysql_fetch_assoc($searchResult)) {
            $results[] = "{$i}: {$row['sLocation']}<br />{$row['sNumberofbedrooms']}<br />{$row['sbody']}<br /><br />";
            $i++;
         }
      }
   }
}

function removeEmpty($var) {
   return (!empty($var)); 
}

if (!$result)
{
    echo "Error running query:<br>";
    trigger_error(mysql_error());
}
elseif(!mysql_num_rows($result))
{
    // no records found by query.
    echo "No records found";
}
else
{
    $i = 0;
    echo '<div class="container" style="float:left;">';
  while($row = mysql_fetch_array($result)) {     // Loop through results
        $i++;
echo '<div class="imageholder" style="float:left;">';
        echo '<img class="image1" src="'. $row['images'] .'" />';   //image
echo '</div>';		
echo '<div class="textholder" style="font-family:helvetica; font-size:13px; float:left; padding-top:10px;">';
        echo "<span style=\"color:green;\"><b>Displaying record $i<br>\n</b><br></span>";
        echo "<b>" . $row['id'] . "</b><br>\n";      // Where 'id' is the column/field title in the database
        echo "Location: ". $row['Location'] . "<br>\n";            // Where 'location' is the column/field title in the database
        echo "Property Type: ". $row['Property_type'] . "<br>\n";       // as above
        echo "Bedrooms: ". $row['Number_of_bedrooms'] . "<br>\n";  // ..
        echo "Purchase Type: ". $row['Purchase_type'] . "<br>\n";       // ..
        echo "Price: ". $row['Price_range'] . "<br>\n";         // ..
echo '</div>';
echo '<div style="clear:both"></div>';				
    }
echo '</div>';	}

mysql_close($con);  // Close the connection to the database after results, not before.
?>
</div>

</div>

</body>
</html>

QuickOldCar · April 17, 2012

If you can display your form for the search that would be great.

But going to guess that you are using $_POST for the form, and your script is looking for $_GET

http://www.tizag.com/phpT/postget.php

usman07 · April 17, 2012

Yeah u were right the method was post but I changed it to 'get', but still remains the same really.

HTML Form Code:


<form action="insert.php" method="get">
<table id="tb1">
<tr>
<td><p class="LOC">Location:</p></td>
<td><div id="LC">

<select multiple="multiple" size="5" style="width: 150px;" >
<option>Armley</option>
<option>Chapel Allerton</option>
<option>Harehills</option>
<option>Headingley</option>
<option>Hyde Park</option>
<option>Moortown</option>
<option>Roundhay</option>
</select>
</div>
</td><td><p class="PT">Property type:</p></td>
<td><div id="PS">

	<select name="property type" style="width: 170px;">
	<option value="none" selected="selected">Any</option>
	<option value="Houses">Houses</option>
	<option value="Flats / Apartments">Flats / Apartments</option>
	</select>
</div>
</td><td>
<div id="ptype">

<input type="radio" class="styled" name="ptype" value="forsale"/> For Sale 

<p class="increase">
<input type="radio" class="styled" name="ptype" value="forrent"/> To Rent
</p>
<p class="increase">
<input type="radio" class="styled" name="ptype" value="any"/> Any
</p>

</div>
</td>
</tr>

	</table>

	<div id="table2">
	<table id="NBtable">
	<tr>
	<td><p class="NBS">Number of bedrooms:</p></td>
	<td><div id="NB">

	<select name="number of bedrooms">
	<option value="none" selected="selected">No Min</option>
	<option value="1">1</option>
	<option value="2">2</option>
	<option value="3">3</option>
	<option value="4">4</option>
	<option value="5">5</option>
	</select> to

	<select name="number of bedrooms">
	<option value="none" selected="selected">No Max</option>
	<option value="1">1</option>
	<option value="2">2</option>
	<option value="3">3</option>
	<option value="4">4</option>
	<option value="5">5</option>
	</select>	
	</div>
</td>

	<td><p class="PR">Price range:</p></td>
	<td><div id="PR">

	<select name="price range">
	<option value="none" selected="selected">No Min</option>
	<option value="50,000">50,000</option>
	<option value="60,000">60,000</option>
	<option value="70,000">70,000</option>
	<option value="80,000">80,000</option>
	<option value="90,000">90,000</option>
	<option value="100,000">100,000</option>
	<option value="110,000">110,000</option>
	<option value="120,000">120,000</option>
	<option value="130,000">130,000</option>
	<option value="140,000">140,000</option>
	<option value="150,000">150,000</option>
	<option value="160,000">160,000</option>
	<option value="170,000">170,000</option>
	<option value="180,000">180,000</option>
	<option value="190,000">190,000</option>
	<option value="200,000">200,000</option>
	<option value="210,000">210,000</option>
	<option value="220,000">220,000</option>
	<option value="230,000">230,000</option>
	<option value="240,000">240,000</option>
	<option value="250,000">250,000</option>
	<option value="260,000">260,000</option>
	<option value="270,000">270,000</option>
	<option value="280,000">280,000</option>
	<option value="290,000">290,000</option>
	<option value="300,000">300,000</option>
	<option value="310,000">310,000</option>
	<option value="320,000">320,000</option>
	<option value="330,000">330,000</option>
	<option value="340,000">340,000</option>
	<option value="350,000">350,000</option>
	</select> to

	<select name="price range">
	<option value="none" selected="selected">No Max</option>
	<option value="50,000">50,000</option>
	<option value="60,000">60,000</option>
	<option value="70,000">70,000</option>
	<option value="80,000">80,000</option>
	<option value="90,000">90,000</option>
	<option value="100,000">100,000</option>
	<option value="110,000">110,000</option>
	<option value="120,000">120,000</option>
	<option value="130,000">130,000</option>
	<option value="140,000">140,000</option>
	<option value="150,000">150,000</option>
	<option value="160,000">160,000</option>
	<option value="170,000">170,000</option>
	<option value="180,000">180,000</option>
	<option value="190,000">190,000</option>
	<option value="200,000">200,000</option>
	<option value="210,000">210,000</option>
	<option value="220,000">220,000</option>
	<option value="230,000">230,000</option>
	<option value="240,000">240,000</option>
	<option value="250,000">250,000</option>
	<option value="260,000">260,000</option>
	<option value="270,000">270,000</option>
	<option value="280,000">280,000</option>
	<option value="290,000">290,000</option>
	<option value="300,000">300,000</option>
	<option value="310,000">310,000</option>
	<option value="320,000">320,000</option>
	<option value="330,000">330,000</option>
	<option value="340,000">340,000</option>
	<option value="350,000">350,000</option>

	</select>

	</div>
</td>
</tr>

	</table>

	<div id="submit1"><input type="submit" value="submit" /></div>
     
     
	</div>
	</form>

batwimp · April 17, 2012

So what does your $searchSQL variable contain once it's all loaded up (echo it to the screen and post it here), and how does this compare to your GET variable? (i.e. you do a var_dump($_GET) and post it here as well);

QuickOldCar · April 17, 2012

You are using $_POST for the form and $_GET for the script, I looked at your source.

I typed some queries in the address bar for that script and got this.

Notice: There was an error.

Unknown column 'simages' in 'field list'

SQL Was: SELECT simages, sLocation, sNumberofbedrooms, sdescription FROM Properties WHERE `simages` LIKE '%Chapel Allerton%' ORDER BY `sLocation` in /home/a2221438/public_html/insert.php on line 76

Some items I noticed from your form to your script:

I see no type of a post or get value coming from your form that you use to check $_GET['search']

In the form your location dropdown is not named location, name="location"

Change your form to get, make sure everything in your form has a name and value, I never space my name values for get requests, usually something like Property Location I would make property_location.

Be sure those columns and fields are in your database are named the same in the script

batwimp · April 17, 2012

I couldn't get to the page at all.

Sign In

PHP code is not searching the correct results.

Recommended Posts

usman07

Link to comment

Share on other sites

QuickOldCar

Link to comment

Share on other sites

usman07

Link to comment

Share on other sites

QuickOldCar

Link to comment

Share on other sites

QuickOldCar

Link to comment

Share on other sites

usman07

Link to comment

Share on other sites

QuickOldCar

Link to comment

Share on other sites

usman07

Link to comment

Share on other sites

batwimp

Link to comment

Share on other sites

QuickOldCar

Link to comment

Share on other sites

batwimp

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information