Page 1 of 1

Help with putting database output into pages

Posted: Wed Mar 10, 2010 4:28 pm
by crazymao
i want to take information from my sql database and put it into pages with 10 outputs on each page. i have experimented with calculating how many pages are needed by getting the sum of all database entries and dividing it by ten but im stuck on creating links to these pages and with outputting the next 10 entries on the next pages. i thought of making an array consisting of all the pages aka page numbers and then creating a general link which would automatically generate links to these pages for example:
<a href='?page=" . $array . "'>" . $array . "</a>

if im not making sense please tell me ill try to rewrite it/be more clear.
any help with this or any info on what im trying to accomplish will be greatly appreciated. thanks

Re: Help with putting database output into pages

Posted: Wed Mar 10, 2010 5:27 pm
by joeyd
I am new to PHP myself but the built in function in Dreamweaver does it like this, if it helps...

Code: Select all

<?php require_once('Connections/conn.php'); ?>
<?php
 
$currentPage = $_SERVER["PHP_SELF"];
 
$maxRows_rslist = 10; //change this value to how many records to show per page.
$pageNum_rslist = 0;
if (isset($_GET['pageNum_rslist'])) {
  $pageNum_rslist = $_GET['pageNum_rslist'];
}
$startRow_rslist = $pageNum_rslist * $maxRows_rslist;
 
mysql_select_db($database_conn, $conn);
$query_rslist = "SELECT * FROM tblnews ORDER BY ArticleDate DESC";
$query_limit_rslist = sprintf("%s LIMIT %d, %d", $query_rslist, $startRow_rslist, $maxRows_rslist);
$rslist = mysql_query($query_limit_rslist, $conn) or die(mysql_error());
$row_rslist = mysql_fetch_assoc($rslist);
 
if (isset($_GET['totalRows_rslist'])) {
  $totalRows_rslist = $_GET['totalRows_rslist'];
} else {
  $all_rslist = mysql_query($query_rslist);
  $totalRows_rslist = mysql_num_rows($all_rslist);
}
$totalPages_rslist = ceil($totalRows_rslist/$maxRows_rslist)-1;
 
$queryString_rslist = "";
if (!empty($_SERVER['QUERY_STRING'])) {
  $params = explode("&", $_SERVER['QUERY_STRING']);
  $newParams = array();
  foreach ($params as $param) {
    if (stristr($param, "pageNum_rslist") == false && 
        stristr($param, "totalRows_rslist") == false) {
      array_push($newParams, $param);
    }
  }
  if (count($newParams) != 0) {
    $queryString_rslist = "&" . htmlentities(implode("&", $newParams));
  }
}
$queryString_rslist = sprintf("&totalRows_rslist=%d%s", $totalRows_rslist, $queryString_rslist);
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>
 
<body>
<?php do { ?>
  <?php echo $row_rslist['Headline']; ?><br />
  <?php } while ($row_rslist = mysql_fetch_assoc($rslist)); ?>
  
 
          <?php if ($pageNum_rslist > 0) { // Show if not first page ?>
          <a href="<?php printf("%s?pageNum_rslist=%d%s", $currentPage, 0, $queryString_rslist); ?>">First</a>
          <?php } // Show if not first page ?>
          
          <br />
          
          <?php if ($pageNum_rslist > 0) { // Show if not first page ?>
          <a href="<?php printf("%s?pageNum_rslist=%d%s", $currentPage, max(0, $pageNum_rslist - 1), $queryString_rslist); ?>">Previous</a>
          <?php } // Show if not first page ?>
          
          <br />
          
          <?php if ($pageNum_rslist < $totalPages_rslist) { // Show if not last page ?>
          <a href="<?php printf("%s?pageNum_rslist=%d%s", $currentPage, min($totalPages_rslist, $pageNum_rslist + 1), $queryString_rslist); ?>">Next</a>
          <?php } // Show if not last page ?>
          
          <br />
          
          <?php if ($pageNum_rslist < $totalPages_rslist) { // Show if not last page ?>
          <a href="<?php printf("%s?pageNum_rslist=%d%s", $currentPage, $totalPages_rslist, $queryString_rslist); ?>">Last</a>
          <?php } // Show if not last page ?>
          
          <br />
 
</body>
</html>
<?php
mysql_free_result($rslist);
?>

Re: Help with putting database output into pages

Posted: Wed Mar 10, 2010 5:48 pm
by crazymao
ok i got it, thanks alot

Re: Help with putting database output into pages

Posted: Wed Mar 10, 2010 6:04 pm
by joeyd
Sorry... It has sample connection info for a table in my database. You would simply change to fit your needs. Here is a generic code to make easier to see where you need to make changes to meet your needs...

Code: Select all

<?php
$hostname_conn = "YOURSERVERHOSTNAME";
$database_conn = "DATABASE_NAME";
$username_conn = "DB_USERNAME";
$password_conn = "DB_PASSWORD";
$conn = mysql_pconnect($hostname_conn, $username_conn, $password_conn) or trigger_error(mysql_error(),E_USER_ERROR); 
?>
<?php
 
$currentPage = $_SERVER["PHP_SELF"];
 
$maxRows_rslist = 10;
$pageNum_rslist = 0;
if (isset($_GET['pageNum_rslist'])) {
  $pageNum_rslist = $_GET['pageNum_rslist'];
}
$startRow_rslist = $pageNum_rslist * $maxRows_rslist;
 
mysql_select_db($database_conn, $conn);
$query_rslist = "SELECT * FROM TBL_NAME "; //Change the SQL to point to your table info.
$query_limit_rslist = sprintf("%s LIMIT %d, %d", $query_rslist, $startRow_rslist, $maxRows_rslist);
$rslist = mysql_query($query_limit_rslist, $conn) or die(mysql_error());
$row_rslist = mysql_fetch_assoc($rslist);
 
if (isset($_GET['totalRows_rslist'])) {
  $totalRows_rslist = $_GET['totalRows_rslist'];
} else {
  $all_rslist = mysql_query($query_rslist);
  $totalRows_rslist = mysql_num_rows($all_rslist);
}
$totalPages_rslist = ceil($totalRows_rslist/$maxRows_rslist)-1;
 
$queryString_rslist = "";
if (!empty($_SERVER['QUERY_STRING'])) {
  $params = explode("&", $_SERVER['QUERY_STRING']);
  $newParams = array();
  foreach ($params as $param) {
    if (stristr($param, "pageNum_rslist") == false && 
        stristr($param, "totalRows_rslist") == false) {
      array_push($newParams, $param);
    }
  }
  if (count($newParams) != 0) {
    $queryString_rslist = "&" . htmlentities(implode("&", $newParams));
  }
}
$queryString_rslist = sprintf("&totalRows_rslist=%d%s", $totalRows_rslist, $queryString_rslist);
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>
 
<body>
<?php do { ?>
  <?php echo $row_rslist['DB_FIELD_NAME']; // change to your table field name?><br />
  <?php } while ($row_rslist = mysql_fetch_assoc($rslist)); ?>
  
 
          <?php if ($pageNum_rslist > 0) { // Show if not first page ?>
          <a href="<?php printf("%s?pageNum_rslist=%d%s", $currentPage, 0, $queryString_rslist); ?>">First</a>
          <?php } // Show if not first page ?>
          
          <br />
          
          <?php if ($pageNum_rslist > 0) { // Show if not first page ?>
          <a href="<?php printf("%s?pageNum_rslist=%d%s", $currentPage, max(0, $pageNum_rslist - 1), $queryString_rslist); ?>">Previous</a>
          <?php } // Show if not first page ?>
          
          <br />
          
          <?php if ($pageNum_rslist < $totalPages_rslist) { // Show if not last page ?>
          <a href="<?php printf("%s?pageNum_rslist=%d%s", $currentPage, min($totalPages_rslist, $pageNum_rslist + 1), $queryString_rslist); ?>">Next</a>
          <?php } // Show if not last page ?>
          
          <br />
          
          <?php if ($pageNum_rslist < $totalPages_rslist) { // Show if not last page ?>
          <a href="<?php printf("%s?pageNum_rslist=%d%s", $currentPage, $totalPages_rslist, $queryString_rslist); ?>">Last</a>
          <?php } // Show if not last page ?>
          
          <br />
 
</body>
</html>
<?php
mysql_free_result($rslist);
?>

Re: Help with putting database output into pages

Posted: Wed Mar 10, 2010 6:05 pm
by crazymao
thanks alot mate

Re: Help with putting database output into pages

Posted: Wed Mar 10, 2010 6:08 pm
by joeyd
No problem. But to make it more clear for anyone else who runs across the thread... if u look at the code above... just change these line numbers to fit your needs...

Line 2 through 5
Line 20
Line 58

Change those to your environment and you are all set.