Thanks for the tutorial! There is another way to get around this problem, and that is by normalising your database. That is, having a lookup table to store all the items. You list all your categories in one table (categories) and then you list all your items that require categories in another table (pictures), you then have a table in between those 2 called something like (pictures_categories) - your
lookup table.
Allow me to demonstrate.
Code:
Table: categories
+-----+-------------+
| id | name |
+-----+-------------+
| 11 | psychology |
| 12 | philosophy |
| 13 | sociology |
+-----+-------------+
Code:
Table: pictures
+-----+-------------+
| id | name |
+-----+-------------+
| 1 | image1 |
| 2 | image2 |
| 3 | image3 |
+-----+-------------+
Code:
Table: pictures_categories
+-------------+--------------+
| picture_id | category_id |
+-------------+--------------+
| 1 | 11 |
| 1 | 12 |
| 2 | 13 |
+-------------+--------------+
As you can see in our pictures_categories table, our image1 has 2 categories, and our image3 has 1 category, whereas our 3rd image has no categories. You would then perform a MySQL
JOIN to get the categories that the images are associated with. Something like so:
Note: Not tested this query, but I'm 95% sure it'll be fine!
sql Code:
SELECT
categories.name
FROM
pictures
LEFT JOIN
pictures_categories
ON
pictures_categories.picture_id = pictures.id
LEFT JOIN
categories
ON
categories.id = pictures_categories.category_id
WHERE
pictures.name = 'image1'
This would then return the names of all the categories that image1 has associated with it. It's a tough one to describe, but I hope you can at least extract some useful points! Also for any one interested,
maybe Wikipedia'll provide more in-depth information.
But basically, our pictures_categories table contains 2 columns of IDs which reference each of the other 2 tables. You just match up the IDs and use the lookup table to store multiple categories.