Structure for "tag" cloud?

[jason2]

I want to "tag" some database entries (MySQL) with single word tags, then be able to search for posts that have certain tags. What's the best way to store tags in a database so I can easily search by tag through PHP?

Right now I insert them into one column, each tag seperated by commas...but I want to be able to search all entries for one specific tag...looking for any pointers on this. Thanks

[utexas_pjm]

You want a schema like this:

blog_entries {entry_id, title, date, content}
tags {tag_id, entry_id, tag_label}

Then you can query like this:

[code]
SELECT b.*, t.tag_label FROM tags AS t LEFT JOIN blog_entries AS b USING (entry_id) WHERE tag_label = 'foo';
[/code]

Best,

Patrick

[jason2]

I don't know why I didn't think of that, I'm using the same sort of idea for my commenting system...*dork*. Although, did you just make up that SQL query or is that legit? lol

[pocobueno1388]

Or you could just have 1 row called "tags" and have them inserted like this "tag1, tag2, tag3, tag4" Then use explode() to retreive them.

[jason2]

Thats exactly what I'm doing, but it won't work. If I want to search for all entries tagged with "tag3", I can't go through and explode every tag string in the database looking for what I want.

[utexas_pjm]

[quote]
Although, did you just make up that SQL query or is that legit? lol
[/quote]

I didn't test it, but it *should* work. ;)

[quote]
Or you could just have 1 row called "tags" and have them inserted like this "tag1, tag2, tag3, tag4" Then use explode() to retreive them.
[/quote]

If you did this you would not be able to efficiently query by tag_label which is, I'm assuming, what the poster is after. 

Best,

Patrick

[jason2]

utexas: can you please explain the SQL statement to me? I am not familiar with using that dot operator, like when you have "b.*, t.tag_label". Also, the "AS" parts are confusing as well.

[utexas_pjm]

[quote]
I am not familiar with using that dot operator, like when you have "b.*, t.tag_label".
[/quote]

When two or more tables are involved in a single query you must explicitly define which table the columns are in.  To illustrate this, consider the following trivial example:

You have two tables:

table_a {userid, name}
table_b {addressid ,userid, address}

If you wanted to query the two tables and get only the name and address columns you would so with the following query

[code]
SELECT table_a.name, table_b.address
FROM table_a, table_b
WHERE table_a.userid = table_b.userid;
[/code]

You can see that we identified the columns in this manner <table name>.<column name>.  In the WHERE clause if we had not included the table name before referencing userid MySQL would not know which userid column we were referring to, the one in table_a or the one in table_b.

[quote]
Also, the "AS" parts are confusing as well.
[/quote]

The AS syntax is no big deal, it's simply a way to alias table names.  The preceding example could be rewritten as follows:

[code]
SELECT a.name, b.address
FROM table_a AS a, table_b as B
WHERE a.userid = b.userid;
[/code]

We told MySQL to treat all instances of a as table_a and b as table_b.  Not necessary, but this is sometimes useful shorthand.

So to recap the original example:

[code]
--Select everything from the blog_entries table and tag_label from the tags table
SELECT b.*, t.tag_label         
-- treat t as tags
FROM tags AS t
-- treat b as blog_entries                   
LEFT JOIN blog_entries AS b
-- join tags and blog_entries by their entry_id
-- ON(t.entryid = b.entryid) would accomplish the same thing.
USING (entry_id)
-- Only return results where tag_label is equal to "foo".
WHERE t.tag_label = 'foo';
[/code]

I hope this was of some help.  Let me know if you have any additional questions.

Best,

Patrick

[jason2]

Wow! That was a great response, thanks a ton. I get it now.

Upon further thought, I really do not need the ID column for the "tags" table, do I? All I need is what tag is associated with which entry, and thats enough uniqueness to find which posts have which tags. If I'm missing something, though, let me know.

[effigy]

To avoid duplicating tag names, create two separate tables that are linked together by a third.

[tt]
Entry
------
id
stuff

Tag
------
id
name

Entry_Tag_Rel
--------------
entry_id
tag_id
[/tt]

[utexas_pjm]

[quote]
Upon further thought, I really do not need the ID column for the "tags" table, do I? All I need is what tag is associated with which entry, and thats enough uniqueness to find which posts have which tags. If I'm missing something, though, let me know.
[/quote]

Yes, the tag name should be enough.

[quote]
To avoid duplicating tag names, create two separate tables that are linked together by a third.
[/quote]

This is generally the best practice, however given that you are only associating a tag name with each relationship I don't think you will gain much by implementing a look up table.

Best of luck,

Patrick