PHP - MySQL queries over three tables

Question

Basically what I have is an image gallery. Just tried to create one for learning purposes. What I have now is when he/she clicks on category the following mysql query to select the galleries is fired:

    SELECT ID, name, gallery_thumb FROM galleries WHERE
    category1=$category_id OR category2=$category_id OR
    category3=$category_id ORDER BY ID DESC

This was just a fast and crappy solution which I threw together. Basically I want one gallery to appear in many categories. Here I made it that way as I create a gallery I can add it to three or less categories, therefore one gallery may appear in different cateogries. But this soultion isn't what I wanted.

Galleries table:

    CREATE TABLE `galleries` (

    `ID` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(1000) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
    `description` varchar(1000) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
    `category1` varchar(500) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
    `category2` varchar(500) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
    `category3` varchar(500) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
    `web_link` varchar(3000) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
    `gallery_thumb` varchar(2000) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
    `reg_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`ID`)
    )

The category2 and category3 will be unnecessary and removed if I get the functionality I want working.

Tags table:

    CREATE TABLE `gallery_tags` (
    `ID` int(11) NOT NULL AUTO_INCREMENT,
    `tag_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
    PRIMARY KEY (`ID`)
    )

Tags and galleries reference table:

    CREATE TABLE `gallery_tag_reference` (
    `ID` int(11) NOT NULL AUTO_INCREMENT,
    `gallery_id` int(11) NOT NULL,
    `tags_id` int(100) NOT NULL,
    PRIMARY KEY (`ID`)
    )

I added a tags functionality. As I create a gallery now I can add as many tags as I like to the gallery. Now the gallery will have only one main category and many tags tied to it. But I want the gallery to appear in other categories as well based on the tags which I added when I created the gallery. The categories will have one or more tags predefined to them. What I want to do now is when visitor clicks on the category the same data would be selected which was before only the query filtering part would be different.

Example:

Category1 will have tags: car, blue, shiny
The gallery1 will have tags: blue, red, fast.
The gallery1 do not belong to category1 but because it has a tag "blue" added to it in the table "gallery_tags" it appears in the result of the query executed when visitor clicked on category1 button.

Example2:

Category1 will have not tags.
Category2 will have not tags.
Category3 will have not tags.
Category4 will have predefined tags: yellow, red.
Category5 will have predefined tags: tall, high.

Gallery1 will have no tags but it belongs to Category1.
Gallery2 will have no tags but it belongs to Category2.
Gallery3 will have tags: yellow, tall, field
Gallery3 belongs to Category3.

When visitor clicks on Category4 button selected are galleries which belong to Category4 AND the galleries which have tags "yellow" or "red". Because here we have no galleries belonging to Category4 but we have gallery3 which has tag yellow and the Category4 also has predefined tag "yellow" gallery3 is showed in Category4 when cliked on.

The same is with Category5. It does not have any galleries belongig to it directly but because gallery3 has a tag "tall" tied to it and Category5 also has predefined tag "tall" the gallery3 is shown also in Category5.

I hope I described it cleary what I want to achieve. As I am not very experienced in MySQL I have trouble putting the necessary query together. I hope someone will help!

Eugen Rieck · Accepted Answer · 2012-07-09 10:58:53Z

OK, so you basic unit of selection is the tag and the categrory - let's start by creating tables for them

CREATE TABLE tags (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  -- Maybe some other things you want? Tag description?
  UNIQUE INDEX name
);

this gives us a tags table, we can fill it with

INSERT INTO tags(name) VALUES
  ('car'),('blue'),('shiny'),('red'),('fast'),('yellow'),('tall'),('high'),('field')
;

basically the same thing happens with the categories:

CREATE TABLE categories (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  -- Maybe some other things you want? Description?
  UNIQUE INDEX name
);

INSERT INTO categories(name) VALUES
  ('cars'),('people'),('colors'),('landscapes'),('misc')
;

and now we can look at the galleries:

every gallery should belong to exactly 1 category (now), every category can have many galleries. so it is a 1:n relationship from the POV of the category, so we need a pointer field in the n-side of the relation, the galleries. I do not use foreign keys here for brevity only, you are strongly encouraged to consider their use.
every gallery can have many tags, every tag can belong to many galleries - so it is an n:m realtionship and we do not need a field in the tables directly, but we need a join table.
every category can have many tags, every tag can belong to many categories - so it is the same thing

So we do:

CREATE TABLE galleries (
  id int(11) PRIMARY KEY AUTO_INCREMENT,
  name varchar(100)  NOT NULL,
  category` INT NOT NULL,
  -- the other fields you want: description, web_link, gallery_thumb,reg_time
  UNIQUE INDEX(name)
)

Mind the data type INT for the category: It will link to the primary key of the categories table, which is INT also.

And we may not forget the join tables:

CREATE TABLE galleries_tags (
  gallery INT,
  tag INT,
  PRIMARY KEY(tag, gallery),
  KEY(gallery)
);

CREATE TABLE categories_tags (
  category INT,
  tag INT,
  PRIMARY KEY(tag, category),
  KEY(category)
);

The tag field of type INT will link these join tables to the id field of the tags table, while the gallery resp. category fields will link to the id field of the respective tables.

Again we fill the tables with INSERT queries. (To lazy to write them out)

Now you have your data in the database, time to get it out again:

You usecase: User clicks on a category, you want to select the galleries, that have

either been filed under this category
or share a tag with this category

This is quite easy, but it needs a little thinking:

Your target table is categories
For selection by category, you have a direct selector: the category field
For selection by tag, you have to join via the above join tables
For combined selection, you would need to LEFT JOIN, if you don't want to miss category-only selections, but this will kill your performance. So you select separate and UNION the results

Assuming your user selected category 4:

SELECT * FROM galleries WHERE category=4
UNION
SELECT categories.* 
FROM galleries
INNER JOIN galleries_tags ON galleries.id=galleries_tags.gallery
INNER JOIN categories_tags ON categories_tags.tag=galleries_tags.tag
WHERE categories_tags.category=4

and Bob's your uncle.

What does this query do? the first part is simple, thesecond part selects those galleries, that

have tags attached (INNER JOIN galleries_tags ON ...)
which tags have categories attached (INNER JOIN categories_tags ON ...)
which categories have the id 4

Collectives™ on Stack Overflow

PHP - MySQL queries over three tables

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related