My table has one row for each page (that is, if an item has 3 pages, there will be 3 separate rows). I need to perform 2 tasks:
1) Display the content of the requested page; and
2) Check if the item has more than one page, if so, display links to allow users to change pages.
I have written two versions of the PHP code to do this.
The first way specifically queries for the row that is the correct item AND page number, then displays the content for the requested page. It then makes another request to determine how many pages there are for that given item, and if there are more than one page, it displays page change links.
The second way queries for ALL pages for that item. An array is created, and each row of the array represents the data for one page (this is accomplished using a while loop). Then, page change links are created based on the number of rows in that array.
Clearly, the first one needs two queries, but both relatively small. The second needs one potentially large query. I assume the second one requires more memory for PHP given that data for ALL the pages is stored in an array, while the first puts more load on MySQL since two separate queries are made. I also assume the second is more efficient for items with shorter and/or fewer pages, and the first is more efficient for items with longer and/or more pages.
Am I correct in my assumptions, and which method would you recommend for a production site? Sorry for the lengthy post, and thanks in advance for any answers.