Advance Search Query

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
kerepuki
Forum Commoner
Posts: 30
Joined: Fri Oct 29, 2004 12:28 am

Advance Search Query

Post by kerepuki »

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.
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Re: Advance Search Query

Post by superdezign »

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.

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);
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.
kerepuki
Forum Commoner
Posts: 30
Joined: Fri Oct 29, 2004 12:28 am

Re: Advance Search Query

Post by kerepuki »

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
Post Reply