SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Addict phptek's Avatar
    Join Date
    Jun 2002
    Location
    Wellington, NZ
    Posts
    363
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Sorting Results Problem

    Hi folks:

    As I understand it, once you perform a SQL query on your data, that data is essentially represented in a new 'temporary table'. And if you were to add any ORDER BY or LIMIT clauses to that query, you'd be ordering the results as if they were in the initial, original table, is that right?

    I'd like to ask how other's have managed the process of using a prev-next link system limiting by x results per page, and employing sortable headings to an HTML table of this dataset.

    My question/problem is that I'd like, if possible, to ORDER my data (LIMIT = 50) within the current page of results, not by the entire result set.

    Say I'm viewing 50 rows on page 15 of 100 result pages, I want to sort within those 50 results on page 15, not by all the results as seems to be the case at the moment.

    I'm using MySQL 3.22.32 with Apache/Linux and PHP 4.1.2 - thanks a lot for any help you may be able to offer me

  2. #2
    ********* Wizard silver trophy Cam's Avatar
    Join Date
    Aug 2002
    Location
    Burpengary, Australia
    Posts
    4,495
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)
    I'm not sure if this will do it, but if you already have your paging script going, you could use the LIMIT clause to sort within those particular results. Like
    Code:
    SELECT field1, field2, field3, date
    FROM table
    ORDER BY date
    LIMIT 50, 50
    That LIMIT clause will start 50 rows down and select the following 50, so if you were to order by an auto_incrementing ID then you would get records 50 - 100.

    Hope that helped

  3. #3
    SitePoint Addict phptek's Avatar
    Join Date
    Jun 2002
    Location
    Wellington, NZ
    Posts
    363
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Sorting from within result-set

    DJ P@CkMaN

    Cheers for your reply btw

    What I meant was that if I use the LIMIT clause with an offset as I have for my pagination script, the offset is always going to be an offset from the 1st database auto_incrementing ID. If i'm on page 15 of x results the offset will be taken from that 1st ID and therefore not sorting within the current result set of 50 rows.

    Does that make sense? I wanted to ask if there was an easier way of doing it than apprantly having to detect the ID of the 1st displayed row of the current result set and taking that row as the offset....

    What do you think?
    Ta again.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •