Php implements the next article in the previous article. This article mainly uses SQL to judge based on the current id and then filter out the data before or after the current ID, let's take a look at the specifics. Php implements the next article in the previous article. This article mainly uses SQL to judge based on the current id and then filter out the data before or after the current ID, let's take a look at the specifics.
Script ec (2); script
Implements the writing of SQL statements in the previous and next articles on the website.
The id of the current article is $ article_id, And the id of the corresponding category of the current article is $ cat_id, so the previous article should be:
The Code is as follows:
SELECT max (article_id) FROM article WHERE article_id <$ article_id AND cat_id = $ cat_id;
Run this SQL statement and get $ max_id. Then
SELECT article_id, title FROM article WHERE article_id = $ max_id;
To simplify the process, convert it to subquery:
SELECT article_id, title FROM article WHERE article_id = (SELECT max (article_id) FROM article WHERE article_id <$ article_id AND cat_id = $ cat_id );
Next article:
The Code is as follows:
SELECT min (article_id) FROM article WHERE article_id> $ article_id AND cat_id = $ cat_id;
Run this SQL statement and get $ min_id. Then
SELECT article_id, title FROM article WHERE article_id = $ min_id;
To simplify the process, convert it to subquery:
The Code is as follows:
SELECT article_id, title FROM article WHERE article_id = (SELECT min (article_id) FROM article WHERE article_id> $ article_id AND cat_id = $ cat_id );
Finally, many of my friends prefer to use the following statements:
Previous:
The Code is as follows:
Select id from table where id10 limit 0, 1;
This is certainly no problem, but the performance is not very good.
SQL statement Optimization
You can use union all to obtain three rows of data in a statement, provided that the Three query fields are the same.
The first row of the query result is the previous article, the second row is the current article, and the third row is the next article.
The Code is as follows:
(Select id from table where id <10 order by id asc limit 1) union all (select id from table where id = 10) union all (select id from table where id> 10 order by id desc limit 1 );
Now let's take a look at some examples in cms to implement phpcms.
Obtain the id of the current browser article
$ Id = isset ($ _ GET ['id'])> 0? Intval ($ _ GET ['id']): "";
Next article
$ Query = mysql_query ("SELECT id, title FROM article WHERE id> '$ id' order by id asc limit 1 ");
$ Next = mysql_fetch_array ($ query );
Previous Article
$ Query = mysql_query ("SELECT id, title FROM article WHERE id <'$ id' order by id desc limit 1 ");
$ Prev = mysql_fetch_array ($ query );
">
">