Pagination Question

[Sam_Rusch]

I am in the process of making a website right now, and I will require some type of pagination. What I am in a bind about, is how I will go about this.

I have a table named stories. The fields I have in this table are id, active, story, user, and date.

I have seen pagination go by the id before, saying that id #1 - #10 is on page 1, then #11 - #20 on page 2, and so on. However, I will have missing id numbers on this website. This is going to be user submitted media, and some will be disapproved before it makes it live.

So, say I have this in my id list:
1, 3, 6, 11, 12, 13, 14, 17, 18, 20, 25, 30, 31, 33, 37

How would I accurately tell my code to choose 10 for each page?

If you would like me to explain a little more, or give some more details, please let me know!

Thank you very much for helping!

[Jake_Arkinstall]

Are you using MySQL (or a similar variant) for your database?

If, in MySQL, you order your result sets however you want to (Doesn’t have to be by ID - in fact by reverse ID would most likely be the most beneficial, or by Views or Rating etc) then use a LIMIT statement, you can limit results.

An example:
Page 1:

SELECT ID, Name FROM SomeTable ORDER BY ID DESC LIMIT 0, 10

That will select the first 10 rows - So in your case above that would be 1 -> 20.

The LIMIT statement works like this.
To get the first n results:

... LIMIT n

To get n results starting with record i, where the first record is i=0:

... LIMIT i, n

So to get the second page of results:

 ... LIMIT 10, 10

The third:

 ... LIMIT 20, 10

So, on to the PHP:

$SortColumn = 'ID';
$SortDirection = 'DESC';
$ResultsPerPage = 10;
$CurrentPage = array_key_exists('page', $_GET) ? (int)$_GET['page'] : 1;
if($CurrentPage <= 0){ $CurrentPage = 1; } //prevents dodgy page numbers

$Start = ($CurrentPage - 1) * $ResultsPerPage;

$Query = "SELECT * FROM YourTable ORDER BY {$SortColumn} {$SortDirection} LIMIT {$Start}, {$ResultsPerPage}";

To determine how many pages there would be, use a COUNT query:

$CountSQL = "SELECT COUNT(*) FROM YourTable";
$CountQuery = mysql_query($CountSQL);
$Count = mysql_result($CountQuery, 0);
$CurrentPage = floor($Count / $ResultsPerPage) + 1;

And voila

If you’re familiar with Object Oriented programming, you can make a nicer way of managing these kinds of tasks, but you’d still be using the same concepts.

[Sam_Rusch]

The way you explained the LIMIT statement was perfect. I am going to switch around the code a little bit, but I should be able to get it to work…I hope

Thanks for the help!

[Jake_Arkinstall]

Off Topic:

As this is a forum for people, even observers, to learn, I’ll put this PM question here so the answer may benefit others.

@Sam_Rusch

Thank you for the help regarding the pagination. I got it to work, except for one part I will be stuck on.

I need to have the bar of numbers for the pages. Example:

Previous - 1 - 2 - 3 - 4 - 5 … 12 - Next

I know how to make the code to make it count how many pages, divide by how many per page, and then display each page consecutively. But, as I showed above, say I had 12 pages. I wouldn’t want to show all 12 links. How would I be able to write the code so it cuts out a few numbers? I plan on making a “jump to page” box as well, which I know how to make

Thanks again,
Sam

Basically you need to think about how you want to split the numbers in different circumstances. E.g. you may have 1-2-3-4-5–12 at page 1, 2 and 3 - but what about 4? At 4 do you want 1-2-3-4-5–12, 1–3-4-5-6–12, 2-3-4-5-6–12, etc? Those variations can affect user experience. Have you thought about the system you will use?

Generally I think it’s a good idea to have first and last pages available, as well as at least 1 either side for simple navigation. For page 7 that system could look like:
1–6-7-8–12

So, the key things to determine are (1)What general number-choosing system you want and (2) What to do in special cases.

The general (as opposed to special) cases aren’t when the page number is close to 0 or the final page number, but in fact the opposite. The general case is 1 - (i-1) - (i) - (i + 1) - n. The special case is when i is 1 or n (in your example n=12), and when n is less than the amount of links you want to show.

So let’s first define a function which will output an array of those numbers depending on the amount of records available. First we define special cases, then go for a general case at the end when the special cases have all been handled:

function pageLinks($PageCount, $CurrentPage=1, $LinkCount = 5){
        $LinksEitherSide = ($LinkCount - 3) / 2; // e.g. with 5, including the first and last pages as well as the current, you will get 1 either side. With 6 you will get 1.5 either side so floor the left and ceil the right so that you see more of what's ahead than behind.
        $LeftLinks = floor($LinksEitherSide);
        $RightLinks = ceil($LinksEitherSide);
    if($PageCount <= 1){ // no point outputting a link for 0 or 1 pages
        return array();
    }else if($PageCount <= $LinkCount){ // Just link to all pages as they all fit within the maximum link count
        return range(1, $LinkCount);
    }else if($CurrentPage <= $LeftLinks){ //If you want 2 links on the left of the number 1, for example, that won't happen. However you can output 1-4 and then 12.
        return array_merge(range(1, $LinkCount - 1), array($PageCount));
    }else if($PageCount - $CurrentPage <= $RightLinks){ //if you want 3 links to the right but you're on number 11, list the link to page 1 and then all of the last 5 (or $LinkCount) pages. 
        return array_merge(array(1), range($PageCount + 1 - $LinkCount, $PageCount));
    }else{ //Return 1, followed by the links to the left of $CurrentPage all the way up to the links to the right of $CurrentPage (range() simplifies this) followed by the final page
        return array_merge(array(1), range($CurrentPage - $LeftLinks, $CurrentPage + $RightLinks, array($PageCount));
    }
}

That will return a list of numbers to return with. I haven’t tested it out but, theoretically, it should work.

From this you can write the code to output these links, possibly disabling/styling the link for the current page to prevent unnecessary (though negligible) load and assist the user.

Hope that helps. It might not be the exact numbering system you’re looking for, but it should lead you in the right direction at least.

[Sam_Rusch]

Sorry to say, but I did not get that explanation

I got lost with all the return range, return array_merge, etc.

I don’t quite see in the code where it makes the links to click, if you put that in there.

[derokorian]

What he did was create a function to return the numbers of pages to use. IE its returning an array with 5 numbers in it, you would then take those numbers and make your links.

[Jake_Arkinstall]

You might want to check out the functions you don’t know, otherwise they will always confuse you.

As derokorian and I said, it returns an array. I.e. array(1,2,3,4,12) or (1,4,5,6,12). The easy bit is your job - to use the numbers returned to create links. I was merely giving you the theory behind generating those numbers, which varies depending on what you want.

[Sam_Rusch]

Would I change the function to incorporate the <a href=“”></a> part? Or would that be after I called the function?

[Jake_Arkinstall]

Basically, the function is only designed to return page numbers.

So use that function to get an array of page numbers. Then use a foreach() on the returned array to loop through making links for them.

In essence it gives your functionality a good bit of structure - I see a lot of code where people just go through code like the above inside the presentation, and when I see that a little part of me dies inside. Splitting functionality into as many separate functions as possible is generally a good thing - the time when it isn’t comes when a function has one line or really basic functionality (unless that one line is long or might not be self-describing, in which case naming a function just so you know what that one line is doing can be useful later on).

In fact when I start planning how code is going to look, I get one function and think what it needs to do and write it out in function names, like as a meaningless example:

<?php
function decorateRoom($Room){
    removeFurniture($Room);
    coverFloor($Room);
    paint($Room);
}

THEN I’d write those functions. Though realistically I would be using OOP, but that’s another story for another day.

Doing this can increase your speed, allows you to get a better understanding of the system you’re making and makes everything make so much more sense.

[Sam_Rusch]

I attempted to write a function based around pagination myself, based on a lot of what you taught me in this post. However, it never returned any values.

Are there any statements that I should attempt to keep out of a function?

[Jake_Arkinstall]

Hmm, what functions don’t work for you? I can have a look at them and see what’s going on.

[Sam_Rusch]

Unfortunately I deleted it a long time ago in frustration it not returning the values. A have a pagination solution working on the pages now, and am happy it is done

It is not pretty, but it works for now. I will touch up on it, and develop something more efficient in the future, based more on your method (which I like much more).

Thanks for your help!