Dynamic PHP pagination for different queries with different result sets

Question

Amaina 14 Newbie Poster

8 Years Ago

I have been trying to get my PHP script return two different pages namely: get2015 and get2016. The aim is to have these two pages have pagination links below them.

To achieve this i have written two scripts in two different files that look like this:

file 1 = functions.php

<?php
include "data.php";
////get results for the year 2015
function get2015(){
$con = dbConnect();

$query = "select * from manuscript where year = 2015";
$sql=$con->prepare($query);
$sql->execute();
$total = $sql->rowCount();
$refs_per_page = 20;
$pages = ceil($total/$refs_per_page);
echo "$total <br>";
//
if (isset($_GET['page']) && is_numeric($_GET["page"])){
$page = (int) $_GET['page'];
}
 else {
  $page = 1;
} //
if ($page > $total) {
  $page = $total;
} //
if ($page < 1) {
   $page = 1;
} //
$offset = ($page - 1) * $refs_per_page;
//
$query = "select * from manuscript where year = 2015 limit $offset, $refs_per_page";
$sql=$con->prepare($query);
$sql->execute();
$sql->setFetchMode(PDO::FETCH_ASSOC);
    while ($row=$sql->fetch()){
    $title = $row['title'];
    $authors = $row['authors'];
    echo "<b>$title</b>" . "<br>" . $authors . "<p>";
    }
    //
    for($x=1; $x<=$pages; $x++){
        ?><a href = "index.php?page= <?php echo $x;?>" style="text-decoration:none"> <?php echo $x;?> </a> <?php
    }
}

//get results for the year 2016
function get2016(){
$con = dbConnect();

$query = "select * from manuscript where year = 2016";
$sql=$con->prepare($query);
$sql->execute();
$total = $sql->rowCount();
$refs_per_page = 20;
$pages = ceil($total/$refs_per_page);
echo "$total <br>";
//
if (isset($_GET['page']) && is_numeric($_GET["page"])){
$page = (int) $_GET['page'];
}
 else {
  $page = 1;
} //
if ($page > $total) {
  $page = $total;
} //
if ($page < 1) {
   $page = 1;
} //
$offset = ($page - 1) * $refs_per_page;
//
$query = "select * from manuscript where year = 2016 limit $offset, $refs_per_page";
$sql=$con->prepare($query);
$sql->execute();
$sql->setFetchMode(PDO::FETCH_ASSOC);
    while ($row=$sql->fetch()){
    $title = $row['title'];
    $authors = $row['authors'];
    echo "<b>$title</b>" . "<br>" . $authors . "<p>";
    }
    //
    for($x=1; $x<=$pages; $x++){
        ?><a href = "index.php?page= <?php echo $x;?>" style="text-decoration:none"> <?php echo $x;?> </a> <?php
    }
}

?>

file 2: Index.php looks like this

<?php
include "functions.php";
$page = $_GET["page"];
if($page){
 if($page=="get2015"){
    get2015();
    }
if($page=="get2016"){
    get2016();
    }
}
?>
<html>
Archive<br>
<a href="index.php?page=get2015"> 2015</a><br>
<a href="index.php?page=get2016"> 2016</a><br>
</html>
<?php ?>

How do iget both funcctions to return paginated pages that work?

mysql php

3 Contributors
15 Replies
490 Views
1 Day Discussion Span
Latest Post 8 Years Ago Latest Post by Amaina

AndrisP 193 Posting Pro in Training

8 Years Ago

Two similar function for different year is irrationally. I would suggest to transform getYear() and put year as parameter eg getYear($year). You can set many parameters eg getYear($year, $page, $refs) you can set default values also eg getYear($year=date("Y"), $page=0, $refs=20) then replace your query

$year = filter_input(INPUT_GET, 'year', FILTER_VALIDATE_INT);
$page = filter_input(INPUT_GET, 'page', FILTER_VALIDATE_INT);
$refs = filter_input(INPUT_GET, 'refs', FILTER_VALIDATE_INT);

$query = "select * from manuscript where year = :year limit :page*:refs,:refs";

never put request variables directly to the SQL query! - use bind variables and bind params after prepare query

$stmt = $conn->prepare($query);
$stmt->bindParam(':year', $year, PDO::PARAM_INT);
$stmt->bindParam(':page', $page, PDO::PARAM_INT);
$stmt->bindParam(':refs', $refs, PDO::PARAM_INT);
$stmt->execute();

Replace HTML links eg

<a href="index.php?year=2015&page=0">2015/1</a>
<a href="index.php?year=2015&page=1">2015/2</a>
<a href="index.php?year=2015&page=2">2015/3</a>
.....
.....
.....
<a href="index.php?year=2016&page=0">2016/1</a>
<a href="index.php?year=2016&page=1">2016/2</a>
<a href="index.php?year=2016&page=2">2016/3</a>

Edited 8 Years Ago by AndrisP

rproffitt commented: Well said. +11

diafol commented: Good advice +14

diafol

8 Years Ago

I'd just add:

    $query = "select * from manuscript where year = 2015";
$sql=$con->prepare($query);
$sql->execute();
$total = $sql->rowCount();

Jeez! That's a heavy query (retrieving every single piece of data!) just to find the number of records. I'd do this:

$year = filter_input(INPUT_GET, 'year', FILTER_VALIDATE_INT);
$sql = "SELECT COUNT(*) AS cnt FROM `manuscript` WHERE `year` = ?";
$stmt = $conn->prepare($sql);
$stmt->execute([$year]); //or use bindValue - $year is cast as string when placed in execute array, but makes no difference here. It does make a difference when using variables for the LIMIT clause!
$num_recs = $stmt->fetchColumn();

AndrisP 193 Posting Pro in Training

8 Years Ago

show me your new version of function

diafol

8 Years Ago

WRT:

$query = "select * from manuscript where year = :year limit, :offset , :refs";

Take off the comma after the "limit":

$query = "SELECT * FROM `manuscript` WHERE `year` = :year LIMIT :offset , :refs";

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

Amaina 14 Newbie Poster · Answer 1 · 2016-09-08T12:12:10+00:00

Thank you @AndrisP and @diafol for your comments. I have a question. Should i maintain the two php files (index.php and functions.php)? Also, when i implement your solution, i get this error "Undefined variable: page in /var/www/pagination/index.php on line 8" and also this "Parse error: syntax error, unexpected '(', expecting ')' in /var/www/pagination/functions.php on line 5". The parse error is caused by the parameter $year = date("Y")

Amaina 14 Newbie Poster · Answer 2 · 2016-09-08T13:12:12+00:00

Here is the new version

include "data.php";
    ////get results for the year 
    function getYear($year=date("Y"), $page=0, $refs=20){
    $con = dbConnect();
    $year = filter_input(INPUT_GET, 'year', FILTER_VALIDATE_INT);
    $page = filter_input(INPUT_GET, 'page', FILTER_VALIDATE_INT);
    $refs = filter_input(INPUT_GET, 'refs', FILTER_VALIDATE_INT);

    $query = "select count(*) from manuscript where year = ?";
    $sql=$con->prepare($query);
    $sql->execute();
    $total = $sql->fetchColumn();
    $refs_per_page = 20;
    $pages = ceil($total/$refs_per_page);
    echo "$total <br>";
    //
    if (isset($_GET['page']) && is_numeric($_GET["page"])){
    $page = (int) $_GET['page'];
    }
     else {
      $page = 1;
    } //
    if ($page > $total) {
      $page = $total;
    } //
    if ($page < 1) {
       $page = 1;
    } //
    $offset = ($page - 1) * $refs_per_page;
    //
    $query = "select * from manuscript where year = :year limit :page*:refs,:refs";
        $sql = $conn->prepare($query);
        $sql->bindParam(':year', $year, PDO::PARAM_INT);
        $sql->bindParam(':page', $page, PDO::PARAM_INT);
        $sql->bindParam(':refs', $refs, PDO::PARAM_INT);
        $sql->execute();
    $sql->execute();
    $sql->setFetchMode(PDO::FETCH_ASSOC);
        while ($row=$sql->fetch()){
        $title = $row['title'];
        $authors = $row['authors'];
        echo "<b>$title</b>" . "<br>" . $authors . "<p>";
        }
        //
        for($x=1; $x<=$pages; $x++){
            ?><a href = "index.php?page= <?php echo $x;?>" style="text-decoration:none"> <?php echo $x;?> </a> <?php
        }
    }

AndrisP 193 Posting Pro in Training · Answer 3 · 2016-09-08T13:30:42+00:00

Initialize request variables outside of function before you call it

    $year = filter_input(INPUT_GET, 'year', FILTER_VALIDATE_INT);
    $page = filter_input(INPUT_GET, 'page', FILTER_VALIDATE_INT);
    $refs = filter_input(INPUT_GET, 'refs', FILTER_VALIDATE_INT);

    getYear($year, $page, $refs);

drop lines 13 and 16-30 put new line instead:
if($page>$pages){ $page = $pages; }
elseif($page<0){ $page = 0 }
change variable name in line 14 $refs_per_page to $refs

AndrisP 193 Posting Pro in Training · Answer 4 · 2016-09-08T14:10:19+00:00

edit links:

for($x=0; $x<$pages; $x++){
       echo '<a href="index.php?page='.$x.'"
                 style="text-decoration:none">'.($x+1).'</a>';
    }

after line 10 insert line $sql->bind_param('i', $year);

Amaina 14 Newbie Poster · Answer 5 · 2016-09-08T14:44:54+00:00

Thanks once again @AndrisP for the veruy valuable comments and code snippets. However, there is another problem where both sql queries are not returning results:

$query = "select count(*) from manuscript where year = ?";
$sql=$con->prepare($query);
$sql->bindParam(':year', $year);
$sql->execute(['year']);
$total = $sql->fetchColumn();
echo $total;

When i echo $total i get 0 results. The seconfd query is also not returning results.

query = "select * from manuscript where year = :year limit, :page*:refs,:refs";
$sql = $con->prepare($query);
    $sql->bindParam(':year', $year, PDO::PARAM_INT);
    $sql->bindParam(':page', $page, PDO::PARAM_INT);
    $sql->bindParam(':refs', $refs, PDO::PARAM_INT);
    $sql->execute();

   $sql->setFetchMode(PDO::FETCH_ASSOC);
  echo $year;
    while ($row=$sql->fetch()){
    $title = $row['title'];
    $authors = $row['authors'];
    echo "<b>$title</b>" . "<br>" . $authors . "<p>";
    }

Another pressing question is , should i change the code on index.php?

AndrisP 193 Posting Pro in Training · Answer 6 · 2016-09-08T15:00:16+00:00

use one of

    $query = "select count(*) from manuscript where year = :year";
    $sql=$con->prepare($query);
    $sql->bindParam(':year', $year);
    $total = $sql->fetchColumn();
    echo $total;

or

    $query = "select count(*) from manuscript where year = ?";
    $sql=$con->prepare($query);
    $sql->execute([$year]);
    $total = $sql->fetchColumn();
    echo $total;

do not mixed two methods of binding variables

Amaina 14 Newbie Poster · Answer 7 · 2016-09-08T15:19:25+00:00

Thanks. The second query is not returning results

$query = "select * from manuscript where year = :year limit, :page*:refs,:refs";
$sql = $con->prepare($query);
    $sql->bindParam(':year', $year, PDO::PARAM_INT);
    $sql->bindParam(':page', $page, PDO::PARAM_INT);
    $sql->bindParam(':refs', $refs, PDO::PARAM_INT);
    $sql->execute();

   $sql->setFetchMode(PDO::FETCH_ASSOC);
  echo $year;
    while ($row=$sql->fetch()){
    $title = $row['title'];
    $authors = $row['authors'];
    echo "<b>$title</b>" . "<br>" . $authors . "<p>";
    }

AndrisP 193 Posting Pro in Training · Answer 8 · 2016-09-08T15:39:29+00:00

maybe try

$query = "select * from manuscript where year = :year limit, :offset , :refs";
$sql = $con->prepare($query);
$offset = $page * $refs;
    $sql->bindParam(':year', $year, PDO::PARAM_INT);
    $sql->bindParam(':offset', $offset, PDO::PARAM_INT);
    $sql->bindParam(':refs', $refs, PDO::PARAM_INT);
    $sql->execute();

AndrisP 193 Posting Pro in Training · Answer 9 · 2016-09-08T16:06:09+00:00

AndrisP 193 Posting Pro in Training

8 Years Ago

oh sorry its a copy-paste

Edited 8 Years Ago by AndrisP

Amaina 14 Newbie Poster · Answer 10 · 2016-09-08T19:20:53+00:00

Thanks @AndrisP and @diafol. Seems like my problems are not going away. I realise that $pages value is zero. It seems like the ceil() function is not working.

$pages = ceil($total/$refs);

Amaina 14 Newbie Poster · Answer 11 · 2016-09-09T05:25:31+00:00

The code now works. Thank you @AndrisP and @diafol for your valuable comments and patience.