Page 1 of 1
Limiting items per page
Posted: Mon Nov 11, 2002 3:37 pm
by evilmonkey
Hello.
I have a db (mysql of course) with id, title and other (unimportant) fields. I want to create a query that will only show for example titles with id 1-10. Then when we get number 11 to create a new page that will say 11-20. I want this to look like any search engine, with the possibility to skip ahead, or click next. How would I go about doing that? Thanks.
Posted: Mon Nov 11, 2002 3:46 pm
by Silver_Eclipse
you could try
Code: Select all
$result = mysql_query($sql, $mysql) or die(mysql_error());
while ($row = mysql_fetch_array($result)){
if ($rowї"id"] > 0 && $rowї"id"] < 11){
print "$rowї"id"]";
}
}
and make multiple pages or use a switch.
or something like this.
Code: Select all
$temp_sql = "SELECT * FROM tabel WHERE id > 0 && id < 11";
while ($row = mysql_fetch_array($result)){
print "$rowї"id"]";
}
Posted: Mon Nov 11, 2002 3:46 pm
by volka
Posted: Mon Nov 11, 2002 4:12 pm
by evilmonkey
Ok, I got something going here:
Code: Select all
<script type="text/javascript">
function popup_all(id)
{
window.open("page2.php?id=" + id, "window_name", "0","0","0","0","yes","yes","200","300");
}
</script>
<?php
$db = mysql_connect("localhost","","");
mysql_select_db("",$db);;
$callsign = $HTTP_POST_VARSї'id'];
$records = 10; // Number of records per page
$start = $_GETї"id"]; // Get "id" value from address line
if ($start == NULL) // No address line variable
{
$start = 0; // Default value
}
$sql = "SELECT id, title FROM table WHERE category=something LIMIT " .$start. ", " .$records;
$result=mysql_query ($sql, $db);
while ($row = mysql_fetch_object ($result))
{
print("<td><a href="javascript:popup_all($row->id)">$row->title</a></td>");
}
// Add number of records per page to starting value to get records for next page
$next = $start + $records;
print("<form method="GET" action="?$next">");
print("<input type="submit" value="Next">");
print("</form>");
?>
This gives me an error on line where 'while' begins. Can someone tell me why, and suggest a way to fix it? Thanks.
Posted: Mon Nov 11, 2002 4:45 pm
by volka
This gives me an error on line where 'while' begins.
not very helpful

try
Code: Select all
$result=mysql_query ($sql, $db) or die($sql.' :'.mysql_error());
and take a look into the (web-)server(-software)'s error.log
Posted: Mon Nov 11, 2002 4:52 pm
by evilmonkey
Thanks Volka. Tht shined the light on my problem. Quite a stupid mistake actually, I made a mistake with the quotes. Thanks.
I have another question though. All this script does is it gives me the 'next' button. Is there a way that I can make links to the beginning number? For example, if my database id filed goes up to 30, can I have three links: 1-10, 11-20, and 21-30? If so, then how?
Thanks. This is my new code, feel free to suggest any alterations.
Code: Select all
<HTML>
<body>
<script type="text/javascript">
function popup_all(id)
{
window.open("somewhere.php?id=" + id, "window_name", "0","0","0","0","yes","yes","200","300");
}
</script>
<?php
$db = mysql_connect("localhost","","");
mysql_select_db("",$db);;
$callsign = $HTTP_POST_VARSї'id'];
$records = 10; // Number of records per page
$start = $_GETї"id"]; // Get "id" value from address line
if ($start == NULL) // No address line variable
{
$start = 0; // Default value
}
$sql = "SELECT id, title FROM userjokes WHERE category='animal' LIMIT " .$start. ", " .$records;
$result=mysql_query ($sql, $db) or die($sql.' :'.mysql_error());
while ($row = mysql_fetch_object ($result))
{
print("<td><a href="javascript:popup_all($row->id)">$row->title</a></td>");
}
// Add number of records per page to starting value to get records for next page
$next = $start + $records;
print("<form method="GET" action="?$next">");
print("<input type="submit" value="Next">");
print("</form>");
?>
</body>
</HTML>
Another problem, ?$next doesn't work. In my browser, once I press the button, all it does is it changes the address by adding a question mark (?) at the end. Why? Thanks.
Posted: Mon Nov 11, 2002 5:05 pm
by volka
with
Code: Select all
SELECT count(*) from tablename WHERE condition
you can get the amount of recordsets. With a simple for-loop you can print those links.
e.g. (without <a href...>)
Code: Select all
for ($i=0; $i<$countRecords; $i+=$recordsPerPage)
echo $i+1, '-', ($i+$recordsPerPage < $countRecords) ? $i+$recordsPerPage+1 : $countRecords, '&nbsp;';
(completely untested, conditions maybe false)
Posted: Mon Nov 11, 2002 5:45 pm
by evilmonkey
Hello Volka. I don't really get the above, but I know where my problem with the button is. Something is wrong with $next. Is:
Code: Select all
print("<form method="GET" action="?$next">");
the proper syntax? Everything else seems to be fine. (If I put it in the address bar manually) When I press the button, all I see on the address bar is it adds "?" at the end of the address. I need it to add "?id=whatever". ID is an integer. Can someone tell me how to fix it?
Thanks.
Posted: Mon Nov 11, 2002 5:53 pm
by volka
try
Code: Select all
echo '<form method="GET" action="', $_SERVERї'PHP_SELF'], '?id=', $theIdVariableForNextPage, '">';
$_SERVER['PHP_SELF'] contains the uri (without protocol or server) to the current script.
You may also add a hidden field to the form.
Code: Select all
...
echo '<form method="GET" action="', $_SERVERї'PHP_SELF'], '">';
echo '<input type="hidden" name="id" value="', $theIdVariableForNextPage, '" />';
....
Posted: Mon Nov 11, 2002 6:39 pm
by evilmonkey
Thanks Volka!
The hidden script worked.
Cheers!