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
Counting occurrences of data
Moderator: General Moderators
- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
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.
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.
So would I. A better database design would eliminate this problem completely.feyd wrote:Personally, I'd disallow duplicate tags to be entered in the first place.
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.
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
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.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.