I have been trying to resolve a paging problem and I do not understand the following code.
$lstart = ($page * 6) -6;
$lend = ($page * 6)-1;
$limit = $lstart.','.$lend;
The results I get are mixed. I should get six articles per page, but it is inconsistent. The code is incorporated in a script I inherited from someone else and I am trying to fix it. Can someone explain this code to me? In the query, LIMIT=$limit.
It should be...
$lstart = ($page * 6) -6; // quite frankly, it's clearer to write...
// ($page - 1) * 6
$limit = $lstart.',6';
The second clause in limit declares how many items. Not up to a certain point.
From mysql docs: (copy/paste)
SELECT * FROM tbl LIMIT 5,10; # Retrieve rows 6-15
So, 1,10 would be rows 2-11 Thus, to get row 1, you need to set the offset to zero: 0,10 which would give you rows 1-10.
You can also check out further tutorials on LIMIT here: http://php.about.com/od/mysqlcommands/g/Limit_sql.htm
Code with explanation.
$rows_per_page = 6; // you're trying to get 6 rows for every "page".
// On page 1, you'd want rows 0-5 (6 rows inclusively)
// On page 2, you'd want rows 6-111 (again, 6 rows inclusively)
// and so forth.
// So when $page == 1, you want to start at 0, on page 2, start at 6....
// To express this pattern mathematically, we write:
$start = ($page - 1) * 6
// mysql takes offset and number as it's two variables in the LIMIT clause,
// in that order, when two are provided.
// So we write:
$query = "SELECT * FROM table WHERE 1 LIMIT $start, $rows_per_page;";
// So, at page 1, you get
$query = "SELECT * FROM table WHERE 1 LIMIT 0, 6;"; // if we were to substitute the variables.
$query = "SELECT * FROM table WHERE 1 LIMIT 6, 6;"; // at page 2
$query = "SELECT * FROM table WHERE 1 LIMIT 12, 6;"; // at page 3
+1 in Johanathan's answer of $lstart = ($page - 1) * 6 + 1; is actually wrong unless you want to start from second record only. The offset value starts from zero. You can confirm this via mysql docs: dev.mysql.com/doc/refman/5.5/en/select.html as well as testing. If you are getting ALL results, it would mean that you have error elsewhere and not in the specific "0,6" string. Edit: I've edited my answer with the excerpt from mysql docs to show how offset works.... LIMIT=5 is equivalent to ... LIMIT=0,5. Additionally, if you're getting synatx error or weird behaviors, the problem is with the rest of the code. Not the limit clause.
LIMIT start, number, it's not astart/stopcontext. It's start with record35, give me the next30. So it doesn't look right from that point.