php mysql 下一个,php-mysql从数据库中获取下一个和上一个ID

I have the following table:

**id** **username** **activity**

1 user1 activity-like

2 user1 activity-share

3 user3 activity-like

4 user4 activity-like

5 user1 activity-share

6 user6 activity-like

8 user1 activity-share

9 user3 activity-like

10 user5 activity-share

(Note: there is no id = 7)

I am trying to use php-mysql to fetch the activity made by user1 and get the id. In this case the activities have the id (1, 2, 5, and 8).

$currentId = 5;

I want to get the next and previous ids if they exist (in this case 2 and 8).

$fetch = mysql_query("SELECT * FROM `main` WHERE `username` = 'user1' AND id > '$id'");

if(mysql_num_rows($fetch) == 1){

while (){

}

$result = $fetch['id'];

}

I am not sure how to write the query for that.

解决方案

You can do something like this:

For next id,

SELECT id from tablename where username = 'user1' AND id > $currentId ORDER BY ID ASC LIMIT 1;

For previous id,

SELECT id from tablename where username = 'user1' AND id < $currentId ORDER BY ID DESC LIMIT 1;