SELECT LIKE '&' and NULL values 1

[cmayo]

My client's PHP ticketing system has a search form at the top of most of its tabular pages, and the tabular data is generated by MySQL using search form fields and wildcarded LIKE statments, i.e.

[pre]SELECT *
FROM inventory
WHERE part_number LIKE '{$_POST['part_number']}%'
AND description LIKE '{$_POST['description']}%'[/pre]

This works well enough in most cases. There are no search criteria on initial page load so the queries all tend to be wildcarded and all data is returned:

[pre]
SELECT *
FROM inventory
WHERE part_number LIKE '%'
AND description LIKE '%'[/pre]

and most searches are done on only one or two fields, so a more typical query will look like:

[pre]
SELECT *
FROM inventory
WHERE part_number LIKE '654%'
AND description LIKE '%'[/pre]

This method works fine unless some of the records contain NULL's in the search fields, in which case those records are omitted since LIKE '%' doesn't match a NULL value.

So I'm looking at a better way to write that code.

All I can think to do is to kludge the WHERE conditions with PHP so the LIKE statements aren't included unless the user has entered search text for that field, i.e. if the user doesn't give us a description to search for, the query won't contain a "description LIKE '%'" condition, hence NULL's in the description field won't be an issue.

Is there a better way?

 

[jpadie]

how about this>

$query = "
SELECT * 
FROM inventory 
WHERE ifnull(part_number,'zzz') LIKE '" . mysql_real_escape_string($_POST['part_number']) . "%' 
AND ifnull(description,'zzz') LIKE '". mysql_realt_escape_string($_POST['description']) . "'";

 

[cmayo]

JPADIE, you absolutely rock. Thank you very much!