Mysqli paganation help

General PHP Help
#21

You need to adjust your logic a small bit. The total number of pages should be changed. It is currently:

$pages = mysqli_num_rows($pages_query) / $per_page;

But, needs to be:

$pages = ceiling(mysqli_num_rows($pages_query) / $per_page);

This is to count the left overs from the division as a page. Also, some other minor changes…

First, change this as we discussed before…

    if (!isset($_GET['pages']))
    {
        echo "<meta http-equiv='refresh' content='0;url=view.php?page=1'>";
    }
    else
    {
        $pages = $_GET['pages'];
    }
    
    $start = (($pages - 1)*$per_page);

To something like this…

    $start = 1;
    if (isset($_GET['pages']))
            $start = $_GET['pages'];
    }

Much less complicated. You don’t need to refresh anything! The starting page is either 1 or the one sent to the page from the pagination buttons.

Next, the second query that pulls just one page of data is using a starting page number not the record number to start at. This needs to be altered slightly. The old one is:

$result = mysqli_query($link,"SELECT * FROM orders WHERE OrderBy = '$username' AND Status = 'Layon' LIMIT $start,$per_page");

New version:

$result = mysqli_query($link,"SELECT * FROM orders WHERE OrderBy = '$username' AND Status = 'Layon' LIMIT ($start-1)*$per_page, $per_page");

This uses the page number 1,2,3, etc times the number per page. So, 1 would be record 0, 2 is record 3, etc.

Try these minor changes and let us know.

#22

i tried that but now its showing no records

<?php include('styles/top.php'); ?> 
 <div id="full">

    <div id="view_box">
        <ul>
            <li><img src="images/1.png" /></li>
            <!-- <li><img src="pics/2.jpg" /></li> -->
            <!-- <li><img src="pics/3.jpg" /></li> -->
        </ul>
    </div>
    
    <div id="button">
        <ul>
            <!-- <li><button class="button" scroll_value="0">*</button></li> -- >
            <!-- <li><button class="button" scroll_value="600">*</button></li> -->
            <!-- <li><button class="button" scroll_value="1200">*</button></li> -->
        </ul>
    </div>
    <hr /><br />
    
   <?php
    
    if (($user_level !=1) && ($user_level !=2)){
        echo "No Access Allowed";
    } else {
    
    $per_page = 8;
    $pages_query = mysqli_query($link,"SELECT COUNT(`id`) FROM(`orders`) WHERE OrderBy = '$username'");
    $pages = ceil(mysqli_num_rows($pages_query) / $per_page);
    
    if (!isset($_GET['page']))
    {
         $start = 1;
     if (isset($_GET['pages']))
        $start = $_GET['pages'];
    }
    
    ?>
    
    <h3>View Orders</h3>
    
    		<table border=1">
			<tr>
				<th>FULL NAME </th>
                <th>DATE GOT </th>
				<th>PAY DATE</th>
				<th>MONEY DUE</th>
                <th>UPDATE ORDER STATUS</th>
			</tr>
            
            <tr>
					<td>&nbsp;</td>
					<td></td>
					<td></td>
                    <td></td>
                    <td></td>
				</tr>
            
            <?php
            
            error_reporting(0);
            $result = mysqli_query($link,"SELECT * FROM orders WHERE OrderBy = '$username' AND Status = 'Layon' LIMIT ($start-1)*$per_page, $per_page");
        
            
            $totmoney = 0;
            $strmoney = "";
            
            while ($myrow = mysql_fetch_array($result))
            {
                $strmoney = $myrow["Money"];
                $strmoney = str_replace(',','',$strmoney);
                
                if(is_numeric($strmoney))
                {
                    settype($strmoney, "float");
                    $strmoney = number_format($strmoney, 2);
                }
                echo "<TR><TD><a href=\"_view.php?id=".$myrow['id']."\">".$myrow["FullName"]."</A>&nbsp;</TD><TD>".$myrow["GotDate"]."&nbsp;</TD><TD>".$myrow["PayDate"]."&nbsp;</TD><TD>&pound;".$strmoney."&nbsp;</TD>";
				echo "<TD><center><a href=\"update_order.php?id=".$myrow['id']."\"><img class=\"displayed\" alt=\"View\" src=\"images/edit.png\" width=\"175\" height=\"25\" /></a></center></TD>";
                echo "</TR>";
                
                $totmoney = $totmoney + (int)$myrow["Money"];
            }
            
            
            ?>
            
            
               <tr>
					<td>&nbsp;</td>
					<td></td>
					<td></td>
                    <td></td>
                    <td></td>
				</tr>
            
            <tr>
					<td>&nbsp;</td>
					<td>TOTAL DUE</td>
					<td>&pound;<?=number_format($totmoney, 2);?>&nbsp;</td>
                    <td></td>
                    <td></td>
				</tr>
          			
		</table>
        
        
        <br />  
   
   <?php
   echo "<center>";
   for ($number=1;$number<=$pages;$number++)
    {
        echo '<a href="?page='.$number.'">'.$number.'</a> | ';
    }
        echo "<br/><br/><font color='white'>Current Page: $page</font>";
   echo "</center></br>";
   
   ?>
           
    <?php
    }
    ?>
    
    
   
</div>
<?php include('styles/bottom.php'); ?>
#23

Remove the error_reporting(0) line. It suppresses errors. I never use that ever! Normally, you trap errors so they are not displayed and you can alter the display to fit the error. But, for testing, just do not suppress them.

Next, to debug this, just before the second query where you currently have the error_reporting(0) line, try this:

die ("SELECT * FROM orders WHERE OrderBy = '$username' AND Status = 'Layon' LIMIT ($start-1)*$per_page, $per_page");

This will show the query that you are using. You can then test it in your MySQL control panel in the and see if it works or not. My guess is that one of these are not correct. Perhaps the “Status” is not correct or one of the LIMIT options are incorrect. Look at that output which will be the query used. It should show any issues.

Also, on the error-reporting for PHP testing. You can add these lines at the very top of your code. These will turn on all of the possible error reporting. Some of these errors will show in the page itself, some will be in a file in the folder that the the page is running in. So, sometimes you have to use FTP to refresh the page for that folder and see if there is a file named errors in it. If so, view that file and you will see errors not sent to the browser. All of these processing are what debugging a script is all about. hope it helps…

<?PHP
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);
#24

hi

its now showing this

SELECT * FROM orders WHERE OrderBy = ‘Mark’ AND Status = ‘Layon’ LIMIT (1-1)*8, 8

#25

Thank you! Me fault… Make the second query look like this instead. Should fix it…

Minor difference, but, does the calculation outside of the quotes…

#26

i am now getting this error

Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in /customers/7/d/c/mbdues.co.uk/httpd.www/mysqli/view.php on line 69

this is line 69

while ($myrow = mysqli_fetch_array($result)) ?

#27

Try while ($myrow = mysqli_fetch_assoc($result)) {

Sponsor our Newsletter | Privacy Policy | Terms of Service