CODEWITHSUNDEEP
phpmysqlarrays
Michael Watson
Michael Watson

Reputation: 1119

SQL query, comparing two arrays

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.

Upvotes: 1

Views: 3977

Answers (3)

Bas
Bas

Reputation: 3096

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.

Upvotes: 1

xenonite
xenonite

Reputation: 1671

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] ...

Upvotes: 1

H Hatfield
H Hatfield

Reputation: 856

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

Upvotes: 2

Related Questions