next / prev links

[timinator]

If I post the code to my results page could someone get me started in the right direction on how to limit the number of records displayed with next and prev links? I have found many examples but end up botching my code when applying it.

Thanks

[python_q]

Okay, I assume you're pulling the results from some sort of SQL database. In that case, use a query like this:

SELECT * FROM database LIMIT $offset, $no_results

Where $offset is the number of the first record to be displayed on the page and $no_results is the number of results per page to display.
Wherever you want to place links for next page and prev. page, just have something like this:

if ($offest!=0) {
   if ($offset <= $no_results) $prev_page = 0;
   else $prev_page = $offset - $no_results;
   echo "<a href=\"?offset=".$prev_page."\">Prev. Page</a>";
}

$count_db = mysql_query("SELECT COUNT(*) FROM database");
if ($offset + $no_results <= $count_db) {
   $next_page = $offset + $no_results;
   echo "<a href=\"?offset=".$next_page."\">Next Page</a>";
}

Also, since you are GETting the variable $offset, you want to have these lines at the top of your page:

if (isset($_GET['offset'])) {
   $offset = $_GET['offset'];
}
else
{
   $offset = 0;
}

Hope this helps. If you have any problems, then post your code.

[timinator]

Had some problems I could'nt identify - I would like to display 50 records per page. Here's the code:

<?
$dbuser = "root";
$dbserver = "localhost";
$dbpass = "xxxxx";
$dbname = "NATFD";

//CONNECTION STRING
mysql_connect($dbserver, $dbuser, $dbpass)
or die ("UNABLE TO CONNECT TO DATABASE");
mysql_select_db($dbname)
or die ("UNABLE TO SELECT DATABASE");

//JUST STATE
if ($GET[city]=="" AND $GET[name]=="")
$sql = ("SELECT MLA_NO, NAME, STREET, CITY, STATE, ZIP, PHONE, FAX, IP_ADDR FROM company WHERE state='$GET[state]' ORDER BY city ASC");
//JUST CITY
elseif ($GET[state]=="" AND $GET[name]=="")
$sql = ("SELECT MLA_NO, NAME, STREET, CITY, STATE, ZIP, PHONE, FAX, IP_ADDR FROM company WHERE city LIKE'$GET[city]%' ORDER BY state ASC");
//JUST COMPANY NAME
elseif ($GET[state]=="" AND $GET[city]=="")
$sql = ("SELECT MLA_NO, NAME, STREET, CITY, STATE, ZIP, PHONE, FAX, IP_ADDR FROM company WHERE name LIKE'$GET[name]%'ORDER BY state ASC");
//ALL
else
$sql = ("SELECT MLA_NO, NAME, STREET, CITY, STATE, ZIP, PHONE, FAX, IP_ADDR FROM company WHERE state='$GET[state]' AND city LIKE'$GET[city]%' AND name LIKE'$GET[name]%'");
//echo($sql);
$result = mysql_query($sql);
$num_rows = mysql_num_rows($result);
print "<p><a href=javascript:history.back(1)><font size=1>BACK</font></a></p>";
print "<font size=3><strong>$num_rows Records found.<P>";
//Set up the Table
print "<table width=100% border=1>\n";
print "<tr>";
print "<td bgcolor=#CCCCCC><div align=center><font size=1>Contacts</font></td>";
print "<td bgcolor=#CCCCCC><div align=center><font size=1>NAME (click for Details)</font></td>";
print "<td bgcolor=#CCCCCC><div align=center><font size=1>STREET</font></td>";
print "<td bgcolor=#CCCCCC><div align=center><font size=1>CITY</font></td>";
print "<td bgcolor=#CCCCCC><div align=center><font size=1>STATE</font></td>";
print "<td bgcolor=#CCCCCC><div align=center><font size=1>ZIP</font></td>";
print "<td bgcolor=#CCCCCC><div align=center><font size=1>PHONE</font></td>";
print "<td bgcolor=#CCCCCC><div align=center><font size=1>FAX</font></td>";
print "<td bgcolor=#CCCCCC><div align=center><font size=1>WEB SITE</font></td>";
print "</tr>";
//Get the data into the table
while ($row = mysql_fetch_array($result))
{
echo("<tr>");
echo("<td nowrap><font face=arial size=1><a href=contacts.php?MLA_NO=$row[MLA_NO]>$row[MLA_NO]</a></td>");
echo("<td nowrap><font face=arial size=1><a href=details2.php?MLA_NO=$row[MLA_NO]>$row[NAME]</a></td>");
echo("<td nowrap><font face=arial size=1>$row[STREET]</td>");
echo("<td nowrap><font face=arial size=1>$row[CITY]</td>");
echo("<td nowrap><font face=arial size=1>$row[STATE]</td>");
echo("<td nowrap><font face=arial size=1>$row[ZIP]</td>");
echo("<td nowrap><font face=arial size=1>$row[PHONE]</td>");
echo("<td nowrap>&nbsp<font face=arial size=1>$row[FAX]</td>");
echo("<td nowrap>&nbsp<font face=arial size=1><a href=http://$row[IP_ADDR]>$row[IP_ADDR]</a></td>");
echo("</tr>");
}

?>

thanks

[timinator]

The error messages:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /sites.php on line 45

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /sites.php on line 62

Fatal error: Call to undefined function: http_build_query() in /sites.php on line 90

Updated code:

<?
$dbuser = "root";
$dbserver = "localhost";
$dbpass = "xxxxx";
$dbname = "NATFD";

//CONNECTION STRING
mysql_connect($dbserver, $dbuser, $dbpass)
or die ("UNABLE TO CONNECT TO DATABASE");
mysql_select_db($dbname)
or die ("UNABLE TO SELECT DATABASE");

if (isset($GET['offset'])) {
$offset = $GET['offset'];
}
else
{
$offset = 0;
}

//JUST STATE
if ($GET[city]=="" AND $GET[name]=="")
$sql = ("SELECT MLA_NO, NAME, STREET, CITY, STATE, ZIP, PHONE, FAX, IP_ADDR FROM company WHERE state='$GET[state]' ORDER BY city ASC LIMIT $offset, $no_results ");
//JUST CITY
elseif ($GET[state]=="" AND $GET[name]=="")
$sql = ("SELECT MLA_NO, NAME, STREET, CITY, STATE, ZIP, PHONE, FAX, IP_ADDR FROM company WHERE city LIKE'$GET[city]%' ORDER BY state ASC LIMIT $offset, $no_results ");
//JUST COMPANY NAME
elseif ($GET[state]=="" AND $GET[city]=="")
$sql = ("SELECT MLA_NO, NAME, STREET, CITY, STATE, ZIP, PHONE, FAX, IP_ADDR FROM company WHERE name LIKE'$GET[name]%'ORDER BY state ASC LIMIT $offset, $no_results ");
//ALL
else
$sql = ("SELECT MLA_NO, NAME, STREET, CITY, STATE, ZIP, PHONE, FAX, IP_ADDR FROM company WHERE state='$GET[state]' AND city LIKE'$GET[city]%' AND name LIKE'$GET[name]%' LIMIT $offset, $no_results ");
//echo($sql);
//$result = mysql_query($sql);
$sql .="LIMIT $offset, $no_results";
$num_rows = mysql_num_rows($result);
print "<p><a href=javascript:history.back(1)><font size=1>BACK</font></a></p>";
print "<font size=3><strong>$num_rows Records found.<P>";
//Set up the Table
print "<table width=100% border=1>\n";
print "<tr>";
print "<td bgcolor=#CCCCCC><div align=center><font size=1>Contacts</font></td>";
print "<td bgcolor=#CCCCCC><div align=center><font size=1>NAME (click for Details)</font></td>";
print "<td bgcolor=#CCCCCC><div align=center><font size=1>STREET</font></td>";
print "<td bgcolor=#CCCCCC><div align=center><font size=1>CITY</font></td>";
print "<td bgcolor=#CCCCCC><div align=center><font size=1>STATE</font></td>";
print "<td bgcolor=#CCCCCC><div align=center><font size=1>ZIP</font></td>";
print "<td bgcolor=#CCCCCC><div align=center><font size=1>PHONE</font></td>";
print "<td bgcolor=#CCCCCC><div align=center><font size=1>FAX</font></td>";
print "<td bgcolor=#CCCCCC><div align=center><font size=1>WEB SITE</font></td>";
print "</tr>";
//Get the data into the table
while ($row = mysql_fetch_array($result))
{
echo("<tr>");
echo("<td nowrap><font face=arial size=1><a href=contacts.php?MLA_NO=$row[MLA_NO]>$row[MLA_NO]</a></td>");
echo("<td nowrap><font face=arial size=1><a href=details2.php?MLA_NO=$row[MLA_NO]>$row[NAME]</a></td>");
echo("<td nowrap><font face=arial size=1>$row[STREET]</td>");
echo("<td nowrap><font face=arial size=1>$row[CITY]</td>");
echo("<td nowrap><font face=arial size=1>$row[STATE]</td>");
echo("<td nowrap><font face=arial size=1>$row[ZIP]</td>");
echo("<td nowrap><font face=arial size=1>$row[PHONE]</td>");
echo("<td nowrap>&nbsp<font face=arial size=1>$row[FAX]</td>");
echo("<td nowrap>&nbsp<font face=arial size=1><a href=http://$row[IP_ADDR]>$row[IP_ADDR]</a></td>");
echo("</tr>");
}

if ($offest!=0) {

if ($offset <= $no_results) $prev_page = 0;

else $prev_page = $offset - $no_results;
$GET['offset'] = $prev_page;
$query_string = http_build_query($GET);
echo "<a href=\"?".$query_string."\">Prev. Page</a>";

}

$count_db = mysql_query("SELECT COUNT(*) FROM database");

if ($offset + $no_results <= $count_db) {

$next_page = $offset + $no_results;

$GET['offset'] = $next_page;
$query_string = http_build_query($GET);
echo "<a href=\"?".$query_string."\">Next Page</a>";

}

?>

thanks

[python_q]

Okay, your code should look like this. Notice that the LIMIT $offset, no_result is added to $sql and is not on the end of every string and also notice the changes to the prev/next link part.

<? 
$dbuser = "root"; 
$dbserver = "localhost"; 
$dbpass = "xxxxx"; 
$dbname = "NATFD"; 

//CONNECTION STRING 
mysql_connect($dbserver, $dbuser, $dbpass) 
or die ("UNABLE TO CONNECT TO DATABASE"); 
mysql_select_db($dbname) 
or die ("UNABLE TO SELECT DATABASE"); 

if (isset($_GET['offset'])) { 
    $offset = $_GET['offset']; 
} 
else 
{ 
    $offset = 0; 
} 


//JUST STATE 
if ($_GET[city]=="" AND $_GET[name]=="") 
$sql = ("SELECT MLA_NO, NAME, STREET, CITY, STATE, ZIP, PHONE, FAX, IP_ADDR FROM company WHERE state='$_GET[state]' ORDER BY city ASC"); 
//JUST CITY 
elseif ($_GET[state]=="" AND $_GET[name]=="") 
$sql = ("SELECT MLA_NO, NAME, STREET, CITY, STATE, ZIP, PHONE, FAX, IP_ADDR FROM company WHERE city LIKE'$_GET[city]%' ORDER BY state ASC"); 
//JUST COMPANY NAME 
elseif ($_GET[state]=="" AND $_GET[city]=="") 
$sql = ("SELECT MLA_NO, NAME, STREET, CITY, STATE, ZIP, PHONE, FAX, IP_ADDR FROM company WHERE name LIKE'$_GET[name]%'ORDER BY state ASC"); 
//ALL 
else 
$sql = ("SELECT MLA_NO, NAME, STREET, CITY, STATE, ZIP, PHONE, FAX, IP_ADDR FROM company WHERE state='$_GET[state]' AND city LIKE'$_GET[city]%' AND name LIKE'$_GET[name]%'"); 
//echo($sql); 
$sql .=" LIMIT $offset, $no_results";
$result = mysql_query($sql) or die("Problem when trying to query Database"); 
$num_rows = mysql_num_rows($result); 
print "<p><a href=java script:history.back(1)><font size=1>BACK</font></a></p>"; 
print "<font size=3><strong>$num_rows Records found.<P>"; 
//Set up the Table 
print "<table width=100% border=1>\n"; 
print "<tr>"; 
print "<td bgcolor=#CCCCCC><div align=center><font size=1>Contacts</font></td>"; 
print "<td bgcolor=#CCCCCC><div align=center><font size=1>NAME (click for Details)</font></td>"; 
print "<td bgcolor=#CCCCCC><div align=center><font size=1>STREET</font></td>"; 
print "<td bgcolor=#CCCCCC><div align=center><font size=1>CITY</font></td>"; 
print "<td bgcolor=#CCCCCC><div align=center><font size=1>STATE</font></td>"; 
print "<td bgcolor=#CCCCCC><div align=center><font size=1>ZIP</font></td>"; 
print "<td bgcolor=#CCCCCC><div align=center><font size=1>PHONE</font></td>"; 
print "<td bgcolor=#CCCCCC><div align=center><font size=1>FAX</font></td>"; 
print "<td bgcolor=#CCCCCC><div align=center><font size=1>WEB SITE</font></td>"; 
print "</tr>"; 
//Get the data into the table 
while ($row = mysql_fetch_array($result)) 
{ 
echo("<tr>"); 
echo("<td nowrap><font face=arial size=1><a href=contacts.php?MLA_NO=$row[MLA_NO]>$row[MLA_NO]</a></td>"); 
echo("<td nowrap><font face=arial size=1><a href=details2.php?MLA_NO=$row[MLA_NO]>$row[NAME]</a></td>"); 
echo("<td nowrap><font face=arial size=1>$row[STREET]</td>"); 
echo("<td nowrap><font face=arial size=1>$row[CITY]</td>"); 
echo("<td nowrap><font face=arial size=1>$row[STATE]</td>"); 
echo("<td nowrap><font face=arial size=1>$row[ZIP]</td>"); 
echo("<td nowrap><font face=arial size=1>$row[PHONE]</td>"); 
echo("<td nowrap> <font face=arial size=1>$row[FAX]</td>"); 
echo("<td nowrap> <font face=arial size=1><a href=http://$row[IP_ADDR]>$row[IP_ADDR]</a></td>"); 
echo("</tr>"); 
} 


if ($offset!=0) {   

   if ($offset <= $no_results) $prev_page = 0;   

   else $prev_page = $offset - $no_results;  

   if(stristr($_SERVER['QUERY_STRING'],"offset=$offset")) {
   	$query_string = str_replace("offset=$offset","offset=$prev_page",$_SERVER['QUERY_STRING']);
   } else {
   	$query_string = $_SERVER['QUERY_STRING']."&offset=$prev_page";
   }
   echo "<a href=\"?".$query_string."\">Prev. Page</a>";   

}   

// $count_db = mysql_query("SELECT COUNT(*) FROM database");   

$count_db =100;
if ($offset + $no_results <= $count_db) {   

   $next_page = $offset + $no_results;   

   if(stristr($_SERVER['QUERY_STRING'],"offset=$offset")) {
   	$query_string = str_replace("offset=$offset","offset=$next_page",$_SERVER['QUERY_STRING']);
   } else {
   	$query_string = $_SERVER['QUERY_STRING']."&offset=$next_page";
   }
   echo "<a href=\"?".$query_string."\">Next Page</a>";   

}
?>

I had forgotten that http_build_query hasn't been released yet. So, just try this and see if it works.

[timinator]

Message on results page:

"Problem when trying to query Database"

if I remove the: or die("Problem when trying to query Database")

I get:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /natfd/sites.php on line 49

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /natfd/sites.php on line 67

thanks

[python_q]

Oops, forgot to declare $no_results. Just put $no_results = however many results per page you want somewhere near the top of the script.

[timinator]

I inserted the code $no_results =50 in 2 different places at top of script and received these errors.

Parse error: parse error, unexpected T_VARIABLE in /natfd/sites.php on line 46

Parse error: parse error, unexpected T_IF in /natfd/sites.php on line 33

thanks

[python_q]

Check around lines 33 and 46 and make sure you didn't forget a semi-colon, quotes, parenthesis or something else that might cause a parse error.

[timinator]

I forgot the semi-colon after $no_results =50 - it seems to be working now!

Is there a way for the NEXT link not to show up if there are less than 50 records?

Also I have a line that counts records found
$num_rows = mysql_num_rows($result);
print "<font size=3><strong>$num_rows Records found.<P>";

but it only counts records (rows) for that page. Can it count "all" records found even if there are multiple pages?

Thanks