Search + Pagination - Please see

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
emilcarlo
Forum Commoner
Posts: 43
Joined: Wed Jul 21, 2010 12:38 pm

Search + Pagination - Please see

Post by emilcarlo »

Hello, I have a problem with my code. The php code function is to allow users to search into the database and display database information in paginated form. My searching is working as well as the pagination. My only problem here is that the pages from search results displays up to 2 pages only even if supposedly it should display 3 or more pages. Here is my code:

Code: Select all

<?php

$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>";
}
 
?>
Can anyone identify what I am doing wrong? I am still newbie and still pushing myself to learn every bit of php. The code I have has been a trial and error and has been with the support of forum sites like phpdn.

Thank you in advanced. I hope to get your comments soon.
User avatar
Jonah Bron
DevNet Master
Posts: 2764
Joined: Thu Mar 15, 2007 6:28 pm
Location: Redding, California

Re: Search + Pagination - Please see

Post by Jonah Bron »

Do you mean it only shows a limited number of little page number links? These:

Prev 1 2 Next

And it's not showing enough of them?
emilcarlo
Forum Commoner
Posts: 43
Joined: Wed Jul 21, 2010 12:38 pm

Re: Search + Pagination - Please see

Post by emilcarlo »

Hi Jonah, thanks for the reply ^^

No, it only shows

1 Next

then if I click Next, it displays

Prev 1
User avatar
Jonah Bron
DevNet Master
Posts: 2764
Joined: Thu Mar 15, 2007 6:28 pm
Location: Redding, California

Re: Search + Pagination - Please see

Post by Jonah Bron »

Lets start debugging. Try echoing $record_count; what does it contain?
emilcarlo
Forum Commoner
Posts: 43
Joined: Wed Jul 21, 2010 12:38 pm

Re: Search + Pagination - Please see

Post by emilcarlo »

The result for echo $record_count; is 58 - it is the number of information contained on my database

edit:

sorry it was 15 ^^
User avatar
Jonah Bron
DevNet Master
Posts: 2764
Joined: Thu Mar 15, 2007 6:28 pm
Location: Redding, California

Re: Search + Pagination - Please see

Post by Jonah Bron »

Ok, the problem is that you are trying to get the result count from the query you ran, but that query is always restricted to 15 at the most. So, you need to remove the LIMIT statement from the query, and handle the start/limit manually with PHP. Just try removing the LIMIT and see what happens first.
emilcarlo
Forum Commoner
Posts: 43
Joined: Wed Jul 21, 2010 12:38 pm

Re: Search + Pagination - Please see

Post by emilcarlo »

Hey Jonah,

Yeah, I've been experimenting on that though I cannot really explain on how it happens. I tried what you suggested, I removed

Code: Select all

LIMIT $start, $per_page
and all the records were displayed, and I have 4 pages containing similar records.
User avatar
Jonah Bron
DevNet Master
Posts: 2764
Joined: Thu Mar 15, 2007 6:28 pm
Location: Redding, California

Re: Search + Pagination - Please see

Post by Jonah Bron »

emilcarlo wrote:...all the records were displayed, and I have 4 pages containing similar records.
Do you mean all of the search results, or everything in the DB table? What 4 pages, and similar in what way?
emilcarlo
Forum Commoner
Posts: 43
Joined: Wed Jul 21, 2010 12:38 pm

Re: Search + Pagination - Please see

Post by emilcarlo »

Everything in the database is displayed. It has 4 pages, with each page containing everything in the database.
User avatar
Jonah Bron
DevNet Master
Posts: 2764
Joined: Thu Mar 15, 2007 6:28 pm
Location: Redding, California

Re: Search + Pagination - Please see

Post by Jonah Bron »

There is probably a more efficient way of doing this (speak up if anyone knows one), but this should work: try it. I neatened you code a smidge :)

Code: Select all

<?php

$per_page = 15;

$start = isset($_GET['start']) && !empty($_GET['start']) ? $_GET['start'] : 0;
$search = isset($_GET['search']) && !empty($_GET['search']) ? $_GET['search'] : '';

$sql = <<<EOS
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
EOS;
 
$get = mysql_query($sql);
$record_count = mysql_num_rows($get);
$max_pages = ciel($record_count / $per_page);
$count = 0;

if ($get) {
     while ($row = mysql_fetch_assoc($get) && $count < ($start + $per_page)) {
          if ($count < $start) {
               $count++;
               continue;
          } else {
               $count++;
          }
          $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 sprintf(
               '<tr>
     <td>%s</td>
     <td>%s</td>
     <td>%s, %s</td>
     <td><a href="edit_client.php?id=%s">edit</a> | <a href="delete_client.php?id=%s">delete</a></td>
</tr>',
              $territory,
              $employer,
              $last_name,
              $first_name,
              $id,
              $id);
     }
} 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>";
}
 
?>
User avatar
McInfo
DevNet Resident
Posts: 1532
Joined: Wed Apr 01, 2009 1:31 pm

Re: Search + Pagination - Please see

Post by McInfo »

There is no ciel() function. It's ceil().

I have a pagination example elsewhere on this forum.
emilcarlo
Forum Commoner
Posts: 43
Joined: Wed Jul 21, 2010 12:38 pm

Re: Search + Pagination - Please see

Post by emilcarlo »

McInfo wrote:There is no ciel() function. It's ceil().

I have a pagination example elsewhere on this forum.
Yup, I saw that ^^ Im gonna check your pagination, thanks McInfo!

For Jonah:

Hi Jonah, thank you for that :) Imma check it now since I wasn't able to check this forum (Weekend and Holiday). Thanks much!
emilcarlo
Forum Commoner
Posts: 43
Joined: Wed Jul 21, 2010 12:38 pm

Re: Search + Pagination - Please see

Post by emilcarlo »

Hi Jonah, the code didn't work.. Also, it has affected my modification link - it does does not get the id.
User avatar
Jonah Bron
DevNet Master
Posts: 2764
Joined: Thu Mar 15, 2007 6:28 pm
Location: Redding, California

Re: Search + Pagination - Please see

Post by Jonah Bron »

What exactly doesn't work? Does it give an error? If you can, attach a screenshot of the page.
Post Reply