SQL query, comparing two arrays

Question

I have an application with images stored in multiple categories, currently being stored by category ID in a column as a space separated list (eg. 1 5 23 2).

I have a query from a search filter, which is currently an array of IDs, (eg. 1 5).

Ideally, I'd find a solution using something like WHERE IN that would see if any of my array values exist in the stored column, although I don't see an easy solution.

At the moment I have to query all the images, bring them into PHP and check there, using "array_intersect". I see this as being a problem if I have 100,000s of images in the future to pull and then check.

Can anyone think of an elegant solution? The application is still in development, so I could arguably change the structure of my tables.

The elegant solution would be to NOT store the IDs in the database the way you do it. You should store one image ID per row, then your query would be much-much simpler. I strongly suggest that you modify your schema before it's too late. — Aleks G
– Aleks G, Commented Mar 29, 2012 at 14:58

H Hatfield · Accepted Answer · 2012-03-29 14:58:07Z

2

I think adding a map table would probably be best here which maps the image_id with the category_id.

answered Mar 29, 2012 at 14:58

H Hatfield

8465 silver badges9 bronze badges

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

1 Comment

Michael Watson Over a year ago

Thanks for the answer. Now that you've said it, it's gleamingly obvious! So, a third table with the corresponding IDs. Thanks again.

xenonite · Accepted Answer · 2012-03-29 15:03:12Z

1

refactor your database tables!!!

use sth like this:

table_image id int name text, content text, ...

and a second table for the categories:

table_category id int, image_id int, category int

this way, you can store categories in a separate table using foreign keys. now, you can do simple sql queries like

SELECT table_image.id FROM table_image, table_category WHERE table_image.id = table_category.image_id and table_category.category = $cat_arr[0] OR table_category.category = $cat_arr[1] ...

answered Mar 29, 2012 at 15:03

xenonite

1,6715 gold badges28 silver badges43 bronze badges

1 Comment

Michael Watson Over a year ago

Thanks! Already done it, I think I was just having a brain burp.

Bas · Accepted Answer · 2012-03-29 18:30:38Z

1

H Hatfield has the best answer. If you really must use a single column (which I do not recommend) you could store the categories as a comma separated list instead of spaces. You can then use the MySql function find_in_set, as such:

WHERE FIND_IN_SET('3', categoryListColumnName) > 0 OR FIND_IN_SET('15', categoryListColumnName) > 0

Using your current database design you could use an IN query:

WHERE categoryListColumnName LIKE '% 3 %' OR categoryListColumnName LIKE '% 15 %'

and add more OR's for every category you want to find. When using this query you have to make sure your list separated by spaces ends and starts with a space, otherwise it won't work.

Let me just reiterate, that these methods will work, but they are not recommended.

edited Mar 29, 2012 at 18:30

answered Mar 29, 2012 at 15:10

Bas

3,1363 gold badges24 silver badges18 bronze badges

Collectives™ on Stack Overflow

SQL query, comparing two arrays

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related