prev/next button HELP!

manny

got the following code from way back. i've been tweaking with it, but couldn't make it work. initial limit works but when i click on another page or next or prev button, the script searches my whole database and presents ALL of my data.
anyone please help!!!

$connection= mysql_connect($hostname, $user, $pwd)
or die ("Error: No connection to MySQL server\n");

$db = mysql_select_db($dbname, $connection)
or die ("Couldn't select DB");

$limit = 5;

$sqlcount= "SELECT * FROM table $where";

$sql_countresult = mysql_query($sqlcount, $connection) or die ("Couldn't execute count query");
$totalrows = mysql_num_rows($sql_countresult);

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

$limitvalue1 = $page$limit-($limit);
$sqlresult = "SELECT FROM table $where LIMIT $limitvalue1, $limit";
$sql_result = mysql_query($sqlresult, $connection) or die ("Couldn't execute result query: $sqlresult.");

echo "There are $totalrows entries found.";
print " ";

if ($row=mysql_fetch_array ($sql_result)){
do{
echo "<a href=\"$PHP_SELF?table_id=$row[table_id]\"
target=\"$PHP_SELF\">";
print $row [lname];
print ", ";
print $row [fname];
print " ";
print $row [mname];
print "</a>";
print " ";
echo "Email address:";
print "<a href=\"mailto:\">";
print $row [email];
print "</a>";
print "";

}while ($row=mysql_fetch_array($sql_result));
} else {print "Sorry, no records are found.";}
echo " ";
echo " ";

if($page != 1) {
$pageprev= $page - 1;
echo "<A HREF=\"$PHP_SELF?page=$pageprev\"> PREV </A>"; // if page is not equal to one, prev goes to $page - 1
}
else {
echo "<A HREF=\"$PHP_SELF?page=$page\"> PREV </A>"; // Otherwise, PREV reloads the page
}

$numofpages = $totalrows/$limit;

for($i= 1; $i < $numofpages+1; $i++) {
echo "<A HREF=\"$PHP_SELF?page=$i\"> $i </A>"; //make number navigation
}

if($totalrows%$limit != 0) {
echo "<A HREF=\"$PHP_SELF?page=$i\"> $i </A>"; ////if there is a remainder, add another page
}

if(($totalrows-($limit$page)) > 0){
$pagenext = $page + 1;
echo "<A HREF=\"$PHP_SELF?page=$pagenext\"> NEXT </A>"; // if the totalrows - $limit $page is > 0 (meaning there is a remainder), leave the next button.
}

mysql_free_result($sql_result);

Anon

Here's what I use:

This goes directly into the page:

if(!isset($end))
{
$begin=0;
$end=25;
}
elseif($direction=="next")
{
$begin=$begin+25;
$end=25;
}
elseif($direction=="previous")
{
$begin=$begin-25;
$end=25;
}
$where.=" LIMIT $begin,$end";

TheLIMITSQLStatement ($columns_to_select,$maintable,$where )

CODE FOR NEXT AND PREVIOUS LINKS:
<a href="page.php?direction=next&<?php echo"begin=$begin&end=$end"?>"> 
<?php if($begin+25<$count) echo"Next 20>>"?>

My SQL STATEMENT:
Function TheLIMITSQLStatement($columns_to_select,$maintable,$where )
{

SELECT $columns_to_select FROM $maintable $where

}

Anon

Thanks. ihave a few questions (couldn\'t make it work):

This goes directly into the page:

****which page? searchform.php or searchform.htm?

if(!isset($end))
{
$begin=0;
$end=25;
}
elseif($direction==\"next\")
{
$begin=$begin+25;
$end=25;
}
elseif($direction==\"previous\")
{
$begin=$begin-25;
$end=25;
}
$where.=\" LIMIT $begin,$end\";
TheLIMITSQLStatement ($columns_to_select,$maintable,$where )

****i only have one table and database. how do i assign $columns_to_select? where do i present the script for my data, after this script?

CODE FOR NEXT AND PREVIOUS LINKS:
<a href=\"page.php?direction=next&<?php echo\"begin=$begin&end=$end\"?>\">
<?php if($begin+25<$count) echo\"Next 20>>\"?>

****can i use $PHP_SELF here?

My SQL STATEMENT:
Function TheLIMITSQLStatement($columns_to_select,$maintable,$where )
{

SELECT $columns_to_select FROM $maintable $where

***** where do i put this code?
}

thanks. im new to php. im trying to learn.

arinepon

Sorry, I should have been more clear:

Thanks. ihave a few questions (couldn\'t make it work):

This goes directly into the page:

****which page? searchform.php or searchform.htm?

Answer:
All of this code can go into the same page (the results page, it would have to be a .php page). The if elseif statements below, the previous and next statements, and the function at the bottom to actually call the SQL.

****i only have one table and database. how do i assign $columns_to_select? where do i present the script for my data, after this script?

ANSWER:
$columns_to_select should be a variable you set equal to the names of the tables in the DB. In your case, replace $columns_to_select with the name of your table in quotes, and send it to the DB that way. You could replace $maintable with the name of your table also (again, send it through in qoutes). The $where gets built by the code above.

Also, you should learn how to use the relational properties of a DB if you are going to be building a DB driven website. Its pretty cool stuff 🙂.

CODE FOR NEXT AND PREVIOUS LINKS:
<a href=\"page.php?direction=next&<?php echo\"begin=$begin&end=$end\"?>\">
<?php if($begin+25<$count) echo\"Next 20>>\"?>

****can i use $PHP_SELF here?

ANSWER:
Yup. I recommend it in fact. Just make sure it works on your server correctly

My SQL STATEMENT:
function TheLIMITSQLStatement($columns_to_select,$maintable,$where )
{

SELECT $columns_to_select FROM $maintable $where

***** where do i put this code?
}

ANSWER:
This code can either go directly in the results page with the rest of the code above, or be included/required from a file with all of your functions. If you are really new, just put it at the bottom, or top of the page (in <?php ?> tags of course). Also note, the function directly above is not complete.

thanks. im new to php. im trying to learn.

ANSWER:
Me too. I've only been coding in it for about 2 months. You will pick it up quickly, especially if you have some prior coding experience (I have a year of C++). Also, read articles from Webmonkey and places like that. I found the book "MySQL/PHP Database Applications" by Greenspan and Bulger to be a great resource. You can learn PHP and MySQL and it has great examples. Let me know if you need any more help.
Ari