[solved]pagination

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
zoe
Forum Commoner
Posts: 36
Joined: Sat Jun 11, 2005 11:51 am
Location: Glasgow

[solved]pagination

Post by zoe »

I'm trying to use LIMIT to restrict the results of my query to 3 per page with 'prev' and 'next' links. I've found a few examples of other people's code that should work, but the problem is that I don't really understand what I'm copying. I can easily limit my results to 3 records, but I can't figure out where the information comes from to control the pagination.

The PHP that is confusing me is the following, where 'page' is the first value after LIMIT. I don't understand how to retrieve the variable 'page'.

Code: Select all

if ($_GET[‘page’]==NULL){
$start = 0;
} else {
$start = $_GET[‘page’];
}
Thanks in advance
Last edited by zoe on Mon Jul 18, 2005 1:34 am, edited 1 time in total.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

We have a look at the mysql docs

http://dev.mysql.com/doc/mysql/en/select.html
LIMIT {[offset,] row_count | row_count OFFSET offset}

So the function accepts 2 paramters: row_count and offset.

row_count: the number of rows
offset: how many rows removed from the base

base [0]
[1]
[2]
x [3]

So, x = base + 3, thus offset is 3.

-------------------------------------------------------------------------------

Now, let's use this info. For example, we have 6 rows and want to show 2 rows per page.

page1 [row0] base
[row1]
page2 [row2] base +2
[row3]
page3 [row4] base +4
[row5]

So if we want to show a page, we need to calculate the offset:
page1 -> offset = 0
page2 -> offset = 2
page3 -> offset = 4

In general we can say:
pageN -> offset = (pagenumber -1) * row_count

(a little test: page1 -> (1 - 1) * 2 = 0)


So, for pages with 3 rows, page17 would have the following LIMIT clause:

rowcount = 3
offset = (17 - 1) * 3 = 48

LIMIT 48, 3
zoe
Forum Commoner
Posts: 36
Joined: Sat Jun 11, 2005 11:51 am
Location: Glasgow

Post by zoe »

thanks for that. I pretty much understand how the function works. What I don't get is how to pass information about which records to display from page to page. Also, how to construct the anchor tags in the 'prev' and 'next' links. I'm sure I'm missing something very obvious here (usually the case).
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

With most pagination scripts you usually see the following

<a href="pagination.php?page=3">previous</a>
<a href="pagination.php?page=5">next</a>

So if someone follows that link, he end up dat pagination.php?page=3
And pagination.php will have a variable $_GET['page'] that has the value 3





(Other ways to pass data: $_POST, $_COOKIE, $_SESSION)
zoe
Forum Commoner
Posts: 36
Joined: Sat Jun 11, 2005 11:51 am
Location: Glasgow

Post by zoe »

Thanks Tim,
It's not actually working yet, but the logic's slowly sinking in. Guess things will eventually come clear. This is the problem with approaching this end of web design from a design background.
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post by onion2k »

http://www.ooer.com/index.php?section=php&id=10

I've got an article for everything!

(Almost)
zoe
Forum Commoner
Posts: 36
Joined: Sat Jun 11, 2005 11:51 am
Location: Glasgow

Post by zoe »

Thanks, that's probably the best article I've seen yet (and I've found a few), but I can't make it work with my example. I'll post my code here, but I'm afraid there's a hell of a lot of it. I'd be extremely grateful, and quite shocked if anyone can be bothered filtering through it.

Code: Select all

$limit = 3;
$page = ($_GET['page'] > 0) ? $_GET['page'] : 1;

if ($village == 'G63') {
$sql= "SELECT count(*) as total FROM table WHERE Name LIKE '%$businesstype%' OR (Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%')";
}
else{
$sql= "SELECT count(*) as total FROM table WHERE Village = '$village' AND (Name LIKE '%$businesstype%' OR Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%')"; 
}
$result= mysql_query($sql);
$record = mysql_fetch_object($result);
$total = $record->total;
$num_pages = ceil($total/$limit);
$page = ($page <= 0) ? 1 : $page;
$page = ($page > $num_pages) ? $num_pages : $page;
$html[] = "<a href=\"Results3.php?page=" .($page-1)."\">Prev</a>";
for ($x=1; $x<=$num_pages; $x++) {
	$html[] = "<a href=\"Results3.php?page=".$x."\".$x</a>";
}
$html[] = "<a href=\"Results3.php?page=".($page+1)."\">Next</a>";
echo implode(" | ",$html);

if ($village == 'G63') {
$sql= "SELECT * FROM table WHERE Name LIKE '%$businesstype%' OR (Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%') LIMIT ".(($page-1)*$limit).",".limit;
}
else {
$sql= "SELECT * FROM table WHERE Village = '$village' AND (Name LIKE '%$businesstype%' OR Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%') LIMIT ".(($page-1)*$limit).",".limit; 
}

//What seems to be happening is that the following statement
//is always returning 0.

if (mysql_num_rows($sql)==0) {
echo "<p>There are currently no businesses in the directory that match your search.</p>
	  <p>Tip: Try using broader search terms; eg. 'farm', rather than 'farmers'</p>";
}
else{

while ($row = mysql_fetch_row($sql)) {
		   print "<p><b>$row[1]</b><br/>\n
		   		  $row[8]<br/>\n
				  $row[2], $row[3]<br/>\n
				  $row[4]<br/>\n 
				  $row[5]<br/>\n
				  phone: $row[6]<br/>\n
				  fax: $row[7]<br/>\n
				  email: <a href=\"mailto:$row[9]\">$row[9]</a><br/>\n
				  web: <a href=\"http://$row[10]\" target=\"_blank\">$row[10]</a><br/>\n
				  &nbsp;</p>";
}
}
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

You might want to output the SQL first... before you execute it..

At first sight: Where do $businesstype and $village come from?
zoe
Forum Commoner
Posts: 36
Joined: Sat Jun 11, 2005 11:51 am
Location: Glasgow

Post by zoe »

sorry, forgot ...

Code: Select all

$businesstype= ($_GET['businesstype']);
$village= ($_GET['village']);
...are stated just before $limit, and come from a form on the same page.
zoe
Forum Commoner
Posts: 36
Joined: Sat Jun 11, 2005 11:51 am
Location: Glasgow

pagination

Post by zoe »

Still battling with this one, but i did find code in another post by charp that 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 charp's code:

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>';
//i'm guessing the above is what i need to alter?
} 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>';
}
?>
And 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");
Would be eternally indebted for any nuggets of wisdom here. I've been stuck on this for days! in another post by charp that 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 charp's code:

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>';
//i'm guessing the above is what i need to alter?
} 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>';
}
?>
And here's my query:

Code: Select all

$result = mysql_query(&quote;SELECT * FROM table WHERE Name LIKE '%$businesstype%' OR (Type LIKE '%$b 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 charp's code:

Code: Select all

&lt;?php
// Connect to database
$databasename= &quote;XXX&quote;;
$username = &quote;XXX&quote;;
$table = &quote;XXX&quote;;
$password = &quote;XXX&quote;;
$db = mysql_connect(&quote;$host&quote;, &quote;$username&quote;, &quote;$password&quote;);
mysql_select_db(&quote;$databasename&quote;, $db);
$num_rows = mysql_num_rows(mysql_query(&quote;SELECT * FROM $table&quote;));
 
$limit = 10; // this many per page
$id=$_GET&#1111;'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(&quote;SELECT * FROM $table LIMIT $id, $limit&quote;);
 
echo '&lt;table border=&quote;1&quote;&gt;'.&quote;\n&quote;;
 
while($row=mysql_fetch_array($results)) {
  $record=$row&#1111;'id'];
  $address=$row&#1111;&quote;address&quote;];
  $city=$row&#1111;&quote;city&quote;];
 
echo '&lt;tr&gt;&lt;td&gt;'.$record.'&lt;/td&gt;&lt;td&gt;'.$address.'&lt;/td&gt;&lt;td&gt;'.$city.'&lt;/td&gt;&lt;/tr&gt;'.&quote;\n&quote;;
}
 
echo '&lt;/table&gt;';
 
// show previous link
if ($id&gt;=$limit) {
  $prev=$id-$limit;
  echo '&lt;a href=&quote;'.$_SERVER&#1111;'PHP_SELF'].'?id='.$prev.'&quote;&gt;prev&lt;/a&gt;';
//i'm guessing the above is what i need to alter?
} else {
  echo '&lt;span style=&quote;color: #999999;&quote;&gt;prev&lt;/span&gt;';
}
 
echo '&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;';
 
// show next link
if ($id&lt;($num_rows - $limit)) {
$next=$id+$limit;
  echo '&lt;a href=&quote;'.$_SERVER&#1111;'PHP_SELF'].'?id='.$next.'&quote;&gt;next&lt;/a&gt;';
} else {
  echo '&lt;span style=&quote;color: #999999;&quote;&gt;next&lt;/span&gt;';
}
?&gt;
And here's my query:

Code: Select all

$re 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 charp's code:

Code: Select all

&lt;?php
// Connect to database
$databasename= &quote;XXX&quote;;
$username = &quote;XXX&quote;;
$table = &quote;XXX&quote;;
$password = &quote;XXX&quote;;
$db = mysql_connect(&quote;$host&quote;, &quote;$username&quote;, &quote;$password&quote;);
mysql_select_db(&quote;$databasename&quote;, $db);
$num_rows = mysql_num_rows(mysql_query(&quote;SELECT * FROM $table&quote;));
 
$limit = 10; // this many per page
$id=$_GET&#1111;'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(&quote;SELECT * FROM $table LIMIT $id, $limit&quote;);
 
echo '&lt;table border=&quote;1&quote;&gt;'.&quote;\n&quote;;
 
while($row=mysql_fetch_array($results)) {
  $record=$row&#1111;'id'];
  $address=$row&#1111;&quote;address&quote;];
  $city=$row&#1111;&quote;city&quote;];
 
echo '&lt;tr&gt;&lt;td&gt;'.$record.'&lt;/td&gt;&lt;td&gt;'.$address.'&lt;/td&gt;&lt;td&gt;'.$city.'&lt;/td&gt;&lt;/tr&gt;'.&quote;\n&quote;;
}
 
echo '&lt;/table&gt;';
 
// show previous link
if ($id&gt;=$limit) {
  $prev=$id-$limit;
  echo '&lt;a href=&quote;'.$_SERVER&#1111;'PHP_SELF'].'?id='.$prev.'&quote;&gt;prev&lt;/a&gt;';
//i'm guessing the above is what i need to alter?
} else {
  echo '&lt;span style=&quote;color: #999999;&quote;&gt;prev&lt;/span&gt;';
}
 
echo '&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;';
 
// show next link
if ($id&lt;($num_rows - $limit)) {
$next=$id+$limit;
  echo '&lt;a href=&quote;'.$_SERVER&#1111;'PHP_SELF'].'?id='.$next.'&quote;&gt;next&lt;/a&gt;';
} else {
  echo '&lt;span style=&quote;color: #999999;&quote;&gt;next&lt;/span&gt;';
}
?&gt;
And here's my query:

Code: Select all

$result = mysql_query(&quote;SELECT * FROMelect * 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 charp's code:

Code: Select all

<?php
// Connect to database
$databasename= "XXX";
$username = "XXX";
$table = "XXX";
$password = "XXX";
$db = mysql_connect("$host&quote;, &quote;$username&quote;, &quote;$password&quote;);
mysql_select_db(&quote;$databasename&quote;, $db);
$num_rows = mysql_num_rows(mysql_query(&quote;SELECT * FROM $table&quote;));
 
$limit = 10; // this many per page
$id=$_GET&#1111;'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(&quote;SELECT * FROM $table LIMIT $id, $limit&quote;);
 
echo '&lt;table border=&quote;1&quote;&gt;'.&quote;\n&quote;;
 
while($row=mysql_fetch_array($results)) {
  $record=$row&#1111;'id'];
  $address=$row&#1111;&quote;address&quote;];
  $city=$row&#1111;&quote;city&quote;];
 
echo '&lt;tr&gt;&lt;td&gt;'.$record.'&lt;/td&gt;&lt;td&gt;'.$address.'&lt;/td&gt;&lt;td&gt;'.$city.'&lt;/td&gt;&lt;/tr&gt;'.&quote;\n&quote;;
}
 
echo '&lt;/table&gt;';
 
// show previous link
if ($id&gt;=$limit) {
  $prev=$id-$limit;
  echo '&lt;a href=&quote;'.$_SERVER&#1111;'PHP_SELF'].'?id='.$prev.'&quote;&gt;prev&lt;/a&gt;';
//i'm guessing the above is what i need to alter?
} else {
  echo '&lt;span style=&quote;color: #999999;&quote;&gt;prev&lt;/span&gt;';
}
 
echo '&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;';
 
// show next link
if ($id&lt;($num_rows - $limit)) {h this one, but i did find code in another post by charp that 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 charp's code:

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>';
//i'm guessing the above is what i need to alter?
} 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>';
}
?>
And 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");
Would be eternally indebted for ah this one, but i did find code in another post by charp that 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 charp's code:

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>';
//i'm guessing the above is what i need to alter?
} else {
  echo '<span s;quote;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>';
//i'm guessing the above is what i need to alter?
} 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>';
}
?>
And 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");
Would be eternally indebted for any nuggets of wisdom here. I've been stuck on this for days!("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ї&quote;address&quote;];
$city=$rowї&quote;city&quote;];

echo '&lt;tr&gt;&lt;td&gt;'.$record.'&lt;/td&gt;&lt;td&gt;'.$address.'&lt;/td&gt;&lt;td&gt;'.$city.'&lt;/td&gt;&lt;/tr&gt;'.&quote;\n&quote;;
}

echo '&lt;/table&r 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 charp's code:

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&quote;, $db);
$num_rows = mysql_num_rows(mysql_query(&quote;SELECT * FROM $table&quote;));
 
$limit = 10; // this many per page
$id=$_GET&#1111;'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(&quote;SELECT * FROM $table LIMIT $id, $limit&quote;);
 
echo '&lt;table border=&quote;1&quote;&gt;'.&quote;\n&quote;;
 
while($row=mysql_fetch_array($results)) {
  $record=$row&#1111;'id'];
  $address=$row&#1111;&quote;address&quote;];
  $city=$row&#1111;&quote;city&quote;];
 
echo '&lt;tr&gt;&lt;td&gt;'.$record.'&lt;/td&gt;&lt;td&gt;'.$address.'&lt;/td&gt;&lt;td&gt;'.$city.'&lt;/td&gt;&lt;/tr&gt;'.&quote;\n&quote;;
}
 
echo '&lt;/table&gt;';
 
// show previous link
if ($id&gt;=$limit) {
  $prev=$id-$limit;
  echo '&lt;a href=&quote;'.$_SERVER&#1111;'PHP_SELF'].'?id='.$prev.'&quote;&gt;prev&lt;/a&gt;';
//i'm guessing the above is what i need to alter?
} else {
  echo '&lt;span style=&quote;color: #999999;&quote;&gt;prev&lt;/span&gt;';
}
 
echo '&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;';
 
// show next link
if ($id&l 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 charp's code:

Code: Select all

<?php
// Connect to database
$databasename= &quote;XXX&quote;;
$username = &quote;XXX&quote;;
$table = &quote;XXX&quote;;
$password = &quote;XXX&quote;;
$db = mysql_connect(&quote;$host&quote;, &quote;$username&quote;, &quote;$password&quote;);
mysql_select_db(&quote;$databasename&quote;, $db);
$num_rows = mysql_num_rows(mysql_query(&quote;SELECT * FROM $table&quote;));
 
$limit = 10; // this many per page
$id=$_GET&#1111;'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(&quote;SELECT * FROM $table LIMIT $id, $limit&quote;);
 
echo '&lt;table border=&quote;1&quote;&gt;'.&quote;\n&quote;;
 
while($row=mysql_fetch_array($results)) {
  $record=$row&#1111;'id'];
  $address=$row&#1111;&quote;address&quote;];
  $city=$row&#1111;&quote;city&quote;];
 
echo '&lt;tr&gt;&lt;td&gt;'.$record.'&lt;/td&gt;&lt;td&gt;'.$address.'&lt;/td&gt;&lt;td&gt;'.$city.'&lt;/td&gt;&lt;/tr&gt;'.&quote;\n&quote;;
}
 
echo '&lt;/table&gt;';
 
// show previous link
if ($id&gt;=$limit) {
  $prev=$id-$limit;
  echo '&lt;a href=&quote;'.$_SERVER&#1111;'PHP_SELF'].'?id='.$prev.'&quote;&gt;prev&lt;/a&gt;';
//i'm guessing the above is what i need to alter?
} else {
  echo '&lt;span style=&quote;color: #999999;&quote;&gt;prev&lt;/span&gt;';
}
 
echo '&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;';
 
// show next link
if ($id&lt;($num_rows - $limit)) {
$next=$id+$limit;
  echo '&lt;a href=&quote;'.$_SERVER&#1111;'PHP_SELF'].'?id='.$next.'&quote;&gt;next&lt;/a&gt;';
} else {
  echo '&lt;span style=&quote;color: #999999;&quother post by charp that 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 charp's code:

Code: Select all

<?php
// Connect to database
$databasename= &quote;XXX&quote;;
$username = &quote;XXX&quote;;
$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(&quote;SELECT * FROM $table LIMIT $id, $limit&quote;);
 
echo '&lt;table border=&quote;1&quote;&gt;'.&quote;\n&quote;;
 
while($row=mysql_fetch_array($results)) {
  $record=$row&#1111;'id'];
  $address=$row&#1111;&quote;address&quote;];
  $city=$row&#1111;&quote;city&quote;];
 
echo '&lt;tr&gt;&lt;td&gt;'.$record.'&lt;/td&gt;&lt;td&gt;'.$address.'&lt;/td&gt;&lt;td&gt;'.$city.'&lt;/td&gt;&lt;/tr&gt;'.&quote;\n&quote;;
}
 
echo '&lt;/table&gt;';
 
// show previous link
if ($id&gt;=$limit) {
  $prev=$id-$limit;
  echo '&lt;a href=&quote;'.$_SERVER&#1111;'PHP_SELF'].'?id='.$prev.'&quote;&gt;prev&lt;/a&gt;';
//i'm guessing the above is what i need to alter?
} else {
  echo '&amphe '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 charp's code:

Code: Select all

&lt;?php
// Connect to database
$databasename= &quote;XXX&quote;;
$username = &quote;XXX&quote;;
$table = &quote;XXX&quote;;
$password = &quote;XXX&quote;;
$db = mysql_connect(&quote;$host&quote;, &quote;$username&quote;, &quote;$password&quote;);
mysql_select_db(&quote;$databasename&quote;, $db);
$num_rows = mysql_num_rows(mysql_query(&quote;SELECT * FROM $table&quote;));
 
$limit = 10; // this many per page
$id=$_GET&#1111;'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(&quote;SELECT * FROM $table LIMIT $id, $limit&quote;);
 
echo '&lt;table border=&quote;1&quote;&gt;'.&quote;\n&quote;;
 
while($row=mysql_fetch_array($results)) {
  $record=$row&#1111;'id'];
  $address=$row&#1111;&quote;address&quote;];
  $city=$row&#1111;&quote;city&quote;];
 
echo '&lt;tr&gt;&lt;td&gt;'.$record.'&lt;/td&gt;&lt;td&gt;'.$address.'&lt;/td&gt;&lt;td&gt;'.$city.'&lt;/td&gt;&lt;/tr&gt;'.&quote;\n&quote;;
}
 
echo '&lt;/table&gt;';
 
// show previous link
if ($id&gt;=$limit) {
  $prev=$id-$limit;
  echo '&lt;a href=&quote;'.$_SERVER&#1111;'PHP_SELF'].'?id='.$prev.'&quote;&gt;prev&lt;/a&gt;';
//i'm guessing the above is what i need to alter?
} else {
  echo '&lt;span style=&quote;color: #999999;&quote;&gt;prev&lt;/span&gt;';
}
 
echo '&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;';
 
// show next link
if ($id&lt;($num_rows - $limit)) {
$next=$id+$limit;
  echo '&lt;a href=&quote;'.$_SERVER&#1111;'PHP_SELF'].'?id='.$next.'&quote;&gt;next&lt;/a&gt;';
} else {
  echo '&lt;span style=&quote;color: #999999;&quote;&gt;next&lt;/span&gt;';
}
?&gt;
And 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");
Would be eter for the links at the end?

Here's charp's code:

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&#1111;'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(&quote;SELECT * FROM $table LIMIT $id, $limit&quote;);
 
echo '&lt;table border=&quote;1&quote;&gt;'.&quote;\n&quote;;
 
while($row=mysql_fetch_array($results)) {
  $record=$row&#1111;'id'];
  $address=$row&#1111;&quote;address&quote;];
  $city=$row&#1111;&quote;city&quote;];
 
echo '&lt;tr&gt;&lt;td&gt;'.$record.'&lt;/td&gt;&lt;td&gt;'.$address.'&lt;/td&gt;&lt;td&gt;'.$city.'&lt;/td&gt;&lt;/tr&gt;'.&quote;\n&quote;;
}
 
echo '&lt;/table&gt;';
 
// show previous link
if ($id&gt;=$limit) {
  $prev=$id-$limit;
  echo '&lt;a href=&quote;'.$_SERVER&#1111;'PHP_SELF'].'?id='.$prev.'&quote;&gt;prev&lt;/a&gt;';
//i'm guessing the above is what i need to alter?
} else {
  echo '&lt;sput i did find code in another post by charp that 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 charp's code:

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>&ampthe 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 charp's code:

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>';
//i'm guessing the above is what i need to alter?
} 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>';
}
?>
And 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");
Would be eternally indebted for any nuggets of wisdom here. I've been d?

Here's charp's code:

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>';
//i'm guessing the above is what i need to alter?
} 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>';
}
?>
And 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");
Would be eternally indebted for any nuggets of wisdom here. I've been stuck on this for days!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>';
//i'm guessing the above is what i need to alter?
} 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>';
}
?>

And 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");
Would be eternally indnk, 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 charp's code:

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>';
//i'm guessing the above is what i need to alter?
} 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>';
}
?>
And here's my query:

Code: Select all

$result = mysql_query(&quote;SELECT * FROM table WHERE Name LIKE '%$businesstype%' OR (Type LIKE '%$buLECT * FROM table.

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

Here's charp's code:

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&#1111;'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(&quote;SELECT * FROM $table LIMIT $id, $limit&quote;);
 
echo '&lt;table border=&quote;1&quote;&gt;'.&quote;\n&quote;;
 
while($row=mysql_fetch_array($results)) {
  $record=$row&#1111;'id'];
  $address=$row&#1111;&quote;address&quote;];
  $city=$row&#1111;&quote;city&quote;];
 
echo '&lt;tr&gt;&lt;td&gt;'.$record.'&lt;/td&gt;&lt;td&gt;'.$address.'&lt;/td&gt;&lt;td&gt;'.$city.'&lt;/td&gt;&lt;/tr&gt;'.&quote;\n&quote;;
}
 
echo '&lt;/table&gt;';
 
// show previous link
if ($id&gt;=$limit) {
  $prev=$id-$limit;
  echo '&lt;a href=&quote;'.$_SERVER&#1111;'PHP_SELF'].'?id='.$prev.'&quote;&gt;prev&lt;/a&gt;';
//i'm guessing the above is what i need to alter?
} else {
  echo '&lt;span style=&quote;color: #999999;&quote;&gt;prev&lt;/span&gt;';
}
 
echo '&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;';
 
// show next link
if ($id&lt;($num_rows - $limit)) {
$next=$id+$limit;
  echo '&lt;a href=&quote;'.$_SERVER&#1111;'PHP_SELF'].'?id='.$next.'&quote;&gt;next&lt;/a&gt;';
} else {
  echo '&lt;span style=&quote;color: #999999;&quote;&gt;next&lt;/span&gt;';
}
?&gt;
[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 '&lt;table border=&quote;1&quote;&gt;'.&quote;\n&quote;;
 
while($row=mysql_fetch_array($results)) {
  $record=$row&#1111;'id'];
  $address=$row&#1111;&quote;address&quote;];
  $city=$row&#1111;&quote;city&quote;];
 
echo '&lt;tr&gt;&lt;td&gt;'.$record.'&lt;/td&gt;&lt;td&gt;'.$address.'&lt;/td&gt;&lt;td&gt;'.$city.'&lt;/td&gt;&lt;/tr&gt;'.&quote;\n&quote;;
}
 
echo '&lt;/table&gt;';
 
// show previouh this one, but i did find code in another post by charp that 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 charp's code:

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>';
//i'm guessing the above is what i need to alter?
} 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&g
<?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>';
//i'm guessing the above is what i need to alter?
} 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>';
}
?>
And 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");
Would be eternally indebted for any nuggets of wisdom here. I've been stuck on this for days!
zoe
Forum Commoner
Posts: 36
Joined: Sat Jun 11, 2005 11:51 am
Location: Glasgow

[solved]pagination

Post by zoe »

Got it: See 'WHERE clause in pagination' in 'PHP Code' section.
Post Reply