Jump to content

Retrieving Categories from Database!


gaza165

Recommended Posts

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

		}

		}


 

 

 

Link to comment
https://forums.phpfreaks.com/topic/120408-retrieving-categories-from-database/
Share on other sites

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

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

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

 

 

 

 

 

 

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

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.