Searching with variables from multiple forms to return accurate results (Php & Mysql)

[JsteRmX]

Ok, I am in the process of developing a new site. The mysql database will contain thousands of records. Each record will contain a name, city, state, zip, phone number, ect.

Now, on the site, I want to have a search feature - where the user will enter the name and either the city & state, or just the zip code.

Obviously, once they begin their search, a page should be returned with all the same or similar names along with that names' address and all other info.

So we will be searching by name, city, state, and zip.

I am thinking i would need to use SELECT to search, but I have no idea really.

So my question is - How do I make this search feature on my site? How do I take the data the user entered in the search form, and use that to search the database and return accurate results? Like if someone enters 'dog' as the name ( i am just making stuff up here for examples sake) how do I make it return ONLY dog versus making the search return 'dogs' 'doggy' 'dog store' etc. And how do I make it return only the results for dog that are in the same city and state or zip? Also, how do retail store websites return locations that are close to the city you entered? Its like it knows how close or far apart the cities are! ... I need all this functionality on my site.

Any insight would be greatly appreciated!

Since my post was kinda long - basically I need to have the search functionality that like, bestbuy.com would have for their store locator.

Help please! or maybe you can direct me to some good tutorials. I know some php and mysql, so I should be able to figure this out, i just need some direction!

THANKS!


edit - oh yeah, i am also wondering if there is something i should know to really optimize my database for this type of searching. I just use phpmyadmin to administer and create my database.

 

[JsteRmX]

ugh, this is driving me crazy.

well i tried a different code, but it still returns an empty page. any ideas? pleeeeasseeee.

here is the new code I tried that doesnt work either.

//mysql connection stuff

if (trim($zip) != 0) { 
$query = "SELECT * FROM businesses WHERE name = '".mysql_real_escape_string($name)."' AND zip_code='".mysql_real_escape_string($zip)."'"; 
} else { 
    $query = "SELECT * FROM businesses WHERE name = '".mysql_real_escape_string($name)."' AND city = '".mysql_real_escape_string($city)."' AND state = '".mysql_real_escape_string($state)."'"; 
} 
$result = mysql_db_query("businesses", $query); 

if ($result) 
{ 
echo "Your search returned the following results:<br><br>"; 
echo "<table width=90% align=center border=1><tr> 
<td align=center bgcolor=#003300>Store</td> 
<td align=center bgcolor=#003300>City</td> 
<td align=center bgcolor=#003300>State</td> 
<td align=center bgcolor=#003300>Zip Code</td> 
</tr>"; 

while ($row = mysql_fetch_array($result)) { // Begin while 
$name = $row["name"]; 
$city = $row["city"];
$state = $row["state"];
$zip = $row["zip_code"];

echo "<tr> 
<td>$name</td> 
<td>$city</td> 
<td>$state</td> 
<td>$zip</td></tr> "; 
} // end while 
}
?>

i dont understand this stuff!

 

[Dan]

Try echoing something like how many rows it found. It seems like it has to be returning no rows.

 

[JsteRmX]

ok so it has been awhile since i updated with my progress, and that is because I havent made much until today. Well I got farther! but still am having some trouble.

When someone does a search now on my site, the searchresults page will return some of the correct stuff.

It will say the following fine....

Search Results for:
Name
City, State Zip

But then I have an error.

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in searchresults.php on line 102

Now this error only shows when someone includes the ZIP CODE in their search. If you do not use the zip code, only the "search results for" will show up with no results and no error.

Well here is the code I am using now:

<?php
$name = $_POST['name']; 
$city = $_POST['city'];
$state = $_POST['state'];
$zip = $_POST['zip'];
?>

<p class="style20">Search Results for:</p>
      <p class="style16"><? echo "$name"?></p>
	  <p class="style16"><? echo "$city"," $state $zip"?></p>

<?
if (trim($zip) != 0) { 
    $query = mysql_query("SELECT * FROM businesses WHERE name = '".mysql_real_escape_string($name)."' AND zip='".mysql_real_escape_string($zip)."'"); 
} else { 
    $query = mysql_query("SELECT * FROM businesses WHERE name = '".mysql_real_escape_string($name)."' AND city = '".mysql_real_escape_string($city)."' AND state = '".mysql_real_escape_string($state)."'"); 
} ?>
<? while ($row = mysql_fetch_array($query)) {  
    ?><p class="style16"><? echo $row['name']?></p>
	<p class="style16"><? echo $row['city'], $row['state'], $row['zip']</p>;  
}
?>

Line 102 is
<? while ($row = mysql_fetch_array($query)) {


Any help would be great. Thanks.

 

[Barrucadu]

<? while ($row = mysql_fetch_array($query)) {

Should be:

<? foreach (mysql_fetch_array($query) as $row) {

 

[JsteRmX]

thanks, though i am still getting errors with the change you suggested.

when i use a city, state and zip to search i get these errors:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in searchresults.php on line 102

Warning: Invalid argument supplied for foreach() in searchresults.php on line 102


searching without the zip will return just the first error

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in searchresults.php on line 102


Any other ideas?

 

[Barrucadu]

thanks, though i am still getting errors with the change you suggested.

when i use a city, state and zip to search i get these errors:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in searchresults.php on line 102

Warning: Invalid argument supplied for foreach() in searchresults.php on line 102


searching without the zip will return just the first error

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in searchresults.php on line 102


Any other ideas?

That means that your queries arn't getting executed, try sticking a

or die(mysql_error())

after your queries, eg:

$query = mysql_query("SELECT * FROM businesses WHERE name = '".mysql_real_escape_string($name)."' AND zip='".mysql_real_escape_string($zip)."'")or die(mysql_error());