MySql 5 - Filters

[HerbRodenhaber]

i want the user to be able to filter data multiple times ... for instance lets say they query the DB
for all gems that are RED ... it returns a list of 1000 items ... but they want to narrow it down even further ... and wany to aply more filters ... what is the best way to approch this task .... keeping in mind that the end user could apply up to 6 filters ...

I was thinking maybe query the DB and write the query into a new table so i could apply a second filter to the new table .... but that doesnt sound effecient of fast ...

as always any help is greatly appreiated...

thanks
Herb

[dalecosp]

It pretty much depends on database design.

I work with a "popular" piece of software at work; it has a table containing "characteristics", and another called "extra" where the characteristics are applied to the main content table. A tad complex for my liking, but it keeps us from having beaucoup fields on the main table for keeping track of all the characteristics.

Needless to say, the filter can get a tad dicey. You have to check "characteristics" to see what filters MIGHT be applicable, "extra" to see if you actually have inventory that matches those characteristics, and then pull the ids of the items from the main table. But it does work, FWIW.

[HerbRodenhaber]

ok ... i think this project is going to be to complex and take to much time to impliment

thanks for the input thought

Herb

[johanafm]

If I understand you correctly, simply construct your where clause based on all the input. That is, if the user first selects GEM, then RED, then other stuff, your first query would have

WHERE type='gem'

and the second page would preselect item type 'gem' in the form, which means this information is retained when they select color 'RED'. The second query would have

type='gem' AND color='red'

just like it would have if they specified both "gem" and "red" at the the same time.

And it can all be easily handled by

# function to build (this part of) the where string
function filterString($filters, $data)
{
	$parts = array();
	foreach($filters as $f => $t)
	{
		if (!empty($data[$f]))
		{
			switch ($t)
			{
				case 'int':
					$parts[] = sprintf('%s=%d', $f, $data[$f]);
					break;
				case 'string':
					$parts[] = sprintf("%s='%s'", $f, $data[$f]);
					break;

		}
	}
}
return ' ' . implode(' AND ', $parts) . ' ';
}

# your post data
$_POST = array(
	'type'	=> 'GEM',
	'size'	=> '4'
);

# settings providing the function with information of which information
# to use for filtering
$filter_vars = array('type' => 'string', 'color' => 'string', 'size' => 'int');
$where = filterString($filter_vars, $_POST);

echo $where;

[HerbRodenhaber]

hmmm interesting .....thanks for the ideas .... time to get to work coding this

herb

[HerbRodenhaber]

seems to be coming along nicely but i cant seem to figure out how to get the function to create something like this

WHERE type = 'red' and quality = '4' or type = 'pink' and quality='4' or type = 'blue' and quality = '4'
so i made a workaround but would rather the function did the work ...as i have 2 more filters to impliment yet and its starting to get a little crazy getting all the querrys to execute correctly

thanks
herb

[johanafm]

As long as the logic for needing "and" or "or" depends solely on the column being checked, this can be easily handled, and this seems to be the case here.

But if the user can set up more than one query set at the same time, such as "color: red & type: gem", "color: pink, quality: 4", you'd need additional logic to keep the search sets separate. The separate sets could still be handled by the existing function and thereafter you'd just "OR the sets together".

Anyway, the solution for case 1

        if (!empty($data[$f]))
        {
        	if (is_array($data[$f]))
        	{
        		foreach ($data[$f] as $v)
        		{
					switch ($t)
					{
						case 'int':
							$parts[$f][] = sprintf('%s=%d', $f, $v);
							break;
						case 'string':
							$parts[$f][] = sprintf("%s='%s'", $f, $v);
							break;

				}
    		}
    		$parts[$f] = '('.implode(' OR ', $parts[$f]).')';
    	}
    	else
    	{
		switch ($t)