I am building a tag cloud at the moment, using PHP and a MySQL database. Inside the database I have got a table called 'pages', which has a column called 'tags'. Tags are in this case separated with line breaks and can contain letters, numbers, hyphens, underscores and periods.

Here is what I have got, so far:

<?
function cloud() {
    global $root, $db;
    // $root contains the root dir of the site, for example 'http://cssbeauty.com/'
    // $db contains an array of details of the database (username, password, prefix, etc)

    db();
    $query = mysql_query('SELECT tags FROM '.$db['prefix'].'pages WHERE tags!=""');
    $rows = mysql_numrows($query);
    mysql_close();

    if ($rows > 0) {
        while($items = mysql_fetch_array($query)) {
            $tagList = explode("\n", $items[0]);

            while(list($key, $value) = each($tagList)){
                if ($value) {
                    $tags[$value] += 1;
                }
            }
        }

        $minval = min(array_values($tags));
        if ($minval > 2)    {
            $minval = 2;
        }
        $maxval = max(array_values($tags));
        if ($maxval < 2)    {
            $maxval = 2;
        }

        echo '<ul>';
        foreach ($tags as $tag => $count) {
            $size = ($count - $minval) / ($maxval - $minval) * 100;

            if ($size > 80) {
                $level = 9;
            } else if ($size > 70) {
                $level = 8;
            } else if ($size > 60) {
                $level = 7;
            } else if ($size > 50) {
                $level = 6;
            } else if ($size > 40) {
                $level = 5;
            } else if ($size > 30) {
                $level = 4;
            } else if ($size > 20) {
                $level = 3;
            } else if ($size > 15) {
                $level = 2;
            } else {
                $level = 1;
            }

            echo '<li><a rel="tag" href="'.$root.'tag/'.$tag.'/?phpMyAdmin=4594f30712f4fabaff6997416810f3f2" class="level'.$level.'">'.$tag.'</a></li>';
        }
        echo '</ul>';
    }
}
?>

What is left to do:

• Sort tags alphabetically.
• Have the ability to set a number which represents the maximum amount of tags that may be displayed: cloud($limit); If the number is 0, all the tags will be displayed. If the number is 1 or higher, for example: 20, the 20 most popular tags will be displayed.

Anyone who can help me with these two? Comments/Adjustments on current code are also welcome :-)

natcasesort ($tags)

before your "foreach statement" for sorting the tags
and

cloud($limit){
...
$offset = "0";
if ($limit == 0) $limit = 18446744073709551615;

$query = mysql_query('SELECT tags FROM '.$db['prefix'].'pages WHERE tags!=""' LIMIT $offset, $limit );
...
}


php & mysql manuals are always handy ;)

>> http://ca.php.net/manual/en/function.natcasesort.php />>> http://dev.mysql.com/doc/refman/5.0/en/select.html

Well, you first problem is that your database is not set up very well for tagging. If you database were configured better, this would be a simple script. Here's the problem:

From what I can tell, you have a table called pages that has a column called tags. This means that if you have 237 pages tagged with sports, you'll have 237 rows in your database containing the sports tag. This is not good. In addition, your tags field can store multiple tags. This is going to make anything you do harder, because it's going to be damn near impossible (if not impossible) to sort the tags in the database.

You understood in your model that there's a relationship between tags and pages, but the relationship was modeled poorly. The relationship between tags and pages is a many-to-many relationship, and the best way to model a many-to-many relationship is with a join table. This may mean more back end work, because you'll have to parse the tags the user gives you before tagging the page in the database, but it's worth it.

A better way to organize your database is to have three tables. Your pages table could remain essentially changed, except you'll remove the tags column. You'll then create a new tags table. The only thing the table needs to store is an ID and the tag itself. Then you'll have a third table that models the many-to-many relationship between tags and pages. Call it pages_tags for the example. This table would have two columns: page_id and tag_id.

Now the database is set up properly. If you have 237 pages tagged with sports, your tags table will have one sports record, and your pages_tags table will have 237 records (each relating the sports tag to a page).

Your query then becomes much easier:

SELECT pages_tags.tag_id, COUNT(pages_tags.page_id) AS 'uses', tags.tag_name
FROM pages_tags
INNER JOIN tags ON pages_tags.tag_id = tags.id
GROUP BY pages_tags.tag_id
ORDER BY COUNT(pages_tags.page_id)
LIMIT 20;

That query will return the 20 most popular tags and the number of times each tag is used (along with the tag id). At this point, the list is sorted in order of popularity. Let's assume you have 10 different font sizes you're going to use in your tag cloud represented by CSS class names level_1 (smallest) to level_10 (largest). Before you do anything to your data, it's in an array like the following:

Array (
[0] => Array(
[tag_id] => ###,
[uses] => ###,
[tag_name] => ###
),
[1] => Array(
[tag_id] => ###,
[uses] => ###,
[tag_name] => ###
),
...,
[19] => Array(
[tag_id] => ###,
[uses] => ###,
[tag_name] => ###
)
)

You should then loop through the array and add a css_class index to each position in the array. Then your array looks like the following:

Array (
[0] => Array(
[tag_id] => ###,
[uses] => ###,
[tag_name] => ###,
[css_class] => '$$$$$'
),
[1] => Array(
[tag_id] => ###,
[uses] => ###,
[tag_name] => ###,
[css_class] => '$$$$$'
),
...,
[19] => Array(
[tag_id] => ###,
[uses] => ###,
[tag_name] => ###,
[css_class] => '$$$$$'
)
)

You can then use PHP's usort function to sort the array by tag_name. At that point, you can loop through the array and print a list item for each index:

foreach ($tags as $tag) {
$this_tag = (object)$tag;
echo("<li><a class=\"{$tag->css_class}\" href=\"/tags/{$tag->tag_name}\">{$tag->tag_name}</a></li>");
}

Hopefully this helps. I know you didn't ask about your database structure, but I think it's kind of at the root of your problem.

You'll have to test the query, because I didn't. I'm fairly sure it'll work, but I didn't feel like creating a database just to test it.

varland: I'd love to see what you would do with a array like that. I'm trying to get about just what you are doing, but have no idea about how I'd handle a array if it has more that 2 properties. (I get it working with only tagName and count, but I need the tagID to avoid non-English characters.)

(sorry for spamming to all you how get this right away)
varland: I'm sure that I'm the one not understanding, I'm not very experienced with coding. But:
I have the 3tables, photo (in my case), photo_has_tag and tag_has_name. The photo_has tag table is just a list of photoIDs and tagIDs, while the tag_has_name is a list of tagIDs and the tag names. It also contains a "is_active"-field, so I can choose to not have certain words (like swearing).

I query what I need (a bit more to be honest):

SELECT *, count(name) AS TagSum FROM photo_has_tag, tag_has_name WHERE photo_has_tag.tid = tag_has_name.tid AND tag_has_name.tactive = 1 GROUP BY name ORDER BY Rand() DESC;

The tagName, the tagID and the count is what I thought I needed. I manage to do this with the tagName and Sum, but come to a full stop at whot to do with the tagID. What I've done is:

while ($myTagsRow = mysql_fetch_array($myTagsQuery)) {
$mytag_cloud[$myTagsRow[name]] = $myTagsRow['TagSum'];
}

$cloud_max = max($mintag_cloud);
$cloud_min = min($mintag_cloud);
$cloud_dist = ($cloud_max - $cloud_min)/3;

foreach($mytag_cloud as $tagname=>$tag){
if ($tag == $cloud_min){
print "<_intentional_a href=\"file.php?tag=".$tid."\" style=\"font-size:0.5em; text-decoration:none;\">".htmlentities($tagname)." < /_intentional_a>\r\n ";
}

elseif ($tag == $cloud_max){
print "<_intentional_a href=\"file.php?tag=".$tid."\" style=\"font-size:5em; text-decoration:none;\">".htmlentities($tagname). " < / _intentional_a>\r\n ";
}

elseif ($tag > ($cloud_max+($cloud_dist*2))) {
print "<_intentional_a href=\"file.php?tag=".$tid."\" style=\"font-size:3em; text-decoration:none;\">".htmlentities($tagname). " < / _intentional_a> \r\n";
}

elseif ($tag > ($cloud_min+$cloud_dist)) {
print "<_intentional_a href=\"file.php?tag=".$tid."\" style=\"font-size:1.6em; text-decoration:none;\">".htmlentities($tagname)." < / _intentional_a> \r\n";
}

else {
print "<_intentional_a href=\"file.php?tag=".$tid."\" style=\"font-size:0.9em; text-decoration:none;\">".htmlentities($tagname)." < / _intentional_a> \r\n";
}
}

This version above does not work, I don't get the tagId ($tid) out of the array, because I don't know how to add that third element in the array (the same tagID). How do I include that?

I'm sorry varland, I didn't get it. But I found a way that works and witch I'm satisfied with, so I'll keep my moth shut and learn. Thanks for all your time and effort.

Yes.