How is 'mapping' between multiple MySQL tables done using PHP? (And what is it called?)

General PHP Help
#1

I’m trying to implement a tagging system on a project. If a user posts something on my website, they should ‘tag’ (categorize) the item appropriately. So I came across this topic on SO: [Best practise for storing tags in a database]

So the first step is the database design i.e. create 3 tables, one for the post details, one for the tag details, and one to map the post and tag tables.

My question is, how would you query the tables if you wanted to search the database for something like, “10 turtles in a row” (title) and “turtles, gimmicks, morals” as the tags, with the third table mapping them? What is this process called, other than a 1:* (one-to-many) relational scheme?

I tried a variety of SELECT statements which were not looking optimal. I don’t even know what to search for (I have a range of database books on my shelf). Can anyone help?

#2

You mean Joins?

It isn’t done in php, it’s done in the query. Other than that, I don’t know what you are asking.

#3

Example, if someone posts an image on Instagram and uses the title “flower wallpaper” with the tags “flower” and “grass”, and another user searches the website for “grass”, how do I build a query so that the “flower wallpaper” will appear on the results page because its associated with the tag “grass” ?

Maybe I am not using the standard terminology… By tags, I mean those category filter thingies you find on most modern websites, even StackOverflow uses them, whenever you create a post you can specify which tags the post should be listed under… So I want to implement a tag system on my project. I’ve got the pieces of the puzzle, but I have no idea how to put them together.

Basically I’m asking how the query looks when I want to search for a certain title and tag in the database. I mentioned PHP because the query will be well integrated with the user’s input.

(I wish there was a way I could learn the terminology so my questions don’t look so silly.)

#4

You had it correct. It is a one to many relationship

One wallpaper has many tags.

So, you have your record in the primary table and in the joining table it links to another table that lists tags.

http://sqlfiddle.com/#!9/eca6f7/2

1 Like
#5

And congratulations, joins are the actual power behind relational databases like MySQL. If you spend a little time with joins and learn your way around them you have made a very important learning step.

2 Likes
#6

Wow, how did you do that?

This is what I was struggling to figure out:

insert into title_tags (title_id, tag_id) VALUES
(1,1),
(1,2),
(2,2),
(3,3)

First time I see this type of insert. Maybe I need to learn a lot more about FKs.
And this saved my life:

SELECT 
  i.title
 FROM images i
 INNER JOIN 
   title_tags tt
   ON i.id = tt.title_id
   INNER JOIN
   tags t
   ON tt.tag_id = t.id
   WHERE t.name LIKE '%grass%'

Thanks so much @astonecipher :slight_smile:

#7

I think I know what you mean. The first time I learnt about joins was right here on this forum, and my knowledge vastly escalated from there. Although I’m still a beginner, I completed a Mikrotik website project and the client is really happy ^.^

Sponsor our Newsletter | Privacy Policy | Terms of Service