MySQL Query Conflict

[wolfcry]

Hey all,

 

I'm in the process of building a pretty complex search script but seem to be missing something.

 

Here is the bit of code pertaining to my search query based on values the user inputs into 1 or a combination of 3 text fields.

 

$result = mysql_query("SELECT * FROM form_$form WHERE col_2 = '$loc' OR col_3 = '$city' OR col_4 = '$state' LIMIT $offset, $entries_per_page") or die("oh man" . mysql_error());

 

What I have set up is three text fields where the user can search forms based on "location" such as different corporate departments, "city", the city the department is located and of course the "state". Now what I'm trying to do is set it up so the user can use any one of the fields by themselves to search for a much broader search or using a combination of 2 or 3 fields to limit the search results and make them more relevant.

 

Everything works great if the user only uses one field at a time but if they combine any or all of the fields, the result is everything in the database and not what was searched for. I've tried everything I could think of from using %'s to LIKE and nothing seems to be working.

 

How would I rewrite the above to make the search more "open" based on the user's preference of search as stated above?

 

Thanks for all your help.

[beboo002]

if u useing or that means it satisfy the first condition and it gives the result but when u use 2 or 3 field then u use and 'and' condition

 

like

if($a!="") 
{
$sql="select * from tablename where a='$a';
}
else if($b!="") 
{
$sql="select * from tablename where b='$b';
}
else if($c!="") 
{
$sql="select * from tablename where c='$c';
}
else if(($c!="") &&($b!="")
{
$sql="select * from tablename where c='$c' and b='$b';
}
else if(($c!="") &&($a!="")
{
$sql="select * from tablename where c='$c' and b='$a';
}
else if(($b!="") &&($a!="")
{
$sql="select * from tablename where c='$a' and b='$b';
}
else
{
$sql="select * from tablename where c='$c' and b='$b' and a='$a';
}

$result=mysql_query($sql,$link)
$row=mysql_fetch_array($result)
echo the value

[Barand]

see

http://www.phpfreaks.com/forums/index.php/topic,198579.msg896711.html#msg896711