A Slightly Complicated MySQL Query

[anthonydamasco]

Hello All,

 

I'm writing an advanced search results page for a website. It's pretty simple except the front end team decided to put a lot of check boxes on the posting page.

 

 

(I'll simplify everything to make the situation ez to understand)

 

So I am constructing a query to pull information from the columns

 

collection

construction

color_e

match

 

and I have variables

$collection

$construction

$red

$green

$blue

$yellow

$match

 

 

Alright so the query needs to be something like

 

Select * From products Where Collection LIKE '$collection' AND construction Like '$construction' AND (color_e like '$red' OR color_e like '$green' OR color_e like '$blue') And match like '$match'

 

The part im having trouble with is the "(color_e like '$red' OR color_e like '$green' OR color_e like '$blue')" part, i want to pull results from any row that has the colors equal to what is being requested. they colors are check boxes so if they are unchecked they equal "XXXXXXX" and if they are checked they equal the color value assigned.

 

Any help?

[mofm]

Try somthing like this hope it works but im not sure it sill

 

Select * From products

 

Where

 

Collection LIKE '$collection'

 

AND construction Like '$construction'

 

AND color_e like '$red' OR '$green' OR '$blue'

[GingerRobot]

Well, if you were to set up your checkboxes like:

 

Blue: <input type="checkbox" name="colours[]" value="Blue" /><br />
Red: <input type="checkbox" name="colours[]" value="Red" /><br />
//etc

 

You could then do something like:

 

<?php
$colour = "'".join("','",$colours)."'";
$sql = "SELECT * FROM products WHERE Collection LIKE '$collection' AND color_e IN ($colours) AND...."
?>

[anthonydamasco]

thanks guys I just have one more question.

 

I have an dropdown

 

<select name="collection">

<option value="" selected="selected">All Collections</option>

<option value="Altair">Altair</option>

<option value="Alto">Alto</option>

</select>

--

$collection = $_post["collection"];

--

SELECT * FROM products WHERE collection = '%$collection%'

 

if someone chooses "All collections" i want all collections to display. what value should be set under "all collections"

 

[anthonydamasco]

Alright, I'm trying this method,

 

how do i post this to create the "$colours" variable

 

Well, if you were to set up your checkboxes like:

 

Blue: <input type="checkbox" name="colours[]" value="Blue" /><br />
Red: <input type="checkbox" name="colours[]" value="Red" /><br />
//etc

 

You could then do something like:

 

<?php
$colour = "'".join("','",$colours)."'";
$sql = "SELECT * FROM products WHERE Collection LIKE '$collection' AND color_e IN ($colours) AND...."
?>

[craygo]

Set the value for all collections as "%" that is Mysql wildcard.

 

<?php
$colour = $_POST['colours'];
$colours = "'".join("','",$colour)."'";
$sql = "SELECT * FROM products WHERE Collection LIKE '$collection' AND color_e IN ($colours) AND...."
?>

 

Ray

[Barand]

or if the query is

 

SELECT * FROM products WHERE collection LIKE '%$collection%'

 

then if $collection is '' (empty string) you get all values.