LinkBack URL
About LinkBacksI couldn't find anything on this one, but it is so common. I must be looking with some wrong terms. So you have a database with 100 records in it. You want the user to view, let us say, 10 at a time. Of course that variable would be pulled in from a form. I have some code posted that works, but just wanted to see if there is a better way to do it, especially as it pertains to performance. I didn't add all of the sorting variable, but is there a better way to structure it, rather than hard coding the variable in if statements.
PHP Code:<?php
include 'sql.php';
$page=$_GET['pid'];
if($page==""){
$page=1;
}
$sort=$_GET['sort'];
if($sort==""){
$sort="id";
}
$db=mysql_connect($host,$user,$pass);
mysql_select_db($database,$db);
$sql="SELECT `id` FROM `post`";
$result=mysql_query($sql);
$num_rows=mysql_num_rows($result);
mysql_free_result($result);
$sort_num=10;//<--- pull in from a form variable eg 10,20,30,...
$num_pages=ceil($num_rows/$sort_num);
$current_end=$page*$sort_num;
$current_start=$current_end-$sort_num;
$sql="SELECT * FROM `post` WHERE 1 ORDER BY `".$sort."` LIMIT ".$current_start.",".$sort_num;
$result=mysql_query($sql,$db);
$output_array=array();
while($val=mysql_fetch_array($result,MYSQL_ASSOC)){
array_push($output_array,$val);
}
//print out meta and head html
echo("
<!DOCTYPE html PUBLIC \"-//W3C//DTD XHTML 1.0 Transitional//EN\" \"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd\">
<html xmlns=\"http://www.w3.org/1999/xhtml\">
<head>
<meta http-equiv=\"Content-Type\" content=\"text/html; charset=ISO-8859-1\" />
<title>Split</title>
</head>
<body>\n");
//print out sort navigation
for($i=1;$i<=$num_pages;$i++){
echo("\t<a href='?pid=".$i."&sort=".$sort."'>".$i."</a> | \n");
}
echo("\n\t<br />\n");
//print out selected page
for($i=0;$i<sizeof($output_array);$i++){
echo("\n\t".$output_array[$i]['id'].". ".$output_array[$i]['name']."<br />");
}
echo("\n\t<p>\n\t\t<a href='?pid=".$page."&sort=id'>sort by id</a> ::\n\t\t<a href='?pid=".$page."&sort=name'>sort by name</a>\n\t</p>");
//close html
echo("\n</body>\n</html>");
?>
If this is your first visit, please click the Sign Up now button to begin the process of creating your account so you can begin posting on our forums! The Sign Up process will only take up about a minute of two of your time.
Results 1 to 6 of 6
-
Oct 15th, 2007, 03:52 PM #1Junior Member
- Join Date
- Jul 2007
- Location
- Orlando Florida
- Posts
- 20
- Member #
- 15537
-
Oct 15th, 2007 03:52 PM
-
Oct 15th, 2007, 03:59 PM #2
google "pagination"
The Rules
Was another WDF member's post helpful? Click the like button below the post.
Admin at houseofhelp.com
-
Oct 15th, 2007, 05:03 PM #3Junior Member
- Join Date
- Jul 2007
- Location
- Orlando Florida
- Posts
- 20
- Member #
- 15537
found this article for anyone else interested. http://www.phpfreaks.com/tutorials/43/0.php . Thanks for the vocab. I think I did it pretty well, just use the count(*) in SQL rather than query the entire database and use mysql_num_rows on the result. Thanks again for your help.
Originally Posted by Wired
-
Oct 15th, 2007, 05:06 PM #4
The LIMIT clause is the only way to do it.
If you're looking to improve performance, you can use subqueries to restrict how much data you're querying, but that can get overly complex.
PS - you can eliminate the "WHERE 1" clause from your query - it's not doing anything.
PPS - make sure all your sortable columns are indexed to optimize performance further.
-
Oct 15th, 2007, 05:07 PM #5Ah, yeah, missed that part... you definitely want to replace a query of all records with a count... that'll improve performance on that query by 99% ++ Originally Posted by Prajna
-
Oct 15th, 2007, 05:12 PM #6Junior Member
- Join Date
- Jul 2007
- Location
- Orlando Florida
- Posts
- 20
- Member #
- 15537
Yea, I'm using the limit as well in the SQL. But thanks for the WHERE 1. (silly rookie I am). I'll confess, I test my SQL in phpMyAdmin before I place them in, and it loves the WHERE 1. It is very redundent (not limiting on performace I wouldn't think), but non-the-less....WHERE true is always everything and would probably just show my SQL age to other developers. Thanks for the heads up. Originally Posted by transio
Reply With Quote
