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.

 

[Hitch]

Didn't have time to read all your post. :red:

But here's a quick reply, in my brothers script, if i remember correctly, i named all the checkboxes the same, and just gave them different values, the table names...

Worked for that... xD

Or try using the OR in your query?

 

[Dan]

Didn't have time to read all your post. :red:

.. you should've..

The easiest code would be for when they enter a zip. The MySQL for it would be:
If the zip field is an integer: SELECT * FROM table WHERE zip = $zip
If it's not: SELECT * FROM table WHERE zip = '$zip'

City and state would be harder unless they enter the exact city. If that's what they do:
SELECT * FROM table WHERE state = '$state' && city = '$city'

 

[PoorDoggie]

you could use a fulltext query, but then again, you want exact matches.

you could do something like this:

SELECT * FROM table WHERE name LIKE '%$value%' AND state = '$state' AND city = '$city' AND zip = '$zip'

I am not sure if thats what you want or not.

 

[Dan]

Oops.. I forgot about name in my SQL query. What PoorDoggie said would work, but using LIKE and wildcards in MySQL isn't that efficient- it would be better to loop through and search using PHP.

 

[JsteRmX]

thanks for the help so far - I went to the library today and got some books out on this too.

So far I think I understand what is going on, but.....

What I havent figured out yet though, is HOW is the data entered by the user used to query the DB. Like how does it get passed from the form into the query?

As for how to do the search, if I just go by the zip code, then many people may not receive any results since one city can have many zip codes, what if the place they are looking for is not in their zip, but maybe it is the next zip code over? That is why I need to search by City && State or Zip (but if we search by zip, the search results should return surrounding zip codes as well. This feature to show surrounding areas would be desirable for the city too.)

Basically I am looking for how to make my search like that of bestbuy.com (or any retail store) store locator. How is this possible? Any ideas?


thanks everyone =)

 

[Dan]

Example form:

<form action="script.php" method="post">
   State: <input type="text" name="state" value="" /><br />
   <input type="submit" value="Submit" />
</form>

Example script.php:

<?php

// You'd have your MySQL connection stuff here.

$state = $_POST['state'];
$rows  = mysql_query("SELECT * FROM table WHERE state = '$state'");
while ($row = mysql_fetch_assoc($rows)) {
   echo $row['name'] . ' is in ' . $state . '<br />';
}

?>

This is unsafe code, but it is only supposed to serve as an example.

 

[JsteRmX]

unsafe? how do you mean?

thanks for the example - i just analyzed it for a minute, and it actually helped me really understand it. Not sure why it all just came to me now though, hmm.

 

[Dan]

It is unsafe because someone (a "hacker") could do MySQL injection through that code. It doesn't strip their input at all, so they could possibly enter harmful information.

http://en.wikipedia.org/wiki/SQL_injection

 

[PoorDoggie]

easy way to make sql querys safe is to use mysql_real_escape_string() in php.

just apply it to every variable as so:

...
$rows  = mysql_query("SELECT * FROM table WHERE state = '".mysql_real_escape_string($state)."'");
...

 

[jerometan]

Additionally, you can also use PDO (php.net/pdo).

Its a much more secure option, as it helps you escape automatically.

Do still validate input though.

 

[JsteRmX]

ive never heard of PDO, guess i will look into it.

thanks alot for all the help here, I think I am prepared to put it all together now.

Ill try to post back here my results.

Just one last question though, how do store locators know what other cities are close to the one you entered? Or how do some websites let you search for all stores located within a 5, 10, or 20 mile radius from the zip you enter?

 

[Dan]

http://www.sanisoft.com/ziploc/

It will tell you how far apart two zip codes are and can tell you zip codes within __ mi (or km) of another zip code.

 

[JsteRmX]

wow thanks alot - that looks great.

 

[JsteRmX]

ok, i finally got around to trying to code my searchscript.php file for this searching feature.

I am wondering how to narrow (filter) down the results though.

See, first we will search by the name, so I use this code
$name = $_POST['name'];
$query = mysql_query("SELECT * FROM businesses WHERE name = '".mysql_real_escape_string($name)."'");



now after this, it should then narrow down the results from everything with the 'name' to everything with the 'name' AND the ('city' AND the 'state') OR the 'zip'. But it needs to be city AND state OR just the zip .. that is why I put city and state in the parentheses before. how would i show this priority in the code?

my question is how can i achieve this? I tryed using the following code, but i am getting an error when i test this. I have posted the error below as well.

$name = $_POST['name']; 
$city = $_POST['city'];
$state = $_POST['state'];
$zip = $_POST['zip']
$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)."'" OR zip = '".mysql_real_escape_string($zip)."'");

while ($query = mysql_fetch_assoc($query)) { 
echo $query['name']
<p>
$query['city']
<p>
$query['$state']
<p>
$query['$zip'] . '<br />'; 
} 
?>

error:

Parse error: parse error, unexpected T_VARIABLE in searchscript.php on line 16

line 16 is the $query line.

Any ideas? thanks

 

[JsteRmX]

ok well i realized that line 16 wasnt written correctly, so i changed it to this:

$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)."' OR zip = '".mysql_real_escape_string($zip)."'");

though i am still getting the same error.

 

[PoorDoggie]

I can't see anything wrong with that, but I would have thought it would have been easier to do something like this, as your query is very confusing:

if(trim($zip)!=0){
  $query = mysql_query("SELECT * FROM businesses WHERE 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)."'");
}

 

[Dan]

Fixed up PoorDoggie's code a little. You definitely need to separate the zip and city/state queries.

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)."'");
}

 

[JsteRmX]

oh i see, yes what i wrote was rather confusing. they way you guys wrote is much clearer, but I am still getting some error.

Parse error: parse error, unexpected T_IF on line 92 which is: if (trim($zip) != 0) {

I really appreciate all your help on this, i have been playing around with the code all day and have not yet got it to work. Well here is the code i am using now, do you see anythign wrong?

$name = $_POST['name']; 
$city = $_POST['city'];
$state = $_POST['state'];
$zip = $_POST['zip_code']


if (trim($zip) != 0) {
	$query = mysql_query("SELECT * FROM businesses WHERE name = '".mysql_real_escape_string($name)."' AND zip_code='".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 ($query = mysql_fetch_assoc($query)) { 
echo $query['name']
<p>
$query['city']
<p>
$query['$state']
<p>
$query['$zip_code'] . '<br />'; 
}

 

[Dan]

You need a ; after $zip = $_POST['zip_code'] (line 4 of the code you posted)

 

[JsteRmX]

ah thanks, i missed that.

I have alot of other fields in the table other than the name, city, state, and zip that I would like to show on my search results page. Once the correct records are selected from the search, how would i go about displaying ALL of the information in EACH record that matches the search criteria? Right now when i do a search, i just get an empty page returned. Atleast there are no more errors! But I am apparently not echoing all the info right.

 

[Dan]

$name  = $_POST['name']; 
$city  = $_POST['city'];
$state = $_POST['state'];
$zip   = $_POST['zip_code'];

if (trim($zip) != 0) {
	$query = mysql_query("SELECT * FROM businesses WHERE name = '".mysql_real_escape_string($name)."' AND zip_code='".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_assoc($query)) { 
	echo $row['name'] . ' - ' . $row['city'] . ' - ' . $row['state'] . ' - ' . $row['zip_code'] . '<br />'; 
}

I don't really know how you want to echo it, so that will just put ' - ' between each thing.

I'm also surprised you weren't getting errors because of how you had those <p>'s.

 

[JsteRmX]

oh yeah i did get an error, so i took them out.

not sure what i am doing wrong, but my search results page is still coming up blank.

thanks for all your help though.

I will play around with it for a while and see what i come up with and report back when i make some progress... or if i make no progress after a long period of time, hah.

many thanks dan and poor doggie, i keep trying to give you more rep points, but it says i cant since i allready gave to you. ohwell... THANKS!

 

[PoorDoggie]

lol thanks

you need to output it now...

I don't know what "mysql_fetch_assoc" is, but I always use "mysql_fetch_array". I don't know if that will help you at all. It should do the same thing I think you are trying to do with the fetch_assoc thing. Oh well, I have never come accross the fetch_assoc thing.

Fixed up PoorDoggie's code a little. You definitely need to separate the zip and city/state queries.

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)."'");
}

yea yea... ok *sticks tongue out*

(lol... thanks - i did it quickly)

 

[JsteRmX]

ok i tried changing it to fetch array instead of assoc, but that didnt work either. I am lost. Any more help would be great. Here is my search script:

//mysql connection stuff

//set variables
$name = $_POST['name']; 
$city = $_POST['city'];
$state = $_POST['state'];
$zip = $_POST['zip_code'];

// open connection 
    $connection = mysql_connect($host, $user, $pass) or die ("Unable to connect!"); 
     
    // select database 
    mysql_select_db($db) or die ("Unable to select database!"); 

if (trim($zip) != 0) { 
    $query = mysql_query("SELECT * FROM businesses WHERE name = '".mysql_real_escape_string($name)."' AND zip_code='".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)) {  
    echo $row['name'] . $row['city'] . $row['state'] . $row['zip_code'] . '<br />';  
}  

?>

What am i doing wrong? This just returns an empty page no results.
Thanks.