Problem using a PREVious] 1 - 2 - 2 [NEXT script

[LordRogaine]

Hello, I am still fairly new and trying desperately to learn, but I'm stumped on a problem. I am using php and mysql to make an interactive dating website. For my search results I am using a NEXT] 1 - 2 - 3 [PREV script to sort and break the results down into seperate pages. I am limiting the results to ten results per page. I believe my problem is somewhere within the "select" statement. If I leave the select statement like this everything works fine.

$getrows = mysql_query("select * from $dbtable");
$numrows=mysql_num_rows($getrows);

$query = mysql_query("select * from $dbtable limit $offset,$limit");
while ($result=mysql_fetch_array($query)){

All my results come up and all the links for the NEXT] 1 - 2 - 3 [PREV work fine.

But if I change the select statement to something like this,

$getrows = mysql_query("select * from $dbtable WHERE state = 'state' and age = 'age'" );
$numrows=mysql_num_rows($getrows);

$query = mysql_query("select * from $dbtable WHERE state = 'state' and age = 'age' limit $offset,$limit");
while ($result=mysql_fetch_array($query)){

I get one page with my first ten results which is what I want. All the links NEXT] 1 - 2 - 3 [PREV are at the bottom of the page. The problem is when I press the NEXT link or one or the 1-2-3 links, I get a blank page and zero results. I know that there are more than just the ten results that are showing on the first page. But everytime I click one of the links for the next page of results I get zero results on my next page.

I have tried four different scripts and I have the same problem with everyone of them.I am leaving a copy of the full script I am using in particular right now at the bottom of this page. If anyone can please tell me where I am going wrong it would be greatly appreciated.

Thankyou,
Steve

<?
echo "<style type=\"text/css\">
body, td {font-family:tahoma; font-size:13; color:black;}
a {font-family:tahoma; font-size:13; color:black;}
a:Visited {font-family:tahoma; font-size:13; color:black;}
a:Hover {font-family:tahoma; font-size:13; color:black;}
a:active {font-family:tahoma; font-size:13; color:black;}
</style>";

$server = "";
$userid = "";
$pass = "";
$database = "";
$dbtable = "";
$limit = 10;

$con = mysql_connect("$server","$userid","$pass") or die ("Can't Connect to Server");
$db = mysql_select_db("$database",$con) or die("Can't Connect to Database");

if (empty($offset) || $offset < 0) {
$offset=0;

}
if (empty($index)) $index=0; / This is to count the line index of the results /

$getrows = mysql_query("select * from $dbtable WHERE category = 'Beanie Bears'", $con);
$numrows=mysql_num_rows($getrows);

$query = mysql_query("select * from $dbtable WHERE category = 'Beanie Bears' limit $offset,$limit", $con);
while ($result=mysql_fetch_array($query)){

			$index++;  /* Increment the line index by 1 */

	// Your results layout goes here	
}

if ($numrows <= $limit) {
/ Added this statement so if the result shows less that or the
equal ammount of the limit to show nothing at all , to kill
the "1" that was just generated /
}
else {

    // Don't display PREV link if on first page 
    if ($offset!=0) {   
        $prevoffset=$offset-$limit; 
        echo   "[Previous]&nbsp &nbsp"; 
    } 
		  else echo "[Previous]&nbsp &nbsp";



    // Calculate total number of pages in result 
    $pages = intval($numrows/$limit); 

    // $pages now contains total number of pages needed unless there is a remainder from division  
    if ($numrows%$limit) { 
        // has remainder so add one page  
        $pages++; 
    } 

    // Now loop through the pages to create numbered links 
    // ex. 1 2 3 4 5 NEXT 
    for ($i=1;$i<=$pages;$i++) { 


        // Check if on current page 
        if (($offset/$limit) == ($i-1)) { 
            // $i is equal to current page, so don't display a link 
            echo " $i  "; 
        } else { 
            // $i is NOT the current page, so display a link to page $i 
            $newoffset=$limit*($i-1); 
            echo  "&nbsp;$i&nbsp;\n"; 
        } 

    } 

// Check to see if current page is last page 
    if (!((($offset/$limit)+1)==$pages) && $pages!=1) { 
        // Not on the last page yet, so display a NEXT Link 
        $newoffset=$offset+$limit; 
        echo   "&nbsp &nbsp [Next]

\n";
}
else echo "&nbsp &nbsp[Next]";
}

mysql_close($con);

?>[/color]

[IWS]

First of all, to get the total number of lines returned, do this:

$query = mysql_query("select count(*) from $dbtable WHERE state = 'state' and age = 'age'" );
list($numrows)=mysql_fetch($query);

$query = mysql_query("select * from $dbtable limit $offset,$limit");
while ($result=mysql_fetch_array($query)){

Notice how I changed the first query to "$query". Using one variable name over and over is faster than using different variable names throughout code.

Next, turn off register_globals. Someone may be able to hack your script with register_globals on.

Third, the pagination bar code should look like this:

if ($numrows>$limit) {
if ($offset>0) {
echo '<a href="page.php?offset='.($offset-10).'">[Previous]</a> ';
} else echo '[Previous] ';
$totpages=ceil($numrows/$limit);
$curpage=floor($offset/$limit)+1;

for ($i=0;$i<$totpages;$i++) {
if ($i+1!=$curpage) echo '<a href="page.php?offset='.($i*10).'">'.($i+1).'</a> ';
else echo ($i+1).' ';
}
if ($curpage<$totpages) {
echo '<a href="page.php?offset='.($offset+10).'">[Next]</a>';
} else echo '[Next]';
}

[LordRogaine]

I appreciate the help but now I seem to have a similar problem with the script you gave me. I probably did something wrong but now instead of getting zero results when I click on a page link I now get "internal server error". Any ideas. Thanks again in advance.

[jaZon]

nm

[ednark]

ok here is the idea: this is basically what IWS posted except i will blab instead of posting code...

each page needs to have this information

a) what/where to seach: sql table, columns, and WHERE clause

b) how many results to show on this page

c) what result to start showing from

each page call must include this information.

first you need to construct two sql statements

sql 1: get the total number of results

SELECT count(*) FROM $table WHERE $where

and

sql 2: get only the results you are interested in

SELECT * FROM $table WHERE $where limit $start_from,$how_many

now after running these you should have all the info you need to simply step through each result from sql 2 and show it

the next button should contain these variables

$offset = $offset + $how_many
$how_many = $how_many
$where = $where

the page when it loads should look for these three variables... if it cannot find them make up default values, then run through the process described

now if you are still having trouble... make sure your page is at least passing that information around per page.... you could perhaps not pass $how_many if you want to just hard code it in...

[LordRogaine]

Thankyou for explaing this to me. I will give it a try. thanks. Much appreciated!

[LordRogaine]

I feel like an idiot, but I'm still having trouble with this. Here is an exact copy of the script I am using. I tried three others and I'm also having the same problem so I must be leaving something out. Here is the script I am trying to use. Does anybody see anything wrong. I'll get my number of results which for arguments sake is "60". I limit my results to ten per page. I have 6 links at the bottom for six pages of results.. The first page shows with the first ten results. When I click on the link for the next page, I get "zero results, with a blank page.

One interesting point. If I leave both select statements like this
("select * from $dbtable")
I get the correct number of results, let's say 80, and I'll have eight pages of results and all the links work perfectly.
Thanks in advance for any help.

P.S. I am truly a newbie so please go easy on me.

echo "<style type=\"text/css\">
body, td {font-family:tahoma; font-size:13; color:black;}
a {font-family:tahoma; font-size:13; color:black;}
a:Visited {font-family:tahoma; font-size:13; color:black;}
a:Hover {font-family:tahoma; font-size:13; color:black;}
a:active {font-family:tahoma; font-size:13; color:black;}
</style>";

$server = "my_server";
$userid = "my_user_id";
$pass = "my_password";
$database = "my_database";
$dbtable = "db_table";
$limit = 10;

$con = mysql_connect("$server","$userid","$pass") or die ("Huh? What Server");
$db = mysql_select_db("$database",$con) or die("I said WHAT database");

if (empty($offset) || $offset < 0) {
$offset=0;

}
if (empty($index)) $index=0; / This is to count the line index of the results /

$getrows = mysql_query("select * from $dbtable WHERE state= '$state' and name= '$name' and age = '$age''", $con);
$numrows=mysql_num_rows($getrows);

$query = mysql_query("select * from $dbtable WHERE state= '$state' and name= '$name' and age = '$age'' limit $offset,$limit", $con);
while ($result=mysql_fetch_array($query)){

			$index++;  /* Increment the line index by 1 */

	// Your results layout goes here	
}

if ($numrows <= $limit) {
/ Added this statement so if the result shows less that or the
equal ammount of the limit to show nothing at all , to kill
the "1" that was just generated /
}
else {

    // Don't display PREV link if on first page 
    if ($offset!=0) {   
        $prevoffset=$offset-$limit; 
        echo   "<a onMouseOver=\"window.status='Previous $limit Results'; return true\"; href=\"$PHP_SELF?offset=$prevoffset&index=$prevoffset\"><B>[Previous]</B></a>&nbsp &nbsp"; 
    } /* I particularly like having the [Previous] on the first page
	      if it has enough results to display at all with my mod , just helps
		  set the over all structure of the navigation system its self
		  so ill just add this to the above if statement */
		  else echo "<b><font color=666666>[Previous]</font></b>&nbsp &nbsp";
		  /* If its not to your likeing simply comment it out ;) */


    // Calculate total number of pages in result 
    $pages = intval($numrows/$limit); 

    // $pages now contains total number of pages needed unless there is a remainder from division  
    if ($numrows%$limit) { 
        // has remainder so add one page  
        $pages++; 
    } 

    // Now loop through the pages to create numbered links 
    // ex. 1 2 3 4 5 NEXT 
    for ($i=1;$i<=$pages;$i++) { 


        // Check if on current page 
        if (($offset/$limit) == ($i-1)) { 
            // $i is equal to current page, so don't display a link 
            echo " <b><font color=666666>$i</font></b>  "; 
        } else { 
            // $i is NOT the current page, so display a link to page $i 
            $newoffset=$limit*($i-1); 
            echo  "&nbsp;<a onMouseOver=\"window.status='Page $i Results'; return true\"; href=\"$PHP_SELF?offset=$newoffset&index=$newoffset\"><B>$i</B></a>&nbsp;\n"; 
        } 

    } 

// Check to see if current page is last page 
    if (!((($offset/$limit)+1)==$pages) && $pages!=1) { 
        // Not on the last page yet, so display a NEXT Link 
        $newoffset=$offset+$limit; 
        echo   "&nbsp &nbsp <a onMouseOver=\"window.status='Next $limit Results'; return true\"; href=\"$PHP_SELF?offset=$newoffset&index=$newoffset\"><B>[Next]</B></a><p>\n"; 
    } /* Im doing the same thing here as i did in the [Previous] above */
	else echo "&nbsp &nbsp<b><font color=666666>[Next]</font></b>";

}

mysql_close($con);

?>

[ednark]

this distilled version runns wonderfully on my computer

make sure that you are looking in the correct place for the data from your GET request... if you are looking in the wrong place offset may get reset to 0 every page.. and then you would get only your first pages worth of results

<style type="text/css">
    body, td {font-family:tahoma; font-size:13; color:black;}
    a {font-family:tahoma; font-size:13; color:black;}
    a:Visited {font-family:tahoma; font-size:13; color:black;}
    a:Hover {font-family:tahoma; font-size:13; color:black;}
    a:active {font-family:tahoma; font-size:13; color:black;}
</style>

<?php

/// fake important data from database call
$numrows = 80;

/// set the limit
$limit   = 10;

/// if register_globals = on, these are already set and live also in $GLOBALS and $HTTP_GET_VARS
/// if register_globals = off they live in $_GET
/// assume register_globlas = Off
$offset = $_GET['offset'];
$index  = $_GET['index'];

echo "found offset=$offset, index=$index<br>";

if (empty($offset) || $offset < 0) {
    $offset=0;
}


if ($offset!=0) {
    $prevoffset=$offset-$limit;
    echo "<a href=\"$PHP_SELF?offset=$prevoffset&index=$prevoffset\"><B>[Previous]</B></a>";
} else {
    echo "<b><font color=666666>[Previous]</font></b> ";
}

// Calculate total number of pages in result
$pages = intval($numrows/$limit);


for ($i=1;$i<=$pages;$i++) {

// Check if on current page
if ( ($offset/$limit) == ($i-1) ) {
    // $i is equal to current page, so don't display a link
    echo " <b><font color=666666>$i</font></b> ";
} else {
    // $i is NOT the current page, so display a link to page $i
    $newoffset=$limit*($i-1);
    echo " <a href=\"$PHP_SELF?offset=$newoffset&index=$newoffset\"><B>$i</B></a> \n";
}
}

// Check to see if current page is last page
if ( !((($offset/$limit)+1)==$pages) && $pages!=1) {
    // Not on the last page yet, so display a NEXT Link
    $newoffset=$offset+$limit;
    echo "    <a href=\"$PHP_SELF?offset=$newoffset&index=$newoffset\"><B>[Next]</B></a><p>\n";
/* Im doing the same thing here as i did in the [Previous] above */
} else {
    echo "   <b><font color=666666>[Next]</font></b>";
}

?>

[LordRogaine]

thankyou very much for your help. I appreciate it.