"Previous page" and "next page" [Archive]

View Full Version : "Previous page" and "next page"

Nusha

08-05-2003, 06:20 PM

Hi!
Can somebody please help me with this?
I have the table in my database(MySQL) named "dogs" with two columns in it "name" (varchar type) and "birth" (date type).

+-----------------+-----------------+
| Name | Birth |
+-----------------+-----------------+
| Buffy | 2001-12-27 |
+-----------------+-----------------+
| Neko | 1998-03-05 |
+-----------------+-----------------+
| Sonya | 1997-01-13 |
+-----------------+-----------------+
| Rony | 1996-02-07 |
+-----------------+-----------------+
| Lord | 1996-07-15 |
+-----------------+-----------------+
| Aris | 1995-10-10 |
+-----------------+-----------------+
| Lin | 1999-09-19 |
+-----------------+-----------------+
| Art | 1992-11-08 |
+-----------------+-----------------+

I needed to be able to retrieve dogs with certain age , so I used this html form

<html>
<form action=search.php method=POST>
age min: <input type=text name=min><br>
age max: <input type=text name=max><br>
<input type=submit name=submit value="Submit">
</form>
</html>

and this script (search.php)

<?php
$conn = mysql_connect("host", "root", "pass") or die(mysql_error());
mysql_select_db("db",$conn) or die(mysql_error());
$sql = "SELECT name, birth, current_date, (year(current_date)-year(birth)) - (right(current_date,5)<right(birth,5)) AS age FROM dogs where (year(current_date)-year(birth)) - (right(current_date,5)<right(birth,5)) <= $_POST[max] AND (year(current_date)-year(birth)) - (right(current_date,5)<right(birth,5)) >= $_POST[min]";
$result = mysql_query($sql, $conn) or die(mysql_error());
echo "<table><tr>";
while ($user = mysql_fetch_array($result)) {
$name = $user['name'];
$age = $user['age'];
echo "<td>$name<br>$age</td>";
if (++$resultcount % 3 == 0)
echo "</tr><tr>";
}
echo "</tr></table>";
?>

Everything works just fine.

But now I would like to retrieve only 6 results on one page and to have "PREVIOUS PAGE" and "NEXT PAGE" links where it needed.

If you have any ideas how to do it please help me.

ilyash

08-05-2003, 09:40 PM

for previous + next..
take the num of pages (TOTAL)
TOTAL/6 = number of pages
[Next] link to xxxx?page=2
[Previous] link to xxxx?page=1
etc...
the mysql statement to select 6 is:
select * from xxx limit(BEGIN,END);

kevint8

08-06-2003, 05:32 AM

Actually, the mysql statement to select 6 rows at a time is:

SELECT * FROM tablename LIMIT startrow, 6

Nusha

08-06-2003, 10:51 PM

Ok I tried this:

<?php
$conn = mysql_connect("host", "root", "pass") or die(mysql_error());
mysql_select_db("db",$conn) or die(mysql_error());
if(!isset($start)) $start = 0;
$sql = "SELECT name, birth, current_date, (year(current_date)-year(birth)) - (right(current_date,5)<right(birth,5)) AS age FROM dogs LIMIT " . $start . ", 6";
$result = mysql_query($sql, $conn) or die(mysql_error());
echo "<table><tr>";
while ($user = mysql_fetch_array($result)) {
$name = $user['name'];
$age = $user['age'];
echo "<td>$name<br>$age</td>";
if (++$resultcount % 3 == 0)
echo "</tr><tr>";
}
$sql = "SELECT count(*) as count FROM dogs";
$result = mysql_query($sql);
$row = mysql_fetch_array($result);
$numrows = $row['count'];
if($start > 0)
echo "<a href=\"" . $PHP_SELF . "?start=" . ($start - 6) .
"\">Previous</a><BR>\n";
if($numrows > ($start + 6))
echo "<a href=\"" . $PHP_SELF . "?start=" . ($start + 6) .
"\">Next</a><BR>\n";
echo "</tr></table>";
?>

It worked just fine, until I added WHERE statement.

<?php
$conn = mysql_connect("host", "root", "pass") or die(mysql_error());
mysql_select_db("db",$conn) or die(mysql_error());
if(!isset($start)) $start = 0;
$sql = "SELECT name, birth, current_date, (year(current_date)-year(birth)) - (right(current_date,5)<right(birth,5)) AS age FROM dogs WHERE (year(current_date)-year(birth)) - (right(current_date,5)<right(birth,5)) <= $_POST[max] AND (year(current_date)-year(birth)) - (right(current_date,5)<right(birth,5)) >= $_POST[min] LIMIT " . $start . ", 6";
$result = mysql_query($sql, $conn) or die(mysql_error());
echo "<table><tr>";
while ($user = mysql_fetch_array($result)) {
$name = $user['name'];
$age = $user['age'];
echo "<td>$name<br>$age</td>";
if (++$resultcount % 3 == 0)
echo "</tr><tr>";
}
$sql = "SELECT count(*) as count FROM dogs";
$result = mysql_query($sql);
$row = mysql_fetch_array($result);
$numrows = $row['count'];
if($start > 0)
echo "<a href=\"" . $PHP_SELF . "?start=" . ($start - 6) .
"\">Previous</a><BR>\n";
if($numrows > ($start + 6))
echo "<a href=\"" . $PHP_SELF . "?start=" . ($start + 6) .
"\">Next</a><BR>\n";
echo "</tr></table>";
?>

Now script shows only the first page and when I click “Next” button I am getting this 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 'AND (YEAR(CURRENT_DATE)-YEAR(birth)) - (RIGHT(CURRENT_DATE,5)<R

I don’t know what the problem is. Plese help a lady in need :-)

Umbongo

08-07-2003, 09:42 AM

Ok here are the problems with the script :
1. I assume you have register_globals turned on as you use $start rather tha $_GET['start'] but use $_POST[min] and $_POST[max]. You should be consistant and really always have register_globals turned off.

2. Because you are going to the new page via a html link $_POST[min] and $_POST[max] are no longer accessable variables on the new page so are being treated as blanks so your WHERE statment is trying to complete logic against blank values.

I got your script to work by changing these things. I reccomend making the html form pass variables by GET rather than POST then you can include min and max via a html link. (note: remove the submit buttons name otherwise this will be passed on the GET also).

<?php

$conn = mysql_connect("host", "root", "pass") or die(mysql_error());
mysql_select_db("db",$conn) or die(mysql_error());
if(!isset($_GET['start'])) $_GET['start'] = 0;
if(!isset($_GET['min'])) $_GET['min'] = 0;
if(!isset($_GET['max'])) $_GET['max'] = 0;

$sql = "SELECT name, birth, current_date, (year(current_date)-year(birth)) - (right(current_date,5)<right(birth,5))" .
"AS age FROM dogs WHERE (year(current_date)-year(birth)) - (right(current_date,5)<right(birth,5))" .
"<= " . $_GET['max'] . " AND (year(current_date)-year(birth)) - (right(current_date,5)<right(birth,5))" .
">= " . $_GET['min'] . " LIMIT " . $_GET['start'] . ", 6";
$result = mysql_query($sql, $conn) or die(mysql_error());
echo "<table><tr>";
while ($user = mysql_fetch_array($result)) {

$name = $user['name'];
$age = $user['age'];
echo "<td>$name<br>$age</td>";
if (++$resultcount % 3 == 0) echo "</tr><tr>";

}

$sql = "SELECT count(*) as count FROM dogs WHERE (year(current_date)-year(birth)) - " .
"(right(current_date,5)<right(birth,5)) <= " . $_GET['max'] . " AND (year(current_date)-year(birth)) - " .
"(right(current_date,5)<right(birth,5)) >= " . $_GET['min'];
$result = mysql_query($sql);
$row = mysql_fetch_array($result);
$numrows = $row['count'];

if($_GET['start'] > 0) echo "<a href=\"" . $PHP_SELF . "?start=" . ($_GET['start'] - 6) . "&min=" . $_GET['min'] .
"&max=" . $_GET['max'] . "\">Previous</a><BR>\n";
if($numrows > ($_GET['start'] + 6)) echo "<a href=\"" . $PHP_SELF . "?start=" . ($_GET['start'] + 6) .
"&min=" . $_GET['min'] . "&max=" . $_GET['max'] . "\">Next</a><BR>\n";
echo "</tr></table>";

?>

Nusha

08-07-2003, 11:14 AM

Thank you very much. I will try it now.

Umbongo

08-07-2003, 11:22 AM

No worries.

Nusha

08-07-2003, 12:18 PM

It works! Thank you again.