Page 1 of 1

[SOLVED] Super simple pagination

Posted: Mon Jul 11, 2005 1:38 pm
by charp
I've created a super simple pagination routine that only displays links for the 'prev' and 'next' set of entries -- no page numbers. Although everything works properly on the small database I created for tesing purposes, my question is about the efficiency of this code. Primarily, my concern is that the database is queried twice with each click of the 'prev' or 'next' links. Could I have accomplished the same with fewer queries?

Your critique is welcomed. Thanks!

Code: Select all

<?php
// Connect to database
$databasename= "XXX";
$username = "XXX";
$table = "XXX";
$password = "XXX";
$db = mysql_connect("$host", "$username", "$password");
mysql_select_db("$databasename", $db);
$num_rows = mysql_num_rows(mysql_query("SELECT * FROM $table"));

$limit = 10; // this many per page
$id=$_GET['id']; // which record to display next

// start with the first row
if (!isset($id)) {
  $id=0;
} else {
  $id = $id;
}

// grab data from database
$results=mysql_query("SELECT * FROM $table LIMIT $id, $limit");

echo '<table border="1">'."\n";

while($row=mysql_fetch_array($results)) {
  $record=$row['id'];
  $address=$row["address"];
  $city=$row["city"];

echo '<tr><td>'.$record.'</td><td>'.$address.'</td><td>'.$city.'</td></tr>'."\n";
}

echo '</table>';

// show previous link
if ($id>=$limit) {
  $prev=$id-$limit;
  echo '<a href="'.$_SERVER['PHP_SELF'].'?id='.$prev.'">prev</a>';
} else {
  echo '<span style="color: #999999;">prev</span>';
}

echo '&nbsp;&nbsp;&nbsp;&nbsp;';

// show next link
if ($id<($num_rows - $limit)) {
$next=$id+$limit;
  echo '<a href="'.$_SERVER['PHP_SELF'].'?id='.$next.'">next</a>';
} else {
  echo '<span style="color: #999999;">next</span>';
}
?>

Posted: Mon Jul 11, 2005 2:01 pm
by hawleyjr
You should be fine having two queries on a page.

I would change the following query:

Code: Select all

$num_rows = mysql_num_rows(mysql_query("SELECT * FROM $table"));
to:

Code: Select all

$num_rows = mysql_query("SELECT count(*) FROM $table");

Posted: Mon Jul 11, 2005 6:41 pm
by charp
Thanks hawleyjr for the feedback. It's helpful to hear from one with more experience.

I did try that change you suggested, but it didn't work for me. Instead of returning the number of rows in the table, it gave something like:

resource id #3

Am I doing something wrong here?

Posted: Mon Jul 11, 2005 6:47 pm
by hawleyjr

Code: Select all

//TRY THIS

$query_rslt = mysql_query("SELECT count(*) as cnt FROM $table");

while($row = mysql_fetch_array($query_rslt))
{
	$num_rows = $row["cnt"];
}

Posted: Mon Jul 11, 2005 6:55 pm
by charp
It works!

Thanks again hawleyjr.

Posted: Fri Jul 15, 2005 11:35 am
by zoe
this is great. i've been battling with pagination for days (very newbie) and charp's code works for me if i pare my query down to 'select * from table'. Trouble is my query contains a WHERE clause and whenever I click the 'next' link, the next page contains unfiltered data as if the query had just been SELECT * FROM table.

I'm guessing I somehow need to adapt the echo statement for the links at the end?

Here's my query:

Code: Select all

$result = mysql_query(&quote;SELECT * FROM table WHERE Name LIKE '%$businesstype%' OR (Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%') LIMIT $id, $limit&quote;);
Any nuggets of wisdom would be much appreciated. I can't wait to be competent enough to give something back to this forum. It's helped me so much already. for days (very newbie) and charp's code works for me if i pare my query down to 'select * from table'. Trouble is my query contains a WHERE clause and whenever I click the 'next' link, the next page contains unfiltered data as if the query had just been SELECT * FROM table.

I'm guessing I somehow need to adapt the echo statement for the links at the end?

Here's my query:

Code: Select all

$result = mysql_query(&quote;SELECT * FROM table WHERE Name LIKE '%$businesstype%' OR (Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%') LIMIT $id, $l works for me if i pare my query down to 'select * from table'. Trouble is my query contains a WHERE clause and whenever I click the 'next' link, the next page contains unfiltered data as if the query had just been SELECT * FROM table.

I'm guessing I somehow need to adapt the echo statement for the links at the end?

Here's my query:

Code: Select all

$result = mysql_query(&quote;SELECT * FROM table WHERE Name LIom table'. Trouble is my query contains a WHERE clause and whenever I click the 'next' link, the next page contains unfiltered data as if the query had just been SELECT * FROM table.

I'm guessing I somehow need to adapt the echo statement for the links at the end?

Here's my query:

Code: Select all

$result = mysql_query(&quote;SELECT * FROM table WHERE Name LIKE '%$businesstype%' OR (Type LIKE '%$busindown to 'select * from table'. Trouble is my query contains a WHERE clause and whenever I click the 'next' link, the next page contains unfiltered data as if the query had just been SELECT * FROM table.

I'm guessing I somehow need to adapt the echo statement for the links at the end?

Here's my query:

Code: Select all

$result = mysql_query("SELECT * FROM table WHERE Name LIKE '%$businesstype%' OR (Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%') LIMIT $id, $limit");
Any nuggets of wisdom woue been battling with pagination for days (very newbie) and charp's code works for me if i pare my query down to 'select * from table'. Trouble is my query contains a WHERE clause and whenever I click the 'next' link, the next page contains unfiltered data as if the query had just been SELECT * FROM table.

I'm guessing I somehow need to adapt the echo statement for the links at the end?

Here's my query:

Code: Select all

$result = mysql_query("SELECT * FROM table WHERE Name LIKE '%$businesstype%' OR (Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%') LIMIT $id, $limit");
Any nuggets of wisdom would be much appreciated. I can't wait to be competent enough to give se been battling with pagination for days (very newbie) and charp's code works for me if i pare my query down to 'select * from table'. Trouble is my query contains a WHERE clause and whenever I click the 'next' link, the next page contains unfiltered data as if the query had just been SELECT * FROM table.

I'm guessing I somehow need to adapt the echo statement for the links at the end?

Here's my query:

Code: Select all

$result = mysql_query("SELECT * FROM table WHERE Name LIKE '%$businesstype%' OR (Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%') LIMIT $id, $limit");
Any nuggets of wisdom would be much appreciated. I can't wait to be competent enough to give something back to this forum. It's helped me so much already.amp;quote;);


Any nuggets of wisdom would be much appreciated. I can't wait to be competent enough to give something back to this forum. It's helped me so much already.e works for me if i pare my query down to 'select * from table'. Trouble is my query contains a WHERE clause and whenever I click the 'next' link, the next page contains unfiltered data as if the query had just been SELECT * FROM table.

I'm guessing I somehow need to adapt the echo statement for the links at the end?

Here's my query:

Code: Select all

$result = mysql_query("SELECT * FROM table WHERE Name LIKE '%$businesstype%' OR (Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%') LIMIT $id, $limit");
Any nuggets of wisdom would be much appreciated. I can't wait to be competent enough to give something back to this forum. It's helped me so mue my query down to 'select * from table'. Trouble is my query contains a WHERE clause and whenever I click the 'next' link, the next page contains unfiltered data as if the query had just been SELECT * FROM table.

I'm guessing I somehow need to adapt the echo statement for the links at the end?

Here's my query:

Code: Select all

$result = mysql_query("SELECTs (very newbie) and charp's code works for me if i pare my query down to 'select * from table'. Trouble is my query contains a WHERE clause and whenever I click the 'next' link, the next page contains unfiltered data as if the query had just been SELECT * FROM table.

I'm guessing I somehow need to adapt the echo statement for the links at the end?

Here's my query:

Code: Select all

$result = mysql_query(&quote;SELECT * FROM table WHERE Name LIKE '%$businesstype%' OR (Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%') LIMIT $id, $limit");
Any nuggets of wisdom would be much appreciated. I can't wait to be competent enough to give something back to this forum. It's helped me so much already. I click the 'next' link, the next page contains unfiltered data as if the query had just been SELECT * FROM table.

I'm guessing I somehow need to adapt the echo statement for the links at the end?

Here's my query:

Code: Select all

$result = mysql_query("SELECT * FROM table WHERE Name LIKE '%$businesstype%' OR (Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%') LIMIT $id, $limit");
Any nuggets of wisdom would be much appreciated. I can't wait to be compet statement for the links at the end?

Here's my query:

Code: Select all

$result = mysql_query("SELECT * FROM table WHERE Name LIKE '%$businesstype%' OR (Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%') LIMIT $id, $limit");
Any nuggets of wisdom would be much appreciated. I can't wait to be competent enough to give something back to this forum. It's helped me so much already.ng with pagination for days (very newbie) and charp's code works for me if i pare my query down to 'select * from table'. Trouble is my query contains a WHERE clause and whenever I click the 'next' link, the next page contains unfiltered data as if the query had just been SELECT * FROM table.

I'm guessing I somehow need to adapt the echo statement for the links at the end?

Here's my query:

Code: Select all

$result = mysql_query("SELECT * FROM table WHERE Name LIKE '%$businesstype%' OR (Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%') LIMIT $id, $limit");
Any nuggets of wisdom would be much appreciated. I can't wait to be competent enough to give something back to this forum. It's helped me so much already.ata as if the query had just been SELECT * FROM table.

I'm guessing I somehow need to adapt the echo statement for the links at the end?

Here's my query:

Code: Select all

$result = mysql_query("SELECT * FROM table WHERE Name LIKE '%$businesstype%' OR (Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%') LIMIT $id, $limit");
Any nuggets of wisdom would be much appreciated. I can't wait to be competent enough to give something back to this forum. It's hel at the end?

Here's my query:

Code: Select all

$result = mysql_query("SELECT * FROM table WHERE Name LIKE '%$businesstype%' OR (Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%') LIMIT $id, $limit");
Any nuggets of wisdom would be much appreciated. I can't wait to be competent enough to give something back to this forum. It's helped me so much already.ed me so much already. 'next' link, the next page contains unfiltered data as if the query had just been SELECT * FROM table.

I'm guessing I somehow need to adapt the echo statement for the links at the end?

Here's my query:

Code: Select all

$result = mysql_query("SELECT * FROM table WHERE Name LIKE '%$businesstype%' OR (Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%') LIMIT $id, $list been SELECT * FROM table.

I'm guessing I somehow need to adapt the echo statement for the links at the end?

Here's my query:

Code: Select all

$result = mysql_query("SELECT * FROM table WHERE Name LIKE '%$businesstype%' OR (Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%') LIMIT $id, $limit");
Any nuggets of wisdom would be much appreciated. I can't wait to be competent enough to give something back to this forum. It's helped me so much already.usinesstype%') LIMIT $id, $limit");


Any nuggets of wisdom would be much appreciated. I can't wait to be competent enough to give something back to this forum. It's helped me so much already.e been battling with pagination for days (very newbie) and charp's code works for me if i pare my query down to 'select * from table'. Trouble is my query contains a WHERE clause and whenever I click the 'next' link, the next page contains unfiltered data as if the query had just been SELECT * FROM table.

I'm guessing I somehow need to adapt the echo statement for the links at the end?

Here's my query:

Code: Select all

$result = mysql_query("SELECT * FROM table WHERE Name LIKE '%$businesstype%' OR (Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%') LIMIT $id, $limit");
Any nuggets of wisdom would be much appreciated. I can't wait to be competent enough to give something back to this forum. It's helped me so much already.f9f6]
$result = mysql_query("SELECT * FROM table WHERE Name LIKE '%$businesstype%' OR (Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%') LIMIT $id, $limit");


Any nuggets of wisdom would be much appreciated. I can't wait to be competent enough to give something back to this forum. It's helped me so much already.on for days (very newbie) and charp's code works for me if i pare my query down to 'select * from table'. Trouble is my query contains a WHERE clause and whenever I click the 'next' link, the next page contains unfiltered data as if the query had just been SELECT * FROM table.

I'm guessing I somehow need to adapt the echo statement for the links at the end?

Here's my query:

Code: Select all

$result = mysql_query("SELECT * FROM table WHERE Name LIKE '%$businesstype%' OR (Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%') LIMIT $id, $limit");
Any nuggets of wisdom would be much appreciated. I can't wait to be competent enough to give slause and whenever I click the 'next' link, the next page contains unfiltered data as if the query had just been SELECT * FROM table.

I'm guessing I somehow need to adapt the echo statement for the links at the end?

Here's my query:

Code: Select all

$result = mysql_query("SELECT * FROM table WHERE Name LIKE '%$businesstype%' OR (Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%') LIMIT $id, $limit");
Any nuggets of wisdom would be much appreciated. I can't wait to be competent enough to give something back to this forum. It's helped me so much already.tion for days (very newbie) and charp's code works for me if i pare my query down to 'select * from table'. Trouble is my query contains a WHERE clause and whenever I click the 'next' link, the next page contains unfiltered data as if the query had just been SELECT * FROM table.

I'm guessing I somehow need to adapt the echo statement for the links at the end?

Here's my query:

Code: Select all

$result = mysql_query("SELECT * FROM table WHERE Name LIKE '%$businesstype%' OR (Type LIKE '%$businesstyppage contains unfiltered data as if the query had just been SELECT * FROM table.

I'm guessing I somehow need to adapt the echo statement for the links at the end?

Here's my query:

Code: Select all

$result = mysql_query("SELECT * FROM table WHERE Name LIKE '%$businesstype%' OR (Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%') LIMIT $id, $limit&quote;);
Any nuggets of wisdom would be much appreciated. I can't wait to be competent enough to give something back to this forum. It's helped k the 'next' link, the next page contains unfiltered data as if the query had just been SELECT * FROM table.

I'm guessing I somehow need to adapt the echo statement for the links at the end?

Here's my query:

Code: Select all

$result = mysql_query("SELECT * FROM table WHERE Name LIKE '%$businesstype%' OR (Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%') LIMIT $id, $limit");
Any nuggets of wisdom would be much appreciated. I can't wait to lt = mysql_query("SELECT * FROM table WHERE Name LIKE '%$businesstype%' OR (Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%') LIMIT $id, $limit&quote;);


Any nuggets of wisdom would be much appreciated. I can't wait to be competent enough to give something back to this forum. It's helped me so much already.the echo statement for the links at the end?

Here's my query:

Code: Select all

$result = mysql_query("SELECT * FROM table WHERE Name LIKE '%$businesstype%' OR (Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%') LIMIT $id, $limit");
Any nuggets of wisdom would be much appreciated. I can't wait to be competent enough to give something back to this forum. It's helped me so much already.i pare my query down to 'select * from table'. Trouble is my query contains a WHERE clause and whenever I click the 'next' link, the next page contains unfiltered data as if the query had just been SELECT * FROM table.

I'm guessing I somehow need to adapt the echo statement for the links at the end?

Here's my query:

Code: Select all

$result = mysql_query("SELECT * FROM table WHERE Name LIKE '%$businesstype%' OR (Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%') LIMIT $id, $limit");
Any nuggets of wisdom would be much appreciated. I can't wait to be competent enough to give something back to this forum. It's helped me so much already. with pagination for days (very newbie) and charp's code works for me if i pare my query down to 'select * from table'. Trouble is my query contains a WHERE clause and whenever I click the 'next' link, the next page contains unfiltered data as if the query had just been SELECT * FROM table.

I'm guessing I somehow need to adapt the echo statement for the links at the end?

Here's my query:

Code: Select all

$result = mysql_query("SELECT * FROM table WHERE Name LIKE '%$businesstype%' OR (Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%') LIMIT $id, $limit");
Any nuggets of wisdom would be much appreciated. I can't wait to be competent enough to give something back to this forum. It's helped me so much already.he end?

Here's my query:

Code: Select all

$result = mysql_query("SELECT * FROM table WHERE Name LIKE '%$businesstype%' OR (Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%') LIMIT $id, $limit");
Any nuggets of wisdom would be much appreciated. I can't wait to be competent enough to give something back to this forum. It's helpede been battling with pagination for days (very newbie) and charp's code works for me if i pare my query down to 'select * from table'. Trouble is my query contains a WHERE clause and whenever I click the 'next' link, the next page contains unfiltered data as if the query had just been SELECT * FROM table.

I'm guessing I somehow need to adapt the echo statement for the links at the end?

Here's my query:

Code: Select all

$result = mysql_query("SELECT * FROM table WHERE Name LIKE '%$businesstype%' OR (Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%') LIMIT $id, $limit");
Any nuggets of wisdom would be much appreciated. I can't wait to be competentlick the 'next' link, the next page contains unfiltered data as if the query had just been SELECT * FROM table.

I'm guessing I somehow need to adapt the echo statement for the links at the end?

Here's my query:

Code: Select all

$result = mysqbie) and charp's code works for me if i pare my query down to 'select * from table'. Trouble is my query contains a WHERE clause and whenever I click the 'next' link, the next page contains unfiltered data as if the query had just been SELECT * FROM table.

I'm guessing I somehow need to adapt the echo statement for the links at the end?

Here's my query:

Code: Select all

$result = mysql_query(&quote;SELECT * FROM table WHERE Name LIKE '%$businesstype%' OR (Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%') LIMIT $id, $limit&quote;);
Any nuggets of wisdom would be much appreciated. I can't wait to be competent enough to give something back to this forum. It's helped me so much already WHERE clause and whenever I click the 'next' link, the next page contains unfiltered data as if the query had just been SELECT * FROM table.

I'm guessing I somehow need to adapt the echo statement for the links at the end?

Here's my query:

Code: Select all

$result = mysql_query("SELECT * FROM table WHERE Name LIKE '%$businesstype%' OR (Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%') LIMIT $id, $limit");
Any nuggets of wisdom would be much appreciated. I can't wait to be competent enough to give something back to this forum. It's helped me so much already.e been battling with pagination for days (very newbie) and charp's code works for me if i pare my query down to 'select * from table'. Trouble is my query contains a WHERE clause and whenever I click the 'next' link, the next page contains unfiltered data as if the query had just been SELECT * FROM table.

I'm guessing I somehow need to adapt the echo statement for the links at the end?

Here's my query:

Code: Select all

$result = mysql_query("SELECT * FROM table WHERE Name LIKE '%$businesstype%' OR (Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%') LIMIT $id, $limit");
Ase and whenever I click the 'next' link, the next page contains unfiltered data as if the query had just been SELECT * FROM table.

I'm guessing I somehow need to adapt the echo statement for the links at the end?

Here's my query:

Code: Select all

$result = mysql_query("SELECT * FROM table WHERE Name LIKE '%$businesstype%' OR (Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%') LIMIT $id, $limit");
Any nuggets of wisdom would be much appreciated. I can't wait to be competent enough to give something back to this forum. It's helped me so much already.is my query contains a WHERE clause and whenever I click the 'next' link, the next page contains unfiltered data as if the query had just been SELECT * FROM table.

I'm guessing I somehow need to adapt the echo statement for the links at the end?

Here's my query:

Code: Select all

$result = mysql_query("SELECT * FROM table WHERE Name LIKE '%$businesstype%' OR (Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%') LIMIT $id, $limit");
Any nuggets of wisdom would be much appreciated. I can't wait to be competent enough to give something back to this forum. It's helped me so much already. $id, $limit");


Any nuggets of wisdom would be much appreciated. I can't wait to be competent enough to give something back to this forum. It's helped me so much already.

Here's my query:

Code: Select all

$result = mysql_query("SELECT * FROM table WHERE Name LIKE '%$businesstype%' OR (Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%') LIMIT $id, $limit");
Any nuggets of wisdom would be much appreciated. I can't wait to be competent enough to give something back to this forum. It's helped me so much already.s a WHERE clause and whenever I click the 'next' link, the next page contains unfiltered data as if the query had just been SELECT * FROM table.

I'm guessing I somehow need to adapt the echo statement for the links at the end?

Here's my query:

Code: Select all

$result = mysql_query("SELECT * FROM table WHERE Name LIKE '%$businesstype%' OR (Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%') LIMIT $id, $limit");
Any nuggets of wisdom would be much appreciated. I can't wait to be competent enough to give something back to this forum. It's helped me so much already.e been battling with pagination for days (very newbie) and charp's code works for me if i pare my query down to 'select * from table'. Trouble is my query contains a WHERE clause and whenever I click the 'next' link, the next page contains unfiltered data as if the query had just been SELECT * FROM table.

I'm guessing I somehow need to adapt the echo statement for the links at the end?

Here's my query:

Code: Select all

$result = mysql_query("SELECT * FROM table WHERE Name LIKE '%$businesstype%' OR (Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%') LIMIT $id, $limit");
Any nuggets of wisdom would be much appreciated. I can't wait to be competent enough to give something back to this forum. Itp's code works for me if i pare my query down to 'select * from table'. Trouble is my query contains a WHERE clause and whenever I click the 'next' link, the next page contains unfiltered data as if the query had just been SELECT * FROM table.

I'm guessing I somehow need to adapt the echo statement for the links at the end?

Here's my query:

Code: Select all

$result = mysql_querination for days (very newbie) and charp's code works for me if i pare my query down to 'select * from table'. Trouble is my query contains a WHERE clause and whenever I click the 'next' link, the next page contains unfiltered data as if the query had just been SELECT * FROM table.

I'm guessing I somehow need to adapt the echo statement for the links at the end?

Here's my query:

Code: Select all

$result = mysql_query("SELECT * FROM table WHERE Name LIKE '%$businesstype%' OR (Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%') LIMIT $id, $limit");
Any nuggets of wisdom would be much appreciated. I can't wait to be competent enough to give something back to this forum. It's helped me so much already.
$result = mysql_query("SELECT * FROM table WHERE Name LIKE '%$businesstype%' OR (Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%') LIMIT $id, $limit");


Any nuggets of wisdom would be much appreciated. I can't wait to be competent enough to give something back to this forum. It's helped me so much already.