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