PDO select multiple fields for pagination

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
chris98
Forum Contributor
Posts: 103
Joined: Tue Jun 11, 2013 10:47 am
Location: England, United Kingdom

PDO select multiple fields for pagination

Post by chris98 »

I would like to create a page system, and I have two page types;

A.) sub_page

B.) normal_page

I have also created a page where users can browse through the pages.However, I only wish them to browse through the normal_page pages, and not the sub_page pages, as I want the normal pages to link to the sub_pages.

It basically outputs all the data from the database, and the pagination works, which is good.

Although as soon as I come to limit the outputs (to normal_page pages), it returns NO results at all.

Here is the code I am using below.

Is the problem something to do with the way I am defining the page type?

Count function for the pagination

Code: Select all

$page = 'normal_page';
$sql = "SELECT COUNT(*) AS id FROM pages WHERE type = :article AND sub_page = :page order by id DESC LIMIT :start, :limit";
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, FALSE);
$ps = $pdo->prepare($sql);
$ps->execute(array( 
':article'=>$_GET['article_type'],
':page'=>$page,
':start'=>$start,
':limit'=>$limit)); 
Actual code

Code: Select all

$sql_2 = "SELECT id,title,username,article,date,email FROM `pages` where type = :type AND sub_page = :page order by id DESC LIMIT :start, :limit";
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, FALSE);
$ps = $pdo->prepare($sql_2);
$ps->execute(array( 
':type'=>$_GET['article_type'],
':start'=>$start,
':page'=>$page,
':limit'=>$limit));
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: PDO select multiple fields for pagination

Post by Christopher »

What error message do you get from the failed query?
(#10850)
User avatar
chris98
Forum Contributor
Posts: 103
Joined: Tue Jun 11, 2013 10:47 am
Location: England, United Kingdom

Re: PDO select multiple fields for pagination

Post by chris98 »

Nothing at all.It displays the page, but with no data from the database.I do have the advanced PDO error reprting connection as well.


(the rest of the page is fine, it's just this part which shows nothing)
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: PDO select multiple fields for pagination

Post by Celauran »

COUNT(*) will only ever return a single result. You have a LIMIT clause on that query, including an offset.
User avatar
chris98
Forum Contributor
Posts: 103
Joined: Tue Jun 11, 2013 10:47 am
Location: England, United Kingdom

Re: PDO select multiple fields for pagination

Post by chris98 »

Ah, thanks.I have changed it to:

Code: Select all

$sql = "SELECT COUNT(*) AS id FROM pages WHERE type = :article AND sub_page = :page";
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, FALSE);
$ps = $pdo->prepare($sql);
$ps->execute(array( 
':article'=>$_GET['article_type'],
':page'=>$page_type));
And it appears to be working.
Post Reply