Hi all,
I am trying to work out the best way to build a custom advance search feature and I have no idea where to start.
First thing is I have all the data in a MySQL table with assigned IDs.
Setup: I have a page with multiple checkboxes belonging to multiple groups i.e. category_1 has options 1_1, 1_2, 1_3. category_2 has options 2_1, 2_2, 2_3 and so on.
In my DB I have a record with category_1 = 1_1 and category_2 = 2_3 for example.
I know how to get the data out of the table with a simple URL i.e. http://get-records.php?category_1=1_1&category_2=2_3 for example. Now how would I get records where:
category_1 = 1_1 AND category_2 = 2_3
OR
category_1 = 1_2 AND category_2 = 2_1
Writing the SELECT statement is no problem but how would I go about writing the URL?
Basically I would like to be able to build on the URL with the current URL so:
If current URL is:
http://get-records.php?category_1=1_1&category_2=2_3
and I select another checkbox (category_1 = 1_2) I would like:
http://get-records.php?category_1=1_1&c ... gory_2=2_3
Is this possible and does this make sense? It does in my mind anyway.
Advance Search Query
Moderator: General Moderators
- superdezign
- DevNet Master
- Posts: 4135
- Joined: Sat Jan 20, 2007 11:06 pm
Re: Advance Search Query
You'd want to use arrays. So, your URL would have the query string "category_1[]=1_1&category_1[]=1_2&category_2=2_3"
Then, in your code, just use logic to check for arrays.
Note that this is untested. Also note that I don't do any sanitation of the data in the $_GET array. You will definitely want to validate and filter that data.
Then, in your code, just use logic to check for arrays.
Code: Select all
function buildWhereClause(array $data, $separator = 'AND') {
$whereClauseParts = array();
foreach ($data as $fieldName => $value) {
$whereClauseParts[] = is_array($value)
? '(' . buildWhereClause($value, 'AND') . ')'
: "`$fieldName` = '$value'";
}
return implode(" $separator ", $whereClauseParts);
}
$sqlQuery = 'select * from `table_name` where ' . buildWhereClause($_GET);Re: Advance Search Query
Thanks for you reply. I was hoping there would be a more elegant method as there are about 5 categories and 10 options for each category so the URL is going to get extremely long.
I guess another way would be to set a session array and loop through. What would you recommendation be between session array or url query array?
Cheers
I guess another way would be to set a session array and loop through. What would you recommendation be between session array or url query array?
Cheers