Structure for "tag" cloud?

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

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

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

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.

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.

[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

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.

[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

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.

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]

[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