INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

SELECT LIKE '&' and NULL values

SELECT LIKE '&' and NULL values

(OP)
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.

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

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:

SELECT * 
FROM inventory 
WHERE  part_number LIKE '%' 
AND description LIKE '%' 

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

SELECT * 
FROM inventory 
WHERE  part_number LIKE '654%' 
AND description LIKE '%' 

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?

RE: SELECT LIKE '&' and NULL values

how about this>

CODE

$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']) . "'"; 

RE: SELECT LIKE '&' and NULL values

(OP)
JPADIE, you absolutely rock. Thank you very much!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close