PHP MySQL Paging

Question

I tried so many different solutions. Im new to php since 1 week, used ASP 12 years ago so I hope I can get some help.

Everything down here works fine. But there are around 1000 rows in the db and I need to split them up in pages.

<?php
$con = mysqli_connect("localhost","test","test","test")or die('could not connect to database');

// Check connection
if (mysqli_connect_errno())
  {
   echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }

echo "<table border='0'>
 <tr>
  <th>Img:</th>
  <th>Text:</th>
 </tr>";

$result = mysqli_query($con,"SELECT Jokes.ID, Categories.CategoryName, Jokes.CategoryID, Jokes.JokeText FROM Jokes LEFT JOIN Categories ON Jokes.CategoryID = Categories.ID ORDER BY  Jokes.JokeText");

while($row = mysqli_fetch_array($result))
  {

   echo "<tr>";
   echo "<td align='center'><img src='webimg/" . $row['CategoryName'] . ".png' height='35' width='35'></td>";
   echo "<td align='left'  width='80%'>" . $row['JokeText'] . "</td>";
   echo "</tr>";

  }
echo "</table>";

mysql_close($con);
?>

Kind Regards.

You mean the output of the table splitting into different pages with pagination (1-2-3) ? — xate
– xate, Commented Apr 11, 2014 at 11:57
take a look at the documentation of mysql regarding LIMIT clause — Ivan Yonkov
– Ivan Yonkov, Commented Apr 11, 2014 at 11:57
@RoyalBg I think he wants to limit the output on the main site, and create a pagination, though im not really sure =D — xate
– xate, Commented Apr 11, 2014 at 11:58

ied3vil · Accepted Answer · 2014-04-11 14:55:25Z

2

You can use MySQL LIMIT for that.

I've used to do it like this:

Get the total number of rows you're paging and have a parameter like in the URL, i.e. "/p/5" or ?page=5 (I will use this for reference, easier to write code and for you to understand) for page no. 5, also do a failsafe, like this:

Say you have 10 records per page:

$page = isset($_GET['page']) ? (int) $_GET['page'] : 1;
$records_per_page = 10;

And, in your SQL you will have something like this

$start = ($page-1) * $records_per_page;
$result = mysql_query("select * from table limit {$start}, {$records_per_page}");

Kind of crude, but you should get the point and be on the right path.

For building your pagination links... that's totally up to you. You should get the total amount of rows with a "select count (PRIMARY_KEY) from table" query prior, so you can calculate the max number of pages.

edited Apr 11, 2014 at 14:55

answered Apr 11, 2014 at 12:05

ied3vil

9291 gold badge8 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

halfer Over a year ago

I've made a couple of tweaks to your code. First, you should use isset to avoid notices when testing array indices that may not exist. Second, it is a good idea to clean input variables - here re-casting to int is fine. There was accidentally no SQL injection in your code on account of the $start calculation, but it's always good to be doubly sure.

ied3vil Over a year ago

Thanks! otherwise adding a @ in front of the $_GET would have done the job! Plain php apps are a thing of the past, nowadays people use frameworks who properly do an xss clean to their inputs and you can use access them with $this->input->post('name') or Input::post('name')

halfer Over a year ago

I don't recommend the error suppression operator, in my view it encourages bad habits. But yes, in this case it would do the job.

ied3vil Over a year ago

What we have here is oldschool coding :D fromt he good'ol days

Maarkoize · Accepted Answer · 2014-04-11 12:05:51Z

0

What you're searching for is the LIMIT of mysql.

The usage is very simple.

For example:

"SELECT * FROM tablename LIMIT 3"

This will give you the first three results.

In your case, you need an offset, depends on the current page:

"SELECT * FROM tablename LIMIT offset,results"

The offset can be calculated, depends on how many results you want each page.

"SELECT * FROM tablename LIMIT 20,10"

This will display 10 results, start at result 20. This could be for the 3. site if you want 10 results each site.

answered Apr 11, 2014 at 12:05

Maarkoize

2,6212 gold badges20 silver badges38 bronze badges

Comments

sss999 · Accepted Answer · 2014-04-11 13:18:44Z

<?php


$per_page = 10; //no. of results to display in one page
$pages_query = mysql_query("SELECT COUNT('id') FROM JOKES");//Or whatever field. this is just to check the number of results.
$pages = ceil(mysql_result($pages_query, 0) / $per_page);//to get the total no. of pages that will be there. For example if u have 60 results than no. og pages will be 60/10=6

$page = (isset($_GET['page'])) ? (int)$_GET['page'] : 1;// if the page variable in your url is set that means that u have clicked a page number. So it will be taking that page number and displaying those results 
$start = ($page - 1) * $per_page;//to get the starting result of that page. If you are in say 6th page, then the starting element would be 6-1*10=50. This makes sure that the results in the previous pages are not displayed

$query = mysql_query("SELECT * FROM JOKES LIMIT $start, $per_page");//set the limit of results that page

while($query_row = mysql_fetch_assoc($query)){
    echo " ur table names ";
}

$prev = $page - 1;//to set the prev page variable
$next = $page + 1;//to set the next page variable

if(!($page<=1)){
    echo "<a href='Yourpagename.php?page=$prev'>Prev</a> ";
}//does not displays the prev variable if you are already one first page

if($pages>=1 && $page<=$pages){

    for($x=1;$x<=$pages;$x++){
        echo ($x == $page) ? '<strong><a href="?page='.$x.'">'.$x.'</a></strong> ' : '<a href="?page='.$x.'">'.$x.'</a> ';

    }//display all the pages. Display the current page as bold

}

if(!($page>=$pages)){
    echo "<a href='Your page name.php?page=$next'>Next</a>";
}//do not display next vvariable if your are in the last page

?>

hugocarlmartin · Accepted Answer · 2014-04-14 08:49:36Z

Thx for all the answers. I came over this tutorial and it worked: http://www.developphp.com/view.php?tid=1349

<?php
include_once("mysqli_connection.php");

$sql = "SELECT COUNT(id) FROM testimonials WHERE approved='1'";
$query = mysqli_query($db_conx, $sql);
$row = mysqli_fetch_row($query);

$rows = $row[0];

$page_rows = 10;

$last = ceil($rows/$page_rows);

if($last < 1){
    $last = 1;
}

$pagenum = 1;

if(isset($_GET['pn'])){
    $pagenum = preg_replace('#[^0-9]#', '', $_GET['pn']);
}

if ($pagenum < 1) { 
    $pagenum = 1; 
} else if ($pagenum > $last) { 
    $pagenum = $last; 
}

$limit = 'LIMIT ' .($pagenum - 1) * $page_rows .',' .$page_rows;

$sql = "SELECT id, firstname, lastname, datemade FROM testimonials WHERE approved='1' ORDER BY id DESC $limit";
$query = mysqli_query($db_conx, $sql);

$textline1 = "Testimonials (<b>$rows</b>)";
$textline2 = "Page <b>$pagenum</b> of <b>$last</b>";

$paginationCtrls = '';

if($last != 1){

    if ($pagenum > 1) {
        $previous = $pagenum - 1;
        $paginationCtrls .= '<a href="'.$_SERVER['PHP_SELF'].'?pn='.$previous.'">Previous</a> &nbsp; &nbsp; ';

        for($i = $pagenum-4; $i < $pagenum; $i++){
            if($i > 0){
                $paginationCtrls .= '<a href="'.$_SERVER['PHP_SELF'].'?pn='.$i.'">'.$i.'</a> &nbsp; ';
            }
        }
    }

    $paginationCtrls .= ''.$pagenum.' &nbsp; ';

    for($i = $pagenum+1; $i <= $last; $i++){
        $paginationCtrls .= '<a href="'.$_SERVER['PHP_SELF'].'?pn='.$i.'">'.$i.'</a> &nbsp; ';
        if($i >= $pagenum+4){
            break;
        }
    }

    if ($pagenum != $last) {
        $next = $pagenum + 1;
        $paginationCtrls .= ' &nbsp; &nbsp; <a href="'.$_SERVER['PHP_SELF'].'?pn='.$next.'">Next</a> ';
    }
}
$list = '';
while($row = mysqli_fetch_array($query, MYSQLI_ASSOC)){
    $id = $row["id"];
    $firstname = $row["firstname"];
    $lastname = $row["lastname"];
    $datemade = $row["datemade"];
    $datemade = strftime("%b %d, %Y", strtotime($datemade));
    $list .= '<p><a href="testimonial.php?id='.$id.'">'.$firstname.' '.$lastname.' Testimonial</a> - Click the link to view this testimonial<br>Written '.$datemade.'</p>';
}

mysqli_close($db_conx);
?>
<!DOCTYPE html>
<html>
<head>
<style type="text/css">
body{ font-family:"Trebuchet MS", Arial, Helvetica, sans-serif;}
div#pagination_controls{font-size:21px;}
div#pagination_controls > a{ color:#06F; }
div#pagination_controls > a:visited{ color:#06F; }
</style>
</head>
<body>
<div>
  <h2><?php echo $textline1; ?> Paged</h2>
  <p><?php echo $textline2; ?></p>
  <p><?php echo $list; ?></p>
  <div id="pagination_controls"><?php echo $paginationCtrls; ?></div>
</div>
</body>
</html>

Collectives™ on Stack Overflow

PHP MySQL Paging

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related