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.
Complex database query
Moderator: General Moderators
-
winereader
- Forum Newbie
- Posts: 1
- Joined: Tue Feb 25, 2003 12:25 pm
- Location: pasadena, ca
I would build the SQL query based on if the form fields are filled in. For example:
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).
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) ;
}And I apologize for any syntax errors - I did not test this in PHP myself, but the logic is correct (I hope).