Jump to content

Search + Pagination

emilcarlo

By emilcarlo
in PHP Coding Help

 Share

Recommended Posts

emilcarlo Newbie

emilcarlo

Posted
Posted

So basically, what happens here now is that the search query and the pagination now somehow works with each other. Each time I search for a key word, it displays the information perfectly, with pagination. However, even if the keyword I looked for has only 2 information, a second page is available for click, and when I try to click, it leads me to a page of blank information. Similarly, if I search for an information that should display huge amount of data, it displays pagination, but displays no information if I click on the second page. What could the reason be behind this? Immediate response is very much appreciated. Thanks!

 

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

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

$query = "SELECT * FROM records";



?>

<!-- Start of table -->
<table width="760" border="0" align="center" cellpadding="0" cellspacing="0">
    <td>
      <table width="760" border="0" cellpadding="0" cellspacing="0">
       
            <!-- Table data containing the Logo -->
            <td width="199" align="center" valign="top">
                <a href="login.html">
                    <img src="asia.gif" alt="" width="152" height="58" border="0" />
                </a>
            </td>
            <!-- Table data containing Home button -->
            <td width="176" align="right" valign="bottom">
                <a href="main.php">
                    <img src="Home.jpg" width="104" height="20" border="0"/>
                </a>
            </td>
            <!-- Table data containing View Client button -->
            <td width="130" align="right" valign="bottom">
                <img src="View.jpg" width="104" height="20" border="0"/>
            </td>
            <!-- Table data containing the Add Client button -->
            <td width="146" align="right" valign="bottom">
                <a href="add_client.php">
                    <img src="Add.jpg" width="104" height="20" border="0"/>
                </a>
            </td>
            <!-- Blank table data -->
            <td width="109" align="right" valign="bottom"> 
               
            </td>
        </table>

        <!-- Table design division and body-->
        <table width="760" border="0" cellpadding="0" cellspacing="0">
            <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>
            <td height="500" colspan="2" align="center" valign="top"  bgcolor="#F3FAFE">
            <!-- Page contents -->
            <!-- Search Query -->
            <br>
            <form name="form" action="view_client.php" method="post">
            <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>
            <!-- End of search query-->
            <!-- Start of Search Results-->        
            <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"> </th>
                </tr>
<?php

//max displayed per page
$per_page = 15;

//get start variable
$start = $_GET['start'];

//count records
$record_count = mysql_num_rows(mysql_query("SELECT * FROM records"));

//count max pages
$max_pages = $record_count / $per_page; //may come out as decimal

if (!$start)
   $start = 0;
   
//display data via search query

if(isset($_POST["btnSearch"]))

{

$get = mysql_query("SELECT * FROM records 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 $start, $per_page");

while ($row = mysql_fetch_assoc($get)) // get data
{

                    $id = trim($row['id']);
                    $territory = trim($row['territory']);
                    $employer = trim($row['employer']);
                    $last_name = trim($row['last_name']);
                    $first_name = trim($row['first_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>";
}

//setup prev and next variables
$prev = $start - $per_page;
$next = $start + $per_page;

//show prev button
if (!($start<=0))
       echo "<a href='view_client.php?start=$prev'>Prev</a> ";

//show page numbers

//set variable for first page
$i=1;

for ($x=0;$x<$record_count;$x=$x+$per_page)
{
if ($start!=$x)
    echo " <a href='view_client.php?start=$x'>$i</a> ";
else
    echo " <a href='view_client.php?start=$x'><b>$i</b></a> ";
$i++;
}

//show next button
if (!($start>=$record_count-$per_page))
       echo " <a href='view_client.php?start=$next'>Next</a>";

}


?>

            </table>
            </form>
            <!-- End of page -->
            </td>
          </tr>
        </table>
    </td>
    <tr>
        <td height="38">
            <table width="760" border="0" cellpadding="0" cellspacing="0">      
                <td width="200" height="35" align="center"  bgcolor="#1B1C78" class=white>
                    <a href="disclaimer.html">
                        <font color="#FFFFFF">Legal Disclaimer</font>
                    </a>
                </td>
                <td width="560" align="center"  bgcolor="#0076CC">
                    Copyright © 2006 - 2010 Limited. All rights reserved.
                </td>
        </table></td>
    </tr>
</table>

Link to comment
Share on other sites
nethnet Member

nethnet

Posted
Posted

You are searching your database directly with the contents of your $_POST['search'] variable, which will ONLY contain information if a form was submitted from the prior page.  This works for page 1 of your queries (since the submit button was pushed), but when you click on page 2, 3, or anything else, the method is no longer POST and this will be empty.

 

On top of this, you are leaving your database very prone to SQL injection by putting the users search query directly into your SQL syntax.  You should always do some hack-prevention on user submitted input before querying your database with it.

 

Anyway, back to your issue.  You should format your links to additional pages (2, 3, etc) with the search query in the URL, and then use $_GET to retrieve it instead of $_POST.

Link to comment
Share on other sites
emilcarlo Newbie

emilcarlo

Posted
  • Author
Posted

Hello nethnet,

 

Thank you very much for your response. Honestly, I barely understood what you just said because I am just like about three weeks in doing php, and is still trying to learn the basics, but due to the project's need, I need to move a little advanced with the tutorials and pre-made codes available in web. If you won't mind at all, can you clarify on the things I need to do?

Link to comment
Share on other sites
nethnet Member

nethnet

Posted
Posted

Apologies; I will try to explain it in beginner's terms.  After taking a clearer look at your script, there are several errors that will produce things you don't want in your pagination.  Your code should look something more like this:

 

<?php

// set this to however many results you want per page
$per_page = 15;

if (!empty($_GET['start'])) {
     $start = $_GET['start'];
} else {
     $start = 0;
}

if (!empty($_GET['search'])) {
     $search = $_GET['search'];
} elseif (!empty($_POST['search'])) {
     $search = $_POST['search'];
} else {
     $search = "";
}

$sql = "SELECT * FROM `records` WHERE `last_name` LIKE '%$search%' OR `first_name` LIKE '%$search%' OR `territory` LIKE '%$search%' OR `job_title` LIKE '%$search%' OR `title` LIKE '%$search%' OR `employer` LIKE '%$search%' ORDER BY `territory` ASC LIMIT $start, $per_page";

$record_count = mysql_num_rows(mysql_query($sql));
$max_pages = ciel($record_count / $per_page);

$get = mysql_query($sql);

if ($get) {
     while ($row = mysql_fetch_assoc($get)) {
          $id = trim($row['id']);
          $territory = trim($row['territory']);
          $employer = trim($row['employer']);
          $last_name = trim($row['last_name']);
          $first_name = trim($row['first_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>";
     }
} else {
     echo "<em>No results found.</em>";
}

$prev = $start - $per_page;
$next = $start + $per_page;

if ($start > 0) {
     echo "<a href='view_client.php?search=$search&start=$prev'>Prev</a> ";
}

for ($x=0;$x<$max_pages;$x++) {
     $y = $x * $per_page;
     $z = $x + 1;
     if ($start !== $y) {
          echo " <a href='view_client.php?search=$search&start=$x'>$z</a> ";
     } else {
          echo " <b>$z</b> ";
     }
}

if ($record_count >= ($next)) {
       echo " <a href='view_client.php?search=$search&start=$next'>Next</a>";
}

?>

Link to comment
Share on other sites
emilcarlo Newbie

emilcarlo

Posted
  • Author
Posted

Hello nethnet,

 

The fixes you did worked ^^ The pagination and the search function is good now, but a new problem surfaced xD I am trying to understand and see where the problem is just like you guys. Anyway, here's the problem.

 

I have couple of data now in my database, but the pages do not go beyond 2nd page xD. The

Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.