Search with Pagination

Question

emilcarlo 0 Newbie Poster

14 Years Ago

Hello,

I am currently working with a project that involves PhP and MySQL. I have a working code for adding, editing, and deleting of information. However, I was required to put up a paginating script in my code. I am actually new to PhP, about 3 weeks old xD, and most of the codes I used are through the help of forums and a programmer friend. I can somehow understand and comprehend PhP scripts now, but I have to admit, I still am the newbie I was 3 weeks ago. Anyway, I have been searching for paginating scripts for about 3 days now, and luckily, I found some. But to my dismay, after I tried integrating my code to the paginating script I found, my search engine began to malfunction. The pagination is fine, but my search engine is now a mess. Can anyone help me identify what I did wrong? Please note, I am still a beginner, and is actually currently reading like the 5th chapter of a dummy book. Any help will be appreciated. And critics will be well accepted, for the purpose of my learning. Thank you very much!

Here's my code before I integrated the pagination script:

<link href="add_client.css" rel="stylesheet" type="text/css">

<?PHP
include("dbconnection.php"); //database connection

$query = "SELECT * FROM records";

if(isset($_POST["btnSearch"])) //search query

{
		$query .= " WHERE last_name LIKE '%".$_POST["search"]."%' OR first_name LIKE '%".$_POST["search"]."%'OR territory LIKE '%".$_POST["search"]."%'OR job_title LIKE '%".$_POST["search"]."%'OR title LIKE '%".$_POST["search"]."%'OR employer LIKE '%".$_POST["search"]."%' ORDER BY territory ASC LIMIT 15"  ;
		
$result = mysql_query($query, $connection) or die(mysql_error());

} 

?>

<table width="760" border="0" align="center" cellpadding="0" cellspacing="0">
  <tr>
    <td><table width="760" border="0" cellpadding="0" cellspacing="0">
      <tr>
        <td width="199" align="center" valign="top"><a href="login.html"><img src="invent-asia.gif" alt="" width="152" height="58" border="0" /></a>        <script type="text/javascript" src="menu.js"></script></td>
        <td width="176" align="right" valign="bottom"><a href="main.php"><img src="Home.jpg" width="104" height="20" border="0"/></a></td>
        <td width="130" align="right" valign="bottom"><img src="View.jpg" width="104" height="20" border="0"/></td>
        <td width="146" align="right" valign="bottom"><a href="add_client.php"><img src="Add.jpg" width="104" height="20" border="0"/></a></td>
        <td width="109" align="right" valign="bottom">&nbsp;</td>
        </tr>

    </table></td>
  </tr>
  <tr>
    <td><table width="760" border="0" cellpadding="0" cellspacing="0">
      <tr>
        <td width="200" height="3"  bgcolor="#1B1C78"><img src="images/topspacerblue.gif" alt="" width="1" height="3" /></td>
        <td width="560"  bgcolor="#0076CC"><img src="images/topspacerlblue.gif" alt="" width="1" height="3" /></td>

      </tr>
    </table></td>

  </tr>
  <tr>
    <td height="553" align="center" valign="top" bgcolor="#F3FAFE"><br />

      <form name="form" action="view_client.php" method="post">
        <br />
      <table width="351" border="0">
          <tr>
            <td width="137" align="left" valign="middle">SEARCH RECORD:</td>
            <td width="144" align="center" valign="middle"><input type="text" name="search" /></td>
            <td width="56" align="left" valign="middle"><input type="submit" name="btnSearch" value="Search" /></td>
          </tr>
        </table>
        <br />
        <table border="0" cellpadding="3" cellspacing="1" bordercolor="38619E" >
          <tr>
            <th width="80" align="center" bgcolor="#E0E8F3">Territory</th>
            <th width="330" align="center" bgcolor="#E0E8F3">Employer</th>
             <th width="160" align="center" bgcolor="#E0E8F3">Name</th>
            <th width="80" align="center" valign="middle" bgcolor="#E0E8F3">&nbsp;</th>
          </tr>
 
 <?php
  if($result)
  {
	  	  
	  for($i=0; $i<mysql_num_rows($result); $i++)
	  {
		  $id = trim(mysql_result($result, $i, "id"));
		  $territory = trim(mysql_result($result, $i, "territory"));
		  $employer = trim(mysql_result($result, $i, "employer"));
		  $first_name = trim(mysql_result($result, $i, "first_name"));
		  $last_name = trim(mysql_result($result, $i, "last_name"));
				  
		  echo "<tr>";
  		  echo "<td>".$territory."</td>";
		  echo "<td>".$employer."</td>";
		  echo "<td>".$last_name.", ".$first_name."</td>";
		  echo "<td><a href='edit_client.php?id=".$id."'>edit</a> | <a href='delete_client.php?id=".$id."'>delete</a> </td>";
		  echo "</tr>";
	  }
	  
  }
  
  ?>

  
        </table>
        <p>
<br />
        </p>
      </form>
    <p>&nbsp;</p></td>

  </tr>
  <tr>
    <td height="38"><table width="760" border="0" cellpadding="0" cellspacing="0">
      <tr>
        <td width="200" height="35" align="center"  bgcolor="#1B1C78" class=white><img src="images/topspacerblue.gif" alt="" width="1" height="3" />&nbsp;<a href="disclaimer.html"><font color="#FFFFFF">Legal Disclaimer</font></a> </td>

        <td width="560" align="center"  bgcolor="#0076CC"  class=white><img src="images/topspacerlblue.gif" alt="" width="1" height="3" />&nbsp;Copyright &copy; 2006 - 2010 InventAsia Limited. All rights reserved.
</td>
      </tr>

    </table></td>
  </tr>
</table>

Here's the pagination script I used

<?php
// database connection info
$conn = mysql_connect('localhost','dbusername','dbpass') or trigger_error("SQL", E_USER_ERROR);
$db = mysql_select_db('dbname',$conn) or trigger_error("SQL", E_USER_ERROR);

// find out how many rows are in the table 
$sql = "SELECT COUNT(*) FROM numbers";
$result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR);
$r = mysql_fetch_row($result);
$numrows = $r[0];

// number of rows to show per page
$rowsperpage = 10;
// find out total pages
$totalpages = ceil($numrows / $rowsperpage);

// get the current page or set a default
if (isset($_GET['currentpage']) && is_numeric($_GET['currentpage'])) {
   // cast var as int
   $currentpage = (int) $_GET['currentpage'];
} else {
   // default page num
   $currentpage = 1;
} // end if

// if current page is greater than total pages...
if ($currentpage > $totalpages) {
   // set current page to last page
   $currentpage = $totalpages;
} // end if
// if current page is less than first page...
if ($currentpage < 1) {
   // set current page to first page
   $currentpage = 1;
} // end if

// the offset of the list, based on current page 
$offset = ($currentpage - 1) * $rowsperpage;

// get the info from the db 
$sql = "SELECT id, number FROM numbers LIMIT $offset, $rowsperpage";
$result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR);

// while there are rows to be fetched...
while ($list = mysql_fetch_assoc($result)) {
   // echo data
   echo $list['id'] . " : " . $list['number'] . "<br />";
} // end while

/******  build the pagination links ******/
// range of num links to show
$range = 3;

// if not on page 1, don't show back links
if ($currentpage > 1) {
   // show << link to go back to page 1
   echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=1'><<</a> ";
   // get previous page num
   $prevpage = $currentpage - 1;
   // show < link to go back to 1 page
   echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$prevpage'><</a> ";
} // end if 

// loop to show links to range of pages around current page
for ($x = ($currentpage - $range); $x < (($currentpage + $range) + 1); $x++) {
   // if it's a valid page number...
   if (($x > 0) && ($x <= $totalpages)) {
      // if we're on current page...
      if ($x == $currentpage) {
         // 'highlight' it but don't make a link
         echo " [<b>$x</b>] ";
      // if not current page...
      } else {
         // make it a link
         echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$x'>$x</a> ";
      } // end else
   } // end if 
} // end for
                 
// if not on last page, show forward and last page links        
if ($currentpage != $totalpages) {
   // get next page
   $nextpage = $currentpage + 1;
    // echo forward link for next page 
   echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$nextpage'>></a> ";
   // echo forward link for lastpage
   echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$totalpages'>>></a> ";
} // end if
/****** end build pagination links ******/
?>

And here's the final code when the pagination script has been integrated:

<link href="add_client.css" rel="stylesheet" type="text/css">

<table width="760" border="0" align="center" cellpadding="0" cellspacing="0">
  <tr>
    <td><table width="760" border="0" cellpadding="0" cellspacing="0">
      <tr>
        <td width="199" align="center" valign="top"><a href="login.html"><img src="invent-asia.gif" alt="" width="152" height="58" border="0" /></a>        <script type="text/javascript" src="menu.js"></script></td>
        <td width="176" align="right" valign="bottom"><a href="main.php"><img src="Home.jpg" width="104" height="20" border="0"/></a></td>
        <td width="130" align="right" valign="bottom"><img src="View.jpg" width="104" height="20" border="0"/></td>
        <td width="146" align="right" valign="bottom"><a href="add_client.php"><img src="Add.jpg" width="104" height="20" border="0"/></a></td>
        <td width="109" align="right" valign="bottom">&nbsp;</td>
        </tr>

    </table></td>
  </tr>
  <tr>
    <td><table width="760" border="0" cellpadding="0" cellspacing="0">
      <tr>
        <td width="200" height="3"  bgcolor="#1B1C78"><img src="images/topspacerblue.gif" alt="" width="1" height="3" /></td>
        <td width="560"  bgcolor="#0076CC"><img src="images/topspacerlblue.gif" alt="" width="1" height="3" /></td>

      </tr>
    </table></td>

  </tr>
  <tr>
    <td height="553" align="center" valign="top" bgcolor="#F3FAFE"><br />

      <form name="form" action="view_client_3.php" method="post">
        <br />
        <table width="351" border="0">
          <tr>
            <td width="137" align="left" valign="middle">SEARCH RECORD:</td>
            <td width="144" align="center" valign="middle"><input type="text" name="search" /></td>
            <td width="56" align="left" valign="middle"><input type="submit" name="btnSearch" value="Search" /></td>
          </tr>
        </table>
        <br />
        <table border="0" cellpadding="3" cellspacing="1" bordercolor="38619E" >
          <tr>
            <th width="80" align="center" bgcolor="#E0E8F3">Territory</th>
            <th width="330" align="center" bgcolor="#E0E8F3">Employer</th>
             <th width="160" align="center" bgcolor="#E0E8F3">Name</th>
            <th width="80" align="center" valign="middle" bgcolor="#E0E8F3">&nbsp;</th>
          </tr>
<?php

$conn = mysql_connect('localhost','root','') or trigger_error("SQL", E_USER_ERROR);
$db = mysql_select_db('invent-asia',$conn) or trigger_error("SQL", E_USER_ERROR);

$sql = "SELECT COUNT(*) FROM records";
$result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR);
$r = mysql_fetch_row($result);
$numrows = $r[0];
$rowsperpage = 15;
$totalpages = ceil($numrows / $rowsperpage);

if (isset($_GET['currentpage']) && is_numeric($_GET['currentpage'])) {
   $currentpage = (int) $_GET['currentpage'];
} else {
   $currentpage = 1;
}
if ($currentpage > $totalpages) {
   $currentpage = $totalpages;
}
if ($currentpage < 1) {
   $currentpage = 1;
} 

$offset = ($currentpage - 1) * $rowsperpage;
$sql = "SELECT * FROM records LIMIT $offset, $rowsperpage";
$result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR);


  for($i=0; $i<mysql_num_rows($result); $i++)
  {
  $id = trim(mysql_result($result, $i, "id"));
  $territory = trim(mysql_result($result, $i, "territory"));
  $employer = trim(mysql_result($result, $i, "employer"));
  $first_name = trim(mysql_result($result, $i, "first_name"));
  $last_name = trim(mysql_result($result, $i, "last_name"));

  echo "<td>".$territory."</td>";
  echo "<td>".$employer."</td>";
  echo "<td>".$last_name.", ".$first_name."</td>";
  echo "<td><a href='edit_client.php?id=".$id."'>edit</a> | <a href='delete_client.php?id=".$id."'>delete</a> </td>";
  echo "</tr>";
  
  }


$range = 3;


if ($currentpage > 1) {
   echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=1'><<</a> ";
   $prevpage = $currentpage - 1;
   echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$prevpage'><</a> ";
} 


for ($x = ($currentpage - $range); $x < (($currentpage + $range) + 1); $x++) {
   if (($x > 0) && ($x <= $totalpages)) {
      if ($x == $currentpage) {
         echo " [<b>$x</b>] ";
      } else {
         echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$x'>$x</a> ";
      } 
   }
} 
                 
       
if ($currentpage != $totalpages) {
   $nextpage = $currentpage + 1;
  
   echo "<a href='{$_SERVER['PHP_SELF']}?currentpage=$nextpage'>></a>";    
   echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$totalpages'>>></a> ";
}

?>
        </table>
        <p>
<br />
        </p>
      </form>
    <p>&nbsp;</p></td>

  </tr>
  <tr>
    <td height="38"><table width="760" border="0" cellpadding="0" cellspacing="0">
      <tr>
        <td width="200" height="35" align="center"  bgcolor="#1B1C78" class=white><img src="images/topspacerblue.gif" alt="" width="1" height="3" />&nbsp;<a href="disclaimer.html"><font color="#FFFFFF">Legal Disclaimer</font></a> </td>

        <td width="560" align="center"  bgcolor="#0076CC"  class=white><img src="images/topspacerlblue.gif" alt="" width="1" height="3" />&nbsp;Copyright &copy; 2006 - 2010 InventAsia Limited. All rights reserved.
</td>
      </tr>

    </table></td>
  </tr>
</table>

Thank you very much ^^

php

3 Contributors
2 Replies
158 Views
6 Days Discussion Span
Latest Post 14 Years Ago Latest Post by FlashCreations

chintan@dani 0 Light Poster

14 Years Ago

Hello emilcarlo,
I have read your post & code looks difficult for newbies.
the logic is correct.
I have simple looking pagination script.
Go to the link below there you will get an idea.(sign)

Thanks & regards,
Chintan

Edited 14 Years Ago by chintan@dani because: n/a

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

FlashCreations 20 Posting Whiz · Answer 1 · 2010-08-26T03:25:24+00:00

A few years back I made a simple Pagination Class w/ documentation. I'm not trying to pedal my script, but if want to take a look you can find it on my site.