Page 1 of 1

Search + Pagination - Please see

Posted: Fri Aug 27, 2010 12:38 pm
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.

Re: Search + Pagination - Please see

Posted: Fri Aug 27, 2010 12:43 pm
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?

Re: Search + Pagination - Please see

Posted: Fri Aug 27, 2010 12:55 pm
by emilcarlo
Hi Jonah, thanks for the reply ^^

No, it only shows

1 Next

then if I click Next, it displays

Prev 1

Re: Search + Pagination - Please see

Posted: Fri Aug 27, 2010 1:23 pm
by Jonah Bron
Lets start debugging. Try echoing $record_count; what does it contain?

Re: Search + Pagination - Please see

Posted: Fri Aug 27, 2010 1:29 pm
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 ^^

Re: Search + Pagination - Please see

Posted: Fri Aug 27, 2010 3:27 pm
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.

Re: Search + Pagination - Please see

Posted: Fri Aug 27, 2010 3:31 pm
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.

Re: Search + Pagination - Please see

Posted: Fri Aug 27, 2010 4:01 pm
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?

Re: Search + Pagination - Please see

Posted: Fri Aug 27, 2010 4:05 pm
by emilcarlo
Everything in the database is displayed. It has 4 pages, with each page containing everything in the database.

Re: Search + Pagination - Please see

Posted: Fri Aug 27, 2010 6:16 pm
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>";
}
 
?>

Re: Search + Pagination - Please see

Posted: Sat Aug 28, 2010 12:50 pm
by McInfo
There is no ciel() function. It's ceil().

I have a pagination example elsewhere on this forum.

Re: Search + Pagination - Please see

Posted: Tue Aug 31, 2010 12:03 am
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!

Re: Search + Pagination - Please see

Posted: Tue Aug 31, 2010 6:55 pm
by emilcarlo
Hi Jonah, the code didn't work.. Also, it has affected my modification link - it does does not get the id.

Re: Search + Pagination - Please see

Posted: Fri Sep 03, 2010 12:56 pm
by Jonah Bron
What exactly doesn't work? Does it give an error? If you can, attach a screenshot of the page.