6 Replies - 2742 Views - Last Post: 14 September 2013 - 05:59 AM Rate Topic: ***** 1 Votes

#1 chris98  Icon User is offline

  • D.I.C Addict

Reputation: 38
  • View blog
  • Posts: 897
  • Joined: 06-July 13

PHP, SQL & Pagination

Posted 13 September 2013 - 12:23 PM

Hello, I would like to create pagination for my site.I find this very confusing, but have found this from w3schools.in.It worked fine when it originally came, as it was in normal mysql, but as soon as I tried to convert it to PDO, it doesn't.It just throws up the errors of

Quote

( ! ) SCREAM: Error suppression ignored for
( ! ) Notice: Undefined index: name in C:\wamp\www\pagination\pagination.php on line 47
Call Stack
# Time Memory Function Location
1 0.0006 258464 {main}( ) ..\pagination.php:0


( ! ) SCREAM: Error suppression ignored for
( ! ) Notice: Undefined index: text in C:\wamp\www\pagination\pagination.php on line 48
Could someone please tell me what is wrong with the codes from below?

Here are the parts that appear to be not working.The pagination works (except this below), and I have double checked the database to see if the rows are case sensitive, and they appear to be.

Literally, as soon as I remove these, it works.But then there is nothing to show, so it's pointless.

<?php echo $row['name']; ?>



<?php echo $row['text']; ?>


Here is the actual php page:

<?php 
require("function.php");
 require("db/db_connection.php");
$id = 'id';
$category = $_GET['category'];
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>PHP Tutorial - Professional Pagination</title>
<link href="style.css" rel="stylesheet" type="text/css">
</head>
<body>
<?php	
$page=1;//Default page
$limit=1;//Records per page
$start=1;//starts displaying records from 0
if(isset($_GET['page']) && $_GET['page']!=''){
	$page=$_GET['page'];
}
	$start=($page-1)*$limit;
?>


<?php
//Get total records (you can also use MySQL COUNT function to count records)
$sql = "SELECT :id FROM `downloads`";
$ps_count = $pdo->prepare($sql);
$ps_count->execute(array( 
':id'=>$id));
$ps_count->execute();
$rows = $ps_count->rowCount();

$sql_2 = "SELECT id FROM `downloads` where category = :category order by id ASC LIMIT :start, :limit";
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, FALSE);
$ps = $pdo->prepare($sql_2);
$ps->execute(array( 
':category'=>$_GET['category'],
':start'=>$start,
':limit'=>$limit));
$ps->execute();
while ($row = $ps->fetch(PDO::FETCH_ASSOC)) {
?>
<div class="article">
<div class="title"></div>

</div>
<?php } ?>




<?php
echo pagination($limit,$page,'pagination.php?category='.$category.'&page=',$rows);	//call function to show pagination
?>
</body>
</html>



Function.php

(basically the pagination code)
<?php 

//Pagination Function
function pagination($per_page = 10, $page = 1, $url = '', $total){    
 
        $adjacents = "2";
 
        $page = ($page == 0 ? 1 : $page); 
        $start = ($page - 1) * $per_page;                              
         
        $prev = $page - 1;                         
        $next = $page + 1;
        $lastpage = ceil($total/$per_page);
        $lpm1 = $lastpage - 1;
         
        $pagination = "";
        if($lastpage > 1)
        {  
            $pagination .= "<ul class='pagination'>";
                    $pagination .= "<li class='details'>Page $page of $lastpage</li>";
            if ($lastpage < 7 + ($adjacents * 2))
            {  
                for ($counter = 1; $counter <= $lastpage; $counter++)
                {
                    if ($counter == $page)
                        $pagination.= "<li><a class='current'>$counter</a></li>";
                    else
                        $pagination.= "<li><a href='{$url}$counter'>$counter</a></li>";                   
                }
            }
            elseif($lastpage > 5 + ($adjacents * 2))
            {
                if($page < 1 + ($adjacents * 2))    
                {
                    for ($counter = 1; $counter < 4 + ($adjacents * 2); $counter++)
                    {
                        if ($counter == $page)
                            $pagination.= "<li><a class='current'>$counter</a></li>";
                        else
                            $pagination.= "<li><a href='{$url}$counter'>$counter</a></li>";                   
                    }
                    $pagination.= "<li class='dot'>...</li>";
                    $pagination.= "<li><a href='{$url}$lpm1'>$lpm1</a></li>";
                    $pagination.= "<li><a href='{$url}$lastpage'>$lastpage</a></li>";     
                }
                elseif($lastpage - ($adjacents * 2) > $page && $page > ($adjacents * 2))
                {
                    $pagination.= "<li><a href='{$url}1'>1</a></li>";
                    $pagination.= "<li><a href='{$url}2'>2</a></li>";
                    $pagination.= "<li class='dot'>...</li>";
                    for ($counter = $page - $adjacents; $counter <= $page + $adjacents; $counter++)
                    {
                        if ($counter == $page)
                            $pagination.= "<li><a class='current'>$counter</a></li>";
                        else
                            $pagination.= "<li><a href='{$url}$counter'>$counter</a></li>";                   
                    }
                    $pagination.= "<li class='dot'>..</li>";
                    $pagination.= "<li><a href='{$url}$lpm1'>$lpm1</a></li>";
                    $pagination.= "<li><a href='{$url}$lastpage'>$lastpage</a></li>";     
                }
                else
                {
                    $pagination.= "<li><a href='{$url}1'>1</a></li>";
                    $pagination.= "<li><a href='{$url}2'>2</a></li>";
                    $pagination.= "<li class='dot'>..</li>";
                    for ($counter = $lastpage - (2 + ($adjacents * 2)); $counter <= $lastpage; $counter++)
                    {
                        if ($counter == $page)
                            $pagination.= "<li><a class='current'>$counter</a></li>";
                        else
                            $pagination.= "<li><a href='{$url}$counter'>$counter</a></li>";                   
                    }
                }
            }
             
            if ($page < $counter - 1){
                $pagination.= "<li><a href='{$url}$next'>Next</a></li>";
                $pagination.= "<li><a href='{$url}$lastpage'>Last</a></li>";
            }else{
                $pagination.= "<li><a class='current'>Next</a></li>";
                $pagination.= "<li><a class='current'>Last</a></li>";
            }
            $pagination.= "</ul>\n";     
        }          
        return $pagination;
    } 		
?>	



Could someone please help, as I don't really understand pagination very much to start with?

Is This A Good Question/Topic? 0
  • +

Replies To: PHP, SQL & Pagination

#2 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 801
  • View blog
  • Posts: 1,700
  • Joined: 30-January 09

Re: PHP, SQL & Pagination

Posted 13 September 2013 - 01:57 PM

Why are you calling $ps->execute twice? Once is sufficient. Have you tried dumping out the contents of $row using var_export or print_r?
Was This Post Helpful? 0
  • +
  • -

#3 chris98  Icon User is offline

  • D.I.C Addict

Reputation: 38
  • View blog
  • Posts: 897
  • Joined: 06-July 13

Re: PHP, SQL & Pagination

Posted 14 September 2013 - 12:33 AM

Well spotted, I thought I had got rid of the $ps twice.It originally had two seperate codes, but when I decided I didn't need the first one they had, I removed it.


I have now tried with print_r, and it doesn't bring up an error, but it shows nothing except the pagination.
Was This Post Helpful? 0
  • +
  • -

#4 chris98  Icon User is offline

  • D.I.C Addict

Reputation: 38
  • View blog
  • Posts: 897
  • Joined: 06-July 13

Re: PHP, SQL & Pagination

Posted 14 September 2013 - 04:16 AM

I have realised what I have done wrong now.And it's a very stupid mistake.


I simply just selected the id from the table, and nothing else.That's why it was undefined.
Was This Post Helpful? 0
  • +
  • -

#5 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3576
  • View blog
  • Posts: 10,441
  • Joined: 08-June 10

Re: PHP, SQL & Pagination

Posted 14 September 2013 - 04:51 AM

Quote

I simply just selected the id from the table, and nothing else.

not even that. SELECT :id FROM `downloads` :id is a data placeholder and it would lead your query to return the value you executed it with (i.e. identical to SELECT :id).

note that in Prepared Statements you cannot put SQL statements through a placeholder. all SQL code must be present in the query string that is prepared. (see also this comment in the Manual)
Was This Post Helpful? 1
  • +
  • -

#6 chris98  Icon User is offline

  • D.I.C Addict

Reputation: 38
  • View blog
  • Posts: 897
  • Joined: 06-July 13

Re: PHP, SQL & Pagination

Posted 14 September 2013 - 05:38 AM

I didn't know that, thanks for the tip.
Was This Post Helpful? 0
  • +
  • -

#7 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3576
  • View blog
  • Posts: 10,441
  • Joined: 08-June 10

Re: PHP, SQL & Pagination

Posted 14 September 2013 - 05:59 AM

if you think about it, it should become obvious. Prepared Statements separate your data from your SQL code (and hence making SQL injection (i.e. passing 'data' that are SQL code) impossible). therefore you cannot pass table/field names (which are part of the SQL code) through placeholders.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1