sql query with tickboxes

[CanMan2004 1]

Hi all

I have html form with a search box on it and a selection of tickboxes, each tickbox has a different value, for example, there might be 3 tick boxes each with a unique value such as '3' or '5' or '12'. The tickboxes are called "tickeditems".

I then have a sql database, one of the columns is called "products" and it has number in it seperated using commas, for example, one row has

1,5,10,23,45

another row has

3,5,12,34

what I want to do is run a query on this sql database which would return any results from the sql database that match what has been ticked.

For example, if the user ticks boxes

1 and 3

then clicks the forms search button, it would return all rows which have 1 and 3 stored in the sql column "products".

I normally run the following query

[code]SELECT * FROM myproducts WHERE products IN ($_GET['tickeditems'])[/code]

But whatever I try, it doesnt seem to work.

Can anyone give me some help?

Thanks in advance

Ed

[jefkin 0]

Hi Can.

You can do this, but it's not very eficient.

Best way to do it is:

table ticks (id integer auto_increment, name varchar(6) not null);
table ticks_for_products (id integer auto_increment, tick_id integer not null, prod_id integer not null);
table myproducts .... something including an id field like maybe (id integer auto_increment, ...

Now, you have a straight query on the ticks_for_products table.


However, that may not be an option, in which case:

Modifiy your tickeditems data so that it begins and ends with a ',' so change:

1,5,10,23,45    and          3,5,12,34

into

,1,5,10,23,45,    and          ,3,5,12,34,

Then build your query as:

[code]
$query = " SELECT * FROM myproducts ";
$join = ' WHERE ';
foreach ($thickeditems as $item)
{
    $query .= $join . "tickeditems like '%,$item,%' ";
    $join      = " AND ";
}
mysql_query($query)
...
[/code]