[MySQL] Getting 1st element of list

[Markstar]

Hi,
I would like to allow visitors of my website to "turn the page" where there are n news items per page. Implementing the "Next " button/link is easy, but I struggle to get the "Previous" button/link to work.

Here is an example with 3 items per page:
news item # (nitem FROM news)
...
16 // previous previous page
15
14
13 // first item of previous page (13-11)
12
11
10 // first item of current page (items 10 - 8)
9
8
7 // first item of next page (that's easy)
6
...

My problem was that nested queries didn't work with LIMITs and I would really like to get this with one SQL-statement and not have to go through a temporary result list in PHP.

Thank you for your time!

 

[xav0989]

The aproach I currently use includes 2 MySQL queries: one to fetch the total count of entries and the second one to fetch the actual entries using an offset & limit. As for the actual code to generate the navigation, you can do some searches for pagination in php on google. The code I use is pretty big, so it'll take a lot of space to post it here.

 

[farscapeone]

Try this: http://www.9lessons.info/2009/09/pagination-with-jquery-mysql-and-php.html

It's bad English but the code is OK

 

[misson]

My problem was that nested queries didn't work with LIMITs

What's your current query? Sub-selects can often be rewritten as joins.

 

[Markstar]

Thanks guys, I have looked into pagination and while there is plenty of stuff out there, it is a little too much for what I'm trying to do. Additionally, most solutions require getting ALL news entries, which is exactly what I wanted to avoid.

@mission: Here is what I was thinking (but obviously doesn't work):

SELECT nitems FROM news WHERE nitem IN (SELECT nitms FROM news WHERE nitems > 10 ORDER BY nitems ASC LIMIT 3) ORDER BY DESC LIMIT 1;

As I said, I know this doesn't work because MySQL doesn't support LIMIT in the nested query and also, ORDER BY is done at the very end.

 

[misson]

I assume "nitem" and "nitms" are misspellings of "nitems", and you're trying to get a specific range of rows à la the head/tail strategy. As it turns out, neither subselects nor joins are needed. MySQL supports an extended LIMIT clause, where you can specify an offset before the row count:

SELECT nitems FROM news
    ORDER BY nitems ASC
    LIMIT :start, :length

When asking about code, always include short, illustrative examples. It's part of being precise and informative.

 

[Markstar]

I assume "nitem" and "nitms" are misspellings of "nitems",

Ups, sorry. I was writing this at the bus station, so I was a little bit in a hurry. Yes, but it's nitem.

and you're trying to get a specific range of rows à la the head/tail strategy. As it turns out, neither subselects nor joins are needed. MySQL supports an extended LIMIT clause, where you can specify an offset before the row count:

SELECT nitems FROM news
    ORDER BY nitems ASC
    LIMIT :start, :length

When asking about code, always include short, illustrative examples. It's part of being precise and informative.

Hmm,

SELECT nitem FROM news
    ORDER BY nitem ASC
    LIMIT 10, 3

Returns an empty result. :happysad:

Here is what I currently have:

SELECT nitem
FROM news
WHERE cat =   'f'
AND nitem >10
ORDER  BY nitem ASC 
LIMIT 3

The output for this is 11, 12, 13 (but in practice could also be 12, 42, 134). Now I need to get the 13, so currently I go through the list in PHP until I reach the last element and save this as the variable that I use for the link to the previous page. :dunno:


Edit: Ahh, I think I finally understood LIMIT x, y. If LIMIT get 2 values, the first one is the offset (not really the start).

Since I don't really need to have a full list of all the pages (first, previous, next and last are enough), this will do nicely and I already have it up and running. :biggrin:

Thank you!


P.S.: But sadly it seems x10 is having some difficulties right now. Oh well, I hope it'll work again shortly.