Jump to content

Archived

This topic is now archived and is closed to further replies.

gamefreak13

Multiple search variables

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?

Share this post


Link to post
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]

Share this post


Link to post
Share on other sites

×

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.