Again tag cloud. Only now there is no conclusion - but input!

Basically, there are examples of tag cloud output everywhere in nete. I would like to show how I keep the tags themselves in the database and how the tags work.

As I said, we have a table where the tags themselves are stored.

CREATE TABLE `users_tags` (
`id` int (11) unsigned NOT NULL auto_increment,
`text` varchar (255) character set utf8 NOT NULL ,
PRIMARY KEY (`id`),
UNIQUE KEY `txt` (` text`)
) ENGINE = MyISAM DEFAULT CHARSET = utf8 COLLATE = utf8_general_ci;

')
Tamblitz tag links

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 COLLATE = utf8_general_ci;

And the table where the photo records are stored

CREATE TABLE `users_fun_photos` (
`id` int (11) unsigned NOT NULL auto_increment,
`title` varchar (255) default NULL ,
`tags` varchar (255) default NULL , / * Here tags are stored as a string of the form tag1, tag2, tag3 * /
`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 COLLATE = utf8_general_ci;

Pay attention to COLLATE = utf8_general_ci - this means that the comparison for text fields will be case-insensitive.

Now the code itself. Go to where you upload a picture to the server.

/ * We get what the user entered in the field <input type = "text" value = "php, mysql, html, css" name = "tags"> * /
$ photo_tags = $ cms-> ValidatePostVar ( 'tags' );

/ * Next (for me) there is a code to add information to the database about the loaded picture - here we are interested only in the tags field in which tags are added by the line as entered by the user (checking for allowed characters, etc. do it yourself * /
sql_query ( "INSERT INTO" . $ prefix. "_users_fun_photos (id, title, tags, cat_id, file, user_id, date, comments, views) VALUES (NULL, '" . $ photo_title. "'," " . $ photo_tags. "', 1,'" . $ Obj-> GetNewFileName (). "','" . $ Cms-> user_id. "', NOW (), 0, 0)" , $ dbi);

/ * Get the record ID of the uploaded picture * /
$ generated_id = mysql_insert_id ();

/ * And call the processing function of the tags entered by the user, with the necessary parameters * /
$ fun-> TagsDataInsert ($ photo_tags, $ generated_id);

But the magic function itself:

function TagsDataInsert ($ photo_tags, $ photo_id)
{
global $ prefix, $ dbi, $ section_folder, $ cms;

/ * Delete the old tag link to the picture - I use it when editing picture data, that is, this function is also used to handle saving changes in tags for the picture being edited * /
sql_query ( "DELETE FROM" . $ prefix. "_users_fun_photos_tags_data WHERE photo_id = '" . $ photo_id. "'" , $ dbi);

# If the user has entered the tags - go further
if ($ photo_tags)
{
/ * Create an array of entered tags * /
$ tags_array = explode ( ',' , $ photo_tags);
/ * Remove spaces * /
array_walk ($ tags_array, array ($ this , 'trim_value' ));
/ * Pass through the array, and add tags to the tag table - if such a tag already exists, simply update the text field * /
foreach ($ tags_array as $ value )
{
sql_query ( "INSERT INTO„ . $ prefix. “_users_tags (id, text) VALUES (NULL, '" .adsl ($ value ). "') ON DUPLICATE KEY UPDATE text = '" .adsl ($ value . ""' " , $ dbi);
/ * The mysql_insert_id function is good because it returns not only the newly created key id, but also the id returned / /
$ generated_tag_id = mysql_insert_id ();
/ * Write data to the link table * /
sql_query ( "INSERT INTO„ . $ prefix. “_users_fun_photos_tags_data (photo_id, tag_id) VALUES ('" . $ photo_id. "', '" . $ generated_tag_id. "')" , $ dbi);
}
}
}

Let's add tags from the field.

< input type = "text" value = "php, mysql, html, css" name = "tags" >

As a result, in the user_tags table we will have the tags themselves and their id



In the communication table users_fun_photos_tags_data there will be data



Well, in the users_fun_photos table in the tags field, we have tags in the form of php, mysql, html, css strings



And now, why in the users_fun_photos table in the tags field we store the data as a string -

php, mysql, html, css

1 - Someone will say that this is not correct - and to some extent it will be right, but - it’s just easier for me to display tags later when displaying a picture like this, without any problems with table joins:

if (! empty ($ photo_tags)) $ photo_tags = explode ( ',' , $ photo_tags);
$ smarty-> assign ( 'photo_tags' , $ photo_tags);

<div class = "tags" style = "color: # 777777;" > Tags → { foreach from = $ photo_tags item = item} <a title = "{$ item}" href = "index.php? section = Fun & file = search & tag = {$ item} " > {$ item} </a>, {/ foreach } </ div>

2. As can be seen from paragraph 1 - - the search for records with a tag will not be performed by tag_id but by the text of the tag. For what? Just in the image search script by tags there is an opportunity to search not only by tag_id when we click on a tag in the tag cloud - but also by the text of the tag when the user enters the required tag in the input field

/ * Here we just take the data and the volume was entered id tag or just text * /
$ tag = urldecode ($ cms-> ValidateGetVar ( 'tag' ));
$ get_tag_id = ( int ) $ cms-> ValidateGetVar ( 'tag_id' );

/ * Here is a check on what was entered - and ultimately we get the tag id if id is entered and not the text, and vice versa * /
if ($ tag &&! $ get_tag_id)
{
$ res = sql_query ( "SELECT id FROM„ . $ prefix. “_users_tags where text = '" .adsl ($ tag) "' '" , $ dbi);
list ($ get_tag_id) = sql_fetch_row ($ res);
}
else if (! $ tag && $ get_tag_id)
{
$ res = sql_query ( "SELECT text FROM" . $ prefix. "_users_tags where id = '" . $ get_tag_id. "'" , $ dbi);
list ($ tag) = sql_fetch_row ($ res);
}

/ * In the end, anyway, the output of the image with the desired tag will be made by tag_id * /
$ res = sql_query ( “SELECT SQL_CALC_FOUND_ROWS a.user_id, a.user_nick, b.id, b.title, b.tags, b.cat_id, b.file, b.dir, DATE_FORMAT (b.date, '% d. % m.% Y?% H:% i:% s'), b.comments, b.views, b.points FROM " . $ prefix. " _users a, " . $ prefix. " _users_fun_photos b, " . $ prefix. “_users_fun_photos_tags_data with where b.id = c.photo_id and a.user_id = b.user_id and c.tag_id = '” . $ get_tag_id. "' order by b.id DESC limit" . $ offset. "," . $ on_page. "" , $ dbi);

In general about item 2 and storing data as a string in the tags field you can bet :)