Previous/Next Buttons 2

overyde · Jun 27, 2003

Hi,
I'm really stuck now. I want to have a set amount of results on my page at a time (paginate) but I cant get the prev/next buttons to work to fetch the next 25 results. I'm using php4 and mysql. Here's the code I used:

<?php

// open the connection
$conn = mysql_connect("localhost", "", "&quot

;

// pick the database to use
mysql_select_db("db",$conn);

$limit = 25;
$query_count = "SELECT count(*) FROM users";
$result_count = mysql_query($query_count);
$totalrows = mysql_num_rows($result_count);

if(empty($page)){
$page = 1;
}

$limitvalue = $page * $limit - ($limit);
$query = "SELECT * FROM users LIMIT $limitvalue, $limit";
$result = mysql_query($query) or die("Error: " . mysql_error());

if(mysql_num_rows($result) == 0){
echo("Nothing to Display!&quot

;
}

print "<table border=1><tr><th>id</th>";
print "<th>name</th><th>desc</th><th>pics</th><th>pic1</th><th>pic2</th></tr>";

// go through each row in the result set and display data
while ($newArray = mysql_fetch_array($result)) {
// give a name to the fields
$id = $newArray['id'];
$name = $newArray['name'];
$desc = $newArray['desc'];
$pics = $newArray['pics'];
$pic1 = $newArray['pic1'];
$pic2 = $newArray['pic2'];

//echo the results on screen
echo "
<tr>
<td>$id</td>
<td>$name</td>
<td><img src=\"$pics"></td>
<td>$desc</td>
<td>$pic1</td>
<td>$pic2</td>
</tr>
";
}

print "</table>";

if($page != 1){
$pageprev = $page--;

echo("<a href=\"$PHP_SELF&page=$pageprev\">PREV".$limit."</a> &quot

;
}else{
echo("PREV".$limit." &quot

;
}

$numofpages = $totalrows / $limit;

for($i = 1; $i <= $numofpages; $i++){
if($i == $page){
echo($i." &quot

;
}else{
echo("<a href=\"$PHP_SELF?page=$i\">$i</a> &quot

;
}
}

if(($totalrows % $limit) != 0){
if($i == $page){
echo($i." &quot

;
}else{
echo("<a href=\"$PHP_SELF?page=$i\">$i</a> &quot

;
}
}

if(($totalrows - ($limit * $page)) > 0){
$pagenext = $page++;

echo("<a href=\"$PHP_SELF?page=$pagenext\">NEXT".$limit."</a>&quot

;
}else{
echo("NEXT".$limit);
}

mysql_free_result($result);

?>

Reality is built on a foundation of dreams.

DRJ478 · Jun 27, 2003

overyde,

I just read quickly through your script.

Please give some more info about:
What is happening or what is not?

kasuals · Jun 27, 2003

I understand the problem. I've never attempted to display (x) number of results per page.

I'll write a quick script and post it for you to look at. I'll try to comment it properly for you.

http://www.angsttechnology.com

- "Delightfully confusing..." raves The New York Times

-Kas

kasuals · Jun 27, 2003

Ok, for this I created a database with two fields:

id int primary key auto_increment
name text

I added 15 rows, which is why MAX_DISPLAY is set to 5. This will allow me to view the results 5 per page. I didn't fix it so it doesn't display the "NEXT" link if we reach the end of the table.

<?
define(MAX_DISPLAY, 5);

$link = mysql_connect("localhost", "yourdb", "yourpass&quot

or die("Could not connect : " . mysql_error());

mysql_select_db("genoa&quot

or die("Could not select database&quot

;

printf(" Row Start: %d Next Row: %d \n", $page, $page+MAX_DISPLAY);
if(!$page) {
$page = 1;
printf("<a href='test.php?page=%s'>Next Page </a>", ($page + MAX_DISPLAY));
$query = "select * from example order by id asc limit ".MAX_DISPLAY."";
} else {
$query = "select * from example order by id asc limit ".($page - 1).",".MAX_DISPLAY."";
$prevpage = ($page - MAX_DISPLAY);
$page = ($page + MAX_DISPLAY);
printf("<a href='test.php?page=%s'>Next Page</a>", $page);
printf("<a href='test.php?page=%s'>Prev Page</a> ", $prevpage);
}

$result = mysql_query($query)
or die("Could not complete query : " . mysql_error());

if(!$result) {
printf("No entries found.\n&quot

;
exit;
}

while($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
printf("[%d] - %s \n", $row["id"], $row["name"]);
}

mysql_free_result($result);
mysql_close($link);

?>

I know I said I'd try to comment the code, but I'll just outline the major factor that allows for simple row manipulation in your results:

What this does is, if we're on the first page, start with the first row retrieved and pull 5 deep.

(You can ignore my ORDER sorting, just habit)
$query = "select * from example order by id asc limit ".MAX_DISPLAY."";

Here we have the next page, so we're going to have to take the number of the last row pulled, subtract one (limit takes your row number and adds one) and pull 5 deep.

$query = "select * from example order by id asc limit ".($page - 1).",".MAX_DISPLAY."";

Sorry if it's not clear enough. You can always goto:
(Sorry it's .de, but I grabbed the first one I found)

http://www.mysql.de/doc/en/SELECT.html

They define and explain LIMIT.

I would have had this done faster, but my neighbor is outside blowing up appliances with homemade pipeboms.

http://www.angsttechnology.com

- "Delightfully confusing..." raves The New York Times

-Kas

vbkris · Jun 27, 2003

as kasuals said use the limit query:
"select FieldName from Table LIMIT 5"

when u display the records use a counter and increase its value.
in this e.g the counter will be 5 the first time.

have a hidden field and assign the value of the counter to it.

in the next page read the counter value
the sql will now be:
"select FieldName from Table LIMIT 5,$counter"...

Known is handfull, Unknown is worldfull

overyde · Jun 29, 2003

Brilliant!
Thanks!

DRJ478, what was happening was I was displaying my limited amount of fields but my next button was not working...it did not even register as a hyperlink.

Will try use kasuals suggestion now.

Reality is built on a foundation of dreams.

overyde · Jun 30, 2003

Hey Kasuals,
I used your script and it got me a lot closer to what I need but when I click next it just shows the first 5 results. What's going wrong here?

<?
define(MAX_DISPLAY, 5);

$link = mysql_connect("localhost", "yourdb", "yourpass&quot

or die("Could not connect : " . mysql_error());

mysql_select_db("genoa&quot

or die("Could not select database&quot

;

printf(" Row Start: %d Next Row: %d \n", $page, $page+MAX_DISPLAY);
if(!$page) {
$page = 1;
printf("<a href='test.php?page=%s'>Next Page </a>", ($page + MAX_DISPLAY));
$query = "select * from example order by id asc limit ".MAX_DISPLAY."";
} else {
$query = "select * from example order by id asc limit ".($page - 1).",".MAX_DISPLAY."";
$prevpage = ($page - MAX_DISPLAY);
$page = ($page + MAX_DISPLAY);
printf("<a href='test.php?page=%s'>Next Page</a>", $page);
printf("<a href='test.php?page=%s'>Prev Page</a> ", $prevpage);
}

$result = mysql_query($query)
or die("Could not complete query : " . mysql_error());

if(!$result) {
printf("No entries found.\n&quot

;
exit;
}

while($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
printf("[%d] - %s \n", $row["id"], $row["name"]);
}

mysql_free_result($result);
mysql_close($link);

?>

Reality is built on a foundation of dreams.

vbkris · Jul 1, 2003

thats because ur $page is always 0...

include a hidden field called page.

slightly modify ur code:
<?
$page=$_POST['page'];
if($page=="&quot

$page=1;
else
$page=$page+5;

define(MAX_DISPLAY, 5);

and in the end of the page:
<input type="hidden" name="page" value="<?=$page;?>">

hth

Known is handfull, Unknown is worldfull

overyde · Jul 1, 2003

I'm really getting frustrated!!!
Still when I click on next it is though nothing is happening.
The addess bar comes up with the URL

http://localhost/test.php?page=6

When I click next but the results aren't showing.

Reality is built on a foundation of dreams.

overyde · Jul 1, 2003

Correction...
only the first 5 results are showing. So it seems it's not fetching the data.

Reality is built on a foundation of dreams.

overyde · Jul 1, 2003

Sorted... I did not have my register globals on.
But

Reality is built on a foundation of dreams.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Previous/Next Buttons 2

overyde

Programmer

DRJ478

IS-IT--Management

kasuals

Programmer

kasuals

Programmer

vbkris

Programmer

overyde

Programmer

overyde

Programmer

vbkris

Programmer

overyde

Programmer

overyde

Programmer

overyde

Programmer

Similar threads

Part and Inventory Search

Sponsor