Retrieving Categories from Database!

[gaza165]

Hi All,

 

I have created a blog and am now developing the tagging system.

 

At present users will fill the tags box in this format

 

science, chemistry

 

I am the pulling these records back from the database but i need to seperate the tags into categories....

 

so on my main blog page they will come out like

 

science(1) * 1 being the amount of times that this tag name has been used in a blog. so there could be 2 blogs with the tag science in it.

 

chemisty(4) and so on

 

i am having difficulty in counting the amount of times that the tag has been used.

 

at the moment i am doing a piece of code that searches through the table and gives me a list of all the tag names that have been used in the blog...

 

After this point i am lost as to what i am doing!!

 

Any advice or guidance would be greatly appreciated.

 

thanks

 

Garry

 

THIS IS THE CODE I HAVE SO FAR

 

include ('dbconnect/dbconnect.php');

$result = mysql_query("SELECT * FROM blog");
while($row = mysql_fetch_array($result))
		{

		$string = $row['tags'].", ";

		$tagarray = explode(", ", $row['tags']); // i am seperating the tags by commos and putting them in an array

		foreach ($tagarray as $category) {

			echo $category."<br/>"; // this echos each tag as a single variable

		}

		}

 

 

 

[JonnoTheDev]

you need to select a count from you database table something like:

SELECT COUNT(t.tagId) AS tagCount, c.categoryName FROM tags t, categories c WHERE t.tagId=c.tagId GROUP BY c.categoryId ORDER BY c.categoryName ASC

Would give you i.e.:

categoryName (3)

 

in my fictitious database

[gaza165]

my database structure looks like this

 

TABLE NAME = "BLOG"

 

id = 1

title = First Blog

body = BODY OF THE BLOG

blog_created = date/time of blog creation

tags = quirkies, science, jetpack

 

can you write me a sql string for use with that function!!

 

thanks

 

Garry

[Mchl]

It would be better if you stored tags in separate table. That would both make things easier, and improve performance (might not be a concern with small base)

[gaza165]

Would you be able to give me a basic structure for the tags table

 

i assume it would be something like

 

tag_id

blog_id to reference to the correct blog

tagname

 

 

 

[Mchl]

Yup. Something along these lines.

Then you can do:

SELECT tagname, COUNT(tagname) AS count FROM tags GROUP BY tagname

[gaza165]

Would there be a new row for every tag i want to add??

 

so i have

 

table name: blog_tags

 

tag_id = 1    ---- auto increment -----

blog_id = 1    ---- refrences to an existing blog in my db -----

tag_name = science

 

tag_id = 2    ---- auto increment -----

blog_id = 1    ---- refrences to an existing blog in my db -----

tag_name = tortoise

 

 

 

 

 

 

[Mchl]

That would be like it.

You could drop tag_id column and create PRIMARY index on blog_id and tag_name

[JonnoTheDev]

This is incorrect you need a joining table between your tags and blogs for normalisation so:

 

blogs

-------

blog_id

title

body

 

tags

-------

tag_id

tag

 

blogs_to_tags

--------------

id

tag_id

blog_id

 

 

So in the blogs_to_tags table you would have records like:

1 5 7

2 1 7

3 9 7

 

blog_id 7 contains the tags 5,1, and 9

[Mchl]

That would be the ideal handbook solution, but personally I think it's not needed for such a task.