We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Pagination

hutmasterx
hutmasterx asked
on
Medium Priority
508 Views
Last Modified: 2008-12-20
I wonder if anybody would be kind enough to adapt my code to have the following pagination.  My code so far is below.  Many thanks.

START PREV 1 2 3 4 5 NEXT END

<?php
                  
            $query = "select vacid, Title, ShortDescription, Location, Salary, Category, Contact, Display, Archive, JobType, Datestamp,  date_format( datestamp,  '%d %M %y'  )  as date from tbl_Vacancies where JobType like '%Permanent%' and ExpirationDate > '$today' order by date desc";
        
      $result = mysql_query($query);
                  
       echo "<table width='100%' border='0' cellspacing='0' cellpadding='0'>";
       echo "<tr>";
     echo "<td width='5%' class='sectiontableheader' align='left'>Job&nbsp;ID&nbsp;</td>";
       echo "<td width='18%' class='sectiontableheader' align='left'>Date Posted</td>";
       echo "<td width='47%' class='sectiontableheader' align='left'>Job Title</td>";
       echo "<td width='30%' class='sectiontableheader' align='left'>Company</td>";
       echo "</tr>";
       $color1 = "#f9f9f9";  
     $color2 = "#ffffff";
       $row_count = 0;
     while($row = mysql_fetch_array($result)){
       $row_color = ($row_count % 2) ? $color1 : $color2;
       echo "<tr class='sectiontableentry'>";
       echo "<td bgcolor='$row_color' width='5%' align='left'>$row[vacid]</td>";
       echo "<td bgcolor='$row_color' width='18%' align='left'>$row[Datestamp]</td>";
       echo "<td bgcolor='$row_color' width='47%' align='left'><b><a href='../vacancies/viewvacancy.php?id=$row[vacid]' class='vaclink'>$row[Title]</a></b></td>";
       echo "<td bgcolor='$row_color' width='50%' align='left'>$row[Company]</td>";
       echo "</tr>";
 
              $row_count++;
                 }
              echo "</table>";
             }
             
         ?>
Comment
Watch Question

Commented:
I'll give you a few rough pointers on how to ge there:


make it so the pages reflect links like this:

mypage.php?page=1
mypage.php?page=2
mypage.php?page=3
mypage.php?page=4
mypage.php?page=5

modify this by adding a LIMIT to the end of the query.
$page_size = 20;
$page_bump = ($_GET['page'] - 1) * $page_size;
$query = "select vacid, Title, ShortDescription, Location, Salary, Category, Contact, Display, Archive, JobType, Datestamp,  date_format( datestamp,  '%d %M %y'  )  as date from tbl_Vacancies where JobType like '%Permanent%' and ExpirationDate > '$today' order by date desc LIMIT $page_bump, $page_size";


for more info see http://dev.mysql.com/doc/refman/5.1/en/select.html

this would give you static pagination and work for a system of 100 records with pages of 20.

to adapt the system to appropriately handle the right number of pages...
instead of statically generating the links for the pages (as in my code above), use the following:

$get_count_result = mysql_query("SELECT count(*) as mycount FROM from tbl_Vacancies where JobType like '%Permanent%' and ExpirationDate > '$today'");
$get_count_obj = mysql_fetch_object($get_count_result);
$total_count = $get_count_obj->mycount;
$page_size = 20;
$count = 0;
$page = 0;
while ($count < $total_count) {
 $count = $count + $page_size;
 $page++;
 echo '<A HREF="/mypage.php?page=' . $page . '">$page</A>';
}

...
hope that tells you where to go from here.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Commented:
here is a sample code taken from some tutorial sites, hope u can follow and understand

<html>
<head>
<title>Implementing Paging with next and prev</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>
<?php
include '../library/config.php';
include '../library/opendb.php';

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

// 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;

$query  = "SELECT val FROM randoms LIMIT $offset, $rowsPerPage";
$result = mysql_query($query) or die('Error, query failed');

// print the random numbers
while($row = mysql_fetch_array($result))
{
    echo $row['val'] . '<br>';
}
echo '<br>';

// how many rows we have in database
$query   = "SELECT COUNT(val) AS numrows FROM randoms";
$result  = mysql_query($query) or die('Error, query failed');
$row     = mysql_fetch_array($result, MYSQL_ASSOC);
$numrows = $row['numrows'];

// how many pages we have when using paging?
$maxPage = ceil($numrows/$rowsPerPage);

// print the link to access each page
$self = $_SERVER['PHP_SELF'];
$nav = '';
for($page = 1; $page <= $maxPage; $page++)
{
    if ($page == $pageNum)
    {
        $nav .= " $page ";   // no need to create a link to current page
    }
    else
    {
        $nav .= " <a href=\"$self?page=$page\">$page</a> ";
    }        
}

// creating previous and next link
// plus the link to go straight to
// the first and last page

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  = '&nbsp;'; // we're on page one, don't print previous link
    $first = '&nbsp;'; // nor the 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 = '&nbsp;'; // we're on the last page, don't print next link
    $last = '&nbsp;'; // nor the last page link
}

// print the navigation link
echo $first . $prev . $nav . $next . $last;

// and close the database connection
include '../library/closedb.php';
?>
</body>
</html>

this code is taken from site, there u'll see full explanation to the above code
http://www.php-mysql-tutorial.com/php-mysql-paging.php

Author

Commented:
Many thanks :-)
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.