Jump to content


Photo

how to add pagination??!?


  • Please log in to reply
11 replies to this topic

#1 techiefreak05

techiefreak05
  • Members
  • PipPipPip
  • Advanced Member
  • 494 posts
  • LocationER, MN

Posted 12 September 2006 - 03:33 AM

i have this code that shows a users friends' bulletins... how can i add pagination to limit 5 per page..... if i dont have pagination.. it just shows all the bulletins on one page.. it looks ugly!!

<?php
$user = $_SESSION[username];
$queryF="SELECT * FROM friends WHERE `username` = '$_SESSION[username]' AND `accepted` = 'yes'";
$resultF=mysql_query($queryF);
while($array=mysql_fetch_assoc($resultF)){
$poster=$array[friend];

$queryB="SELECT * FROM `bulletins` WHERE `from` = '$poster' ORDER by 'id' DESC";

$resultB=mysql_query($queryB);

while($array=mysql_fetch_assoc($resultB)){

echo "<table bgcolor=#99CCFF width=200><tr bgcolor=#548099><td height=28><b><font color=white>From: <a href='getInfo.php?user=" . $array['from'] . "'>" . $array['from'] . "</a></b><br><b>Date: </b>" .$array['date'];
 echo "</b></font></td></tr></table>";
 echo "<table bgcolor=#99CCFF width=200><tr bgcolor=#356B8B><td height=25><center><a href=\"showBulletin.php?id=".$array['id']."\">Subject:  ".$array['subject']."</a></center>";
  echo "</td></tr></table><hr>";
 }
}
?>

the "SELECT * FROM friends ..." part only goes into the friends table to see who is ur friend.. the SECOND mysql query is the buletins.. which is what i want to paginate
Link shortener with advanced, detailed statistics:

http://tyny.us/

#2 techiefreak05

techiefreak05
  • Members
  • PipPipPip
  • Advanced Member
  • 494 posts
  • LocationER, MN

Posted 12 September 2006 - 07:04 AM

anyone? please. if you could help that'd be fabulous :-)

thanks cheers.
Link shortener with advanced, detailed statistics:

http://tyny.us/

#3 jwwceo

jwwceo
  • Members
  • PipPipPip
  • Advanced Member
  • 212 posts

Posted 12 September 2006 - 07:08 AM

http://www.phpfreaks...orials/43/0.php

#4 techiefreak05

techiefreak05
  • Members
  • PipPipPip
  • Advanced Member
  • 494 posts
  • LocationER, MN

Posted 12 September 2006 - 08:03 AM

yeah .. um ... i just tried that.. and it completely messed upi my whole page... i posted the error...
Link shortener with advanced, detailed statistics:

http://tyny.us/

#5 HuggieBear

HuggieBear
  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 12 September 2006 - 09:27 AM

Maybe I'm missing something, but I can't see any attempt at pagination in the code example of your first post.

You've got all the query info, but no attempt at pagination.

Are you expecting your first query (where you select the friends) to return more than one row?

Regards
Rich
Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools

#6 SEV3N

SEV3N
  • New Members
  • Pip
  • Newbie
  • 1 posts

Posted 12 September 2006 - 10:24 AM

//CONNECT AND SELECT DATABASE

if(!isset($_GET['page'])){ $page = 1; } else { $page = $_GET['page']; }
if($page == "") { $page = 1; }
if (preg_match("/[^0-9]/", $page)) { $page = 1; }

$max_results = 5; // MAXIMUM RESULTS PER PAGE
$from = (($page * $max_results) - $max_results);

// YOUR QUERY GOES HERE
$query = mysql_query("SELECT * FROM `reg` LIMIT $from, $max_results");
$total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM `reg`"),0);
// end OF YOUR QUERY

if ( $total_results == 0 ) { print 'ITEM NOT FOUND'; } // NOTHING FOUND MESSAGE
$total_pages = ceil($total_results / $max_results);
if ($page > $total_pages && $page <> "1") { print "Page $page not found"; }

$first = $from + 1;
$i = 0;
while($row = mysql_fetch_array($query)){
$last = $first + $i;
$i = $i + 1;
$name = $row['name'];

// PRINT HERE WHAT YOU WANT
print "Name: $name<br>";
// END OF PRINTING
}

// THE ACTUAL PAGINATION STARTS HERE
print 'Select a page';
// PREVIOUS 
if($page > 1){ $prev = ($page - 1); echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$prev\">Previous</a> "; } 
for($i = 1; $i <= $total_pages; $i++){ 
    if(($page) == $i){ echo "$i "; } else { echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$i\">$i</a> "; } 
} 
// NEXT 
if($page < $total_pages){ 
    $next = ($page + 1); 
    echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$next\">Next</a>"; 
}
This should do it.
Make sure to keep the same structure for the query.
$max_results is the number of items per page.
Since you are using the $_GET method the page number is shown in the URL so this code checks if the user input is valid (that's for safety).
www.vtop.byethost32.com - is a demo website that I made. Go to "Buy a Developed Web Site to see the code in action. I'm using $_POST there.
Hope this helps.

#7 Jenk

Jenk
  • Members
  • PipPipPip
  • Advanced Member
  • 778 posts

Posted 12 September 2006 - 10:39 AM

PHP_SELF can be tainted with user input, so don't use it. Use SCRIPT_NAME instead.

and pagination in it's most simplest form:
<?php

$link = mysql_connect(/* db details */) or die('Connection error');
mysql_select_db(/* db */, $link) or die('Db error');

if (!empty(trim((int)$_GET['page']))) 
{ 
    $page = (int) $_GET['page'];
}
else
{
    $page = 1;
}

$max = 10;
$start = $max * ($page -1);

if (!$result = mysql_query("SELECT * FROM `table` LIMIT $start, $max", $link))
{
    die ('Query error');
}

while ($row = mysql_fetch_assoc($result))
{
    foreach ($row as $cell => $val)
    {
        echo '<p>' . htmlentities($cell) . ' => ' . htmlentities($val) . '</p>' . chr(10);
    }
}


$result = mysql_query('SELECT COUNT(*) AS A FROM `table`');
$numpages = ceil(mysql_result($result, 0) / $max);

for ($i = 1; $i < $numpages; $i += $max)
{
    $j = $i / $max;
    echo '<a href="' . htmlentities($_SERVER['SCRIPT_NAME']) . '?page=' . $j . '">' . $i . '</a>&nbsp;';
}



?>


#8 HuggieBear

HuggieBear
  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 12 September 2006 - 10:44 AM

I've taken code directly from the tutorial on this site and wrapped it around your code, and I think I have it working.

If, as I suspect, you want to select bulletins from multiple friends all at once, but only have one header for each friend with their bulletins listed beneath them (and still only limit to 5 per page) then let me know, as I have the working code for this.  See here (I have a limit of 3 records per page).

Regards
Rich
Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools

#9 techiefreak05

techiefreak05
  • Members
  • PipPipPip
  • Advanced Member
  • 494 posts
  • LocationER, MN

Posted 13 September 2006 - 03:56 AM

hey HuggieBear
yeah could i get that code? that would be FANTASTIC!! lol thx!!!
Link shortener with advanced, detailed statistics:

http://tyny.us/

#10 Jenk

Jenk
  • Members
  • PipPipPip
  • Advanced Member
  • 778 posts

Posted 13 September 2006 - 08:46 AM

Why don't you use code from the umpteen other examples??

.. beacause you rather someone did *all* of your work for you, for free, while you learn nothing at all?

#11 HuggieBear

HuggieBear
  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 13 September 2006 - 09:00 AM

hey HuggieBear, yeah could i get that code? that would be FANTASTIC!! lol thx!!!


I'll post it after I've made the comments on it, they should help you to understand it a bit better and hopefully help you to do it yourself in the future.  Incidently, I managed to achieve it just by searching posts on this forum.

Regards
Rich

EDIT: Code below.  You'll need to add your own SQL statements (which I'm happy to 'help' with if you provide your table structure) and in the fields where I've left my $row['column_name'] variables, you'll need to substitute these for your own.

<?php
// Your user for use in the select statement (YOUR CODE)
	$user = $_SESSION['username'];
	
// Include your DB connection stuff here
	include('connect.php');
	
// If page number exists, use it, if not, set one (PAGINATION)
if(!isset($_GET['page'])){
	$page = 1;
} else {
	$page = $_GET['page'];
}

// Define the number of results per page (PAGINATION)
$max_results = 3;

// Figure out the limit for the query based on the current page number (PAGINATION)
$from = (($page * $max_results) - $max_results);

// I've not included a sql statement as you'll need to write this based on your table structure
// but remember to include the important bit which is the LIMIT as we need this for pagination
// LIMIT $from, $max_results
$sql="Your sql statement goes here";
$result = mysql_query($sql);
$friend = null; // This must be set outside the while loop (FRIEND HEADER)
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)){
   $user_id = $row['username']; // Start of header code (FRIEND HEADER)
   if (is_null($friend) || strcmp($friend,$user_id) !=0){
      $friend = $row['username'];
      // I had a tidy up of what you had already using heredoc (YOUR CODE)
      echo <<<HTML
        <br>
 	<table bgcolor="#99CCFF" width="500">
	  <tr bgcolor="#99CCFF">
	    <td height="28">
	      <font color="white"><b>From: $row[username]</b></font>
	    </td>
	  </tr>
	</table>
HTML;
// End of friend header code (FRIEND HEADER)
   }
$bg = ($bg == "#CCFF99" ? "#CCCC99" : "#CCFF99"); // Assign color to variable (COLOR ALTERNATION)
	// I had a tidy up of what you had already using heredoc (YOUR CODE)
   echo <<<HTML
	<table bgcolor="#99CCFF" width="500">
	  <tr bgcolor="$bg"> <!-- Notice the variable instead of color (COLOR ALTERNATION) -->
	    <td height="25">
	      <font color="white"><b>Date: </b>$row[date] - </font>$row[title]
	    </td>
	  </tr>
	</table>
HTML;
}

// Figure out the total number of results in DB (PAGINATION)
// This sql statement will be almost identical to what you have above.
// we can't just use 'SELECT * FROM bulletins' as we don't want to know
// all the results, only a select few.
$total_results = mysql_result(mysql_query("Same as previous query"),0);

// Figure out the total number of pages. Always round up using ceil()
$total_pages = ceil($total_results / $max_results);

//Previous and next links
$plink = "&lt;&lt; Previous";
$nlink = "Next &gt;&gt;";

echo "<center>";

// Build Previous Link
if($page > 1){
	$prev = ($page - 1);
	echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$prev\">$plink</a> ";
}

for($i = 1; $i <= $total_pages; $i++){
	if(($page) == $i){
		echo "$i ";
	}
	else {
		echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$i\">$i</a> ";
	}
}

// Build Next Link
if($page < $total_pages){
	$next = ($page + 1);
	echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$next\">$nlink</a>";
}

echo "</center>";
?>

Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools

#12 techiefreak05

techiefreak05
  • Members
  • PipPipPip
  • Advanced Member
  • 494 posts
  • LocationER, MN

Posted 14 September 2006 - 02:34 AM

Please keep all help contained on these forums
Link shortener with advanced, detailed statistics:

http://tyny.us/




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users