LIMIT with pagination

[ingvaro]

Hey guys, im new at this forum and also at PHP and MySQL, but ive been learning for the past week and have learned some nifty stuff (from my point of view ;D) but ran into some trouble when i was trying to create some sort of pagination for my site.

[code]
<?php

include("gallery/nav.php");

$username="root";
$password="kindarlegt";
$database="ingvar";

if ( $_GET['number'] == NULL ){
$limit = 1;
} else {
$limit = $_GET['number'];
}

if ($_GET['page']==NULL){
$start = 0;
} else {
$start = $_GET['page'];
}

if($_GET['page'] < 0){
header("Location: index.php?site=gallery/galleries.php&type=allt&page=0&limit=".$limit);
}

mysql_connect(localhost,$username,$password);
mysql_select_db($database) or die( "Unable to select database");
$query1="SELECT * FROM gallery_list WHERE (type = 'hjol') OR (type='annad')  ORDER BY id DESC LIMIT $start, $limit";
$query2="SELECT * FROM gallery_list WHERE (type = 'hjol') ORDER BY id DESC LIMIT $start, $limit";
$query3="SELECT * FROM gallery_list WHERE (type = 'annad') ORDER BY id DESC LIMIT $start, $limit";

if ($_GET['type'] == 'allt') $result=mysql_query($query1)or die(mysql_error());;
if ($_GET['type'] == 'hjol') $result=mysql_query($query2)or die(mysql_error());;
if ($_GET['type'] == 'annad') $result=mysql_query($query3)or die(mysql_error());;

while ( $row = mysql_fetch_array( $result ))
{echo "
<a href=\"gallery/{$row[ 'linkur' ]}/gallery.php\" target=\"_parent\">
<div class=\"gallery_menu\">

<img src=\"gallery/thumbs/{$row[ 'linkur' ]}.jpg\" alt=\"description\" width=\"60\" height=\"60\" border=\"0\" />

<p><strong>
{$row[ 'id' ]} - {$row[ 'date' ]}
</strong></p>

<p>
{$row[ 'description' ]}
</p>

</div>
</a>
";}

$previous = $start + $limit;
$next = $start - $limit;

echo '<a href="index.php?site=gallery/galleries.php&type=allt&start='.$previous.'">Previous Page</a> - ';
echo '<a href="index.php?site=gallery/galleries.php&type=allt&start='.$next.'">Next Page</a>';

?>
[/code]

See when i press the next page or previous page it sometimes works to go 1 page forward, but only one time, and previous never works, so i just want to ask if you can spot some errors in my script, thanks :)

[mansuang]

I think your problem is around the link for "Previous" and "Next"

echo '<a href="index.php?site=gallery/galleries.php&type=allt&[color=red]page[/color]='.$previous.'[color=red]&number='.$limit.'[/color]">Previous Page</a> - ';
echo '<a href="index.php?site=gallery/galleries.php&type=allt&[color=red]page[/color]='.$next.'[color=red]&number='.$limit.'[/color]">Next Page</a>';

[ingvaro]

[quote author=mansuang link=topic=117671.msg480254#msg480254 date=1165461892]
I think your problem is around the link for "Previous" and "Next"

echo '<a href="index.php?site=gallery/galleries.php&type=allt&[color=red]page[/color]='.$previous.'[color=red]&number='.$limit.'[/color]">Previous Page</a> - ';
echo '<a href="index.php?site=gallery/galleries.php&type=allt&[color=red]page[/color]='.$next.'[color=red]&number='.$limit.'[/color]">Next Page</a>';

[/quote]

Thank you SO MUCH, that saved my day, ive been tearing hairs from ... places because of this haha. But i do have one more question hehe :) for the next button everything works even when i reach the end, it just shows nothing, but the previous (which also works:)) gives this error when it reaches beyond the first entry:

[code]
Warning: Cannot modify header information - headers already sent by (output started at N:\test\index.php:10) in N:\test\gallery\galleries.php on line 24
Query failed: with 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 '-2, 2' at line 1
[/code]

and i think it is because of this not working

[code]
if($_GET['page'] < 0){
header("Location: index.php?site=gallery/galleries.php&type=allt&page=0&limit=".$limit);
}
[/code]

actually, ive never understood headers, or what they do and how they work, so i guess im doing something wrong there :)

Also, one more thing :) does it matter where in the script something is?

[mansuang]

The HTTP status header line will always be the first sent to the client.
So, try to move
[code]if($_GET['page'] < 0){
header("Location: index.php?site=gallery/galleries.php&type=allt&page=0&limit=".$limit);
}
[/code]
To the first line of your code

cheer!

[ingvaro]

Yeah, but my site is built so that its all on index.php, that includes the header, and the footer, and the main contents, which is this file

[code]http://localhost/index.php?site=gallery/galleries.php&type=allt&page=0&number=5[/code]

so i think i have to have that piece of code on the top of my INDEX.php page, which sucks, because i think thats messy, and also it doesnt set the number at what i had it before i went too far and it redirects, i.e. if i had number=5 and press the previous button too many times and activate that header thingy it just says number= and nothing :S

Is there another way to handle this "going too far in the previous" problem than to use a header?

[mansuang]

There is many way to prevent that problem.
For exampe:

remove
[code]if($_GET['page'] < 0){
header("Location: index.php?site=gallery/galleries.php&type=allt&page=0&limit=".$limit);
}[/code]

then modify something
[code]
<?php
...
...
...

if ($_GET['page']==NULL || $_GET['page']<0){
$start = 0;
} else {
$start = $_GET['page'];
}

...
...
...

$previous = $start + $limit;
$next = $start - $limit;

if($previous<0){
$previous = 0;
}

echo '<a href="index.php?site=gallery/galleries.php&type=allt&page='.$previous.'&number='.$limit.'">Previous Page[/url] - ';
echo '<a href="index.php?site=gallery/galleries.php&type=allt&page='.$next.'&number='.$limit.'">Next Page[/url]';
?>
[/code]

[ingvaro]

Man, you have made my day! Everything works, thanks to you :D

[mansuang]

no problem  ;D