SQL statements with good performance

To obtain related articles using SQL, do not simply use select * from article where catid = ** and aid <> **. In this way, the relevant articles obtained by each article are fixed, not conducive to experience and seo.

We recommend that you obtain the first and last articles of the current article. In this way, each article has the opportunity to be displayed. This is an algorithm used to write articles related to Dai Ruibo.

SQL reference example (php language ):

$ RArt1 = $ db-> query ('select * from article where catid = $ catid and aid <$ aid order by aid desc limit 0, 4 ');
$ RArt2 = $ db-> query ('select * from article where catid = $ catid and aid> $ aid order by aid asc limit 0, 4 ');

If (! Empty ($ rArt1 )&&! Empty ($ rArt2 )){
$ RelationArt = array_merge ($ rArt1, $ rArt2 );
}
Else {
$ RelationArt = empty ($ rArt1 )? $ RArt2: $ rArt1;
}

Obtain the eight adjacent articles of the current article. When the article is in the last or last article, it only contains the first or last four articles. In fact, you can make a judgment here and let it take up to eight relevant articles. This depends on your own needs.


Comparison of five methods for WordPress code implementation

Many WordPress plug-ins can implement the functions of relevant articles. The advantage of the plug-in is that the configuration is simple, but it may have a slight impact on the speed of the website, therefore, many people prefer to use code to implement the required functions, but once again, code implementation also has its disadvantages, that is, complicated configuration, people who do not understand the code are completely confused or can only copy others' code. It is better to use plug-ins.

Here I have compiled several methods to use code to implement relevant articles. This will detail the functions of each part of the code and how to customize the functions you want. I hope this will be helpful to you, if you have any questions, please comment on this article. I will reply to you in time. Before getting started, it should be noted that the HTML code format output by all of the following methods is in the following format. You can modify it as needed:

<Ul id = "xxx">
<Li> * <a title = "article title 1" rel = "bookmark" href = "article link 1"> Article title 1 </a> </li>
<Li> * <a title = "article title 2" rel = "bookmark" href = "article link 2"> Article title 2 </a> </li>
......
</Ul>

Method 1: tag-related

First, obtain all the tags of the article, and then obtain n articles under these tags. Then these n articles are related to the article. This method is used for WordPress-related article plug-ins. The following is the implementation code:

<Ul id = "tags_related">
<? Php
Global $ post;
$ Post_tags = wp_get_post_tags ($ post-> ID );
If ($ post_tags ){
Foreach ($ post_tags as $ tag ){
// Obtain the tag list
$ Tag_list []. = $ tag-> term_id;
  }

// Randomly retrieve a tag from the tag list
$ Post_tag = $ tag_list [mt_rand (0, count ($ tag_list)-1)];

// This method uses the query_posts () function to call related articles. The following lists the parameters.
$ Args = array (
'Tag _ in' => array ($ post_tag ),
'Category _ not_in '=> array (NULL), // category ID not included
'Post _ not_in '=> array ($ post-> ID ),
'Showposts' => 6, // display the number of related articles
'Caller _ get_posts' => 1
);
Query_posts ($ args );

If (have_posts ()){
While (have_posts ()){
The_post (); update_post_caches ($ posts);?>
<Li> * <a href = "<? Php the_permalink ();?> "Rel =" bookmark "title =" <? Php the_title_attribute ();?> "> <? Php the_title ();?> </A> </li>
<? Php
    }
  }
Else {
Echo '<li> * no related articles </li> ';
  }
Wp_reset_query ();
}
Else {
Echo '<li> * no related articles </li> ';
}
?>
</Ul>

Usage: "excluded Category ID" indicates that the articles under this category are not displayed in the relevant articles. You can change the NULL value of the same row to the ID of the document category, multiple IDs are separated by commas. Because only six related articles are displayed here, no matter how many values are assigned to the tag _ in parameter of query_posts (), only six articles under one tag are displayed, except the first tag, the second tag, and the third tag ...... Therefore, if this article has multiple tags, we will randomly obtain the id of a tag, assign the tag _ in parameter, and obtain the six articles under the tag.

Method 2: Classification

This method is to obtain the Category id of the article and then the articles under the category to obtain the relevant articles.

<Ul id = "cat_related">
<? Php
Global $ post;
$ Cats = wp_get_post_categories ($ post-> ID );
If ($ cats ){
$ Args = array (
'Category _ in' => array ($ cats [0]),
'Post _ not_in '=> array ($ post-> ID ),
'Showposts' => 6,
'Caller _ get_posts' => 1
);
Query_posts ($ args );

If (have_posts ()){
While (have_posts ()){
The_post (); update_post_caches ($ posts);?>
<Li> * <a href = "<? Php the_permalink ();?> "Rel =" bookmark "title =" <? Php the_title_attribute ();?> "> <? Php the_title ();?> </A> </li>
<? Php
    }
  }
Else {
Echo '<li> * no related articles </li> ';
  }
Wp_reset_query ();
}
Else {
Echo '<li> * no related articles </li> ';
}
?>
</Ul>

Method 3: tag-related, obtained by SQL

The principles and methods for obtaining relevant articles are similar. However, when obtaining articles, you can directly read the database using SQL statements, so that you can obtain 6 relevant article records at random, instead of the WordPress function query_posts ().

<Ul id = "tags_related">
<? Php
Global $ post, $ wpdb;
$ Post_tags = wp_get_post_tags ($ post-> ID );
If ($ post_tags ){
$ Tag_list = '';
Foreach ($ post_tags as $ tag ){
// Obtain the tag list
$ Tag_list. = $ tag-> term_id .',';
    }
$ Tag_list = substr ($ tag_list, 0, strlen ($ tag_list)-1 );

$ Related_posts = $ wpdb-> get_results ("
Select distinct id, post_title
FROM {$ wpdb-> prefix} posts, {$ wpdb-> prefix} term_relationships, {$ wpdb-> prefix} term_taxonomy
WHERE {$ wpdb-> prefix} term_taxonomy.term_taxonomy_id = {$ wpdb-> prefix} term_relationships.term_taxonomy_id
And id = object_id
AND taxonomy = 'post _ tag'
AND post_status = 'Publish'
AND post_type = 'post'
AND term_id IN (". $ tag_list .")
And id! = '". $ Post-> ID ."'
Order by rand ()
LIMIT 6 ");
// In the code above, only 6 related articles are obtained.
// Modify the number 6 to modify the number of articles you want

If ($ related_posts ){
Foreach ($ related_posts as $ related_post ){
?>
<Li> <a href = "<? Php echo get_permalink ($ related_post-> ID);?> "Rel =" bookmark "title =" <? Php echo $ related_post-> post_title;?> "> <? Php echo $ related_post-> post_title;?> </A> </li>
<? Php}
    }
Else {
Echo '<li> no related articles </li> ';
    }
}
Else {
Echo '<li> no related articles </li> ';
}
?>
</Ul>

Method 4: Classification

The principle of getting related articles is similar to Method 2. However, when getting articles, you can directly read the database using SQL statements, so that you can obtain 6 related article records at random, instead of the WordPress function query_posts ().

<Ul id = "cat_related">
<? Php
Global $ post, $ wpdb;
$ Cats = wp_get_post_categories ($ post-> ID );
If ($ cats ){
$ Related = $ wpdb-> get_results ("
SELECT post_title, ID
FROM {$ wpdb-> prefix} posts, {$ wpdb-> prefix} term_relationships, {$ wpdb-> prefix} term_taxonomy
WHERE {$ wpdb-> prefix} posts. ID = {$ wpdb-> prefix} term_relationships.object_id
AND {$ wpdb-> prefix} term_taxonomy.taxonomy = 'Category'
AND {$ wpdb-> prefix} term_taxonomy.term_taxonomy_id = {$ wpdb-> prefix} term_relationships.term_taxonomy_id
AND {$ wpdb-> prefix} posts. post_status = 'Publish'
AND {$ wpdb-> prefix} posts. post_type = 'post'
AND {$ wpdb-> prefix} term_taxonomy.term_id = '". $ cats [0]."'
AND {$ wpdb-> prefix} posts. ID! = '". $ Post-> ID ."'
Order by rand ()
LIMIT 6 ");

If ($ related ){
Foreach ($ related as $ related_post ){
?>
<Li> * <a href = "<? Php echo get_permalink ($ related_post-> ID);?> "Rel =" bookmark "title =" <? Php echo $ related_post-> post_title;?> "> <? Php echo $ related_post-> post_title;?> </A> </li>
<? Php
    }
  }
Else {
Echo '<li> * no related articles </li> ';
  }
}
Else {
Echo '<li> * no related articles </li> ';
}
?>
</Ul>

Method 5: Author-related

This method is to obtain other articles from the author of this article to act as relevant articles. The code is as follows:

<Ul id = "author_related">
<? Php
Global $ post;
$ Post_author = get_the_author_meta ('User _ login ');
$ Args = array (
'Author _ name' => $ post_author,
'Post _ not_in '=> array ($ post-> ID ),
'Showposts' => 6, // display the number of related articles
'Orderby' => date, // Sort by time
'Caller _ get_posts' => 1
);
Query_posts ($ args );

If (have_posts ()){
While (have_posts ()){
The_post (); update_post_caches ($ posts);?>
<Li> * <a href = "<? Php the_permalink ();?> "Rel =" bookmark "title =" <? Php the_title_attribute ();?> "> <? Php the_title ();?> </A> </li>
<? Php
    }
  }
Else {
Echo '<li> * no related articles </li> ';
  }
Wp_reset_query ();
?>
</Ul>

Time efficiency comparison

We will use a previous php code to calculate the execution time of the code in the above articles, so as to improve the efficiency of the above methods and provide a reference for your selection. The following are 6 related articles in the same article. The final calculation time of the above methods is as follows:

Method 1: 0.18067908287048 seconds
Method 2: 0.057158946990967 seconds
Method 3: 0.037126064300537 seconds
Method 4: 0.045628070831299 seconds
Method 5: 0.023991823196411 seconds