[php] Printing records with multipages?

[Decfor]

hey.
im back.

so im working on this PHP project, and on one page, i need to print out all record in a MySQL table. The thing is, i only want to have a limited number of records per page, as a variable.

say 10 for example.

how do i retrieve the records, but only print out 10 on each page?

(its a PM system....)

so far i have this:



<?php
include("session.php");
$page = $_GET['page'];
if($page==""){
$page='1';
}
$nextpage = $page + 1;
$prevpage = $page - 1;
$action = $_POST['action'];

mysql_connect ("localhost", "XXXXXX", "XXXXXX") or die ('Cannot connect to the database because: ' . mysql_error());
mysql_select_db ("XXXXXX");
$getuser = mysql_query("SELECT * FROM contacts WHERE user = '$user_info[0]'");

$userdetails = mysql_fetch_array($getuser);
$stat_id = $userdetails['stat_id'];
$getmail = mysql_query("SELECT * FROM `pms` WHERE `to` = '$stat_id' ORDER BY `timestamp` ASC");
?>
//break for html



//further down the page:

<?php
$numofrows = mysql_num_rows($getmail);

$maxperpage = 10;
if($numofrows > $maxperpage){
$use = $maxperpage;
}elseif($numofrows <= $maxperpage){
$use = $numofrows;
}
$lastpage = ceil($numofrows/$maxperpage);
if($page==1){
$i=0;
}else{
$i=($page*$maxperpage)-($maxperpage-1);
}
if($numofrows>=1){
echo 'You have: <strong>'.$numofrows.'</strong> messages';
echo '<br />';
echo'page: <strong>'.$page.'</strong> / <strong>'.$lastpage.'</strong>';
echo '<br>start row: '.$i;
echo'<table width="75%" border="0" cellspacing="5" cellpadding="0" align="center">';
echo'<tr>';
echo'<td width="50%" style="border: 1px #666666 solid;"><h3><div align="center">From:</div></h3></td>';
echo'<td width="50%" style="border: 1px #666666 solid;"><h3><div align="center">Sent:</div></h3></td>';
echo'</tr>';
echo'</table>';
echo'<br>';

}else{
echo'You have no mail.';
}


for ($i; $i < $use; $i++){
echo'<table width="75%" style="border: 1px #666666 solid;" cellspacing="5" cellpadding="0" align="center">';
echo '<tr>';
echo '<td>';
echo '<a href="stats.php?id=' . mysql_result( $getmail,$i,'sender_id') . '"><center>' . mysql_result( $getmail,$i,'from') . '</center></a></td>';
echo '<td><center>'. mysql_result( $getmail,$i,'sent') .'</center></td>';
echo '</tr>';

echo'<tr>';
echo'<td colspan="2" align="center"><hr width="100%"></td>';
echo'</tr>';

echo '<tr>';
echo '<td colspan="2" bgcolor="#333333" style="border: 1px #666666 solid;"><center><br>'. mysql_result( $getmail,$i,'message') .'<br><br></center></td>';
echo '</tr>';
echo '<tr>';
echo '<td width="50%" align="right">';
?>
<form name="Reply" method="post" action="newmail.php">
<input name="id" type="hidden" value="<?php echo $mail['sender_id']; ?>">
<input name="action" type="submit" class="general" id="action" value="Reply">
</form>
<?php echo '</td><td width="50%" align="left">'; ?>
<form name="Reply" method="post" action="inbox.php">
<input name="id" type="hidden" value="<?php echo $mail['sender_id']; ?>">
<input name="action" type="submit" class="general" id="action" value="Delete">
</form>
<?php
echo'</td>';
echo'</tr>';
echo '</table>';
echo'<br>';
}

echo'<table width="75%" border="0" cellspacing="5" cellpadding="0">';
echo'<tr>';
if($page!=1){
echo'<td><p align="left"><a href="inbox.php?page='.$prevpage.'">Previous</a></p></td>';
}
if($page!=$lastpage){
echo'<td><p align="right"><a href="inbox.php?page='.$nextpage.'">Next</a></p></td>';
}
echo'</tr>';
echo'</table>';

?>



the "if($numofrows > $maxperpage){" determines how many records there are. if there are less than the maximum allowed for each page, then print all of them. if not, then print the maximum.

$i is the row (record in the table) to start printing out from.

the for statement is supposed to get the start row, and print out records up the maximum number allowed for each page.

it does restrict to the correct amount per page, but when you view page 2, the records that should be there arent.

anyone any ideas why?

thanks, Decfor

[Decfor]

anyone?

if you need more information, or you want me to explain something, please post and tell me, and i will

decfor

[bwh2]

i don't have time to write it all out now, but use LIMIT.

[Decfor]

OK, i tried to use LIMIT, but it simply tells me i have 10 messages, and wont let me read the others.

how do i make it so that it only Shows 10, acknowledges that i have more tha that?

thanx, decfor

[CriTiCeRz]

If I'm right..... it's like this:


$getmail = mysql_query("SELECT * FROM `pms` WHERE `to` = '$stat_id' ORDER BY `timestamp` ASC LIMIT 0, 10");


But then if it's page 2, it would be LIMIT 11, 20... (I think).. I'm not sure about this!

[Decfor]

ok, but that means id have to code out each page. could i not do it like this:


$maxperpage = 10;
$startrow=($page*$maxperpage)-($maxperpage-1);
$endrow = $i=((page*$maxperpage)-($maxperpage-1)+9)
$getmail = mysql_query("SELECT * FROM `pms` WHERE `to` = '$stat_id' ORDER BY `timestamp` ASC LIMIT ".$startrow.", ".$endrow);

[bwh2]

you need 2 separate queries:
1) SELECT COUNT(*) FROM pms
2) select using LIMIT to get the ones you want.

[Decfor]

you need 2 separate queries:
1) SELECT COUNT(*) FROM pms
2) select using LIMIT to get the ones you want.

i dont understand.

can you give an example?

[CriTiCeRz]

I don't get what bwh2 is saying... but instead of doin that query ... do this:


$getmail = mysql_query("SELECT * FROM pms WHERE to = '$stat_id' ORDER BY timestamp ASC LIMIT {$startrow}, {$endrow}");
You can insert variables in ""s using {}'s. :P

[Decfor]

I don't get what bwh2 is saying... but instead of doin that query ... do this:


$getmail = mysql_query("SELECT * FROM pms WHERE to = '$stat_id' ORDER BY timestamp ASC LIMIT {$startrow}, {$endrow}");
You can insert variables in ""s using {}'s. :P


thanks

(thanks anyways bwh2 :))

[CriTiCeRz]

So did it work?

[Decfor]

im working on it now (i used a for statement, so i have to rethink it :P)

[bwh2]

you need 2 separate queries:
1) SELECT COUNT(*) FROM pms
2) select using LIMIT to get the ones you want.
^ i was responding to: "how do i make it so that it only Shows 10, acknowledges that i have more tha that?"

1) shows you how many total you have
2) selects only the 10

i don't have much time now. i'll try to respond later with a more thorough answer.

[Decfor]

ok. thanks :)

[binime]

If I'm right..... it's like this:


$getmail = mysql_query("SELECT * FROM `pms` WHERE `to` = '$stat_id' ORDER BY `timestamp` ASC LIMIT 0, 10");


But then if it's page 2, it would be LIMIT 11, 20... (I think).. I'm not sure about this!

The syntax for SQL's LIMIT Command is

LIMIT start, max

so you could have LIMIT 10, 20. LIMIT 20, 20 etc

Just have a variable in your url, something like www.mypage.com/inbox.php&show=10 (http://www.mypage.com/inbox.php&show=10)

then

$start = intval( $_GET['show'] );

LIMIT $start, 20

[Decfor]

ah! thats what im doing wrong! thank you kind sir!

[Voetsjoeba]

Here's a pagination class I'm using in a project. It is designed to output a list of links to the next pages, like this:

< Previous 1 2 3 4 5 6 7 Next >

If you have lots of pages, it'll only show a certain amount (what is called the 'cutoff' in this class) at the beginning, at the end and around the current page. For example:

< Previous 1 2 3 4 5 6 7 8 ... 122 123 124 125 Next >

< Previous 1 2 3 4 ... 119 120 121 122 123 124 125 Next >

< Previous 1 2 3 4 ... 56 57 58 59 60 61 62 63 64 ... 122 123 124 125 Next >

The constructor takes 4 arguments: the current page you're on, the total amount of records you have (not the amount of pages), the amounts of records to show per page, and a url prefix to append &p= to.

The project I'm using it in has a global object named $SETTINGS, that holds various information including the pagination cutoff. Unless you have the exact same setup as me, you don't have that available, so I hardcoded in the value 4. It also holds the total amount of rows for each data source I want to paginate, so I guess in your case you'd have to get that count every time, which isn't the right thing to do. However, this allows for you to improve upon that by caching that number of rows (which is exactly what my $SETTINGS object does) :)

It outputs the generated pagination by wrapping it in a div container, making it easy for you to apply certain stylesheets on it.

Here it is:



<?php

class Pagination {

public $curPage;
public $totalItems;
public $itemsPerPage;
public $urlPrefix;

public $pageCount;
public $links;
public $cutOff;

function __construct( $curPage , $totalItems , $itemsPerPage , $urlPrefix ){

$this->curPage = $curPage;
$this->totalItems = $totalItems;
$this->itemsPerPage = $itemsPerPage;
$this->urlPrefix = $urlPrefix;

if( $this->totalItems > $this->itemsPerPage ) $this->init( );

}

private function wrapInContainer( $what ){
echo '<div class="pagination">'.$what.'</div>';
}

private function init( ){

//global $SETTINGS;

$this->links = array( );
$this->cutOff = 4;//$SETTINGS->get( 'PAGINATION_CUTOFF' );
$this->pageCount = ceil( $this->totalItems / $this->itemsPerPage );

if( $this->curPage > 1 ){
$links[] = '<a href="'.$this->urlPrefix.'&p='.( $this->curPage - 1 ).'">&lt;&nbsp;Previous</a>';
}

for( $i = 1; $i <= $this->pageCount ; $i++ ){

if(
( $i > ( $this->cutOff + 1 ) && $i < ( $this->curPage - $this->cutOff ) ) ||
( $i > ( $this->curPage + $this->cutOff ) && $i < ( $this->pageCount - $this->cutOff ) )
){

$links[] = '.';

} else {

if( $i == $this->curPage ){

$links[] = '<b>'.$i.'</b>';

} else {

$links[] = '<a href="'.$this->urlPrefix.'&p='.$i.'">'.$i.'</a>';

}

}

}

$imploded = implode( ',' , $links );
$imploded = preg_replace( '/(\.,)+/' , '...,' , $imploded );
$links = explode( ',' , $imploded );

if( $this->curPage < $this->pageCount ){
$links[] = '<a href="'.$this->urlPrefix.'&p='.( $this->curPage + 1 ).'">Next&nbsp;&gt;</a>';
}

$output = implode( ' ' , $links );
$this->wrapInContainer( $output );

}

}

?>

[Decfor]

hey, thanks dude.

sorry i havent been on here in a ahwile, my life just stepped a notch, had lotsa stuff to do.

i havent actually tried this out yet, but im getting onto it now.

i'll post again when ive done it.

thanks again!

<edit>
question:
this code you just gave me.

should i actually imput this into my page, or save it as a separate pagination.php and include/require it?

also, where exactly do i define the 4 arguements it requires?
(i havent coded any php in over 2 months *embarrased*)
</edit>

[Voetsjoeba]

hey, thanks dude.

sorry i havent been on here in a ahwile, my life just stepped a notch, had lotsa stuff to do.

i havent actually tried this out yet, but im getting onto it now.

i'll post again when ive done it.

thanks again!

<edit>
question:
this code you just gave me.

should i actually imput this into my page, or save it as a separate pagination.php and include/require it?

also, where exactly do i define the 4 arguements it requires?
(i havent coded any php in over 2 months *embarrased*)
</edit>

I suggest you save that code as a seperate file and include it, since it is a class definition. The 4 arguments it requires are:

1) The page you're currently viewing. This should be the page number you get from the querystring, eg. file.php?page=2
2) The total amount of records you want to paginate. If you have 1000 records you want to paginate showing 50 per page, then 1000 is your number.
3) The amount of records to show per page. From this number and the total amount of records, the class will determine how many pages there are.
4) The URL you want to link the pages to. I called this the URL Prefix because the class will append ?page=<a number here> to this URL to link the page numbers to.

These arguments are values that you should calculate on the page you're using the pagination on. To add the pagination to your page, simply instantiate it.

[Decfor]

I suggest you save that code as a seperate file and include it, since it is a class definition. The 4 arguments it requires are:

1) The page you're currently viewing. This should be the page number you get from the querystring, eg. file.php?page=2
2) The total amount of records you want to paginate. If you have 1000 records you want to paginate showing 50 per page, then 1000 is your number.
3) The amount of records to show per page. From this number and the total amount of records, the class will determine how many pages there are.
4) The URL you want to link the pages to. I called this the URL Prefix because the class will append ?page=<a number here> to this URL to link the page numbers to.

These arguments are values that you should calculate on the page you're using the pagination on. To add the pagination to your page, simply instantiate it.

ok, thanks.

But, what exactly do you mean by "To add the pagination to your page, simply instantiate it."

[Voetsjoeba]

ok, thanks.

But, what exactly do you mean by "To add the pagination to your page, simply instantiate it."



new Pagination( $curPage , $totalItems , $itemsPerPage , $urlPrefix ); will output the pagination on that position.

[Decfor]

new Pagination( $curPage , $totalItems , $itemsPerPage , $urlPrefix ); will output the pagination on that position.

ok, so if i am using the pagination class (which is stored as pagination.php) to paginate inbox.php, i have to define
$curPage, $totalItems, $itemsPerPage and $urlPrefix
as variables on my inbox.php page, then input:

new Pagination( $curPage , $totalItems , $itemsPerPage , $urlPrefix );into my inbox.php page?

where abouts do i insert this code?

at the start, or where i want the pagionation to appear?

sorry about all the questions, but i was never brilliant at PHP to begin with, and i havent coded any for months, so im a but rusty... :sure:

[Decfor]

ok, i had a go at implementing it, but i just remembered:

im running PHP version 4.4.3.

i get errors for the lines in the pagination script with "public" or "protected" in them.

is this script by any chance not compatible with PHP<5?

[Voetsjoeba]

This is a PHP5 class, yes. Converting it to PHP4 right now, hang on. To answer your other question - you indeed put that new Pagination( ... line at the spot you want the pagination to appear.

[Voetsjoeba]

<?php

class Pagination {

var $curPage;
var $totalItems;
var $itemsPerPage;
var $urlPrefix;

var $pageCount;
var $links;
var $cutOff;

function Pagination( $curPage , $totalItems , $itemsPerPage , $urlPrefix ){

$this->curPage = $curPage;
$this->totalItems = $totalItems;
$this->itemsPerPage = $itemsPerPage;
$this->urlPrefix = $urlPrefix;

if( $this->totalItems > $this->itemsPerPage ) $this->init( );

}

function wrapInContainer( $what ){
echo '<div class="pagination">'.$what.'</div>';
}

function init( ){

$this->links = array( );
$this->cutOff = 4;
$this->pageCount = ceil( $this->totalItems / $this->itemsPerPage );

if( $this->curPage > 1 ){
$links[] = '<a href="'.$this->urlPrefix.'&p='.( $this->curPage - 1 ).'">&lt;&nbsp;Previous</a>';
}

for( $i = 1; $i <= $this->pageCount ; $i++ ){

if(
( $i > ( $this->cutOff + 1 ) && $i < ( $this->curPage - $this->cutOff ) ) ||
( $i > ( $this->curPage + $this->cutOff ) && $i < ( $this->pageCount - $this->cutOff ) )
){

$links[] = '.';

} else {

if( $i == $this->curPage ){

$links[] = '<b>'.$i.'</b>';

} else {

$links[] = '<a href="'.$this->urlPrefix.'&p='.$i.'">'.$i.'</a>';

}

}

}

$imploded = implode( ',' , $links );
$imploded = preg_replace( '/(\.,)+/' , '...,' , $imploded );
$links = explode( ',' , $imploded );

if( $this->curPage < $this->pageCount ){
$links[] = '<a href="'.$this->urlPrefix.'&p='.( $this->curPage + 1 ).'">Next&nbsp;&gt;</a>';
}

$output = implode( ' ' , $links );
$this->wrapInContainer( $output );

}

}

?>

That should be it.

[deletedUser2352352]

Thats wicked cheers for this class. Its very useful.

[Decfor]

got it working now.

i mismatched the GET names.

the class you gave me appended the URL with &p=<num>

whereas i needed it to be ?page=<num>

but, as bugboy says, this is an excellent, very useful class.

much appreciated! :D

[darknessmdk]

I've never used classes before, how do I implement this script
into what I have?


<?PHP //Search Results

// $priceresult = mysql_query("SELECT price FROM properties");



// echo $state;



$query_count = "SELECT * FROM gallery";



$result_count = mysql_query($query_count);

$totalrows = mysql_num_rows($result_count);



if(empty($page)){

$page = 1;
}

$pages = ceil($totalrows / $limit);



$limitvalue = $page * $limit - ($limit);


$query_order = " LIMIT $limitvalue, $limit";

$query = $query_count."".$query_order ;
//echo $query_count;

$result = mysql_query($query) or die("Error: " . mysql_error());

//$row=mysql_fetch_array($result);

$countrows = mysql_num_rows($result);
if($countrows == NULL) {

echo ("No images currently uploaded");


// echo ("<center>There are currently no listings</center>");

} else {
echo ("<table border='0' cellspacing='0' cellpadding='5' align='center'>");
if($result && mysql_num_rows($result) > 0)
{

$i = 0;

$max_columns = 3;

while($row = mysql_fetch_array($result))

{

// make the variables easy to deal with

extract($row);

// open row if counter is zero

if($i == 0){

echo "<tr>";

}

echo ("<td align='center'><a href=\"javascript:openwindow('$row[image]',500,375,600,525,'The Better Basement Company')\" class=\"linkcolor\"><img src='$row[image]' width='182' height='124' border='1' class=\"linkcolor\" /></a></td>");


// increment counter - if counter = max columns, reset counter and close row

if(++$i == $max_columns)

{

echo "</tr>";

$i=0;

} // end if

} // end while

} // end if results
// clean up table

if($i < $max_columns)

{

for($j=$i; $j<$max_columns;$j++)

echo "";

}
echo ("</table>");
}
?>