[SOLVED] new page, with page numbers

saltedm8 · October 5th, 2007, 09:21 AM

i have limited the amount of database info to be displayed on my blog, and i am trying to dynamically create a second page, third page etc... as the information is inputted into the database, so if i had

page 1 limit 5

if there are 6, then create next page and it goes on

http://www.mywebsite.com/mypage.php?id=2
http://www.mywebsite.com/mypage.php?id=3
etc...

also a numbering system, p1,p2,p3,p4 linking to newly created 'pages'

how can i do that ?

thanks

Code:


			
<?php error_reporting(E_ALL);?>
<?php include 'config.php';?>
<?php
$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');
mysql_select_db( $dbname, $conn );?>
<?php
$sql = "SELECT * FROM form order by date desc limit 5 ";
$res = mysql_query($sql, $conn) or die(mysql_error());
while ($row = mysql_fetch_object($res)) {
    
     print  $row->title ;
  echo nl2br("\n");
  print $row->date ;
  print '<br></br>';
  print $row->blog_mes;
  print '<br></br>';
}
?>

c010depunkk · October 5th, 2007, 10:16 AM

I'm working on a solution. Give me a bit of time.... [HINT:] I'm using the mysql_num_rows function....

Rakuli · October 5th, 2007, 10:17 AM

You just need to set a starting point and limit in your query.

You can do this by doing a count of the number of results in the database.

Here's a simple example I just whipped up, commented for your viewing pleasure

Code:


			
<?php include 'config.php';



$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');

mysql_select_db( $dbname, $conn );







$perPage = 5; // number to display on per page



// Start by counting the number of posts



$res = mysql_query ("SELECT count(*) AS num FROM form") or die (mysql_error() . 'dying hurts');



$num = mysql_fetch_assoc($res);

$num = $num['num'];



mysql_free_result($res);



// now work out how many pages there are, check if page has been sent via GET



$numPages = ceil($num / $perPage);



// Check to make sure pagenum will be 1 or greater, less than numpages and an actual number

$pageNum  = (!empty($_GET['id']) && 

             is_numeric($_GET['id']) && 

             $_GET['id'] > 1) 

             

             ? ($_GET['id'] < $numPages ? int ($_GET['id']) : $numPages) : 1; // Set to 1 if not set or something dodgy



// Okay, now we run the query and set the limits LIMIT xx, yy (x= start from , y= limit)



$sql = "SELECT * FROM form order by date desc LIMIT " . ($pagenum - 1) * $perPage . ", $perPage";

$res = mysql_query($sql, $conn) or die(mysql_error());



while ($row = mysql_fetch_object($res)) {         



print  $row->title ;  

echo nl2br("\n");  

print $row->date ; 

print '<br></br>'; 

print $row->blog_mes;  

print '<br></br>';

}



// Some paginananananananation



echo '<div>';

for ($i = 1; $i <= $numPages; $i++)

{

    if ($pageNum != $i)

    echo '<a href="thispage.php?id=', $i, '">', $i, '</a> ';

    else 

    echo '<b>', $i, '</b>';

}

echo '</div>';



?>

c010depunkk · October 5th, 2007, 10:25 AM

ok, looks like Rakuli beat me to it...

Rakuli · October 5th, 2007, 10:29 AM

Quote:

Originally Posted by c010depunkk

ok, looks like Rakuli beat me to it...

Sorry C010depunk

Always rearing for a pagination script.

c010depunkk · October 5th, 2007, 10:32 AM

I didn't know about the LIMIT 3,7 command. (I'm assuming that selects results from #3 to #7, correct me if not...) I was going to use mysql_data_seek, but LIMIT is much more efficient, especially with larger databases.

saltedm8 · October 5th, 2007, 10:34 AM

thank you for your help, but i am recieving this

Quote:

Notice

Quote:

Undefined variable: pagenum in c:\program files\easyphp1-8\www\blog\main.php on line 31

Quote:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-5, 5' at line 1

Rakuli · October 5th, 2007, 10:38 AM

Nearly but not quite.

LIMIT 3, 7 would select starting from result 3 and give you 7 results.

If you wanted #3 - #7 you would use LIMIT 3, 4

You want to try get as much processing and limiting done with SQL as possible as by comparison, MYSQL is much faster than PHP.

Rakuli · October 5th, 2007, 10:41 AM

Okay, change this line

Code:


			
$sql = "SELECT * FROM form order by date desc LIMIT " . ($pagenum - 1) * $perPage . ", $perPage";

to:

Code:


			
$sql = "SELECT * FROM form order by date desc LIMIT " . ($pageNum - 1) * $perPage . ", $perPage";

I forgot to capitalise the 'N'

saltedm8 · October 5th, 2007, 10:45 AM

wow, thats fantasically kind of you thank you - works great