Jump to content

Multiple search variables


gamefreak13

Recommended Posts

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:

[code]SELECT * FROM stores WHERE state LIKE '%$state%' AND LIKE '%$country%'[/code]

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.

[code]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);
}[/code]

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?
Link to comment
Share on other sites

Maybe try something like this?

[code]<?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());
?>[/code]
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.