get query string and loop varaibles onto sql query

[Ashpb]

Hi,

I am currently trying to build a product catalogue where users can filter the results (Type, Material, Colour, Size, Range etc.)

I need to get the query string

i.e.
page.php?colour=dark+wood&material=oak&range=ruby

and add it to the bottom of my mysql query

SELECT
 p.id, p.name, p.range_name, p.manufacturer
FROM `attributes_entity` `ae`
 INNER JOIN `products` `p` ON `ae`.`product_id`=`p`.`id`
 INNER JOIN `attributes` `a` ON `ae`.`attribute_id`=`a`.`id`
 INNER JOIN `attributes_values` `av` ON `ae`.`value_id`=`av`.`id`
WHERE `p`.`visible`='1'
AND (`a`.`name`='colour' AND `av`.`value`='dark wood') OR (`a`.`name`='material' AND `av`.`value`='oak') OR (`a`.`name`='range' AND `av`.`value`='ruby')

Anyone know how i can turn

colour=dark+wood
material=oak

into

(`a`.`name`='colour' AND `av`.`value`='dark wood')
(`a`.`name`='material' AND `av`.`value`='oak')

i know i could just use

$colour = $_REQUEST['colour'];
$material = $_REQUEST['material'];

but because there is going to be so many different attributes and only some being relevant to the particular category, there must be a dynamic way or even a better way.
Hope this all makes sense...

Ashley.

[penders]

IMO... you need an array that contains all the valid names:

array('colour','material,...)

Step through this array and check for a value in the $_GET array (rather than $_REQUEST). If the value exists, mysql_real_escape_string() (to sanitize the input) and build your SQL.

[Ashpb]

well i figured it out i think.

it may not be the prettiest bit of coding but its the best i can do :)

ive swapped round the key and value from

page.php?colour=dark+wood&material=oak

to

page.php?dark+wood=colour&oak=material

as if the user wanted so view 'light wood' AND 'dark wood' at the same time is was only showing the last value of the colour key.

also i kept getting an underscore when the plus sign was so had to use str_replace to sort the problem out.

$string = $_SERVER['QUERY_STRING'];

$array=array();
parse_str($string,$array);
$array_num = count($array);

$query_filter = '';

forEach($array as $key => $value) {
$value = str_replace('_', ' ', $value);
$key = str_replace('_', ' ', $key);

$query_filter .= "(`a`.`name`='$value' AND `av`.`value`='$key')";

if ($array_num > '1'){
$query_filter .= " OR ";
}

$array_num--;

}

if anyone knows of a better / cleaner way to get the same results them please let me know as unfortunately im not a master of PHP yet :(

[penders]

I intended replying ages ago... just found the tab still open... (!)

ive swapped round the key and value from

page.php?colour=dark+wood&material=oak

to

page.php?dark+wood=colour&oak=material

as if the user wanted so view 'light wood' AND 'dark wood' at the same time is was only showing the last value of the colour key.

To specify multiple values for the same parameter name you would use square bracket syntax in the name eg.

page.php?colour[]=dark+wood&colour[]=blue

'colour' then gets returned as an array to your script...
$_GET['colour'][0] == 'dark wood'
$_GET['colour'][1] == 'blue'

This also avoids any problems with decoding the parameters. And can be passed directly from your HTML form without any additional processing.

[rocknbil]

A month? You left a tab open for a month? I thought I was bad. :-)

[penders]

I know; I'm bad... it had gotten like tab soup up there! All the little favicons had long since gone and the machine was starting the buckle - it was time for the monthly tab tidy!