Tag database structure design (under the tag topic)

Differences between wordpress and sablog in tag database structure design and program

Differences between wordpress and sablog in tag database structure design and program

Sablog_articles table of sablog

'Keyword' varchar (120) not null default '',

Keywords field: Separate all tags used in this article ","

Sablog_tags table of sablog

PHP code

1. Create table if not exists 'sablog _ tags '(
2. 'Tagid' int (11) unsigned not null auto_increment,
3. 'Tag' varchar (100) not null default '',
4. 'Usenum' int (11) not null default '0 ',
5. 'Aids 'text not null,
6. Primary key ('tagid '),
7. KEY 'usenum' ('usenum ')
8. ) ENGINE = MyISAM default charset = utf8;

Tag field: stores the tag name.
Usenum field: total number of articles using this tag
Aids field: Separate all article IDs using this tag ","

This is the case when the document tag is displayed.

PHP code

1. $ Tagdb = explode (',', $ article ['keyword']);
2. $ Articletags = $ tmark = '';
3. For ($ I = 0; $ I
4. $ Tagdb [$ I] = trim ($ tagdb [$ I]);
5. $ Articletags. = $ tmark. '. urlencode ($ tagdb [$ I]).' "> '.html specialchars ($ tagdb [$ I]).'';
6. $ Tmark = ',';
7. }
8. $ Article ['tags'] = $ articletags;

Retrieve the keywords field of the sablog_articles table and generate a link after explode.

Show the program section of the relevant article

PHP code

1. $ Tags = $ comma = '';
2. For ($ I = 0; $ I
3. $ Tags. = $ comma. "'". addslashes ($ tagdb [$ I]). "'";
4. $ Comma = ',';
5. }
6. $ Query = $ DB-> query ("SELECT aids FROM {$ db_prefix} tags WHERE tag IN ($ tags )");
7. $ Relaids = 0;
8. While ($ tag = $ DB-> fetch_array ($ query )){
9. $ Relaids. = ','. $ tag ['AIDS '];
10. }
11. $ Relids = explode (',', $ relaids );
12. // Clear the cells with duplicate values and delete the current ID
13. $ Relids = array_unique ($ relids );
14. $ Relids = array_flip ($ relids );
15. Unset ($ relids [$ articleid]);
16. $ Relids = array_flip ($ relids );
17. ////////
18. $ Related_tatol = count ($ relids );
19. $ Relids = implode (',', $ relids );
20. If ($ related_tatol> 1 & $ relids! = $ Articleid ){
21. $ Order = in_array ($ options ['related _ order'], array ('Dateline ', 'view', 'comments '))? $ Options ['lated _ order']: 'Dateline ';
22. $ Query = $ DB-> query ("SELECT articleid, title, views, comments FROM {$ db_prefix} articles WHERE visible = '1' AND articleid IN ($ relids) order ". $ order. "desc limit ". intval ($ options ['related _ shownum']);
23. $ Titledb = array ();
24. While ($ title = $ DB-> fetch_array ($ query )){
25. $ Title ['title'] = trimmed_title ($ title ['title'], $ options ['related _ title_limit ']);
26. $ Titledb [] = $ title;
27. }
28. Unset ($ title );
29. $ DB-> free_result ($ query );
30. }

Obtain the keywords field of the sablog_articles table and generate the query condition $ query = $ DB-> query ("SELECT aids FROM {$ db_prefix} tags WHERE tag IN ('tag1 ', 'tag2', 'tag3 ')");

In this way, we can get the id of all the articles that contain tag1, tag2, and tag3. Note the content of the aids field. I have described the aids field: separate all the article IDs that use this tag ",".

Wordpress uses four tables wp_posts, wp_terms, wp_term_relationships, wp_term_taxonomy
In the new version of wordpress, the database structure has been adjusted, and the structure and name are different from the previous ones. the category and tag are merged, and the difference is in the taxonomy field in wp_term_taxonomy.
In category, the content of the taxonomy field is category.
The taxonomy field is post_tag.
Wp_posts does not store any tag data
The wp_terms table is used to store tags and category. The structure is as follows:

PHP code

1. Create table if not exists 'WP _ terms '(
2. 'Term _ id' bigint (20) not null auto_increment,
3. 'Name' varchar (55) not null default '',
4. 'Slug' varchar (200) not null default '',
5. 'Term _ group' bigint (10) not null default '0 ',
6. Primary key ('term _ id '),
7. Unique key 'slug' ('Slug ')
8. ) ENGINE = MyISAM default charset = utf8;

The term_id in 'WP _ terms 'is associated with the term_id in 'WP _ term_taxono'my '.
ID in 'WP _ posts' is associated with object_id in wp_term_relationships
Term_taxonomy_id in 'WP _ term_relationships 'is associated with term_id in 'WP _ terms '.
Let's talk about this first. let's take a look at the specific database. skilled programmers can understand how the program is written by using the database structure.

The structure of the wordpress tag database is much clearer, so the structure of the sablog tag database is not very conducive to maintenance, but I have not tested the display performance of the two.