Page 1 of 1

Help with a multi-level search function...

Posted: Wed Apr 20, 2011 8:06 am
by dshiplett
I am creating a search function for a website that does the following: a visitor comes to the site and can select from multiple search choices for a company's services (ie: acrylics, pedicures, massage). They also put in their city, state or zip code, a search radius (so many miles from their location) and submit the search. It goes to a page that first creates an array out of the selected services. Then it queries the database table for the location and, from within that loop, splits the array and searches for companies who's services match each service selected (this is the only way I could get the search to work, though I'm sure there's a simpler, cleaner solution out there!).

Here's a small example: a visitor comes to the site. He/she chooses acrylics, pedicures, and massage for services and chooses his/her location of Los Angeles, California, with a search radius of 10 miles. He/she clicks the submit button and it takes him/her to a page that (in the background) first finds all companies listed in the database that are within 10 miles of LA. Then it breaks up the array of services and searches (within the list of companies within 10 miles of LA) that match first the service "Acrylics", then the service "Pedicures", and finally the service "Massage". The page (currently) prints these results.

The problem: Since it loops through the table for each service, companies are duplicated within the results. I need to find a way to look for those companies that match ALL the selected services. Those that match are the ones I want to actually show on the page (obviously, since the ones that don't match all the services are not what the visitor is looking for).

Here is code for the search function (in case it is needed):

Code: Select all

$sql1 = mysql_query("select * from zipcodes where city='$search_city' && state='$search_state'") or die (mysql_error());
while ($row1 = mysql_fetch_array($sql1))
{
$latitude1 = $row1["latitude"];
$longitude1 = $row1["longitude"];
$zipcode1 = $row1["zipcode"];
$city1 = $row1["city"];
$state1 = $row1["state"];
//print ('<br/>'.$zipcode1.' in '.$city1.', '.$state1.'<br/>');
}

// ITITIAL POINT
$coords = array('latitude' => $latitude1, 'longitude' => $longitude1);

//RADIUS
$radius = $miles;

// SQL FOR KILOMETERS
//$sql = "SELECT zipcode, ( 6371 * acos( cos( radians( {$coords['latitude']} ) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians( {$coords['longitude']} ) ) + sin( radians( {$coords['latitude']} ) ) * sin( radians( latitude ) ) ) ) AS distance FROM zipcodes HAVING distance <= {$radius} ORDER BY distance";

// SQL FOR MILES
$sql2 = "SELECT zipcode, ( 3959 * acos( cos( radians( {$coords['latitude']} ) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians( {$coords['longitude']} ) ) + sin( radians( {$coords['latitude']} ) ) * sin( radians( latitude ) ) ) ) AS distance FROM zipcodes HAVING distance <= {$radius} ORDER BY distance";

// OUTPUT THE ZIPCODES AND DISTANCES     
$query = mysql_query($sql2);
while($row2 = mysql_fetch_assoc($query)){
$zipcode2 = $row2["zipcode"];
$distance2 = $row2["distance"];
$distance2 = round($distance2, 1);

//search for salons
$sql = mysql_query("select * from users where salon<>'' && zip='$zipcode2' order by salon") or die (mysql_error());
$salonrows = mysql_num_rows($sql);
if ($salonrows>0) {
while ($row = mysql_fetch_array($sql)) {
$salonid = $row["idnum"];
$name = $row["name"];
$phone1 = $row["phone1"];
$email1 = $row["email1"];
$salon = $row["salon"];
$phone2 = $row["phone2"];
$fax = $row["fax"];
$email2 = $row["email2"];
$address = $row["address"];
$city = $row["city"];
$state = $row["state"];
$zip = $row["zip"];
$url = $row["url"];
$about = $row["about"];
$about = stripslashes($about);
$about = nl2br($about);
$services = $row["services"];
$image = $row["image"];
$img = "images/salons/".$image;
$thumb = "images/salons/thumbs/".$image;
$gal = "images/salons/gallery/".$image;

if ($search_services<>"") { 
//services search

if (is_array($search_services)){ 
$service_list = implode("|", $search_services);
} else {
$service_list = $search_services;
}

$service_list = explode("|", $service_list);
for($x=0;$x<count($service_list);$x++)
{

print ('<tr><td>Services: ');
print ($service_list[$x]);
print ('</td></tr>');

$sql = mysql_query("select * from users where zip='$zip' && services like '%$service_list[$x]%' order by salon") or die (mysql_error());

while ($row = mysql_fetch_array($sql)) {
$salonid = $row["idnum"];
$name = $row["name"];
$phone1 = $row["phone1"];
$email1 = $row["email1"];
$salon = $row["salon"];
$phone2 = $row["phone2"];
$fax = $row["fax"];
$email2 = $row["email2"];
$address = $row["address"];
$city = $row["city"];
$state = $row["state"];
$zip = $row["zip"];
$url = $row["url"];
$about = $row["about"];
$about = stripslashes($about);
$about = nl2br($about);
$services = $row["services"];
$image = $row["image"];
$img = "images/salons/".$image;
$thumb = "images/salons/thumbs/".$image;
$gal = "images/salons/gallery/".$image;

print ('<tr>');
print ('<td width="100" style="border-bottom:1px solid #ccc;">');
if ($image<>"") { 
if (file_exists($gal)) {
	list ($w, $h, $type, $attr) = getimagesize($gal);
	print ('<a href="view_salon.php?id='.$salonid.'"><img src="'.$gal.'" width="'.$w.'" height="'.$h.'" alt="" border="0" style="border:1px solid #000;float:left;margin-right:20px;"/></a>'); 
	}
} //image
print ('</td>');
print ('<td style="border-bottom:1px solid #ccc;">');
print ('<p style="float:right;border-bottom:1px dotted #ccc;"><a href="view_salon.php?id='.$salonid.'">View Salon</a></p>');
print ('<p style="border-bottom:1px dotted #ccc;"><strong>'.$salon.'</strong></p>');
print ('<p style="color:#999;text-align:right;font-size:14px;">'.$address.'<br/>'.$city.', '.$state.' '.$zip.'</p>');
print ('<p style="color:#93C;text-align:right;font-size:12px;">(Distance: '.$distance2.' miles)</p>');
print ('</td>');
print ('</tr>');
print ('<tr><td colspan=2 height="5"></td></tr>');
}

} //end explode
}

if ($search_keywords=="" && $search_services=="") {

print ('<tr>');
print ('<td width="100" style="border-bottom:1px solid #ccc;">');
if ($image<>"") { 
if (file_exists($gal)) {
	list ($w, $h, $type, $attr) = getimagesize($gal);
	print ('<a href="view_salon.php?id='.$salonid.'"><img src="'.$gal.'" width="'.$w.'" height="'.$h.'" alt="" border="0" style="border:1px solid #000;float:left;margin-right:20px;"/></a>'); 
	}
} //image
print ('</td>');
print ('<td style="border-bottom:1px solid #ccc;">');
print ('<p style="float:right;border-bottom:1px dotted #ccc;"><a href="view_salon.php?id='.$salonid.'">View Salon</a></p>');
print ('<p style="border-bottom:1px dotted #ccc;"><strong>'.$salon.'</strong></p>');
print ('<p style="color:#999;text-align:right;font-size:14px;">'.$address.'<br/>'.$city.', '.$state.' '.$zip.'</p>');
print ('<p style="color:#93C;text-align:right;font-size:12px;">(Distance: '.$distance2.' miles)</p>');
print ('</td>');
print ('</tr>');
print ('<tr><td colspan=2 height="5"></td></tr>');

} //end keywords vs. services

}

} else { 
$nosalons = "<p>No salons were found. <a href=\"index.php\">Please try again!</a></p>";
} //end salonrows

} //end $query

?>
Now, I thought that maybe I could break up the mysql_query function (I don't know the technical term, but when you do, say, .= on each line so you can add extra code within it? I've seen it done but can't remember how to do it or what it's called :( ). If I did that, I could maybe loop through the services array WITHIN the query and it would end up printing something like (as an example, and using the three services from above) " where zip='90210' && services like '%acrylic%' || zip='90210' && services like '%pedicures%' || zip='90210' && services like '%massage%' order by .... " That way instead of looping through the table multiple times, it would only loop once?

I'm sorry if that makes no sense whatsoever! I'm self-taught in PHP code so I'm not up on the correct language sometimes :)

I appreciate any help y'all can give. I'm tearing my hair out at this point!!! :banghead:

Re: Help with a multi-level search function...

Posted: Wed Apr 20, 2011 5:25 pm
by incubi
Hi,

Form what I can see you could do much of what you want in SQL using joins and views and save a lot precessing time and php code.
If you're not already try to use Mysql it will be less painful. :)

Lee