Pagination omitting first DB entry and failing to display navigation.

[Digital Wallfare]

Hi all,

 

I understand that you answer a lot of pagination questions, so I apologise for adding another.

 

I'm having problems with the pagination code i've written, it is omitting the first entry from the search and not displaying the "Next" and "Prev." page navigation tags. It is also only displaying 1 less than the max amount i've set for each page (I'm thinking thats linked with the omission of the first result).

 

I am a PHP beginner, this is my first attempt at building a website, subsequently i dont have the experience with PHP to be able to identify areas that could be causing this so i'm asking the pro's - please help!!

 

Here is my code so far:

 

$connection = mysql_connect($hostname, $username, $password);
    if (!$connection) {
       die("A connection to the server could not be established");
    }
/*Select Database */
    mysql_select_db($database) or die("Database could not be selected!");

/*set variables*/
if (isset($_POST['MerchantType']) && isset($_POST['County'])){
    $MerchantType = $_POST["MerchantType"];
    $County = $_POST["County"];
    echo $County . "  " . $MerchantType;
}
   

/* Set current, prev and next page */
$page = (!isset($_GET['page']))? 1 : $_GET['page'];  
$prev = ($page - 1);
$next = ($page + 1);

/* Max results per page */
$max_results = 5;


/*Figre out how many items there are in your DB */
$result = mysql_query("SELECT * FROM $tablename WHERE County = '$County' AND MerchantType = '$MerchantType'") or die(mysql_error());
$total_results = mysql_num_rows($result); 

/*Must know where to begin grabbing the results from the DB */
$limitvalue = $page * $max_results - ($max_results);

/*Query */        
$result = mysql_query("SELECT * FROM $tablename WHERE County = '$County' AND MerchantType = '$MerchantType' LIMIT $limitvalue, $max_results") or die(mysql_error());


$total_results = mysql_num_rows($result);

$total_pages = ceil($total_results / $max_results);

$pagination = '';

/* Create a PREV link if there is one */
if($page > 1)
{
$pagination .= '<a href="index.php?page='.$prev.'">Previous</a> ';
}

/* Loop through the total pages */
for($i = 1; $i <= $total_pages; $i++)
{
if(($page) == $i)
{
$pagination .= $i;
}
else
{
$pagination .= '<a href="index.php?page='.$i.'">$i</a>';
}
}

/* Print NEXT link if there is one */
if($page < $total_pages)
{
$pagination .= '<a href="index.php?page='.$next.'">Next</a>';
}




while ($i = mysql_fetch_array($result))



while ($row_details = mysql_fetch_array($result))
    {                                
    echo    '
        <table class="results" border="1" bordercolor="#1d9346">
        <style="background-color:#FFFFFF" width="800" cellpadding="3" cellspacing="3">
        <tr>
        <td width="200" align="left" rowspan="7"><img src = "images/Test.png" /></td>
        </tr>
        <tr>
        <td width="573" height="27" align="left">Name: '.$row_details['MerchantName'].'</td>
        </tr>
	<tr>
        <td height="27" align="left">Address 1: '.$row_details['AddressLine1'].'</td>
        </tr>
	<tr>
        <td height="27" align="left">Address 2: '.$row_details['AddressLine2'].'</td>
        </tr>
        <tr>
        <td height="27" align="left">Telephone: '.$row_details['Telephone'].'</td>
        </tr>
        <tr>
        <td height="27" align="left">Website: '.$row_details['Website'].'</td>
        </tr>
        <tr>
        <td height="27" align="left">Description: '.$row_details['Description'].'</td>
        </tr>
        </table><br />';
    }

?>
</div>
</div>

<?php

 

I am really lost on this, and brain baked from 8 hours of trial and error so any help would be much appreciated,

 

Sam

[Cosizzle]

Hmm, well lets break it down:

 

// check if the URI is is NOT set. If it;s not, set it to 1, else if it is set - set $page to the URI value.
$page = (!isset($_GET['page']))? 1 : $_GET['page'];  

$prev = ($page - 1);
$next = ($page + 1);

 

Next you should debug these messages.

 

echo $page . ' ' . $prev . ' ' . $next;

 

This will show you what page is currently set to.

 

After this lets see a few more variables:

 

echo '$total_results='.$total_results;
echo ' $total_pages='.$total_pages;

 

[Cosizzle]

Once we've done all that we can start debugging the buttons

 

// if $page is equal to 1 and less than $total pages
// show the next button

// if $page is greater than 1 and less than $toalpages
// show prev button and next button

// if $page is equal to $total_pages 
// disable next button

if ($page == 1 && <= $total_pages) {
// show next button
}

if ($page > 1 && <= $total_pages) {
// show prev button and next button
}

if ($page == $total_pages) {
// show prev
}