Query with optional params
| From: | Rich Hutchins | 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