Jump to content

Archived

This topic is now archived and is closed to further replies.

CanMan2004

sql query with tickboxes

Recommended Posts

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

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.