Solved

Introduce paging to display database records

Posted on 2011-09-09
4
174 Views
Last Modified: 2012-05-12
Hi:

I have this php code to display database records in the table form.  But as the database grows, the page will grow longer.  Therefore, I want to display only 10 records per page.  How can this be done?

Thanks,
<?PHP
require_once("./include/membersite_config.php");

if(!$fgmembersite->CheckLogin())
{
    $fgmembersite->RedirectToURL("login.php");
    exit;
}

?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en-US" lang="en-US">
<head>
      <meta http-equiv='Content-Type' content='text/html; charset=utf-8'/>
      <title>Home page</title>
      <link rel="STYLESHEET" type="text/css" href="style/fg_membersite.css">
</head>
<body>
<div id='fg_membersite_content'>
<h2>Home Page</h2>
Welcome back <?= $fgmembersite->UserFullName(); ?>!
<?php 
$orderCol=$_POST['orderCol'];
$orderWay=$_POST['orderWay'];
$filterCat=$_POST['filterCat'];
$filterCo=$_POST['filterCo']; 

if (!$orderCol) $orderCol="resid";
if (!$orderWay) $orderWay="DESC";

$sql="SELECT * FROM responses WHERE 1=1";
if ($filterCat) $sql.=" AND categories='{$filterCat}'";
if ($filterCo) $sql.=" AND Country='{$filterCo}'";
$sql.=" ORDER BY {$orderCol} {$orderWay}";


        // connect to the database 
        include('connect-db.php'); 
 
        // get results from database 
        $result = mysql_query($sql)  
                or die(mysql_error());   

$sqlCat="SELECT DISTINCT categories FROM responses ORDER BY 1";
        $rsCat = mysql_query($sqlCat)  
                or die(mysql_error());   

$sqlCo="SELECT DISTINCT Country FROM responses ORDER BY 1";
        $rsCo = mysql_query($sqlCo)  
                or die(mysql_error());   
                 
        // display data in table 
        // display data in table 
        echo "<p><b>View All</b> | <a href='view-paginated.php?page=1'>View Paginated</a></p>"; 
        echo "<form method=POST>";
        echo "<table border='0' cellpadding='10';>";
	echo "<tr><td>&nbsp;</td><td>&nbsp;</td>";
                echo "<th><select name=filterCat size=1 onChange='this.form.submit();'>";
	echo "<option value='' SELECTED>-choose a filter-</option>";
	while($row = mysql_fetch_array( $rsCat )) { 
		$isSel="";
		if ($row[0]==$filterCat) $isSel=" SELECTED";
		echo "<option value='{$row[0]}' {$isSel}>{$row[0]}</option>";
	}
	echo "</select></th><td>&nbsp;</td><td>&nbsp;</td>";
	echo "<th><select name=filterCo size=1 onChange='this.form.submit();'>";
	echo "<option value='' SELECTED>-choose a filter-</option>";
	while($row = mysql_fetch_array( $rsCo )) { 
		$isSel="";
		if ($row[0]==$filterCo) $isSel=" SELECTED";
		echo "<option value='{$row[0]}' {$isSel}>{$row[0]}</option>";
	}
	echo "</select></th><td>&nbsp;</td>";
        echo "<tr> <th>ID</th> <th>Title</th> <th>Category</th> <th>Description</th> <th>City</th><th>Country</th><th>Zip Code</th></tr>";	
 
        // loop through results of database query, displaying them in the table 
        while($row = mysql_fetch_array( $result )) { 
                // echo out the contents of each row into a table

                echo "<tr align='center' valign='top'>"; 
                echo '<td>' . $row['resid'] . '</td>'; 
//$res=$row['resid'];
                echo '<td>' . $row['title'] . '</td>';
                echo '<td>' . $row['categories'] . '</td>';

                //$position=255; // Define how many character you want to display.
                //$descp = substr($row['description'], 0, $position); 
	//echo '<td style="WORD-BREAK:BREAK-ALL">' . $descp . '</td>'; 
               echo '<td style="WORD-BREAK:BREAK-ALL">' . substr($row['description'],0,255).
             ( (strlen($row['description'])>255) ? " <a href='http://domain.com/registration/partrecord.php?resid={$row['resid']}'>read more&hellip;</a>" : ''). '</td>'; 
                echo '<td>' . $row['city'] . '</td>'; 
                echo '<td>' . $row['country'] . '</td>';
                echo '<td>' . $row['zipcode'] . '</td>'; 
                //echo '<td><a href="edit.php?id=' . $row['id'] . '">Edit</a></td>'; 
                //echo '<td><a href="delete.php?id=' . $row['id'] . '">Delete</a></td>'; 
                echo "</tr>"; 
                echo "<tr><td colspan='9'><hr></td></tr>";  
                      }  
        // close table> 
       echo "</table>"; 

?> 
<p><a href="new.php">Add a new record</a></p><p><a href='logout.php'>Logout</a></p>
</div>

</body>
</html>

Open in new window

0
Comment
Question by:adamssap
  • 2
4 Comments
 
LVL 17

Accepted Solution

by:
OmniUnlimited earned 250 total points
ID: 36515134
See my comment 09/06/11 01:30 AM, ID: 36487391 (the first expert post) on http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/Q_27292617.html for the code I use for pagination.
0
 
LVL 108

Assisted Solution

by:Ray Paseur
Ray Paseur earned 250 total points
ID: 36516087
The canonical article on PHP pagination is available from SitePoint, well-documented with good examples, here:
http://www.sitepoint.com/perfect-php-pagination/
0
 
LVL 1

Author Comment

by:adamssap
ID: 36524799
Thanks for your responses.  I added the pagination, url works, but no data is been displayed.
<?PHP
require_once("./include/membersite_config.php");

if(!$fgmembersite->CheckLogin())
{
    $fgmembersite->RedirectToURL("login.php");
    exit;
}

?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en-US" lang="en-US">
<head>
      <meta http-equiv='Content-Type' content='text/html; charset=utf-8'/>
      <title>Home page</title>
      <link rel="STYLESHEET" type="text/css" href="style/fg_membersite.css">
</head>
<body>
<div id='fg_membersite_content'>
<h2>Home Page</h2>
Welcome back <?= $fgmembersite->UserFullName(); ?>!
<?php 
$orderCol=$_POST['orderCol'];
$orderWay=$_POST['orderWay'];
$filterCat=$_POST['filterCat'];
$filterCo=$_POST['filterCo']; 

if (!$orderCol) $orderCol="resid";
if (!$orderWay) $orderWay="DESC";

$sql="SELECT * FROM responses WHERE 1=1";
if ($filterCat) $sql.=" AND categories='{$filterCat}'";
if ($filterCo) $sql.=" AND Country='{$filterCo}'";
$sql.=" ORDER BY {$orderCol} {$orderWay}";


        // connect to the database 
        include('connect-db.php'); 
$per_page = 3; 
 
        // get results from database 
        $result = mysql_query($sql)  
                or die(mysql_error());
$total_results = mysql_num_rows($result); 
$total_pages = ceil($total_results / $per_page);
        // check if the 'page' variable is set in the URL (ex: view-paginated.php?page=1) 
        if (isset($_GET['page']) && is_numeric($_GET['page'])) 
        { 
                $show_page = $_GET['page']; 
                 
                // make sure the $show_page value is valid 
                if ($show_page > 0 && $show_page <= $total_pages) 
                { 
                        $start = ($show_page -1) * $per_page; 
                        $end = $start + $per_page;  
                } 
                else 
                { 
                        // error - show first set of results 
                        $start = 0; 
                        $end = $per_page;  
                }                
        } 
        else 
        { 
                // if page isn't set, show first set of results 
                $start = 0; 
                $end = $per_page;  
        } 
         
        // display pagination 
         
        echo "<p><a href='login-home.php'>View All</a> | <b>View Page:</b> "; 
        for ($i = 1; $i <= $total_pages; $i++) 
        { 
                echo "<a href='view-paginated.php?page=$i'>$i</a> "; 
        } 
        echo "</p>"; 
   

$sqlCat="SELECT DISTINCT categories FROM responses ORDER BY 1";
        $rsCat = mysql_query($sqlCat)  
                or die(mysql_error());   

$sqlCo="SELECT DISTINCT Country FROM responses ORDER BY 1";
        $rsCo = mysql_query($sqlCo)  
                or die(mysql_error());   
                 
        // display data in table 
        echo "<form method=POST>";
        echo "<table border='0' cellpadding='10';>";
	echo "<tr><td>&nbsp;</td><td>&nbsp;</td>";
                echo "<th><select name=filterCat size=1 onChange='this.form.submit();'>";
	echo "<option value='' SELECTED>-choose a filter-</option>";
	while($row = mysql_fetch_array( $rsCat )) { 
		$isSel="";
		if ($row[0]==$filterCat) $isSel=" SELECTED";
		echo "<option value='{$row[0]}' {$isSel}>{$row[0]}</option>";
	}
	echo "</select></th><td>&nbsp;</td><td>&nbsp;</td>";
	echo "<th><select name=filterCo size=1 onChange='this.form.submit();'>";
	echo "<option value='' SELECTED>-choose a filter-</option>";
	while($row = mysql_fetch_array( $rsCo )) { 
		$isSel="";
		if ($row[0]==$filterCo) $isSel=" SELECTED";
		echo "<option value='{$row[0]}' {$isSel}>{$row[0]}</option>";
	}
	echo "</select></th><td>&nbsp;</td>";
        echo "<tr> <th>ID</th> <th>Title</th> <th>Category</th> <th>Description</th> <th>City</th><th>Country</th><th>Zip Code</th></tr>";	
// loop through results of database query, displaying them in the table  
        for ($i = $start; $i < $end; $i++)
        { 
                // make sure that PHP doesn't try to show results that don't exist 
                if ($i == $total_results) { break; } 
         
        // loop through results of database query, displaying them in the table 
        //while($row = mysql_fetch_array( $result )) { 
                // echo out the contents of each row into a table

                echo "<tr align='center' valign='top'>"; 
                echo '<td>' . $row['resid'] . '</td>'; 
//$res=$row['resid'];
                echo '<td>' . $row['title'] . '</td>';
                echo '<td>' . $row['categories'] . '</td>';

                //$position=255; // Define how many character you want to display.
                //$descp = substr($row['description'], 0, $position); 
	//echo '<td style="WORD-BREAK:BREAK-ALL">' . $descp . '</td>'; 
               echo '<td style="WORD-BREAK:BREAK-ALL">' . substr($row['description'],0,255).
             ( (strlen($row['description'])>255) ? " <a href='http://domain.com/registration/partrecord.php?resid={$row['resid']}'>read more&hellip;</a>" : ''). '</td>'; 
                echo '<td>' . $row['city'] . '</td>'; 
                echo '<td>' . $row['country'] . '</td>';
                echo '<td>' . $row['zipcode'] . '</td>'; 
                //echo '<td><a href="edit.php?id=' . $row['id'] . '">Edit</a></td>'; 
                //echo '<td><a href="delete.php?id=' . $row['id'] . '">Delete</a></td>'; 
                echo "</tr>"; 
                echo "<tr><td colspan='9'><hr></td></tr>";  
                      }  
        // close table> 
       echo "</table>"; 

?> 
<p><a href="new.php">Add a new record</a></p><p><a href='logout.php'>Logout</a></p>
</div>

</body>
</html>

Open in new window

0
 
LVL 1

Author Closing Comment

by:adamssap
ID: 36530212
Thanks
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
change password links 7 74
Parts and Products table schema in mysql 6 44
Alignment is not working correctly. 8 33
Website Interactive tool that will do calculations 3 31
Building a website can seem like a daunting task to the uninitiated but it really only requires knowledge of two basic languages: HTML and CSS.
Finding original email is quite difficult due to their duplicates. From this article, you will come to know why multiple duplicates of same emails appear and how to delete duplicate emails from Outlook securely and instantly while vital emails remai…
In this tutorial viewers will learn how to style elements, such a divs, with a "drop shadow" effect using the CSS box-shadow property Start with a normal styled element, such as a div.: In the element's style, type the box shadow property: "box-shad…
The viewer will learn how to dynamically set the form action using jQuery.

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now