Pagination

[assgar]

Hello

I have a search page where you select the letter of the alphabet
and the person's information who's name beginning with that letter is displayed.

This function is placed on the web page to search and display the information.

Everything works except one thing.

The problem is if there are 3 pages page one is OK, but when you select next to go to page two the information on page two is no displaying.

Can you see something I missed or have any suggestion?


[PHP]
<?php

function search_display($org_code, $field, $searching, $find, $let, $stat_type, $contact_field)
{

//db connection and selection
$mysqli = db_connect();
db_select($mysqli, $org_code);

/**-----------------------pagination begins ------------------------**/
//how many rows to show per page
$rowsPerPage = 11;

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



//alphabet listing search except all
if(!empty($let) && $let !== "All" )
{
$query = "SELECT p.patient_id, p.last_name, p.first_name, p.id,
c.contact_number
FROM pat p, contact c
WHERE p.id = c.id
AND $field LIKE '$let%'
AND p.pat_status = '$stat_type'
AND p.org_code = '$org_code'
AND p.org_code = c.org_code
AND c.$contact_field = '$contact'
AND p.deleted = 'N'
ORDER BY p.last_name, p.first_name
";
}

//table begins
echo "<table width=\"100%\" height=\"332\" left =\"4\" align = \"\" border=\"0
\" font face =\"arial\">\n";


//------------------------------looping----------------------------------
$num_service = mysqli_num_rows($result);
for($i=0; $i < $num_service; $i++)
{
$row = mysqli_fetch_array($result);

list($id, $last_name, $first_name, $id, $contact_number) = $row;


//zebra stripping
if($i % 2) //alternate row colour
{
$bgcolor="#eeeee0";
}
else
{
$bgcolor="#ebeae0";
}

echo"<tr height=\"10\"><td width=\"20%\" height=\"10\"
bgcolor=\"$bgcolor\"><span class=\"style20\">
<a href =\"../search_form.php?\"> $last_name</a></span></td>
<td width=\"18%\" height=\"10\" bgcolor=\"$bgcolor\">
<span class=\"style20\"> <a href =\"../search_form.php?
\">$first_name</a></span></td>
<td width=\"9%\" height=\"10\" bgcolor=\"$bgcolor\"><span
class=\"style20\">$id</span></td><td width=\"17%\"
height=\"10\" bgcolor=\"$bgcolor\"><span class=\"style20
\">$contact_number</span></td>\n";
echo"</tr>\n";

}//end of for loop

/**----------------pagination continue-------------------**/
echo '<br>';

//how many rows we have in database
$result = mysqli_query($mysqli,$query) or die('Error, query failed');
$numrows = mysqli_num_rows($result);

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

$self = $_SERVER['PHP_SELF'];

/** creating 'previous' and 'next' link plus 'first page' and 'last page' link
print 'previous' link only if not on page one **/

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 = '[Prev]'; /* we're on page one, don't enable 'previous'
link*/
$first = '[First Page]'; // nor 'first page' link
}

//print 'next' link only if we're not
//on the last page
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 = '[Next]'; /* we're on the last page, don't
enable 'next' link*/
$last = '[Last Page]'; // nor 'last page' link
}

// print the page navigation link
echo"<center> ". $first . $prev . " <strong>$pageNum</strong> of
<strong>$maxPage</strong> pages " . $next . $last."</center>";

$mysqli->close();

}

?>
[/php]

[theRamones]

add 'limit' statement on query

      $query = "SELECT p.patient_id, p.last_name, p.first_name, p.id, 
                   c.contact_number 
              FROM pat p, contact c 
        WHERE p.id = c.id 
        AND $field LIKE '$let%' 
        AND p.pat_status = '$stat_type' 
        AND p.org_code = '$org_code' 
        AND p.org_code = c.org_code 
        AND c.$contact_field = '$contact' 
        AND p.deleted = 'N' 
        ORDER BY p.last_name, p.first_name 
        limit $offset,11
          ";

[assgar]

add 'limit' statement on query
$query = "SELECT p.patient_id, p.last_name, p.first_name, p.id,
c.contact_number
FROM pat p, contact c
WHERE p.id = c.id
AND $field LIKE '$let%'
AND p.pat_status = '$stat_type'
AND p.org_code = '$org_code'
AND p.org_code = c.org_code
AND c.$contact_field = '$contact'
AND p.deleted = 'N'
ORDER BY p.last_name, p.first_name
limit $offset,11
";

Thanks for responding and your suggestion. I will try it.

I forgt to include a bit of code found after the select statement.
Won't this bit of code do the same thging?
[php]
$pagingQuery = "LIMIT $offset, $rowsPerPage";
$result = mysqli_query($mysqli,$query.$pagingQuery) or die('Error, query failed');

[/php]

[assgar]

Thanks for the suggestions.

I have resolved the problem.
Using the next and other navigation links to pass variables via the URL that is used to retrive data from the database like the "ID" was required.