Tag Cloud for PHP + MySQL

Just inspired by the topic - habrahabr.ru/blog/php/48543.html#habracut



Everything is easy to impossible. This is an example of displaying a tag cloud for photos. The code is not perfect, the algorithm is also simple. But the result is pretty kosher.



Tags are stored in the table in the form - id | text

')

CREATE TABLE `users_tags` (

`id` int(11) unsigned NOT NULL auto_increment,

`text` varchar(255) NOT NULL,

PRIMARY KEY (`id`),

UNIQUE KEY `txt` (`text`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8;



And this is a link table.



CREATE TABLE `users_fun_photos_tags_data` (

`photo_id` int(11) unsigned NOT NULL,

`tag_id` int(11) unsigned default NULL,

KEY `tag_id` (`tag_id`),

KEY `photo_id` (`photo_id`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8;





This is the table where the photos themselves sit.



CREATE TABLE `users_fun_photos` (

`id` int(11) unsigned NOT NULL auto_increment,

`title` varchar(255) default NULL,

`tags` varchar(255) default NULL, /* , */

`cat_id` int(11) unsigned NOT NULL default '1',

`file` varchar(30) default NULL,

`dir` varchar(3) default NULL,

`user_id` int(11) unsigned default '1',

`date` datetime NOT NULL default '0000-00-00 00:00:00',

`date_last_comment` datetime default NULL,

`comments` int(11) unsigned NOT NULL default '0',

`views` int(11) NOT NULL default '0',

`points` int(11) default '0',

PRIMARY KEY (`id`),

KEY `views` (`views`),

KEY `user_id` (`user_id`),

KEY `comments` (`comments`),

KEY `cat_id` (`cat_id`),

KEY `title` (`title`),

KEY `tags` (`tags`),

KEY `date` (`date`),

KEY `date_last_comment` (`date_last_comment`),

KEY `points` (`points`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8;



Well, here is the actual function that displays the cloud itself.

function GetAllTagsFromDB ()

{

# This is mine, you may be different

global $ prefix, $ dbi;



$ data = NULL; # There will be output

$ list_tags = array (); # There will be text tags

$ list_ids = array (); # Id tags here



# Specifying maximum and minimum font size

$ max_size = 300; # Maximum font size in percent

$ min_size = 100; #Minimum font size, also in percent



# Select data where quantity - how many times a tag occurs, tag_text - text and tag_id - tag id, respectively

$ res = sql_query ( "SELECT COUNT (a.photo_id) AS quantity, b.text, b.id from„ . $ prefix. "_users_fun_photos_tags_data a," . $ prefix. "_users_tags b where a.tag_id = b.id group by a.tag_id " , $ dbi);

while (list ($ quantity, $ tag_text, $ tag_id) = sql_fetch_row ($ res))

{

$ list_ids [$ tag_id] = $ quantity; # Add id tags to the array

$ list_tags [$ tag_id] = $ tag_text; # Add tag text to array

# Note that the key of the array is the id of the tag

}



# Sort the tag array

asort ($ list_tags);



# Check if array is not empty

if (! empty ($ list_tags))

{

# Choose the maximum and minimum quantity

$ max_qty = max (array_values ​​($ list_ids));

$ min_qty = min (array_values ​​($ list_ids));



# Calculate the difference between the maximum and minimum quantity

$ difference = $ max_qty - $ min_qty;

# It is impossible to divide by zero

if (0 == $ difference)

{

$ difference = 1;

}

# Calculate increment of font size

$ step = ($ max_size - $ min_size) / ($ difference);



# Go through the array of tags

foreach ($ list_tags as $ key => $ value )

{

# Calculate future font size

$ size = ceil ($ min_size + (($ list_ids [$ key] - $ min_qty) * $ step));



$ data. = '<a style = "font-size:' . $ size. '% "Href =" index.php? Section = Fun & file = search & tag_id =' . $ Key. ''> ' . $ List_tags [$ key]. '</a>' ;



}

}

return $ data;

} * This source code was highlighted with Source Code Highlighter .