Thank you so much for your suggestion. That will certainly do the trick. I
struggled with the concatenation of the sql string and IF statements similar
you wrote before I sent my original post, but couldn't get the concatenation
right. I wasn't sure if I was going about I the wrong way logically or if I
just wasn't getting the syntax right.

I'll code this up and things should be just fine from there.

Thanks again for your time.


-----Original Message-----
From: David Smith [mailto:[EMAIL PROTECTED]]
Sent: Sunday, January 05, 2003 5:33 PM
To: Rich Hutchins
Subject: Re: [PHP-DB] Query with optional params


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.


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
> 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
> 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
> 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
> 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
> I want to send.
> Essentially, I want to skip sending the subtitle LIKE "" part when
> field is not filled in by the user.
> Incidentally, the query works fine if I use OR instead of AND, but
> 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
> 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

PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Reply via email to