Jump to content


Photo

Multiple search variables


  • Please log in to reply
1 reply to this topic

#1 gamefreak13

gamefreak13
  • Members
  • PipPipPip
  • Advanced Member
  • 96 posts

Posted 04 April 2006 - 11:32 AM

I'm working on a php script that is a directory of stores. People can search by name, city, state, country, and type. The problem is the SQL query.

Lets say someone wants to search using the state and country fields, but leave the others blank. I simply use:

SELECT * FROM stores WHERE state LIKE '%$state%' AND LIKE '%$country%'

And thats easy. But what if they DONT want to search with the country? Maybe they want all grocery stores in the world.

My problem is, that I need my query to be constructed according to what fields are not empty. Here is what I currently have. When I choose more than 1 search type, it only listens to one of them. If I say I want a California grocery store, it only listens that I am looking for grocery stores and returns grocery stores in Ohio and such. The state search is not broken, because I can only search using the state, and it return only California stores. In other words, its one or the other. I need to combine and mix-match them.

if($city) {
    $getstore = mysql_query("SELECT * FROM stores WHERE city LIKE '%$city%'", $db);
}
if($state) {
    $getstore = mysql_query("SELECT * FROM stores WHERE state LIKE '%$state%'", $db);
}
if($type) {
    $getstore = mysql_query("SELECT * FROM stores WHERE type LIKE '%$type%'", $db);
}

So I need to somehow make something like "if not empty, write AND WHERE variable LIKE '%$variable%'" for each additional (But not the first, as it doesn't start with "AND") field the fill out.

Hope I made sense. Anyone know what I'm overlooking/not understanding?

#2 Kris

Kris
  • Staff Alumni
  • Advanced Member
  • 2,755 posts
  • LocationThe Internet

Posted 04 April 2006 - 01:27 PM

Maybe try something like this?

<?php
$query = "SELECT * FROM stores WHERE ";
if(isset($city)) {
    $query .= "city LIKE '%$city%' AND ";
}
if(isset($state)) {
    $query .= "state LIKE '%$state%' AND ";
}
if(isset($type)) {
    $query .= "type LIKE '%$type%' AND ";
}
$query = substr($query,0,-5);

mysql_query($query) or die(mysql_error());
?>





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users