PHP/mySQL and pagination

PHP
1

I need to run a query and have the results post back to a table on my webpage, however, it is messing up my webpage. Basically, it is timing my web page out. I know the query works because I tested it in mySQL and got back the results I wanted. Can anyone see where I went wront at? I’ve used this exact same query on the project before and it worked GREAT, but now adding some pagination it isnt. If i run a simple query (IE: select * from Customers) I get results, but obvisouly these results are not what I need. Thanks!!


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<body >
<?php

	// This script retrieves all the records from the users table.

	echo '<h1>Registered Users:</h1>';

	require_once ('mysqli_connect.php'); 	// Connect to the data base.
	include('ps_pagination.php');           //Include the PS_Pagination class
			
	  /*
		Write the query...
		Note: CONCAT_WS is a function that concatenates items together with a given separator.
		In this case, the last name and first name are added together , and separated by a comma and a space. 
	  */

	$q = "SELECT Customers.CustomerID, Customers.OldCustomerID, CONCAT(Customers.LastName,', ', Customers.FirstName,', ', Customers.MiddleName) AS 'Customer Name', 
	CONCAT(LEFT(Customers.FirstName ,1), LEFT(Customers.MiddleName,1), LEFT(Customers.LastName,1)) AS Username, 
	zlu_Cars.Description AS Car, zlu_CarColor.Description AS 'Car Color', zlu_Computers.Description AS Computer, 
	CASE Customers.IsLaptop WHEN 1 THEN 'Yes' WHEN 0 THEN 'No'END AS Laptop, 
	zlu_Race.Description AS Race, zlu_Residence.Description AS Residence, zlu_BirthMonth.Description AS 'Birth Month'
	FROM (((((Customers INNER JOIN zlu_Cars ON Customers.CarID = zlu_Cars.CarID) INNER JOIN zlu_CarColor ON Customers.CarColorID = zlu_CarColor.CarColorID) INNER JOIN zlu_Computers ON Customers.ComputerID = zlu_Computers.ComputerID) INNER JOIN zlu_Race ON Customers.RaceID = zlu_Race.RaceID) INNER JOIN zlu_Residence ON Customers.ResidenceID = zlu_Residence.ResidenceID) INNER JOIN zlu_BirthMonth ON Customers.BirthMonthID = zlu_BirthMonth.BirthMonthID
	ORDER BY Customers.LastName, Customers.FirstName";

	
	//Create a PS_Pagination object
	$countperpage = 18;
	$pager = new PS_Pagination($dbc,$q,$countperpage,10); 
	
	//The paginate() function returns a mysql result set 
	$rs = $pager->paginate();

	// Count the number of returned rows:
	$num = $pager->total_rows; 

	// If it ran OK, display the records.
	if ($num > 0) { 

	// Print the number of users:
	echo "<p>There are currently $num registered users.</p>";
    
	// Table header.
	echo '<table margin-left="auto" margin-right="auto" cellspacing="2" cellpadding="5" border="1" width="75%">
	<tr>
	    <td><b>CustomerID</b></td>
	    <td><b>OldCustomerID</b></td>
        <td><b>Customer Name</b></td>
        <td><b>Username</b></td>
        <td><b>Car</b></td>
        <td><b>Car Color</b></td>
        <td><b>Computer</b></td>
        <td><b>Laptop</b></td>
        <td><b>Race</b></td>
        <td><b>Residence</b></td>
        <td><b>Birth Month</b></td>
	
     
	</tr>';
	
	/* Fetch and print all the records:
		fetch_array
		Fetch a result row as an associative, a numeric array, or both. 

		Parameters:
		type - one of MYSQLI_ASSOC, MYSQLI_NUM, or MYSQLI_BOTH (default).
		By using the MYSQLI_ASSOC constant this function will behave identically to the mysqli_fetch_assoc(),
		while MYSQLI_NUM will behave identically to the mysqli_fetch_row() function. The final option MYSQLI_BOTH 
		will create a single array with the attributes of both. 

		Returns:
		a result row as an associative, a numeric array, or both or null if there are no more rows in the result set
	*/

	while ($row = mysqli_fetch_array($rs, MYSQLI_ASSOC)) {
		echo '<tr>
                    <td>' . $row['CustomerID'] . '</td><td>' . $row['OldCustomerID'] . '</td><td>' . $row['Customer Name'] . '</td>
                    <td>' . $row['Username'] . '</td><td>' . $row['Car'] . '</td><td>' . $row['Car Color'] . '</td>
                    <td>' . $row['Computer'] . '</td><td>' . $row['Laptop'] . '</td><td>' . $row['Race'] . '</td>
                    <td>' . $row['Residence'] . '</td><td>' . $row['Birth Month'] . '</td>
             </tr>';
	}

	echo '</table>'; // Close the table.
	
	mysqli_free_result ($rs); // Free up the resources.	

} else { // If no records were returned.

	echo '<p class="error">There are currently no registered users.</p>';

}

mysqli_close($dbc); // Close the database connection.

?>
</body>
</html>
2

can you show us the pagination class you are using?. also i would suggest doing some query optimizing as you basically you have a do it all query witch could be also be very slow depending on the data in your tables and there structure.

3

Here is my pagination file:

Thanks…

<?php
/**
 * PHPSense Pagination Class
 *
 * PHP tutorials and scripts
 *
 * @package        PHPSense
 * @author        Jatinder Singh Thind
 * @copyright        Copyright (c) 2006, Jatinder Singh Thind
 * @link        http://www.phpsense.com
 */
 
// ------------------------------------------------------------------------

class PS_Pagination {
    var $php_self;
    var $rows_per_page; //Number of records to display per page
    var $total_rows; //Total number of rows returned by the query
    var $links_per_page; //Number of links to display per page
    var $sql;
    var $debug = false;
    var $conn;
    var $page;
    var $max_pages;
    var $offset;
    
    /**
     * Constructor
     *
     * @param resource $connection Mysql connection link
     * @param string $sql SQL query to paginate. Example : SELECT * FROM users
     * @param integer $rows_per_page Number of records to display per page. Defaults to 10
     * @param integer $links_per_page Number of links to display per page. Defaults to 5
     */
     
    function PS_Pagination($connection, $sql, $rows_per_page = 10, $links_per_page = 5) {
        $this->conn = $connection;
        $this->sql = $sql;
        $this->rows_per_page = $rows_per_page;
        $this->links_per_page = $links_per_page;
        $this->php_self = htmlspecialchars($_SERVER['PHP_SELF']);
        if(isset($_GET['page'])) {
            $this->page = intval($_GET['page']);
        }
    }
    
    /**
     * Executes the SQL query and initializes internal variables
     *
     * @access public
     * @return resource
     */
    function paginate() {
        if(!$this->conn) {
            if($this->debug) echo "MySQL connection missing<br />";
            return false;
        }
        
        $all_rs = @mysql_query($this->sql);
        if(!$all_rs) {
            if($this->debug) echo "SQL query failed. Check your query.<br />";
            return false;
        }
        $this->total_rows = mysql_num_rows($all_rs);
        @mysql_close($all_rs);
        
        $this->max_pages = ceil($this->total_rows/$this->rows_per_page);

        //Check the page value just in case someone is trying to input an arbitrary value
        if($this->page > $this->max_pages || $this->page <= 0) {
            $this->page = 1;
        }
        
        //Calculate Offset
        $this->offset = $this->rows_per_page * ($this->page-1);
        
        //Fetch the required result set
        $rs = @mysql_query($this->sql." LIMIT {$this->offset}, {$this->rows_per_page}");
        if(!$rs) {
            if($this->debug) echo "Pagination query failed. Check your query.<br />";
            return false;
        }
        return $rs;
    }
    
    /**
     * Display the link to the first page
     *
     * @access public
     * @param string $tag Text string to be displayed as the link. Defaults to 'First'
     * @return string
     */
    function renderFirst($tag='First') {
        if($this->page == 1) {
            return $tag;
        }
        else {
            return '<a id="pagination-digg" href="'.$this->php_self.'?menukey=7&page=">'.$tag.'</a>';
        }
    }
    
    /**
     * Display the link to the last page
     *
     * @access public
     * @param string $tag Text string to be displayed as the link. Defaults to 'Last'
     * @return string
     */
    function renderLast($tag='Last') {
        if($this->page == $this->max_pages) {
            return $tag;
        }
        else {
            return '<a id="pagination-digg" href="'.$this->php_self.'?menukey=7&page='.$this->max_pages.'">'.$tag.'</a>';
        }
    }
    
    /**
     * Display the next link
     *
     * @access public
     * @param string $tag Text string to be displayed as the link. Defaults to '>>'
     * @return string
     */
    function renderNext($tag=' >>') {
        if($this->page < $this->max_pages) {
            return '<a id="pagination-digg" href="'.$this->php_self.'?menukey=7&page='.($this->page+1).'">'.$tag.'</a>';
        }
        else {
            return $tag;
        }
    }
    
    /**
     * Display the previous link
     *
     * @access public
     * @param string $tag Text string to be displayed as the link. Defaults to '<<'
     * @return string
     */
    function renderPrev($tag='<<') {
        if($this->page > 1) {
            return '<a id="pagination-digg"  href="'.$this->php_self.'?menukey=7&page='.($this->page-1).'">'.$tag.'</a>';
        }
        else {
            return $tag;
        }
    }
    
    /**
     * Display the page links
     *
     * @access public
     * @return string
     */
    function renderNav() {
        for($i=1;$i<=$this->max_pages;$i+=$this->links_per_page) {
            if($this->page >= $i) {
                $start = $i;
            }
        }
        
        if($this->max_pages > $this->links_per_page) {
            $end = $start+$this->links_per_page;
            if($end > $this->max_pages) $end = $this->max_pages+1;
        }
        else {
            $end = $this->max_pages;
        }
            
        $links = '';
        
        for( $i=$start ; $i<$end ; $i++) {
            if($i == $this->page) {
                $links .= " $i ";
            }
            else {
                $links .= ' <a id="pagination-digg"  href="'.$this->php_self.'?menukey=7&page='.$i.'">'.$i.'</a> ';
            }
        }
        
        return $links;
    }
    
    /**
     * Display full pagination navigation
     *
     * @access public
     * @return string
     */
    function renderFullNav() {
        return $this->renderFirst().'&nbsp;'.$this->renderPrev().'&nbsp;'.$this->renderNav().'&nbsp;'.$this->renderNext().'&nbsp;'.$this->renderLast();    
    }
    
    /**
     * Set debug mode
     *
     * @access public
     * @param bool $debug Set to TRUE to enable debug messages
     * @return void
     */
    function setDebug($debug) {
        $this->debug = $debug;
    }
}
?>
4

Apparently we are taking the same class at cincinnati state? here is what i am getting, that might be the problem.

while ($row = mysqli_fetch_array($rs, MYSQLI_ASSOC))

shouldn’t that bewhile ($row = mysqli_fetch_array($q, MYSQLI_ASSOC)) since that is the query you want to pull against.

Did you get the first and last with the number bar? that is what i’m having issues with.