Counting occurrences of data

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
7colors
Forum Newbie
Posts: 3
Joined: Tue Aug 22, 2006 6:48 am

Counting occurrences of data

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
GM
Forum Contributor
Posts: 365
Joined: Wed Apr 26, 2006 4:19 am
Location: Italy

Post 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.
7colors
Forum Newbie
Posts: 3
Joined: Tue Aug 22, 2006 6:48 am

Post 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.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
7colors
Forum Newbie
Posts: 3
Joined: Tue Aug 22, 2006 6:48 am

Post by 7colors »

Yes, I created a new table and everything works nicely.
Thanks again. This thread is resolved.
Post Reply