Kacper Werema Kacper Werema - 5 months ago 15
SQL Question

MySql selecting context limited by number starting from another one, no duplicate

Hey I need to make a MySql query and get from it some number of user activities, lets say 10, then after scrolling on page I need to take another portion of activities stored in DB and start from 10 to 20 and so on... As I made this already by loading the whole DB Content for user and then dynamically show it with AJAX and jQuery I need to change the method I am doing this. So my query looks like this:

SELECT some rows FROM table WHERE User_ID = @memberID ORDER By date LIMIT limit


As this query works to take only limited records from DB I have no idea how to make a parameter that would determine which records should we take now. The problem starts when user refreshes the page - we want to start from 0 and again go 10 by 10 down.

EDIT:
I am giving the query 2 params (LIMIT and OFFSET) and then in jQuery function gonna try to increase both of them.

Answer

you can do it like this

example 

mysql> SELECT * FROM MAXWELL;
+------+-------+
| ID   | NAME  |
+------+-------+
|    3 | TWO   |
|    4 | FOUR  |
|    5 | FIVE  |
|    6 | SIX   |
|    7 | SEVEN |
+------+-------+
5 rows in set (0.00 sec)

mysql> SELECT * FROM MAXWELL limit 0,2;
+------+------+
| ID   | NAME |
+------+------+
|    3 | TWO  |
|    4 | FOUR |
+------+------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM MAXWELL limit 2,4;
+------+-------+
| ID   | NAME  |
+------+-------+
|    5 | FIVE  |
|    6 | SIX   |
|    7 | SEVEN |
|   10 | ten   |
+------+-------+
4 rows in set (0.00 sec)