[SOLVED] I need help with pagenation

[vetman]

My code  for pagenation works, but my output from the database lists all the items (48) instead of the 10 per page for a total of 5 pages. At  the bottom of the page it lists the pages as required, but when I goto the next page I get the same list as the first page but labeled as page 2, etc.

Can anyone help me with this problem?

Thanks in advance!

 

<?php
// Make a MySQL Connection
include 'config.php';

$con = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');
mysql_select_db("rwts_webmaster") or die(mysql_error());

echo "Connected to Database <br>";

// Retrieve all the data from the "example" table
$result = mysql_query("SELECT * FROM example ORDER BY age") or die(mysql_error());

    if($_GET['page']) // Is page defined?
    {
        $page = $_GET['page']; // Set to the page defined
    }else{
        $page = 1; // Set to default page 1
    }
$max = 10; // Set maximum to 10

$cur = (($page * $max) - $max); // Work out what results to show

$counttotal = mysql_query ("SELECT * FROM example ORDER BY age") or die(mysql_error());
$counttotal = mysql_num_rows($counttotal); // count records
echo $counttotal;
echo "<br>";


$total_pages = ceil($counttotal / $max); // dive the total, by the maximum results to show
echo $total_pages;
echo "<br>";

// store the record of the "example" table into $row
echo "<table border='1' width='400'>";
echo "<tr> <th>Name</th> <th>Age</th> </tr>";
// keeps getting the next row until there are no more to get
while($row = mysql_fetch_array( $result )) {
// Print out the contents of each row into a table
echo "<tr><td>";
echo $row['name'];
echo "</td><td>";
echo $row['age'];
echo "</td></tr>";
}

echo "</table>";

if($page > 1){ // is the page number more than 1?
                $prev = ($page - 1); // if so, do the following. take 1 away from the current page number
                echo '<a href="?page=' . $prev . '">ｫ Previous</a>'; // echo a previous page link
                }

for($i = 1; $i <= $total_pages; $i++) // for each page number
                {
                    if($page == $i) // if this page were about to echo = the current page
                        {
                            echo'<b>' . $i .'</b> '; // echo the page number bold
                                } else {
                            echo '<a href="?page=' . $i . '">' . $i . '</a> '; // echo a link to the page
                        }
                }

if($page < $total_pages){ // is there a next page?
                    $next = ($page + 1); // if so, add 1 to the current
                echo '<a href="?page=' . $next . '">Next ｻ</a>'; // echo the next page link
                    }

// store the record of the "example" table into $row
echo "<table border='1' width='400'>";
echo "<tr> <th>Name</th> <th>Age</th> </tr>";
// keeps getting the next row until there are no more to get
while($row = mysql_fetch_array( $result )) {
// Print out the contents of each row into a table
echo "<tr><td>";
echo $row['name'];
echo "</td><td>";
echo $row['age'];
echo "</td></tr>";
}

echo "</table>";

?>

[jesushax]

becuase you ahvent set any limits for your sql

 

<?php

$result = mysql_query("SELECT * FROM example ORDER BY age") or die(mysql_error());

    if($_GET['page']) // Is page defined?
    {
        $page = $_GET['page']; // Set to the page defined
    }else{
        $page = 1; // Set to default page 1
    }
$max = 10; // Set maximum to 10

$cur = (($page * $max) - $max); // Work out what results to show

$counttotal = mysql_query("SELECT * FROM example ORDER BY age LIMIT $cur, $max") or die(mysql_error());

?>

[saint959]

becuase you ahvent set any limits for your sql

 

<?php

$result = mysql_query("SELECT * FROM example ORDER BY age") or die(mysql_error());

    if($_GET['page']) // Is page defined?
    {
        $page = $_GET['page']; // Set to the page defined
    }else{
        $page = 1; // Set to default page 1
    }
$max = 10; // Set maximum to 10

$cur = (($page * $max) - $max); // Work out what results to show

$counttotal = mysql_query("SELECT * FROM example ORDER BY age LIMIT $cur, $max") or die(mysql_error());

?>

 

i saw the same, but the limit should go on the $result = mysql....

 

<?php

    if($_GET['page']) // Is page defined?
    {
        $page = $_GET['page']; // Set to the page defined
    }else{
        $page = 1; // Set to default page 1
    }
$max = 10; // Set maximum to 10

$cur = (($page * $max) - $max); // Work out what results to show

$result = mysql_query("SELECT * FROM example ORDER BY age LIMIT $cur, $max") or die(mysql_error());

?>

 

[jesushax]

oops yeah sorry cheers

[vetman]

Okay, thanks. I tried it but get this error message now.

 

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 '' at line 1

[jesushax]

can you debug yoru sql

 

eg

 

get the bit between ()

 

so echo "SELECT FROM TBL......";

 

then post what that comes out with

[vetman]

Not sure how to do that , it doesn't get past the error message for line 1, but if i take out the code I just put in from you , it's back to normal.

[jesushax]

add the echo bit and tell us what it puts

 

echo "SELECT * FROM example ORDER BY age LIMIT $cur, $max";

$result = mysql_query("SELECT * FROM example ORDER BY age LIMIT $cur, $max") or die(mysql_error());

 

[vetman]

Okay, here's the error:

 

Connected to Database

SELECT * FROM example ORDER BY age LIMIT , 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 '' at line 1

 

 

[jesushax]

ok its not picking up the variables

 

try this

 

$result = mysql_query("SELECT * FROM example ORDER BY age LIMIT ".$cur.", ".$max."") or die(mysql_error());

[saint959]

Hi, sorry, i just wanna make sure of this:

 

did you move the $result = mysql... query TO BELOW where you work out the $cur and $max values?

 

[vetman]

No!, I didn't see that before. I just made that change and it now works.

Thanks so much, I really appreciate  all the help from everyone.