I have problem with page navigation.My problem is that am getting all the records in one page. $rowsPerPage = 10; I have 30 records, all are showed in one page...not the 10 records.Here is my code.

<?php
include ('conn.php');

// how many rows to show per page
$rowsPerPage = 10;

// by default we show first page
$pageNum = 1;

// if $_GET['page'] defined, use it as page number
if(isset($_GET['page']))
{
	$pageNum = $_GET['page'];
}


// counting the offset
$offset = ($pageNum - 1) * $rowsPerPage;

$q = "show tables";
$r = mysql_query($q);
//$searchresult = array();
$var = $_POST['keyword'] ;
  $trimmed = trim($var);
  
  // rows to return
$limit=10; 

// check for an empty string and display a message.
if ($trimmed == "")
  {
  echo "<p>Please enter a search...</p>";
  exit;
  }

// check for a search parameter
if (!isset($var))
  {
  echo "<p>We dont seem to have a search parameter!</p>";
  exit;
  }
  
 while($data = mysql_fetch_array($r)) {
	$table = $data[0]; // get the tablename
	$query = "select * from ".$table." where dispname like '%".$trimmed."%'";
    $result = mysql_query($query);	
	 while($data = mysql_fetch_array($result)) {
	 	//	$searchresult[] = $rows['name'];
         //$var =$rows['dispname'];
		  
 echo '  <tr> <td> <a href="'.$data['MedName'].'" >' . $data['dispname'].' </a> </td>';
		 echo'</tr>'; 
         	
	}  
}
//$query  = "SELECT val FROM randoms LIMIT $offset, $rowsPerPage";
//$result = mysql_query($query) or die('Error, query failed');

while(list($val) = mysql_fetch_array($result))
{
	echo "$val <br>";
}

echo '<br>';


$query   = "select * from ".$table." where dispname like '%".$trimmed."%'";
$result  = mysql_query($query) or die('Error, query failed');
$row     = mysql_fetch_array($result, MYSQL_ASSOC);
$numrows = $row['numrows'];

$maxPage = ceil($numrows/$rowsPerPage);

$self = $_SERVER['PHP_SELF'];




if ($pageNum > 1)
{
	$page = $pageNum - 1;
	$prev = " <a href=\"$self?page=$page\">[Prev]</a> ";
	
	$first = " <a href=\"$self?page=1\">[First Page]</a> ";
} 
else
{
	$prev  = ' [Prev] ';       // we're on page one, don't enable 'previous' link
	$first = ' [First Page] '; // nor 'first page' link
}

if ($pageNum < $maxPage)
{
	$page = $pageNum + 1;
	$next = " <a href=\"$self?page=$page\">[Next]</a> ";
	$last = " <a href=\"$self?page=$maxPage\">[Last Page]</a> ";
} 
else
{
	$next = ' [Next] ';      // we're on the last page, don't enable 'next' link
	$last = ' [Last Page] '; // nor 'last page' link
}


echo $first . $prev . " Showing page <strong>$pageNum</strong> of <strong>$maxPage</strong> pages " . $next . $last;


?>

I changed my code.Here is my new code:

<?php 
include ('conn.php'); 
// how many rows to show per page 
$rowsPerPage = 10; 
// by default we show first page 
$pageNum = 1;
// if $_GET['page'] defined, use it as page number 
if(isset($_GET['page'])) 
{ 
$pageNum = $_GET['page']; 
} 
// counting the offset 
$offset = ($pageNum - 1) * $rowsPerPage;
//$searchresult = array(); 
$var = $_REQUEST['keyword'] ; 
$trimmed = trim($var); 
// rows to return 


// check for an empty string and display a message. 
if ($trimmed == "") 
{ 
echo "<p>Please enter a search...</p>"; 
exit; 
} 

// check for a search parameter 
if (!isset($var)) 
{ 
echo "<p>We dont seem to have a search parameter!</p>"; 
exit; 
} 

//$table ="results"; 
$query ="select count(*) from medlist where dispname like '%".$trimmed."%'"; 
$rsid = mysql_query($query); 
if(!$rsid){die(mysql_error());}
$resArr = mysql_fetch_row($rsid); 
$numrows = $resArr[0]; 

//$query ="select * from medlist where dispname like '%".$trimmed."%' limit $strpos, $rowsPerPage"; 
$query = "select * from medlist where dispname like '%".$trimmed."%' LIMIT ". $start .", ". 
$rowsPerPage ;
$result = mysql_query($query); 
if(!$result){die(mysql_error());}
//$query = "SELECT val FROM randoms LIMIT $offset, $rowsPerPage"; 
//$result = mysql_query($query) or die('Error, query failed'); 

while($val = mysql_fetch_row($result)) 
{ 
print_r($val); 
} 

echo '<br>'; 


$maxPage = ceil($numrows/$rowsPerPage); 

$self = $_SERVER['PHP_SELF']; 
if ($pageNum > 1) 
{ 
$page = $pageNum - 1; 
$prev = " <a href=\"$self?page=$page&keyword=$var\">[Prev]</a> "; 

$first = " <a href=\"$self?page=1&keyword=$var\">[First Page]</a> "; 
} 
else 
{ 
$prev = ' [Prev] '; // we're on page one, don't enable 'previous' link 
$first = ' [First Page] '; // nor 'first page' link 
} 

if ($pageNum < $maxPage) 
{ 
$page = $pageNum + 1; 
$next = " <a href=\"$self?page=$page&keyword=$var\">[Next]</a> "; 
$last = " <a href=\"$self?page=$maxPage&keyword=$var\">[Last Page]</a> "; 
} 
else 
{ 
$next = ' [Next] '; // we're on the last page, don't enable 'next' link 
$last = ' [Last Page] '; // nor 'last page' link 
} 
echo $first . $prev . " Showing page <strong>$pageNum</strong> of <strong>$maxPage</strong> pages " . $next . $last; 
?>

Error occured is :
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 ' 10' at line 1.
This error is some where around this line:if(!$result){die(mysql_error());}
What is the solution?

Actually in your code you have to put $offset instead of $start in your query

check once

Actually I got this code from some where.I wanted to write my own code..Can anyone tell me how to start with that?

From the above code I am not getting any error but,the link to next page is not enabled.Even though $limit=10; & $rowsPerPage =10; both are declared as '10'.If I have 15 records all of them are displayed in the same page,but not in the 2 different pages.
Anyone to help regarding this??

Changed the code again.

<?php 
include ('conn.php');

// by default we show first page 
$page = 1; 

// if $_GET['page'] defined, use it as page number 
if(isset($_GET['page'])) 
{ 
$page = $_GET['page']; 
} 
// counting the offset 
$offset = ($page - 1) * $rowsPerPage; 


//$searchresult = array(); 
$var = $_POST['keyword'] ; 
$trimmed = trim($var); 

// rows to return 
$limit=10; 

$str = "select * from medlist where dispname like '%".$trimmed."%'"; 
echo $str.'<br>';
$res= mysql_query($str); 
if(!$res){die(mysql_error());}
$num_rows= mysql_num_rows($res); 
echo $num_rows.'<br>';

// how many rows to show per page 
$rowsPerPage =10; 
$maxPage = ceil($num_rows/$rowsPerPage);
//$page=(isset($_GET['page']))?$_GET['page']:1;
//$offset=($page-1)*$rowsPerPage;

$query = "select * from medlist where dispname like '%".$trimmed."%' limit $offset,$rowsPerPage";
$result = mysql_query($query);
//<a href=page.php?search=$string&offset=$offset>
if(!$result){die(mysql_error());}
while($data = mysql_fetch_array($result)) { 
    
    echo ' <tr> <td colspan="3" > <a href="'.$data['MedName'].'" >' . $data['dispname'].' </a> </td>'; 
    echo'</tr>'; 
}
echo "<tr><td align='left' colspan='2'>Result Pages:";
for($i=1;$i<=$maxPage;$i++)
 {
   
   echo "<a href=pagno.php?rowsPerPage=$rowsPerPage&page=$i>".$i."&nbsp;|&nbsp;";
 }   
echo "</td>
<td>Showing page <strong>".$page."</strong> of <strong>".$maxPage."</strong> pages </td>
</tr>";	

?>

In Page1 data displayed is from A1 to A10. Now the problem is that if I go to Page 2 data displayed must be from A11 to A12 but in this case it displaying the data where Id=11 in my db.
What is wrong with this code?

well, you are doing this

// counting the offset 
$offset = ($page - 1) * $rowsPerPage;

before you do this:

// how many rows to show per page 
$rowsPerPage =10;

i think it shoudl be other way around...

This is what i once used in my code:

$showfromthisid = ceil($pagenumber*10) -10;
$maxshownmessages = 10;

$result = mysql_query("SELECT * FROM table ORDER BY id DESC LIMIT $showfromthisid,$maxshownmessages") or die(mysql_error());

Changed the code again.

<?php 
include ('conn.php');

// by default we show first page 
$page = 1; 

// if $_GET['page'] defined, use it as page number 
if(isset($_GET['page'])) 
{ 
$page = $_GET['page']; 
} 
// counting the offset 
$offset = ($page - 1) * $rowsPerPage; 


//$searchresult = array(); 
$var = $_POST['keyword'] ; 
$trimmed = trim($var); 

// rows to return 
$limit=10; 

$str = "select * from medlist where dispname like '%".$trimmed."%'"; 
echo $str.'<br>';
$res= mysql_query($str); 
if(!$res){die(mysql_error());}
$num_rows= mysql_num_rows($res); 
echo $num_rows.'<br>';

// how many rows to show per page 
$rowsPerPage =10; 
$maxPage = ceil($num_rows/$rowsPerPage);
//$page=(isset($_GET['page']))?$_GET['page']:1;
//$offset=($page-1)*$rowsPerPage;

$query = "select * from medlist where dispname like '%".$trimmed."%' limit $offset,$rowsPerPage";
$result = mysql_query($query);
//<a href=page.php?search=$string&offset=$offset>
if(!$result){die(mysql_error());}
while($data = mysql_fetch_array($result)) { 
    
    echo ' <tr> <td colspan="3" > <a href="'.$data['MedName'].'" >' . $data['dispname'].' </a> </td>'; 
    echo'</tr>'; 
}
echo "<tr><td align='left' colspan='2'>Result Pages:";
for($i=1;$i<=$maxPage;$i++)
 {
   
   echo "<a href=pagno.php?rowsPerPage=$rowsPerPage&page=$i>".$i."&nbsp;|&nbsp;";
 }   
echo "</td>
<td>Showing page <strong>".$page."</strong> of <strong>".$maxPage."</strong> pages </td>
</tr>";	

?>

In Page1 data displayed is from A1 to A10. Now the problem is that if I go to Page 2 data displayed must be from A11 to A12 but in this case it displaying the data where Id=11 in my db.
What is wrong with this code?

I don't really understand the question here. What is the difference between A11 and Id=11?

Sam

Actually am using this for search.In my db I am storing few words A-Z but not in alphbetic order.so when i wanted to search 'A',then all data from my db related to 'A' must display.Here am able to display A1 to A10 in 1st page.In 2nd page it should start with A11 but it is displaying the data from my db whose Id=11.Am I clear to you now?

Actually am using this for search.In my db I am storing few words A-Z but not in alphbetic order.so when i wanted to search 'A',then all data from my db related to 'A' must display.Here am able to display A1 to A10 in 1st page.In 2nd page it should start with A11 but it is displaying the data from my db whose Id=11.Am I clear to you now?

I see, that makes a bit more sense now. So 11 could be K or something.. Well, I think your search is working perfectly (that I know of) but you're using LIKE %$trimmed%

Let's say that $trimmed is your search for A, it should return A1 - A10 then A11-A20 (or w/e) then if where id 11 dispname is TALC 'N' POWDER - it'll pick it up because it has an A in it. the %A% means anything before the A and anything after the A so TALC would get in but ZINC wouldn't. So if you just want to narrow your search to A--- on disp name.

Use this:

<?php

include ('conn.php');


// if $_GET['page'] defined, use it as page number 
if(isset($_GET['page'])) 
{ 
$page = $_GET['page']; 
} else { // if $_GET['page'] is not defined then $page == 1
$page = 1;
}
// counting the offset 


//$searchresult = array(); 
$var = $_POST['keyword'] ; 
$trimmed = mysql_real_escape_string(trim($var)); 

// rows to return 


$str = "SELECT * FROM `medlist` WHERE `dispname` LIKE '$trimmed%'"; 
echo $str.'<br>';
$res= mysql_query($str); 
if(!$res){die(mysql_error());}
$num_rows= mysql_num_rows($res); 
echo $num_rows.'<br>';

// how many rows to show per page 
$rowsPerPage =10; 
$maxPage = ceil($num_rows/$rowsPerPage);
//$page=(isset($_GET['page']))?$_GET['page']:1;
//$offset=($page-1)*$rowsPerPage;


if ($page < 1) {
    $page = 1;
} elseif ($page > $maxPage) {
    $page = $maxPage;
}

$max = 'LIMIT ' . ($page - 1) * $rowsPerPage . ',' . $rowsPerPage;

$query = "SELECT * FROM `medlist` WHERE `dispname` LIKE '$trimmed%' $max";
$result = mysql_query($query);
//<a href=page.php?search=$string&offset=$offset>
if(!$result){die(mysql_error());}
while($data = mysql_fetch_array($result)) { 
    
    echo ' <tr> <td colspan="3" > <a href="'.$data['MedName'].'" >' . $data['dispname'].' </a> </td>'; 
    echo'</tr>'; 
}
echo "<tr><td align='left' colspan='2'>Result Pages:";
for($i=1;$i<=$maxPage;$i++)
 {
   
   echo "<a href=pagno.php?rowsPerPage=$rowsPerPage&page=$i>".$i."&nbsp;|&nbsp;";
 }   
echo "</td>
<td>Showing page <strong>".$page."</strong> of <strong>".$maxPage."</strong> pages </td>
</tr>";	

 

?>

Does that help?

If there is no 'A' anywhere in the word also its displaying.I changed my code to the above one,but nothing has changed.same output.

Humm.....

Could you give me the structure of the table in your database?

Like

id INT Auto_Increment
dispname (VARCHAR)

or whatever it may be? That way I can have a play in my own db.

Sam

My table structure is:
Id bigint(20) Auto_Inc Not Null
MedName Varchar(60)
dispname Varchar(60)
alphabet Varcahr(15)

so what exactly was wrong with the code i typed up for you.

the reason you getting the problem like i told you earlier is that you are not sending the search term along with each link in the script. the $_POST which contains the term on the first page is reset once you click a link. making it not show the same results from before with the different limit.

here is my code i made for you:

(others, please check for errors. i haven't found any, but i like to miss little things.)

<?php

if (isset($_POST['submit'])) {
	$var = trim($_POST['keyword']);
	header('Location: pageno.php?keyword=' . $var);
	die();
}

?>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>PgNo</title>
<link href="links.css" rel="stylesheet" type="text/css" />
</head>
<body>
	<table border="1" width="100%">
		<tr>
			<td align="center">
				<form method="post" action="pagno.php">
					<input type="text" size="40%" id="keyword" name="keyword" />
					<input type="submit" name="submit" value="Search" />
				</form>
			</td>
		</tr>
		<tr> 
			<td class="imag" id="def">| <a href="home.html"> Home </a> | <a href="dc.php"> D & C </a>| <a href="st.php"> S & T</a> | <a href="medi.php"> Med</a> |<a href="hc.html"> HC</a> |<a href="news.html"> News </a>|</td>
		</tr>
	</table>
	<table border="1" align="center">
		<tr>
			<th align="left"> Search Results :</th>
		</tr>
		<tr>
			<td align="center">
<?php

//make sure you add your database connection here

//Set Rows Per Page
$rowsPage = 10;

if (isset($_GET['keyword'])) {
	$word = mysql_real_escape_string($_GET['keyword']);
	if ($word !== '') {
		if (isset($_GET['page'])) {
			$page = $_GET['page'];
		}
		else {
			$page = 1;
		}
		$start = ($rowsPage * $page) - $rowsPage;
		$sql   = "SELECT * FROM `medlist` WHERE `dispname` LIKE '%" . $word . "%'";
		$query = mysql_query($sql);
		$total = mysql_num_rows($query);
		$pages = ceil($total/$rowsPage);
		if ($pages > 1) {
			$limit = ' LIMIT ' . $start . ', ' . $rowsPage;
		}
		else {
			$limit = '';
		}
		$sql   = "SELECT * FROM `medlist` WHERE `dispname` LIKE '%" . $word . "%'" . $limit;
		$query = mysql_query($sql);
		echo '<table border="0" cellspacing="0" cellpadding="3">';
		while ($row = mysql_fetch_assoc($query)) {
			echo '<tr>';
			echo '<td><a href="' . $row['MedName'] . '">' . $row['dispname'] . '</a></td>';
			echo '</tr>';
		}
		echo '</table>';
		echo '<center>Pages:&nbsp;';
		$i = 1;
		while ($i < count($pages)) {
			echo '<a href="pagno.php?keyword=' . $word . '&page=' . $i . '">' . ($page == $i ? '<font color="#FF0000">' . $i . '</font>' : $i) . '</a>&nbsp;&nbsp;&nbsp;';
		$i++;
		}
		echo '</center>';
	}
}

?>
			</td>
		</tr>
	</table>
</body>
</html>

Hey Kkeith29 some problem with this code...I dont know what it is..but it is not dislpaying anything.

Sam,In the code we have I found the problem.We have 2 queries

$str = "SELECT * FROM `medlist` WHERE `dispname` LIKE '%".$trimmed."%'";
$query = "SELECT * FROM `medlist` WHERE `dispname` LIKE '%".$trimmed."%'".$max ;

When I print them,In 1st page they are printing :

SELECT * FROM `medlist` WHERE `dispname` LIKE '%a%'  &
SELECT * FROM `medlist` WHERE `dispname` LIKE '%a%' limit 0,10

In 2nd page they are printing

SELECT * FROM `medlist` WHERE `dispname` LIKE '%%'  &
SELECT * FROM `medlist` WHERE `dispname` LIKE '%%' limit 10,10

So the problem here is in 2nd page the keyword is not there so it is displaying Id 11 from db.I changed the a href tag which is in the for loop :

echo "<a href=pagno.php?rowsPerPage=$rowsPerPage&page=$i&trimmed=$trimmed&offset=$offset>".$i."&nbsp;|&nbsp;";

I have added keyword and offset values but even this doesnt bring any change in the output.
What can be done next?

finally i got sick of this stuff and tested it on my server.

i created the table and filled it with random values.

here is the test script url: http://www.banditssoftball.org/pagno.php

to find a result, type in a number into the search box.

i made a mistake on my last script i posted for you. i used count() for some reason, use to it i guess, when i was echoing out the pages.

ya its the same i need...did u use the same script which you posted for me?

yes, the one i typed for you (with minor changes).

here it is:

<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>PgNo</title>
<link href="links.css" rel="stylesheet" type="text/css" />
</head>
<body>
	<table border="1" width="100%">
		<tr>
			<td align="center">
				<form method="get" action="pagno.php">
					<input type="text" size="40%" id="keyword" name="keyword" />
					<input type="submit" value="Search" />
				</form>
			</td>
		</tr>
		<tr> 
			<td class="imag" id="def">| <a href="home.html"> Home </a> | <a href="dc.php"> D & C </a>| <a href="st.php"> S & T</a> | <a href="medi.php"> Med</a> |<a href="hc.html"> HC</a> |<a href="news.html"> News </a>|</td>
		</tr>
	</table>
	<table border="1" align="center">
		<tr>
			<th align="left"> Search Results :</th>
		</tr>
		<tr>
			<td align="center">
<?php

//Make sure you input your database connection here

//Set Rows Per Page
$rowsPage = 10;

if (isset($_GET['keyword'])) {
	$word = mysql_real_escape_string($_GET['keyword']);
	if ($word !== '') {
		if (isset($_GET['page'])) {
			$page = $_GET['page'];
		}
		else {
			$page = 1;
		}
		$start = ($rowsPage * $page) - $rowsPage;
		$sql   = "SELECT * FROM `medlist` WHERE `dispname` LIKE '%" . $word . "%'";
		$query = mysql_query($sql);
		$total = mysql_num_rows($query);
		$pages = ceil($total/$rowsPage);
		if ($pages > 1) {
			$limit = ' LIMIT ' . $start . ', ' . $rowsPage;
		}
		else {
			$limit = '';
		}
		$sql   = "SELECT * FROM `medlist` WHERE `dispname` LIKE '%" . $word . "%'" . $limit;
		$query = mysql_query($sql);
		echo '<table border="0" cellspacing="0" cellpadding="3">';
		while ($row = mysql_fetch_assoc($query)) {
			echo '<tr>';
			echo '<td><a href="' . $row['MedName'] . '">' . $row['dispname'] . '</a></td>';
			echo '</tr>';
		}
		echo '</table>';
		echo '<center>Pages:&nbsp;';
		$i = 1;
		while ($i <= $pages) {
			echo '<a href="pagno.php?keyword=' . $word . '&page=' . $i . '">' . ($page == $i ? '<font color="#FF0000">' . $i . '</font>' : $i) . '</a>&nbsp;&nbsp;&nbsp;';
		$i++;
		}
		echo '</center>';
	}
}

?>
			</td>
		</tr>
	</table>
</body>
</html>

I think some wrong with this :

<?php

if (isset($_POST['submit'])) {
	$var = trim($_POST['keyword']);
	header('Location: pageno.php?keyword='. $var);
	die();
	
}
?>

Because I tried to print $var but nothing is displayed.and in the code we need this keyword.As a result the final output is also empty... Its working for you but Why is it not working for ME :(

hmmm....Finally its working.Thanks a lot.
To get First ,Prev,Next,Last... what can i do?

i'll let you figure this one out.

heres what i will tell you.

Prev = $page - 1;
Next = $page + 1;
First = 1 (first page)
Last = $pages (total number of pages)

i think you will be able to add this to the code.

At last got the complete pagination along with prev & next also.Thanks to all of you who helped me.

This question has already been answered. Start a new discussion instead.