RE: [PHP-DB] Query with optional params
Dave, 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. Rich -Original Message- From: David Smith [mailto:[EMAIL PROTECTED]] Sent: Sunday, January 05, 2003 5:33 PM To: Rich Hutchins Cc: [EMAIL PROTECTED] Subject: Re: [PHP-DB] Query with optional params 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 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Query with optional params
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
[PHP-DB] Query with optional params
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
