Query with optional params

From: Date: Sun, 05 Jan 2003 19:52:43 +0000
Subject: Query with optional params
Groups: php.db 
Request: Send a blank email to php-db+get-24569@lists.php.net to get a copy of this message
I have a search page on which I place six fields into which users may enter data to perform a search. For ease of explanation, the fields are: Part Number Title Subtitle Print Date Status Type All of these fields reside in the same table, named docmeta, in a MySQL database. I'm running into a logic problem when a user enters data into only one or two of the six fields. The question/problem is how do I write a MySQL query when not all of the parameters are filled in each time? Is this a problem that needs to be handled on the PHP side with a bunch of IF statements when constructing the MySQL string or is there a MySQL command or syntax that accommodates variable length parameter sets? The problem is when a search parameter is left blank by the user. If I include the empty parameter in the query (e.g. SELECT * FROM docmeta WHERE partnum LIKE "%" AND title LIKE "%" AND subtitle LIKE "";) then an empty set is returned when subtitle is not blank in the database. This is totally understandable, since that's what subtitle LIKE "" means, but it's not what I want to send. Essentially, I want to skip sending the subtitle LIKE "" part when subtitle field is not filled in by the user. Incidentally, the query works fine if I use OR instead of AND, but switching to an all OR query is not an option; I need to be able to perform both. I've checked the archives and the MySQL manual (especially the language reference) and I haven't found anything that answers my question. I'm fairly certain I'll be able to figure this out if somebody points me in the right direction, but I don't think I even know what (or where) to look for the answer. Any help would be appreciated. Thanks, Rich

« previous php.db (#24569) next »