Rich,
When I do searches with multiple fields, I construct the SQL string on
the PHP side. You can do something like this:
$sql = "SELECT * FROM docmeta WHERE ";
if( $PartNumber )
$sql .= "PartNumber='$PartNumber'";
if( $Title )
$sql .= " AND Title='$Title'";
and so on...
That has worked well for me in the past. You could make it more general
by creating an array of field names and iterating through that array
checking for the presence of the appropriate variables. Then, if you add
an additional field, just add it to the array of field names.
If there is a way to do this sort of "optional search field" with MySQL,
then I don't know about it.
--Dave
On Sun, 2003-01-05 at 12:52, Rich Hutchins wrote:
> 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
>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
