TalkPHP - View Single Post

Haris · 12-09-2007, 07:07 PM

Quote:

Originally Posted by Wildhoney

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.

Can you use the same structure for categories and sub-categories? I am confused. :(