Selecting Correct Entries From Database

[NalaTheFurious]

I have a table in my database called "blogs" which contains all of the relevant information for each blog entry. In addition to that I have the url structure on my website set up like the following:

 

http//website.com/page/1

http://website.com/page2

http://website.com/page3

 

Where each page will contain 10 results from that table "blogs" (no problem up to this point). I can easily display 10 results from my database onto each page, however this is based off the incrementing"id" field in the table "blogs" which shows all of the entries in the order they are put into the database.

 

Here is the code that I am using thus far:

$blogCount = BlogCount();

$page = (isset($_GET["page"]) && is_numeric($_GET["page"])) ? (int) htmlentities($_GET["page"]) : 1;
$page = ($page < 1) ? 1 : $page;

$pageCount = ceil($blogCount / 10);
$page = ($page > $pageCount) ? $pageCount : $page;


$stop = $page * 10;
$start = $stop - 9;
    
$blogs = BlogRange($start, $stop);

In the above code the function BlogRange() will select the blogs based off the "id" range which is calcualated based off the page number, which uses this mysqli prepared statement:

SELECT * FROM blog WHERE id BETWEEN ? AND ?

--

 

Now, my question is.. taking all of this into consideration.. how would I select the newer entries? I want the first page of my blog to show the newest entries on the first page instead of what was submitted into the database first.

[requinix]

Don't use the id at all. Use a LIMIT, as well as an ORDER BY to do the sorting.

SELECT *
FROM blog
ORDER BY whatever your date column is DESC
LIMIT ?, 10

The ? is the starting offset but counting from 0: $start = ($page - 1) * 10. Or if you want to keep specifying the start and end as a range,

LIMIT ?, ?

$offset = $start - 1;
$count = $stop - $start + 1;

By the way, this process is called "pagination". Edited June 4, 2015 by requinix

[fastsol]

There isn't really any reason to do as much validating as you are on the first line of $page. You could shorten it to this and still come out with the same result.

$page = (!empty($_GET["page"])) ? (int)$_GET["page"] : 1;

Also, when type casting to number formats, there is no reason to use some thing like htmlentities() on the var too.  The type cast will strip anything that is not valid for the number formatting you are casting to.