Page 1 of 1

PDO select multiple fields for pagination

Posted: Thu Sep 19, 2013 10:13 am
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));

Re: PDO select multiple fields for pagination

Posted: Thu Sep 19, 2013 2:34 pm
by Christopher
What error message do you get from the failed query?

Re: PDO select multiple fields for pagination

Posted: Fri Sep 20, 2013 2:25 am
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)

Re: PDO select multiple fields for pagination

Posted: Fri Sep 20, 2013 6:24 am
by Celauran
COUNT(*) will only ever return a single result. You have a LIMIT clause on that query, including an offset.

Re: PDO select multiple fields for pagination

Posted: Fri Sep 20, 2013 7:35 am
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.