Pagination code, help requested.

Problem with passing on search criteria

[RealeT]

First off, hello to everyone, its my first time here and after reading some helpful googled forum posts about other difficulties I was having that came up from this site, I thought i would join.

Heres my current situation, Im working on a site learning PHP as i go with manuals, reading sites online etc, Ive done ok so far and I'm learning a lot. I've come to a bit of a hurdle tho at this point.

------------------------------------------------------------------------
Pagination, Using a tutorial on the internet I am close to having it working but I have hit a snag. Here is the situation. I have got a search page that passes on these variables :

$_POST['searchjobtitle'];
$_POST['lid'];
$_POST['cid'];
$_POST['sid'];
$_POST['searchjobtext'];

which then add to the WHERE part of the following:

$select = 'SELECT DISTINCT id, title, salary, salarytype, jobdescription, town, loc, categoryid, employerid';
$from = ' FROM joblist';
$where = ' WHERE 1=1';

then I add the limit part of the pagination code to what is displayed (please excuse me if Im not explaining myself correctly, as I say Im still learning so could be viewing things incorrectly).

$where .= " LIMIT $limit_value, $limit";

which all works great, my results are worked out on the search page and displayed nicely. The problem lies with the fact that if the results of the search criteria produce less than the :

$limit = 20;

I still get a numbered next page, ie "PREV 1 2 NEXT" with the 2 and NEXT appearing as a link to another page showing more results that dont relate to this search but to the entries 20+ as if I had done a search for all entries with no search criteria from the first 5 posted variables.

I think this has something to do with the way the information is passed on in in the links for pagination $_GET between paginated pages but then why do I get "2 NEXT" as links as the code should see theres not enough results for more than one page. Im unsure how to fix or proceed with this, the code was partly from a tutorial that I have adapted to my site and was buggy according to the many comments following it. I feel loathe to give up now I have got this far and so any help from anyone in this matter would be amazingly appreciated.

Here is the code for my page that shows what happens once the top 5 $_POST variables have been passed to it:

(please excuse any poor layout)

$dbcnx = @mysql_connect('mydatabase', 'mydatabase', 'mydatabase');
if (!$dbcnx) {
exit('<p>Unable to connect to the ' .
'database server at this time.</p>');
}

if (!@mysql_select_db('mydatabasetablename')) {
exit('<p>Unable to locate the myname ' .
'database at this time.</p>');
}
$limit = 20;

$query_count = "SELECT id FROM joblist";
$result_count = mysql_query($query_count);
$total_rows = mysql_num_rows($result_count);
$page = $_GET['page'];

if(empty($page)) {
$page = "1";
}

$limit_value = $page * $limit - ($limit);

$select = 'SELECT DISTINCT id, title, salary, salarytype, jobdescription, town, loc, categoryid, employerid';
$from = ' FROM joblist';
$where = ' WHERE 1=1';
$where .= " LIMIT $limit_value, $limit";

$result = mysql_query($select . $from . $where);
if (!$result) {
echo '</table>';
exit('<p>Error retrieving jobs information from database!<br />'.
'Error: ' . mysql_error() . '</p>');
}

if(mysql_num_rows($result) == 0) {
echo '</table>';
echo nl2br("\n\n No search results matched your current search criteria, Please try again. \n");
}

while($row = mysql_fetch_array($result)) {
if ($bgcolor == "#E0E0E0") {
$bgcolor = "#FFFFFF";
} else {
$bgcolor = "#E0E0E0";
}

if($page!= 1) {
$pageprev = $page -1;
echo("&nbsp;<b><a href='$PHP_SELF?page=$pageprev'>PREV</a></b>&nbsp;"); ///
} else {
echo("&nbsp;PREV&nbsp;");
}

$num_of_pages = $total_rows / $limit;

for($i = 1; $i <= $num_of_pages; $i++) {
if($i == $page) {
echo($i." ");
} else {
echo("&nbsp;<b><a href='$PHP_SELF?page=$i'>$i</a></b>&nbsp;");
}
}

if(($total_rows % $limit)!= 0) {
if($i == $page) {
echo($i." ");
} else {
echo("<a href='{$_SERVER['PHP_SELF']}?page=$i'>$i</a>");
}
}

if(($total_rows - ($limit * $page)) >= 0) {
$pagenext = $page +1;
echo("&nbsp;<b><a href='$PHP_SELF?page=$pagenext'>NEXT</a></b> ");
} else {
echo("&nbsp;NEXT&nbsp;");
}

For anyone who has taken the time to read through all this, thank you, for anyone who offers any help in this matter you have my gratitude.

thx

John

[edited by: jatar_k at 3:53 pm (utc) on July 29, 2006]
[edit reason]
[1][edit reason] trimmed and formatted code [/edit] [/edit][/1]

[jatar_k]

Welcome to WebmasterWorld RealeT,

since next is the problem spot then I am guessing this is the misbehaving code

if(($total_rows - ($limit * $page)) >= 0) {

have you checked on these problem pages whether the variables are set properly? Try echo'ing the values of these variables to see what they contain. You could also break it into 2 steps to make it easier

$shownext = $total_rows - ($limit * $page);
echo '<p>total_rows: ',$total_rows;
echo '<br>limit: ',$limit;
echo '<br>page: ',$page;
echo '<br>shownext: ',$shownext;
if($shownext >= 0) {

if these vars all look right then you can try looking at other comparisons, if they aren't they may help point you in the right direction.

I am also not sure you really want >= as if it is = to 0 then it shouldn't be shown.

[RealeT]

Thanks for replying jatar_k.

Really helpful, once I outputed the variables onto the page I could see that the $total_rows was always the max in the table because of :

$query_count = "SELECT id FROM joblist";
$result_count = mysql_query($query_count);
$total_rows = mysql_num_rows($result_count);

which was before any of the search criteria was applied.

So following that I then replaced in the PREV 1 2 NEXT part of the page code the:

if(($total_rows - ($limit * $page)) >= 0)
{

part with:

if(($row_count - ($limit * $page)) >= 0)
{

with $row_count being a variable i created that checks the number of rows that come from the search query but before the limit is applied.

I did try it on the current query that was being searched but found as there is a limit placed at the end of the WHERE variable i would only get the limited amount of queries, ie whatever limit has been set to.

So after the search criteria has been applied to the query but before the limit variable is taken into account i put this code:

$currentresult = mysql_query($select . $from . $where);
$row_count = mysql_num_rows($currentresult);

This seems to have fixed the link problem, so thanks for your help Jatar_k

Heres the code I am using now, and underneath is the problem that I still have :

<?php
$dbcnx = @mysql_connect('mydatabase', 'mydatabase', 'mydatabase');
if (!$dbcnx) {
exit('<p>Unable to connect to the ' .
'database server at this time.</p>');
}

if (!@mysql_select_db('mydatabasetablename')) {
exit('<p>Unable to locate the myname ' .
'database at this time.</p>');
}
$limit = 20;

$query_count = "SELECT id FROM joblist";
$result_count = mysql_query($query_count);
$total_rows = mysql_num_rows($result_count);
$page = $_GET['page'];

if(empty($page))
{
$page = "1";
}

$limit_value = $page * $limit - ($limit);

?>

<table width="714">
<tr><th width =220 align="left">Job Title</th>
<th width =60 align="left">Salary</th>
<th width =55 align="left">Wizref</th>
<th width =75 align="left">Contract</th>
<th width =115 align="left">Town</th>
<th width =120 align="left">Area</th>
<th width =82 align="left">Category</th>
</tr>

<?php

$select = 'SELECT DISTINCT id, title, salary, salarytype, jobdescription, town, loc, categoryid, employerid';
$from = ' FROM joblist';
$where = ' WHERE 1=1';

$searchjobtitle = $_POST['searchjobtitle'];
if ($searchjobtitle!= '') { // Some search text was specified
$where .= " AND title LIKE '%$searchjobtitle%'";
}

$lid = $_POST['lid'];
if ($lid == 'Any Location') { // remove it
$lid ="";

}

if ($lid!= '') { // A location is selected
$where .= " AND loc='$lid'";
}

$cid = $_POST['cid'];
if ($cid!= '') { // A category is selected
$where .= " AND categoryid='$cid'";
}

$sid = $_POST['sid'];
if ($sid!= '') { // A salary option is selected
$where .= " AND salarytype='$sid'";
}

$searchjobtext = $_POST['searchjobtext'];
if ($searchjobtext!= '') { // Some search text was specified
$where .= " AND jobdescription LIKE '%$searchjobtext%'";
}

$currentresult = mysql_query($select . $from . $where);
$row_count = mysql_num_rows($currentresult);

$where .= " LIMIT $limit_value, $limit";

$result = mysql_query($select . $from . $where);
if (!$result) {
echo '</table>';
exit('<p>Error retrieving jobs information from database!<br />'.
'Error: ' . mysql_error() . '</p>');
}

if(mysql_num_rows($result) == 0)
{
echo '</table>';
echo nl2br("\n\n No search results matched your current search criteria, Please try again. \n");
?>
Please click <a href="searchjobs.php">HERE</a><br /><br /><br /><br /><br />
<p class="block"><strong>Please note:</strong> just some test here</p>

</div>
<div id="footer">
just some text here<a href="http://www.mylink.co.uk" title="mywords" target="_self">mywords</a>
</div>
</div>
</body>
</html>
<?php
die;
//echo("No search results matched your current search criteria, please try again.\n");
}

$bgcolor = "#FFFFFF"; // light gray
//echo("<table>");

while($row = mysql_fetch_array($result))
{
if ($bgcolor == "#E0E0E0")
{
$bgcolor = "#FFFFFF";
}
else
{
$bgcolor = "#E0E0E0";
}
//echo "<tr valign='top'>\n";
echo("<tr valign='top' bgcolor=".$bgcolor.">\n");
$id = htmlspecialchars($row['id']);
$title = htmlspecialchars($row['title']);
$salary = htmlspecialchars($row['salary']);
$reference = htmlspecialchars($row['id']);
$salarytype = htmlspecialchars($row['salarytype']);
$town = htmlspecialchars($row['town']);
$loc = htmlspecialchars($row['loc']);
$categoryid = htmlspecialchars($row['categoryid']);
echo "<td>$title</td>\n";
echo "<td>$salary</td>\n";
echo "<td>$reference</td>\n";
echo "<td>$salarytype</td>\n";
echo "<td>$town</td>\n";
echo "<td>$loc</td>\n";
echo "<td>$categoryid</td>\n";
echo "<td width =50 align =center> <a href='jobdetails.php?id=$id' TARGET='_blank'>Details</a></td>\n";
echo "</tr>\n";
}

?>
</table>
<?php

if($page!= 1)
{
$pageprev = $page -1;

echo("&nbsp;<b><a href='$PHP_SELF?page=$pageprev'>PREV</a></b>&nbsp;"); ///
}
else
{
echo("&nbsp;PREV&nbsp;");
}

$num_of_pages = $row_count / $limit;

for($i = 1; $i <= $num_of_pages; $i++)
{
if($i == $page)
{
echo($i." ");
}
else
{

echo("&nbsp;<b><a href='$PHP_SELF?page=$i'>$i</a></b>&nbsp;");
}
}

if(($row_count % $limit)!= 0)
{
if($i == $page)
{
echo($i." ");
}
else
{
echo("<a href='{$_SERVER['PHP_SELF']}?page=$i'>$i</a>");
}
}

if(($row_count - ($limit * $page)) >= 0)
{
$pagenext = $page +1;
echo("&nbsp;<b><a href='$PHP_SELF?page=$pagenext'>NEXT</a></b> ");
}
else
{
echo("&nbsp;NEXT&nbsp;");
}

mysql_free_result($result);
$result='';
?>

The problem is once I click to go to the next page of results after the first page has displayed (either through the NEXT or numbered links 1 2 etc the results dont show the next set of results that are left ie total number of results is 23, first 20 are displayed on page 1 then on page 2 instead of results 21-23 I get results 20-29 (with 29 being the total amount of entries). The PREV and numbered links that return to the previous page also then do not show the correct results but just show entries 1-20, frustrating, any ideas or help is appreciated.

I believe it's what is passed page to page but im not sure how to show entries 21-23 of the current search from page 1.

Could it be the way the code is and Global variable settings or something? clutching at straws.

Any help or thoughts or pointers is again greatly appreciated.

[RealeT]

Also, when the results page has entries less than the $limit set which is 20 everything is ok, ie displays for example 3 results and nothing is linked to go one a page. So the page1 seems ok just not anything following.

[jatar_k]

you are only passing $pagenext to your other pages which is probably where the problem lies

you need to recreate the search, your subsequent pages need all of the same information that the original one used like the search and anything else you originally pass. The only difference in the call is that you pass it an offset or page number.