Page 1 of 1

Counting occurrences of data

Posted: Tue Aug 22, 2006 7:22 am
by 7colors
I have been reading a lot and I'm more confused then at the beginning.

I have a table into which I put posts.
There are rows for the post id, title, text, date and a row for the tags I add to the post.
The tags are separated by a comma (ex: art,web,digital).
I have many posts in the database, sometimes the tags are repeated.
I need to know how many of a certain tags there are (to make a tag cloud. For example: how many "web" tags I have?).
Is there a way to perform a count with the data stored like that or I have to store the data in a different way? If the latter is true, how?
I use php to display the data.
So far I can get all the tags into an array, use array_unique and obtain all the single tags used, but to count how many times each of them occurs, I'm going mental!! Tried substr_count but "web" gets counted twice if there is a "website" tag. So I thought maybe I can do it using MySQL commands. Anybody knows how?
Thanks

Posted: Tue Aug 22, 2006 7:49 am
by feyd
There's array_count_values()

As for a MySQL native way, FIND_IN_SET() can be used to see if a particular string appears in the stored tags. Personally, I'd disallow duplicate tags to be entered in the first place.

Posted: Tue Aug 22, 2006 8:53 am
by GM
feyd wrote:Personally, I'd disallow duplicate tags to be entered in the first place.
So would I. A better database design would eliminate this problem completely.

You need a table for posts, with primary key Post_Id, and a table for Post Tags, with primary key (Post_Id, Tag_Id).

Each post creates 1 row in your posts table, and one row for each tag associated to it in your Post Tags table.

Then you can do a simple "SELECT COUNT(*) FROM Post_Tags WHERE Tag_Id = 'whatever'"; To find how many of each tag you've got.

Posted: Tue Aug 22, 2006 11:55 pm
by 7colors
I cannot disallow duplicate tags. Some posts have similar content.
I suppose the problem really is the database design. I hoped I didn't need to add another table just for tags but it seems as the best solution.
Problem solved.
Thanks.

Posted: Wed Aug 23, 2006 12:00 am
by RobertGonzalez
GM wrote:So would I. A better database design would eliminate this problem completely.

You need a table for posts, with primary key Post_Id, and a table for Post Tags, with primary key (Post_Id, Tag_Id).

Each post creates 1 row in your posts table, and one row for each tag associated to it in your Post Tags table.

Then you can do a simple "SELECT COUNT(*) FROM Post_Tags WHERE Tag_Id = 'whatever'"; To find how many of each tag you've got.
I like this idea a lot better than storing tags as strings. I think you are doing the right thing 7colors, by going to this type of setup.

Posted: Wed Aug 23, 2006 8:08 am
by 7colors
Yes, I created a new table and everything works nicely.
Thanks again. This thread is resolved.