Help with tag cloud/MySql query

[nfs2]

Here's my query, i need to display the number of each tag

$sql = "SELECT tag, COUNT(tag) as count FROM journal_tags WHERE journal_id = '$owner' GROUP BY tag ORDER BY count";
$result = mysql_query($sql);
$tag = count($result);
if ($tag > 1) {
$class = 'small_tag';
} else {
$class = 'big_tag';
}

while ($row = mysql_fetch_assoc($result))
{
?>
<?=$tag?><a class="<?=$class?>" href="http://<?=$owner?>.example.com/tags/<?=$row['tag']?>"><?=$row['tag']?></a>&nbsp;
<?php
}

I'd like to assign a class based on the number of times a particular tag has been used, but as you can see im kinda lost as to how to do that

Anyone have any ideas?

[whoisgregg]

I think tag clouds are best done with inline font-size declaration, but that's just me. Hopefully this code will be helpful:

// All your DB connection stuff here
// Then some preferences
$biggest_font_size = 48;
$smallest_font_size = 8;
// To determine relative weight of each tag, we need to know the total number of tags the user has generated
$sql = "SELECT COUNT(tag) as count FROM journal_tags WHERE journal_id ='whoisgregg'"; 
$result = mysql_query($sql);
while ($row = mysql_fetch_assoc($result)) {
$total_count = $row['count'];
}
// Now we get a per tag breakdown of totals
$sql = "SELECT `key`,`tag`,COUNT(tag) as count FROM journal_tags WHERE journal_id ='whoisgregg' GROUP BY tag ORDER BY RAND()"; 
$result = mysql_query($sql);
// remember our preferences above?
$multiplier = $biggest_font_size-$smallest_font_size;
while ($row = mysql_fetch_assoc($result)) {
$tags[$row['key']]['tag'] = $row['tag'];
$tags[$row['key']]['count'] = $row['count'];
// the real trick is this line, we determine the relative value in pxs of each tag group
$tags[$row['key']]['fontsize'] = round(($row['count']/$total_count)*$multiplier)+$smallest_font_size;
}
// you could also output this during the while loop above, but this way you have a loaded up array you can output later in the page
foreach($tags as $tag){
echo '<a href="'.$tag['tag'].'" style="font-size: '.$tag['fontsize'].'px;">'.$tag['tag'].'</a> &nbsp;';
}

Added: It's been so long since I switched to using a class to handle all my DB stuff that I've forgotten how to optimize the PHP. Don't mind the excessive "while ($row = mysql_fetch_assoc" stuff... :/

[nfs2]

Thanks for the code, i'll try it out now.

Im using a class for the tag cloud because i need the CSS editable by the end user for a specific application. That can't be done inline.

Ill let you know if it works

EDIT

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/me/public_html/journal on line 726

Warning: Invalid argument supplied for foreach() in /home/me/public_html/journal on line 733

The problem is with the following query

$sql = "SELECT `key`,`tag`,COUNT(tag) as count FROM journal_tags WHERE journal_id ='$owner' GROUP BY tag ORDER BY RAND()";
$result = mysql_query($sql);
// remember our preferences above?
$multiplier = $biggest_font_size-$smallest_font_size;
while ($row = mysql_fetch_assoc($result)) {
$tags[$row['key']]['tag'] = $row['tag'];
$tags[$row['key']]['count'] = $row['count'];
// the real trick is this line, we determine the relative value in pxs of each tag group
$tags[$row['key']]['fontsize'] = round(($row['count']/$total_count)*$multiplier)+$smallest_font_size;
}
// you could also output this during the while loop above, but this way you have a loaded up array you can output later in the page
foreach($tags as $tag){
echo '<a href="'.$tag['tag'].'" style="font-size: '.$tag['fontsize'].'px;">'.$tag['tag'].'</a> &nbsp;';
}

Now if i get rid of `key`, from the query, it works but only returns one result

Any ideas?

[whoisgregg]

Ahh, sorry about that, I had to make a test table to write this and I assigned a primary key to each row named `key`. Here you go:

$sql = "SELECT `tag`,COUNT(tag) as count FROM journal_tags WHERE journal_id ='$owner' GROUP BY tag ORDER BY RAND()"; 
$result = mysql_query($sql); 
// remember our preferences above? 
$multiplier = $biggest_font_size-$smallest_font_size; 
while ($row = mysql_fetch_assoc($result)) { 
$tags[$row['tag']]['tag'] = $row['tag']; 
$tags[$row['tag']]['count'] = $row['count']; 
// the real trick is this line, we determine the relative value in pxs of each tag group 
$tags[$row['tag']]['fontsize'] = round(($row['count']/$total_count)*$multiplier)+$smallest_font_size; 
} 

[whoisgregg]

Let's say you want 5 classes. You just declare them like so:

$classnames[] = 'smallest';
$classnames[] = 'smaller';
$classnames[] = 'small';
$classnames[] = 'big';
$classnames[] = 'huge';
$multiplier = count($classnames)-1;

And then in your loop, assign the classname like so:

$tags[$row['tag']]['classname'] = $classnames[round(($row['count']/$total_count)*$multiplier)];

And when you output:

echo '<a href="'.$tag['tag'].'" class="'.$tag['classname'].'">'.$tag['tag'].'</a> &nbsp;';

[nfs2]

Awesome, works great

Thanks man

[whoisgregg]

No problem. Thanks for giving me an excuse to finally write some tag cloud code. :)