Ok, this problem has me a bit stumped so I'm hoping someone can help me with it! I'll try explain it the best I can but if you need any additional background information it stems from this old thread.

My SQL query (based on libraries vs. shelves vs. books) produces the following resultset:

http://www.alistairmahoney.com/z_sgoldie/my_result.html

I have successfully fed this resultset into an array for display purposes, the array looks something like this:

array (
   'Green Library' => array (
      'shelf' => array (
         'A' => array (
            0 => 'Book 1',
            1 => 'Book 7',
            2 => 'Book 13',
            3 => 'Book 19'
         ),
         'B' => array (
            0 => 'Book 2',
            1 => 'Book 8',
            2 => 'Book 14',
            3 => 'Book 20'
         )
      ),
      'address' => '123 Green Street, Greensville',
      'hours' => '9am - 5pm'
   ),
   'Blue Library' => array(
      'shelf' => array (
         'C' => array (
            0 => 'Book 3',
            1 => 'Book 9',
            2 => 'Book 15',
            3 => 'Book 21'
         ),
         'D' => array (
            0 => 'Book 4',
            1 => 'Book 10',
            2 => 'Book 16',
         )
      ),
      'address' => '456 Blue Road, Bluetown',
      'hours' => '8am - 6pm'
   ),
   /* etc.... */
)

I use this array to loop through and produce the following HTML output (see old thread if you need to know the reason why):

------------------ROW 1---------------------
Green Library
Shelf A: Book 1, Book 7, Book 13, Book19
Shelf B: Book 2, Book 8, Book 14, Book 20
Address: 123 Green Street, Greensville
Opening Hours: 9am - 5pm
------------------ROW 2---------------------
Blue Library
Shelf C: Book 3, Book 9, Book 15, Book 21
Shelf D: Book 4, Book 10, Book 16
Address: 456 Blue Road, Bluetown
Opening Hours: 8am - 6pm
------------------ROW 3---------------------
Yellow Library
Shelf E: Book 5, Book 11, Book 17
Shelf F: Book 6, Book 12, Book 18
Address: 789 Yellow Lane, Yellopolis

Opening Hours: 9am - 2pm

... in reality I actually have 50+ libraries, but for this example I'm only showing three.

Now for my problem:

If I want to apply pagination to my results, how would I do so? If I put a LIMIT value on the original SQL query (e.g. LIMIT 0, 2) it wouldn't work because it would only retrieve the first 2 books from 'Shelf A' from the 'Green Library', instead of returning ALL BOOKS from ALL SHELVES from the FIRST 2 libraries (i.e. 'Green Library' and 'Blue Library')

Is is possible to limit the results to the first 2 ORIGINAL/DISTINCT LIB_ID rather than just the first 2 rows? Or do I somehow need to apply my pagination to my array rather than the SQL statement? If so, how would I do this?

I have had a look at changing my query to use the MYSQL GROUP_CONCAT function for shelves and libraries but I can't seem to get this to work the way I want.

Any help would be greatly appreciated!

    Maybe save the array in $_SESSION and paginate via it rather than via the DB query?

      NogDog;10979412 wrote:

      Maybe save the array in $_SESSION and paginate via it rather than via the DB query?

      Cheers for the advice... I'm not too sure how to paginate using session variables, could you possibly explain it to me?

      Also, I guess a large advantage of pagination is that the page loads quickly... if the entire resultset is loaded into the session variable first, will this degrade performance at all? I could potentially have 1000+ libraries that I would want to paginate by multiples of 25, 50 or 100 (user-selected). If I'm running the query for ALL RESULTS then paginating after this, do you think it will affect the page load by much?

        I don't think a session is going to work in this case (well it will, but there isn't much point). You would be maintaining a session for with the data for each user, meaning you are storing multiple copies of the same data, and it's not necessarily SQL that is the bottleneck to begin with. If you are concerned with the SQL processing you may want to look into server caching like memcache.

        Anyway, to answer your question. I don't know of a SQL query offhand that will allow you to do what you want (There might be, but I'm no SQL expert). What I would do in this situation is break it down into two queries.

        1) The first query will be for getting the paginated results for the library
        $sql = "SELECT library FROM table LIMIT $start_index, $num_results";

        2) This is where you query for all the data for the libraries
        $sql = "SELECT shelf, book FROM table WHERE library IN ($library_list) ORDER BY library ASC";
        where $library_list is a comma delimited string with a list of libraries you get from the previous query

        This will allow you to paginate the libraries yet still get all of the data for each.

        The above queries are assuming you have a single table with all the data, where each item contains an id for the library, shelf, and book. If you have this broken down into multiple tables the principle are the same, but the second query will need more modification.

          OK great, I understand what you're getting at in relation to this query structure and it seems to me that it's a good way of handling the problem. Ideally I would have liked to have done this all within one query but if two queries is the way to go then I'll give that a shot.

          I won't have the opportunity to trial this for the next 48hrs or so, so I'm going to leave this thread unresolved just in case some other suggestions arise ;-)

          Thanks!

            Sweet, seems to be working pretty well. Thanks again!

              Write a Reply...