Topic: [SOLVED] MySQL query sucks or my programming sucks?

[himegoto]

Code: [Select]

SELECT * FROM blog ORDER BY id DESC LIMIT 7
I made a simple blog and I wanted it to display the first 2 rows from the above query, then put the next 5 into another section.  That part works fine.  Then you click one of those 5, it kicks up the id # from the link and goes something this, where 3 is the id# and 7 is the max # of records I want to pull up:

Code: [Select]

SELECT * FROM blog ORDER BY id DESC LIMIT 3,7
This is where the problem is.  I expect to get the first row as id #3, then the next is id# 2, then id #1.  But thats not whats happening.
Instead, with the above example.  I get id #6, 5, 4, then 3.  If I change the 3 to 1, I get id 8 down to 2 and 1 never even comes up.
You can easily see what I'm talking about at http://tremor.myftp.biz/srcg/index.php?mode=blog&id=3.

So what sucks more? My novice MySQL skills or my novice PhP skills?

[dannyb785]

using "Limit a,b" is mainly for pagination(that is, if you have too much to display on one page, you make page numbers and each page # is calculated and the proper value placed in front of the comma). You're getting (a) number of records starting from the (b)th record. So in your example, you're getting the first 7 rows, starting with the 3rd row.

[himegoto]

Thats what I want, but not what I'm getting.  Another example is I want 7 rows starting at id 13 so I use

Code: [Select]

SELECT * FROM blog ORDER BY id DESC LIMIT 13, 7What I expect to get is first row id # 13, then 7 more down to id #6.  but what I get instead is id #3, #2, and #1. Nothing more.
In my understanding, that query should never even come close to calling up those 3 ids.  That is what leads me to believe it something with my query and not my coding.  But if the query is correct, that makes me even more confused so I guess I'll try the php forum.

[Barand]

"13" in the LIMIT clause has nothing to to do with record id 13.

First it gets the results of your query then puts them in order as specified in the ORDER BY.

so if you have 20 records you get

ID
---
20
19
18
.
.
.
4
3
2
1

It will then return 7 (or as many as remain) records starting at offset 13 (14th row) in these results, which should be

7
6
5
4
3
2
1

[himegoto]

That makes sense.  I really did misunderstand how the LIMIT works.  Thank you.

[dannyb785]

Thank you barand. Like I said, with LIMIT a,n you are starting with the nth row(in whichever order may be specified). Not the row with id n.

[Barand]

It's actually starting at the ath row and returning n rows

[dannyb785]

It's actually starting at the ath row and returning n rows

oops