sql query when form returns empty field 2

[rmpuk]

I have a form which passes fields $formitem, $keyword, and $type to the following PHP script. The problem is that when the user leaves a field blank, the script interprets it as "all fields". eg if $formitem=015 and $keyword=dog, it will show BOTH item 015 and items with DOG in the text. Similarly, if $keyword is left blank, it returns all records. Snippet of code is below.

Is the problem with the SQL statement, or do I need to use PHP to deal with "empty" variables? Or something completely different?! What I think I need to do is tell the script that if a variable is blank, then ignore it.

Any ideas gratefully appreciated Thanks.

-----------------

$result = mysql_query("SELECT * FROM tab1 WHERE
((catno='%$formitem%'
OR (text1 LIKE '%$keyword%' OR text2 LIKE '%$keyword%'))
AND section LIKE '%$type%')
AND sold='Unsold'
ORDER BY $order",$db);

 

[AnakinPt]

of course it does.

when you have a blank field, you compare it with '%%' witch means "leading by 0 or more chars and trailing with 0 or more chars, witch means, EVERYTHING. As you are doing 'OR' camparations, you only need one clause to be TRUE to get that record.

You must build the query based in the fields that are really filled.

<?
$cond=array();
if($keyword) $cond[]=&quot;keyword like '%$keyword%'&quot;;
if ($formitem) $cond[]=&quot;formitem like '%$formitem%'&quot;;
...

$query=&quot;SELECT .... FROM ... WHERE '.implode(' OR ',$cond).' ORDER BY ... ';

Another thing, I used OR cause you put it in your code. It will returns all the fields that have at least one condition true. If you want ONLY the rows with ALL conditions true, just change in the implode the OR by AND. Anikin
Hugo Alexandre Dias
Web-Programmer
anikin_jedi@hotmail.com

 

[rmpuk]

Worked a treat, thanks!

RMP