[SOLVED] Pagination Help

[pocobueno1388]

Try this

 

<?php

$username       = $_SESSION['username'];
$limit          = 10;
$query_count    = "SELECT count(*) FROM goldies WHERE Sender='$username'";
$result_count   = mysql_query($query_count) or die("Error: " . mysql_error());
$totalrows      = mysql_result($result_count, 0, 0);
$numofpages     = ceil($totalrows/$limit);

if (isset($_GET['page'])) $page = $_GET['page'];
else $page = 1;

$limitvalue = $page * $limit - ($limit);
$limitrange = $limitvalue+$limit;
$query  = "SELECT * FROM goldies WHERE Sender='$username' LIMIT $limitvalue, $limitrange";
echo  $query .'<br>';
$result = mysql_query($query) or die("Error: " . mysql_error());

//Exit if no records to display
if (mysql_num_rows($result) == 0) {
    exit("Nothing to Display!");
}

//Create the table of output data
echo "<table>\n";
while ($row = mysql_fetch_array($result)) {
    //Alternate the row background color
    $bgcolor = ($bgcolor == "#737373")?'#737373':'#737373';
    //Display the row of data
    echo "<tr bgcolor=\"$bgcolor\">\n";
    echo "  <td>".$row["Receiver"]."</td>\n";
    echo "  <td>".$row["Amount"]."</td>\n";
    echo "  <td>".$row["Date"]."</td>\n";
    echo "</tr>";
}
echo "</table>\n";

//Enable the Prev link if not first page
if ($page > 1) {
    echo("<a href=\"creditshistory.php?page=".($page-1)."\">PREV</a> ");
} else {
    echo("PREV ");
}

//Create links for each page in report
for ($i=1; $i<=$numofpages; $i++) {
    if ($i == $page) {
        echo "$i ";
    } else {
        echo "<a href=\"creditshistory.php?page=$i\">$i</a> ";
    }
}

//Enable the Next link if not last page
if ($page < $numofpages) {
    echo("<a href=\"creditshistory.php?page=".($page+1)."\">NEXT</a>");
} else {
    echo("NEXT");
} 

?>

[phpSensei]

Edit: Try what Poco posted.

 

 

[RealDrift]

POCO IT WORKS!!!!

ahh man i am so happy

 

thankyou man, thanks to you to sensei and teng, damato aswell

 

this was one heck of a problem, thanks a lot for your time and effort

[RealDrift]

I could kiss you all right now, for the help you have given to me lol 

[phpSensei]

Good job POCO, you trully are a Guru.

[pocobueno1388]

RealDrift - You might want to double check. With me it worked on the first page, then it kept displaying more results than I wanted on the other pages...so make sure thats not happening to you.

[pocobueno1388]

Good job POCO, you trully are a Guru.

 

phpSensei - LMAO, far from it. It's just a lot easier when you test the code out yourself rather than guessing over and over. With pagination it's almost impossible to help with unless you take it into your own hands, hah.

[RealDrift]

Poco you are a Guru, no questions

 

and yes the results are as follows:

 

page 1 = 10 rows

page 2 = 11 rows

page 3 = 1 row which was the last row on page 2

 

maybe this has something to do with remainders when ya divide total rows by the amount of rows you want to see?

[pocobueno1388]

See, the query says

SELECT * FROM table LIMIT 10, 20

 

Yet it displays more than results 10-20. Am I confused about how LIMIT works? It should start at result 10, and end on result 20, right?

[phpSensei]

it should be

 

SELECT * FROM table LIMIT 10

[RealDrift]

also this is echoed above table:

 

SELECT * FROM goldies WHERE Sender='WeedHunteR' LIMIT 0, 10

[pocobueno1388]

it should be

 

SELECT * FROM table LIMIT 10

 

I'm talking about for the second page...you need two parameters for LIMIT.

[phpSensei]

try

 

<?php

$username       = $_SESSION['username'];
$limit          = 10;
$query_count    = "SELECT count(*) FROM goldies WHERE Sender='$username'";
$result_count   = mysql_query($query_count) or die("Error: " . mysql_error());
$totalrows      = mysql_result($result_count, 0, 0);
$numofpages     = ceil($totalrows/$limit);

if (isset($_GET['page'])) $page = $_GET['page'];
else $page = 1;


$query  = "SELECT * FROM goldies WHERE Sender='$username' LIMIT '$limit'";
echo  $query .'<br>';
$result = mysql_query($query) or die("Error: " . mysql_error());

//Exit if no records to display
if (mysql_num_rows($result) == 0) {
    exit("Nothing to Display!");
}

//Create the table of output data
echo "<table>\n";
while ($row = mysql_fetch_array($result)) {
    //Alternate the row background color
    $bgcolor = ($bgcolor == "#737373")?'#737373':'#737373';
    //Display the row of data
    echo "<tr bgcolor=\"$bgcolor\">\n";
    echo "  <td>".$row["Receiver"]."</td>\n";
    echo "  <td>".$row["Amount"]."</td>\n";
    echo "  <td>".$row["Date"]."</td>\n";
    echo "</tr>";
}
echo "</table>\n";

//Enable the Prev link if not first page
if ($page > 1) {
    echo("<a href=\"creditshistory.php?page=".($page-1)."\">PREV</a> ");
} else {
    echo("PREV ");
}

//Create links for each page in report
for ($i=1; $i<=$numofpages; $i++) {
    if ($i == $page) {
        echo "$i ";
    } else {
        echo "<a href=\"creditshistory.php?page=$i\">$i</a> ";
    }
}

//Enable the Next link if not last page
if ($page < $numofpages) {
    echo("<a href=\"creditshistory.php?page=".($page+1)."\">NEXT</a>");
} else {
    echo("NEXT");
} 

?>

 

 

edit: O wait, nvm this then.

[RealDrift]

i have never used LIMIT before, so i wouldn't know poco

[RealDrift]

if it says limit 10,20 it should display 10 rows

 

am baffled aswell as to why it echoes 11 rows

[phpSensei]

Why don't you just use

 

 $query  = "SELECT * FROM goldies WHERE Sender='$username' LIMIT '$limit' "; 

 

Showing 10 records at a time per page.. Ama right?

[pocobueno1388]

Hold on...I'm trying to figure out how it works.

[RealDrift]

Why don't you just use

 

 $query  = "SELECT * FROM goldies WHERE Sender='$username' LIMIT '$limit' "; 

 

Showing 10 records at a time per page.. Ama right?

 

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

[pocobueno1388]

Why don't you just use

 

 $query  = "SELECT * FROM goldies WHERE Sender='$username' LIMIT '$limit' "; 

 

Showing 10 records at a time per page.. Ama right?

 

That will only show the first 10 records...that works for the first page, but we are trying to figure out how to make it work for all the others as well.

[phpSensei]

Remove Quotes between the $limits.

[RealDrift]

sorry sensei that way the page reloads everytime to show page 1 data

[pocobueno1388]

I've got it to display the correct amount of rows now...but now it keeps repeating information on different pages >.>

[RealDrift]

oh god it never ends, does it?

 

 

[pocobueno1388]

AH! I am such an idiot. The reason I was getting duplicates is because I had duplicates in the DB! So I've been doing it right for a while now and didn't even know it, haha.

 

So here is the FINAL working code (hopefully)

 

<?php

$username       = $_SESSION['username'];
$limit          = 10;
$query_count    = "SELECT count(*) FROM goldies WHERE Sender='$username'";
$result_count   = mysql_query($query_count) or die("Error: " . mysql_error());
$totalrows      = mysql_result($result_count, 0, 0);
$numofpages     = ceil($totalrows/$limit);
$rowsperpage = ceil($totalrows/$numofpages);

if (isset($_GET['page'])) $page = $_GET['page'];
else $page = 1;

$offset = ($page - 1) * $rowsperpage;
$query  = "SELECT * FROM i_horses LIMIT $offset,10";

echo  $query .'<br>';
$result = mysql_query($query) or die("Error: " . mysql_error());

//Exit if no records to display
if (mysql_num_rows($result) == 0) {
    exit("Nothing to Display!");
}

//Create the table of output data
echo "<table>\n";
while ($row = mysql_fetch_array($result)) {
    //Alternate the row background color
    $bgcolor = ($bgcolor == "#737373")?'#737373':'#737373';
    //Display the row of data
    echo "<tr bgcolor=\"$bgcolor\">\n";
    echo "  <td>".$row["Receiver"]."</td>\n";
    echo "  <td>".$row["Amount"]."</td>\n";
    echo "  <td>".$row["Date"]."</td>\n";
    echo "</tr>";
}
echo "</table>\n";

//Enable the Prev link if not first page
if ($page > 1) {
    echo("<a href=\"creditshistory.php?page=".($page-1)."\">PREV</a> ");
} else {
    echo("PREV ");
}

//Create links for each page in report
for ($i=1; $i<=$numofpages; $i++) {
    if ($i == $page) {
        echo "$i ";
    } else {
        echo "<a href=\"creditshistory.php?page=$i\">$i</a> ";
    }
}

//Enable the Next link if not last page
if ($page < $numofpages) {
    echo("<a href=\"creditshistory.php?page=".($page+1)."\">NEXT</a>");
} else {
    echo("NEXT");
} 

?>

[pocobueno1388]

Also, just so everyone know, this is how the LIMIT works

 

LIMIT start_from_row, number_of_rows_per_page