Help with pagination.

pinger · March 12, 2009

I know this has been covered in quite a few instances but as I am only starting to learn mysql quite a bit is confusing to me, direct help would be appreciated. I have got a display page working showing the data from the database. My problem is that I want to show these registration detail tables in 3/4 columns and rows per page but haven't the first idea of how to do page pagination. I would like to be able to go through page after page as well.

I have put my code below and please ignore the fact that I may have been able to do it better in a different way, as I said earlier I am only learning to crawl.

<html>
<head>
</head>

<body>
<?php
$username="xxxxxxxxxxxxxxxxxxxxxx";
$password="xxxxxxxxxxxxxxx";
$database="xxxxxxxxxxxxxxxxxxxxxx";
$hostname = xxxxxxxxxxxxxxxxxxxxxxxxxx;
mysql_connect($hostname,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM playerreg";
$result=mysql_query($query);
$num=mysql_numrows($result);
mysql_close();
?>
<?php
$i=0;
while ($i < $num) {
$f1=mysql_result($result,$i,"cf_id");
$f2=mysql_result($result,$i,"text_1");
$f3=mysql_result($result,$i,"text_4");
$f4=mysql_result($result,$i,"date_6");
$f5=mysql_result($result,$i,"file_10");
$f6=mysql_result($result,$i,"select_11");
$f7=mysql_result($result,$i,"text_7");
$f8=mysql_result($result,$i,"select_3");
?>

<table style="width: 260px" cellspacing="0" cellpadding="0">
<tr>
  <td style="width: 260px">

<table style="width: 260px; float: left;" align="left" cellspacing="0" cellpadding="0" class="style8">
<tr>
  <td style="width: 260px" class="style4" valign="top">
  <table style="width: 300px; float: left;" class="style2" cellspacing="0" cellpadding="0">
   <tr>
    <td colspan="5" style="height: 3px"></td>
   </tr>
   <tr>
    <td colspan="5" class="style3" style="height: 12px"><strong>REGISTRATION</strong></td>
   </tr>
   <tr>
    <td colspan="5" style="height: 3px"></td>
   </tr>
   <tr>
    <td style="width: 5px" rowspan="6" class="style4"> </td>
    <td rowspan="6" class="style4">
    <img alt="" src="images/PlayerReg/<?php echo $f5; ?>" width="120" height="120"></td>
    <td rowspan="6" class="style4"></td>
    <td style="width: 150px" class="style10"><font face="Arial, Helvetica, sans-serif">
    <strong><?php echo $f3; ?></strong></font></td>
    <td rowspan="6" style="width: 5px" class="style4"> </td>
   </tr>
   <tr>
    <td style="width: 150px; height: 3px;" class="style10"></td>
   </tr>
   <tr>
    <td style="width: 150px" class="style10">
    <span class="style7">Squad No<br></span>
    <strong><?php echo $f7; ?></strong></td>
   </tr>
   <tr>
    <td style="width: 150px" class="style5">
    <span class="style12">DOB</span><strong><span class="style11"><br>
    </span>
    <?php echo $f4; ?></strong></td>
   </tr>
   <tr>
    <td valign="bottom" class="style13" style="width: 150px">ID No</td>
   </tr>
   <tr>
    <td valign="top" class="style5" style="width: 150px">
    <strong>U10-</strong><font face="Arial, Helvetica, sans-serif"><strong><?php echo $f1; ?> </strong></font></td>
   </tr>
   <tr>
    <td colspan="5" style="height: 3px"></td>
   </tr>
   <tr>
    <td colspan="5" class="style6"><strong><span class="style11"><?php echo $f2; ?></span> <span class="style11"><?php echo $f8; ?></span></strong></td>
   </tr>
   <tr>
    <td colspan="5" style="height: 3px"></td>
   </tr>
  </table>
<?php
$i++;
}
?></td>
</tr>
</table>

<body>
<html>

fenway · March 13, 2009

There are countless tutorials on this subject -- and many such posts/topics here as well.

pinger · March 13, 2009

I accept that there are, but I cannot find anything helpful regarding spreading over columns as well as rows. I have since my initial post used the example http://www.phpfreaks.com/tutorial/basic-pagination (don't want to list all the code and fill the page but I can if required and sucessfully have it up and running but I am still left with row output only.

Much appreciated for reading anyway.

pinger · March 13, 2009

Hindsight is a wonderful thing, just in case anyone thinks I didn't actually use the example.

I have included code below. sorry it includes styling at present.

<head>
<meta http-equiv="Content-Language" content="en-gb">
<style type="text/css">
.style2 {
border: 6px solid #808000;
}
.style3 {
text-align: center;
text-decoration: underline;
font-size: small;
color: #0000FF;
}
.style4 {
font-size: small;
}
.style5 {
background-image: url('images/images/7.jpg');
}
.style6 {
font-size: small;
color: #003300;
}
.style7 {
color: #003300;
}
</style>
</head>

<?php
// database connection info

// database connection info  
//$conn = mysql_connect('$host','$user','$password') or trigger_error("SQL", E_USER_ERROR);  
$conn = mysql_connect('xxxxxxxxxxxxxx','xxxxxxxxxxxxxxxxxxxxxxxx','xxxxxxxxxxxxxxxxxx') or trigger_error("SQL", E_USER_ERROR);
$db = mysql_select_db('xxxxxxxxxxxxxxxxxxxxx',$conn) or trigger_error("SQL", E_USER_ERROR);  

// find out how many rows are in the table 
$sql = "SELECT COUNT(*) FROM jos_chronoforms_playerreg ORDER by text_1" ;
$result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR);
$r = mysql_fetch_row($result);
$numrows = $r[0];

// number of rows to show per page
$rowsperpage = 4;
// find out total pages
$totalpages = ceil($numrows / $rowsperpage);

// get the current page or set a default
if (isset($_GET['currentpage']) && is_numeric($_GET['currentpage'])) {
   // cast var as int
   $currentpage = (int) $_GET['currentpage'];
} else {
   // default page num
   $currentpage = 1;
} // end if

// if current page is greater than total pages...
if ($currentpage > $totalpages) {
   // set current page to last page
   $currentpage = $totalpages;
} // end if
// if current page is less than first page...
if ($currentpage < 1) {
   // set current page to first page
   $currentpage = 1;
} // end if

// the offset of the list, based on current page 
$offset = ($currentpage - 1) * $rowsperpage;

// get the info from the db 
$sql = "SELECT select_3, cf_id, text_4, date_6, text_1, file_10 FROM jos_chronoforms_playerreg ORDER by text_1 LIMIT $offset, $rowsperpage";
$result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR);

// while there are rows to be fetched...
while ($list = mysql_fetch_assoc($result)) {
   // echo data


//Registration Card Layout
   ?>
<table style="width: 245px" class="style2">
<tr>
	<td colspan="5" style="height: 2px" class="style3"><strong>GBYL REGISTRATION</strong></td>
</tr>
<tr>
	<td style="width: 2px" class="style4"> </td>
	<td class="style4" style="width: 90px">
	<strong>
	<img alt="" src="components/com_chronocontact/uploads/PlayerReg/<?php echo $list['file_10']?>" width="100" height="124"></strong></td>
	<td style="width: 2px" class="style4"> </td>
	<td><table style="width: 100%; height: 124px;" class="style5">
		<tr>
			<td class="style6"><strong><?php echo $list['text_4']?></strong></td>
		</tr>
		<tr>
			<td class="style4">D.O.B. <span class="style7"> <strong> <?php echo $list['date_6']?></strong></span></td>
		</tr>
		<tr>
			<td class="style4">ID <strong> <span class="style7"> <?php echo $list['select_3']?> - <?php echo $list['cf_id']?></span></strong></td>
		</tr>
	</table>
	</td>
	<td style="width: 2px" class="style4"> </td>
</tr>
<tr>
	<td colspan="5" class="style3"><strong><?php echo $list['text_1']?></strong></td>
</tr>
</table>

<?php

} // end while
/******  build the pagination links ******/
// range of num links to show
$range = 3;

// if not on page 1, don't show back links
if ($currentpage > 1) {
   // show << link to go back to page 1
   echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=1'><<</a> ";
   // get previous page num
   $prevpage = $currentpage - 1;
   // show < link to go back to 1 page
   echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$prevpage'><</a> ";
} // end if 

// loop to show links to range of pages around current page
for ($x = ($currentpage - $range); $x < (($currentpage + $range) + 1); $x++) {
   // if it's a valid page number...
   if (($x > 0) && ($x <= $totalpages)) {
      // if we're on current page...
      if ($x == $currentpage) {
         // 'highlight' it but don't make a link
         echo " [<b>$x</b>] ";
      // if not current page...
      } else {
         // make it a link
 echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$x'>$x</a> ";
      } // end else
   } // end if 
} // end for

// if not on last page, show forward and last page links	
if ($currentpage != $totalpages) {
   // get next page
   $nextpage = $currentpage + 1;
    // echo forward link for next page 
   echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$nextpage'>></a> ";
   // echo forward link for lastpage
   echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$totalpages'>>></a> ";
} // end if
/****** end build pagination links ******/
?>

The coding may not be perfect but starts have to be made somewhere.

fenway · March 15, 2009

I accept that there are, but I cannot find anything helpful regarding spreading over columns as well as rows.

What does that mean??? And the code wasn't necessary...really.

pinger · March 15, 2009

The completed page will show registration details of players in a youth league where each table displays a particular players details.

At present I have 4 individual tables showing per page and was hoping that rather than have only the 4 tables per page I could increase this to say 16 as in 4 rows by 4 columns of individual tables before moving onto the next page, in other words utilise the whole page instead of just down the left hand side as it is presently showing.

If this is not possible please let me know or if this is known as something different to page pagination I do apologise in advance for posting in the wrong place

fenway · March 16, 2009

I suppose it is possible... but you'll still need to get the "horizontal" pagination working first.

scarhand · March 17, 2009

pagination works on the concept that you are dividing the mysql query into different chunks.

each page represents a different chunk that is selected.

so if you had 500 rows grabbed from a select query, and wanted to display 100 on each page, then 500/100=5

if youre on $_GET page 2, you'd change the LIMIT in the mysql query to 100, 100

then to display the actual page links you would use some PHP coding in order to break up the pages themselves into chunks so you don't have 200 page links.

Sign In

Help with pagination.

Recommended Posts

pinger

Link to comment

Share on other sites

fenway

Link to comment

Share on other sites

pinger

Link to comment

Share on other sites

pinger

Link to comment

Share on other sites

fenway

Link to comment

Share on other sites

pinger

Link to comment

Share on other sites

fenway

Link to comment

Share on other sites

scarhand

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information