mysql_query question

I need some help with a PHP/mysql_query
Here's the line:

$result = mysql_query ("SELECT * FROM data1 WHERE location LIKE '$location%' AND rental LIKE '$rental%' AND purchase LIKE '$purchase%'",$db);

if ($row = mysql_fetch_array($result)) {

It mostly works, but if the user chooses a location and both rental AND purchase, it shows no records.

If the user chooses either rental OR purchase it pulls up the appropriate records correctly.

I've tried:
"AND rental LIKE '$rental%' OR purchase LIKE '$purchase%"
but it still doesn't work.

I need the search to work for users that want to choose both rental AND purchase from a location.
What am I doing wrong?

$result = mysql_query ("SELECT * FROM data1 WHERE location LIKE '$location%' AND ( rental LIKE '$rental%' OR purchase LIKE '$purchase%' )",$db);

Nearly...
If a user chooses a location
And rental
And purchase
It pulls up the correct entries.

BUT
If a user chooses a location
And rental or purchase
It pulls up all fields with both rental and purchase.... which is incorrect, it should only pull up location and either rental or purchase, not both.

A simple thing maybe, but it's driving me mad!

Okay..you have to construct some logic to create your query. If they didn't enter a rental, then you don't want it in your query. Something like this...
<BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">code:</font><HR><pre>
$query = "SELECT * FROM data1 WHERE location LIKE '%$location%' ";
if ($rental != "")
{
$query .= " AND rental LIKE '%$rental' ";
}
if ($purchase != "")
{
$query .= " AND purchase LIKE '%$purchase%' ";
}

$result = mysql_query($query,$db);
[/code]
The problem was if the user didn't enter rental, then $rental eithe wasn't set or equaled an empty string. So when you search for something like

rental LIKE '%$rental%'

You're actually searching for something like

rental LIKE '%%'

which will return all of the records.

---John Holmes...

[This message has been edited by SepodatiCreations (edited October 14, 2000).]

But if a user chooses rental AND purchase.

There's no records found, because there are no records that are both... they are either one or the other.

What I need is that when the user chooses both, they get records that have the chosen location AND are both rental and purchase.

I hope this makes sense, I appreciate your help!

Same concept, just merging the two posted answers above. I haven't tested this at all, so caveat emptor.

<BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">code:</font><HR><pre>$query = "SELECT * FROM data1 WHERE location LIKE '$location%'";

if ($rental != "" && $purchase != "") {
$query .= " AND (rental LIKE '$rental%' OR purchase LIKE '$purchase%')";
} elseif ($rental != "" && $purchase == "") {
$query .= " AND rental LIKE '$rental%'";
} elseif ($rental == "" && purchase != "") {
$query .= " AND purchase LIKE '$purchase%'";
}

$result = mysql_query($query,$db);[/code]

Thanks John, that's exactly what I was looking for.

It works as I wanted.