Complex database query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
winereader
Forum Newbie
Posts: 1
Joined: Tue Feb 25, 2003 12:25 pm
Location: pasadena, ca

Complex database query

Post by winereader »

I've built several web sites where the user could search the MySQL database, but never one as complex as this, and I could use a little insight from someone who's been there.

This is a searchable eyewear catalog that I'm adding to an existing website. I have a search form containing multiple criteria that the user can choose to search with. For example, the user can search by brand, gender, temple size, bridge size, etc., or ANY combination.

I'm using PHP/MySQL and having a problem writing the query to search the proper columns in the table based on which fields the user filled in on the search form. Would I just go with a lot of nested conditionals? It seems that there must be a better way, but I can't think what it is. I've got a ton of MySQL/PHP books, but none of them look at this problem (you would think a catalog search using multiple optional criteria would be a common need).

Any help or pointers to tutorials would be appreciated.
Rob the R
Forum Contributor
Posts: 128
Joined: Wed Nov 06, 2002 2:25 pm
Location: Houston

Post by Rob the R »

I would build the SQL query based on if the form fields are filled in. For example:

Code: Select all

$sql = "select * from table" ;
$whereclause = "" ;
if ($_POST["formfield1"] != "") {
   $whereclause .= "and dbfield1 like '%" . $_POST["formfield1"] . "%' " ;
}
if ($_POST["formfield2"] != "") {
   $whereclause .= "and dbfield2 like '%" . $_POST["formfield2"] . "%' " ;
}
// ... as many fields as you need to use ...

// remove the first "and" and append it to the query
if ($whereclause != "") {
   $sql .= " where " . substr($whereclause,4) ;
}
This still needs an if clause for every form field you want to append. You could convert this into a "for each" loop if the form fields were stored in an array.

And I apologize for any syntax errors - I did not test this in PHP myself, but the logic is correct (I hope).
Post Reply