MySQL Database Query for Field with Multiple Options

Question

In MySQL database, images in gallery can be assigned to a product range so that galleries can be filtered to only display images from a particular range.

Currently the main image gallery contains:-

$query_Cur_range_img = "SELECT * FROM `images` WHERE active = 'y' AND gallery = '1' LIMIT ".$showfrom.", ".$max;

If gallery is '1' then images are to be displayed in main gallery (ALL).

Some images are also added to product ranges from within a separate database column 'product_range_id' and the product range id's are 1, 2, or 3.

In the include file for a filtered gallery, we have:-

 $query_Cur_range_img = "SELECT * FROM `images` WHERE active = 'y' AND gallery = '1' AND product_range_id = '1' LIMIT ".$showfrom.", ".$max;

This is only displaying images that ONLY are assigned to product_range_id 1 however. Some images will be displayed to product ranges 2 and 3 for example or maybe 1 and 2. I guess we need to include an OR statement? Any help with writing the query for this would be greatly appreciated please so as above, I think we need AND product_range_id = '1' (or 2 or 3) for example?

Thanks in advance.

UPDATE

I've tried the following but all this did when filtering images by rage id 1 was display images that are ONLY assigned to range id 1 (didn't include images that assigned to range id's 1 and 2 for example).

$query_Cur_range_img = "SELECT * FROM `images` WHERE active = 'y' AND gallery = '1' AND (product_range_id = '1' OR product_range_id = '1' AND product_range_id = '2' OR product_range_id = '1' AND product_range_id = '3') LIMIT ".$showfrom.", ".$max;

UPDATE - 13/01/2012

Now Resolved - Fix Below (Thanks to Elen)

$query_Cur_range_img = "SELECT * FROM images WHERE active = 'y' AND gallery = 1 AND (FIND_IN_SET('1', product_range_id) OR product_range_id = '1') LIMIT ".$showfrom.", ".$max;

And

$query_Cur_range_img = "SELECT * FROM images WHERE active = 'y' AND gallery = 1 AND (FIND_IN_SET('2', product_range_id) OR product_range_id = '2') LIMIT ".$showfrom.", ".$max;

And

$query_Cur_range_img = "SELECT * FROM images WHERE active = 'y' AND gallery = 1 AND (FIND_IN_SET('3', product_range_id) OR product_range_id = '3') LIMIT ".$showfrom.", ".$max;

As required...

What is the table structure? The options given should have worked but it seems like the table structure may be different than expected. — Brian
– Brian, Commented Jan 11, 2012 at 13:55

Elen · Accepted Answer · 2012-01-11 17:46:22Z

2

is this good enough?

SELECT * FROM `images` WHERE active = 'y' AND gallery = '1' AND (product_range_id = '1' OR product_range_id = '2' OR product_range_id = '3') LIMIT ".$showfrom.", ".$max.";

edit

$query_Cur_range_img = "SELECT * FROM images WHERE active = 'y' AND gallery = 1 AND 
(FIND_IN_SET('1', product_range_id) OR product_range_id = '1') LIMIT ".$showfrom.", ".$max;

edited Jan 11, 2012 at 17:46

answered Jan 11, 2012 at 13:00

Elen

2,3433 gold badges24 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

zigojacko Over a year ago

Thanks for your comment - this doesn't work as it only displays images that displayed in EITHER product_range_id 1, 2 or 3 but doesn't include images that may be assigned to more than one range id (such as 1 and 2 for example). Thanks

Elen Over a year ago

ok, it's quite difficult without seeing you table structure and some examples. please see edit.

zigojacko Over a year ago

I appreciate that it is difficult without seeing exactly what we've got - your latest edit broke the page with a custom error message 'Page number does not exist' (which I appreciate is not something you will be able to help with) - no idea why that happened :/

zigojacko Over a year ago

I don't think that last quotation mark was meant to be there because now it breaks with: Parse error: syntax error, unexpected '"'

zigojacko Over a year ago

I just tried this but still doesn't show all the correct images: $query_Cur_range_img = "SELECT * FROM images WHERE active = 'y' AND gallery = '1' AND product_range_id = '1' OR product_range_id = '1,2' OR product_range_id = '1,3' LIMIT ".$showfrom.", ".$max;

|

jogesh_pi · Accepted Answer · 2012-01-11 13:02:29Z

2

try with this:

$query_Cur_range_img = "SELECT * FROM `images` 
WHERE active = 'y' 
AND gallery = '1' 
AND product_range_id IN ('1', '2', '3') 
LIMIT ".$showfrom.", ".$max;

hope it will help,,

PS if you get the product_range_id with php then plz never forget to use mysql_real_escape_string()

answered Jan 11, 2012 at 13:02

jogesh_pi

9,7745 gold badges40 silver badges65 bronze badges

1 Comment

zigojacko Over a year ago

Thanks for your help, I've tried this and what this is now doing when filtering images by product_range_id 1 is including images that are only assigned to product_range_id 2 as well as others. This needs to only display images that are assigned to one other category but has to be assigned to product_range_id 1. Thanks

Collectives™ on Stack Overflow

MySQL Database Query for Field with Multiple Options

2 Answers 2

10 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

10 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related