Help with array. Searching/Counting tags

[DBHostS]

Hello, i need some advice here, i have a database with tags in it.

Here is the database:

CREATE TABLE `tags` (
`parentid` int(5) NOT NULL,
`tags` varchar(255) NOT NULL,
`section` varchar(255) NOT NULL,
`categoryID` int(4) NOT NULL default '0',
PRIMARY KEY (`parentid`)
) TYPE=MyISAM;

The user can enter multiple tags by using the space button as the delimiter.
Now what i am trying to do is some sort of tag cloud.

What i am trying to do:
I am trying to pull the tags from the tags field. search through each row of tags to see if there are any duplicate words. And if there are any duplicate words count them and make another array.
Like so: array=># and only print one if there are any dups. i would like to then take those numbers and change the text sizes the higher the number means bigger the text size.

For example:

row[1] = "Test Again Testing Code";
row[2] = "Home Again Test PHP";

Test => 2
Again => 2
Home => 1
Testing=> 1
PHP=> 1
Code=> 1

Here is what i have so far: I just want to make sure i am heading in the right direction. i am looking at the array_search in the array functions on php.net ... Is this the way to go?

function showTagCloud (){
	global $database;
	$tagCounts = array(); 

$q = "SELECT * FROM ".TBL_TAGS." GROUP BY parentid";
$result = $database->query($q);

$i = 0;

while($row = mysql_fetch_array($result)){

	$allTags = explode(" ", $row['tags']);	
	foreach($allTags as $tags => $word){
	   echo $allTags[$tags] = trim($word); //Test to make sure it works
}
	$i++;
}

}

Right now as you can see i am not too far. All i am doing is getting the tags. and exploding them to another array which i can get by $allTags[0] or $allTags[1]. And then all i do now is just print them out to make sure this code works.

Am i going about this the right way?

Thanks in advance

[DBHostS]

would it be a better idea to insert the data into the tag database separately?

check for dups before the insert, also make a new field in the database as 'numbers' ??

i would then just search the database for dups then add to the numbers field each time there is a dup if not enter a new record for that tag?

Does this sound right? did it make any sense? haha i just thought of this.

[bretticus]

Well, right off the bat you could save some major space by making a normalized tags table. That is, insert each word into a table only once. Something like this...

If a word already exists, create a tag record with a reference to the primary key for that word. If it doesn't exist, insert the word into the table a create the tag record with the new auto-incremented primary key. Form there you can do a COUNT(*) in your query with a GROUP BY and get the word with it's corresponding count also.

[bretticus]

ha, you must have been finishing up with your post as I started writing mine. As you know, I agree with you 😉

[DBHostS]

bretticus;10886505 wrote:

ha, you must have been finishing up with your post as I started writing mine. As you know, I agree with you 😉

awesome thanks for the reply.

[DBHostS]

bretticus;10886504 wrote:

Well, right off the bat you could save some major space by making a normalized tags table. That is, insert each word into a table only once. Something like this...

If a word already exists, create a tag record with a reference to the primary key for that word. If it doesn't exist, insert the word into the table a create the tag record with the new auto-incremented primary key. Form there you can do a COUNT(*) in your query with a GROUP BY and get the word with it's corresponding count also.

hrmmm....

i am trying to see how this would work with my system now.

I have a white paper section and blog section.

When the user enters in tags it does the following:
it inserts the blog/whitepaper id into the database. (parentid)
tags go into (tag)
blog or white depending on the section go into (section)
category goes into (categoryID)

With the new code and database i will have it like this.

CREATE TABLE `tags` ( 
`tagid` int(5) NOT NULL auto_increment,
`parentid` int(5) NOT NULL, 
`tag` varchar(255) NOT NULL, 
`section` varchar(255) NOT NULL, 
`categoryID` int(4) NOT NULL default '0', 
PRIMARY KEY (`parentid`) 
) TYPE=MyISAM; 

Will i still enter the blog/white paper id into the (parentid) field?

if a value exsists in parentid just append to the end of it?

so the database could look something like this:
tagid: 1
parentid: 20, 25, 45, 20, 25 (blogs and white papers can have the same id)
tag: Test
section: ??
categoryID: 5, 2,

but then i dont see how i can make the section or categoryID field work.

Any ideas?

The main idea here is so the user can enter in multiple tags, in either the blog or white paper section.

I am going to want to search it as well...(both sections and each section individually)

[bretticus]

Not real sure I comprehend the scope of your project, but I would have a many-to-many relationship instead of a one-to-many relationships:

CREATE TABLE `blogs` (
  `blogid` int(11) unsigned NOT NULL auto_increment,
  `bogtitle` varchar(255) NOT NULL,
  PRIMARY KEY  (`blogid`)
) ENGINE=InnoDB;


CREATE TABLE `blogtags` (
  `blogid` int(10) unsigned NOT NULL,
  `tagid` int(10) unsigned NOT NULL,
  UNIQUE KEY `blogid` (`blogid`,`tagid`)
) ENGINE=InnoDB;


CREATE TABLE `tags` (
  `tagid` int(10) unsigned NOT NULL auto_increment,
  `tag` varchar(50) NOT NULL,
  PRIMARY KEY  (`tagid`)
) ENGINE=InnoDB ;

[DBHostS]

bretticus;10886509 wrote:

Not real sure I comprehend the scope of your project, but I would have a many-to-many relationship instead of a one-to-many relationships:

CREATE TABLE `blogs` (
  `blogid` int(11) unsigned NOT NULL auto_increment,
  `bogtitle` varchar(255) NOT NULL,
  PRIMARY KEY  (`blogid`)
) ENGINE=InnoDB;


CREATE TABLE `blogtags` (
  `blogid` int(10) unsigned NOT NULL,
  `tagid` int(10) unsigned NOT NULL,
  UNIQUE KEY `blogid` (`blogid`,`tagid`)
) ENGINE=InnoDB;


CREATE TABLE `tags` (
  `tagid` int(10) unsigned NOT NULL auto_increment,
  `tag` varchar(50) NOT NULL,
  PRIMARY KEY  (`tagid`)
) ENGINE=InnoDB ;

Ok i gotcha.

Right now i have it setup like so if this helps explains it:

CREATE TABLE `blog_article` (
`artid` int(5) NOT NULL auto_increment,
`name` varchar(255) NOT NULL default '',
`title` varchar(255) NOT NULL default '',
`comments` text NOT NULL, 
`date_posted` datetime NOT NULL default '0000-00-00 00:00:00',
`categoryID` int(4) NOT NULL default '0',
`artchild` int(5) NOT NULL default '0',
`views` int(11) NOT NULL default '0',
`edit` datetime NULL default 'NULL',
PRIMARY KEY (`artid`)
) TYPE=MyISAM AUTO_INCREMENT=30 ;


CREATE TABLE `blog_categories` (
`catid` int(5) NOT NULL auto_increment,
`category` varchar(255) NOT NULL default '',
`catdesc`  VARCHAR(255) NOT NULL,
PRIMARY KEY (`catid`)
) TYPE=MyISAM AUTO_INCREMENT=3 ;


CREATE TABLE `wp_papers` (
`paperid` int(5) NOT NULL auto_increment,
`name` varchar(255) NOT NULL default '',
`title` varchar(255) NOT NULL default '',
`comments` text NOT NULL, 
`date_posted` datetime NOT NULL default '0000-00-00 00:00:00',
`categoryID` int(4) NOT NULL default '0',
`paperchild` int(5) NOT NULL default '0',
`views` int(11) NOT NULL default '0',
`downloads` int(11) NOT NULL default '0',
`edit` datetime NULL,
PRIMARY KEY (`paperid`)
) TYPE=MyISAM AUTO_INCREMENT=30 ;


CREATE TABLE `wp_categories` (
`catid` int(5) NOT NULL auto_increment,
`category` varchar(255) NOT NULL default '',
`catdesc`  VARCHAR(255) NOT NULL,
PRIMARY KEY (`catid`)
) TYPE=MyISAM AUTO_INCREMENT=3 ;


CREATE TABLE `tag_sys` (
`parentid` int(5) NOT NULL,
`tags` varchar(255) NOT NULL default '',
`section` varchar(255) NOT NULL default '',
`categoryID` int(4) NOT NULL default '0',
PRIMARY KEY (`parentid`)
) TYPE=MyISAM;

Basically i have a blog, and a white papers section (downloads for pdfs) and i want to be able to have a tag system for both. i guess i will have to make a tag db for each section (blog/whitepapers)?

I will do a little changing and see if i cant come up with this.

I will post back tomorrow. Thanks for your help. 🙂

Any more help would be great! Thanks again

[DBHostS]

here is my new structure:

CREATE TABLE `tag_sys` (
`tagid` int(11) NOT NULL auto_increment,
`tag` varchar(255) NOT NULL default '',
`count` varchar(255) NOT NULL default '',
PRIMARY KEY (`tagid`)
) TYPE=MyISAM;


CREATE TABLE `blog_tags` (
`mainid` int(5) NOT NULL,
`tagid` int(11) NOT NULL,
`categoryID` int(4) NOT NULL default '0',
PRIMARY KEY (`mainid`)
) TYPE=MyISAM;


CREATE TABLE `wp_tags` (
`mainid` int(5) NOT NULL,
`tagid` int(11) NOT NULL,
`categoryID` int(4) NOT NULL default '0',
PRIMARY KEY (`mainid`)
) TYPE=MyISAM;

does this look like it will work better?

a user is about to submit a new blog entry. they enter 5 tags.

article goes into the artticle db.

blogid:25 
article: <article>
catid: 4    

Tags: (PHP, Code, HTML, Test, Help)

Tags get put into the tag sys db like so:
tagid: 1      tagid: 2       tagid: 3       tagid: 4       tagid: 5
tag: PHP    tag: Code    tag: HTML    tag: Test    tag: Help
count: 1     count: 1      count: 1       count: 1     count: 1

Blog tag DB.

since it was a blog the blog tag db will be updated like so:
mainid: 25  

tagid: 1, 2, 3, 4, 5    

categoryID: 4    

Now if another article is entered it with the same tags it will add to the count field in the tag db.

Also if a white paper is entered into the db. the main white paper article will go into the wp DB. (lets say that id is 45 with a categoryid of 6)

then the tags into the tag db. like so:
Tags: (Download, White Paper, PDF, Test, Help)

Tags get put into the tag sys db like so:
tagid: 6                   tagid: 7                  tagid: 8     tagid: 4       tagid: 5
tag: Download       tag: White Paper          tag: PDF    tag: Test    tag: Help
count: 1                  count: 1         count: 1     count: 3     count: 3

White Paper tag DB.

since it was a white paper the wp tag db will be updated like so:
mainid: 45  

tagid: 6, 7, 8, 4, 5    

categoryID: 6    

Please let me know if this seems like its the right way to go about this.

i have two sections where a user can eneter tags into. i want it to be pretty much the same way this forum does it with tags. but instead of me just doing it for my blog system, id like to use it for the white paper section as well.

Thanks again for your help.

[DBHostS]

Do i even need to use a count field?

if the tag is used twice in two different articles should i just enter the tag in the db again? and i can count how many times that tag is used by the COUNT in mysql?

I just need a little clarification here, i am getting confused on how i should go about doing this..

Any help is great!

Thanks

[DBHostS]

any advice on my last two posts? just need a little push here. thanks