Article tags design method, article tags database design method for example __ database

Source: Internet
Author: User
Tags tag name

Article tags design method, article tags database design method examples

The following we take WordPress and Sablog as an example, said that WordPress and Sablog in the tag database structure design and procedures of the differences, I hope we can learn from the article label design methods.

Sablog's Sablog_articles table

' keywords ' varchar not NULL default ',

Keywords field: "," to separate all the tags used in this article

Sablog's sablog_tags table PHP code

1
2
3
4
5
6 7 8
CREATE TABLE IF not EXISTS ' sablog_tags ' (    
' tagid ' int (one) unsigned not NULL auto_increment,    
' tag ' varchar (100) Not null default ',    
' usenum ' int (one) not null default ' 0 ',    
' aids ' text is not NULL,    
PRIMARY KEY  (' TagID ' ),    
KEY ' usenum ' (' usenum ')    
engine=myisam  DEFAULT Charset=utf8  ;

Tag field: Store tag name
Usenum field: The total number of articles that are stored with this tag
Aids field: "," separating all article IDs using this tag

When I show the article tag, it's written like this. PHP code

1
2
3
4
5
6 7 8
$tagdb = Explode (', ', $article [' keywords ']);    
            $articletags = $tmark = ';    
            For ($i =0 $i <count ($tagdb); $i + +) {    
                $tagdb [$i] = Trim ($tagdb [$i]);    
                $articletags. = $tmark. ' <a href= "./?action=tags&item= '. UrlEncode ($tagdb [$i]). '" > '. Htmlspecialchars ($tagdb [$i]). ' </a> ';    
                $tmark = ', ';    
            }    
            $article [' tags '] = $articletags;

Remove the Sablog_articles table keywords field, explode a link

Show the relevant articles of the program part of the PHP code

1
2
3
4
5
6 7 8 9 (15) An-i-am
)
30
	$tags = $comma = '; For ($i =0 $i <count ($tagdb); $i + +) {$tags. = $comma. "'".    
                    Addslashes ($tagdb [$i]). "'";    
                $comma = ', ';    
                $query = $DB->query ("Select Aids from {$db _prefix}tags WHERE tag in ($tags)");    
                $relaids = 0;    
                while ($tag = $DB->fetch_array ($query)) {$relaids. = ', '. $tag [' AIDS '];    
                $relids = Explode (', ', $relaids);    
                Clears the cell of the duplicate value and deletes the current id $relids = Array_unique ($relids);    
                $relids = Array_flip ($relids);    
                Unset ($relids [$articleid]);    
                $relids = Array_flip ($relids);    
                $related _tatol = count ($relids);    
                $relids = Implode (', ', $relids); if ($related _tatol > 1 && $relids!= $articleid){$order = In_array ($options [' Related_order '], array (' Dateline ', ' views ', ' comments '))? $options [    
                    ' Related_order ']: ' Dateline '; $query = $DB->query ("select articleid,title,views,comments from {$db _prefix}articles WHERE visible= ' 1 ' and ArticleID In ($relids) Order by ". $order."    
                    DESC LIMIT ". Intval ($options [' related_shownum ']);    
                    $titledb =array (); while ($title = $DB->fetch_array ($query)) {$title [' title '] = Trimmed_title ($title [' title ']    
                        , $options [' related_title_limit ']);    
                    $titledb [] = $title;    
                    } unset ($title);    
                $DB->free_result ($query); }

Take out the Sablog_articles table keywords field to generate the query conditions for AIDS $query = $DB->query ("Select Aids from {$db _prefix}tags WHERE tag in (' Tag1 ′, ' tag2′, ' tag3′) ');

To get all the article ID containing TAG1,TAG2,TAG3, pay attention to the contents of the AIDS field, above I have explained the AIDS field: to "," separate all the use of this tag article ID

WordPress uses 4 sheets of wp_posts, Wp_terms, Wp_term_relationships, wp_term_taxonomy
WordPress in the new version, the database structure has been adjusted, the structure and name is not the same as before, category and tag merged, the difference in the Wp_term_taxonomy taxonomy field
Category Taxonomy field content is category
Tag then taxonomy the field content to Post_tag
No tag data is stored in the wp_posts
The Wp_terms table is used to store tag and category, structured as follows PHP code

1
2
3
4
5
6 7 8
CREATE TABLE IF not EXISTS ' wp_terms ' (    
' term_id ' bigint not null auto_increment,    
' name ' varchar (+) NOT NULL  Default ',    
' slug ' varchar NOT null default ',    
' Term_group ' bigint (a) NOT null default ' 0 ',    
PRIMARY Key  (' term_id '),    
UNIQUE key ' slug ' (' slug ')    
) Engine=myisam  DEFAULT Charset=utf8;

The term_id in ' wp_terms ' and the term_id in ' wp_term_taxonomy '
The ID in ' wp_posts ' and the OBJECT_ID Association in Wp_term_relationships.
The term_taxonomy_id in ' wp_term_relationships ' and the term_id in ' wp_terms '
First of all, the specific to see the database, skilled programmers, by the database structure, you can know how the program is written

WordPress's tag database structure to be more clearly organized, SABLOG tag database structure is not conducive to maintenance, but for the performance of 2 in the display, I have not done testing.

Free reprint, reprint please specify: reprinted from the Web Development notes www.chhua.com

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.