Jump to content

Simple pagination help?


man5

Recommended Posts

I am trying to add a simple pagination to my page. I know there are tons of tutorials out there for this. Well I have taken one of them and used my own queries with it. 

 

Below is the code. The records do show up. The only problem is that all the records(from mysql table) show up, as oppose to limiting them per page.

 

Can you take a look at it and tell me what I might be doing wrong?

try {

            // Find out how many items are in the table
            $total = DB::getInstance()->query("SELECT COUNT(*) FROM records");


            // How many items to list per page
            $limit = 20;

            // How many pages will there be
            $pages = ceil($total / $limit);

            // What page are we currently on?
            $page = min($pages, filter_input(INPUT_GET, 'page', FILTER_VALIDATE_INT, array(
                'options' => array(
                    'default'   => 1,
                    'min_range' => 1,
                ),
            )));

            // Calculate the offset for the query
            $offset = ($page - 1)  * $limit;

            // Some information to display to the user
            $start = $offset + 1;
            $end = min(($offset + $limit), $total);


            // Prepare the paged query
            $stmt = DB::getInstance()->query("SELECT images.*, records.* FROM records
            LEFT JOIN images ON records.user_id = images.user_id
            ORDER BY record_id LIMIT {$limit} OFFSET {$offset}");

            if($stmt->count()) {

                // Display the results
                foreach($stmt->results() as $row) {

                    $thumb_img  =   escape($row->thumb_path);
                    $title  =   trim($row->title);
                    ?>
                      <div class="item">
                        <?php echo $thumb_img; ?>
                        <?php echo $title; ?>
                      </div>
                    <?php
                }
            } else {
                echo '<p>No results could be displayed.</p>';
            }

            // The "back" link
            $prevlink = ($page > 1) ? '<a href="?page=1" title="First page">«</a> <a href="?page=' . ($page - 1) . '" title="Previous page">‹</a>' : '<span class="disabled">«</span> <span class="disabled">‹</span>';

            // The "forward" link
            $nextlink = ($page < $pages) ? '<a href="?page=' . ($page + 1) . '" title="Next page">›</a> <a href="?page=' . $pages . '" title="Last page">»</a>' : '<span class="disabled">›</span> <span class="disabled">»</span>';

            // Display the paging information
            echo '<div id="paging"><p>', $prevlink, ' Page ', $page, ' of ', $pages, ' pages, displaying ', $start, '-', $end, ' of ', $total, ' results ', $nextlink, ' </p></div>';


    } catch (Exception $e) {
        echo '<p>', $e->getMessage(), '</p>';
    }
    ?>
Link to comment
Share on other sites

You should really break apart the code into functions based upon specific activities to be accomplished. For example, a function to run the query and return the results would be helpful. In any event, I'm pretty sure this is at least part of the problem:

$total = DB::getInstance()->query("SELECT COUNT(*) FROM records");

$total is the result resource of the query not the value returned from the query. You need to extract the value.

 

Also:

 

           $stmt = DB::getInstance()->query("SELECT images.*, records.* FROM records
            LEFT JOIN images ON records.user_id = images.user_id
            ORDER BY record_id LIMIT {$limit} OFFSET {$offset}");

 

The parameters for limit are [start_position] and [count]. You have them reversed.

Edited by Psycho
Link to comment
Share on other sites

You should really break apart the code into functions based upon specific activities to be accomplished. For example, a function to run the query and return the results would be helpful. In any event, I'm pretty sure this is at least part of the problem:

$total = DB::getInstance()->query("SELECT COUNT(*) FROM records");

$total is the result resource of the query not the value returned from the query. You need to extract the value.

 

Also:

           $stmt = DB::getInstance()->query("SELECT images.*, records.* FROM records
            LEFT JOIN images ON records.user_id = images.user_id
            ORDER BY record_id LIMIT {$limit} OFFSET {$offset}");

The parameters for limit are [start_position] and [count]. You have them reversed.

 

 

For the first part, you are right. To fix it, it's something like this.

$q = DB::getInstance()->query("SELECT * FROM records");
				
$total = $q->count();

Also notice how I removed the " COUNT * "? For some strange reason, that does not work for me. It works fine with only *.

 

So everything is good now.  Thanks for clearing that up.

Link to comment
Share on other sites

This is extremely inefficient, because you load the entire table into PHP only to count the rows and then throw the result set away.

 

The correct way to count rows is indeed a COUNT(*) query. You just need to actually fetch the result as explained by Psycho.

 

In your case, however, you don't need an extra query to count the rows. You can do it in the main query with the SQL_CALC_FOUND_ROWS option. This makes MySQL remember the total number of rows which the query would return if there was no LIMIT clause. You can then get this number with FOUND_ROWS().

Link to comment
Share on other sites

For the first part, you are right. To fix it, it's something like this.

$q = DB::getInstance()->query("SELECT * FROM records");
				
$total = $q->count();

Also notice how I removed the " COUNT * "? For some strange reason, that does not work for me. It works fine with only *.

 

So everything is good now.  Thanks for clearing that up.

 

That's not how you should do that. That query is returning ALL the records from the table and then you are getting the count of those records. That is very inefficient. There is no need to have the query return ALL the data, just to get the count. Use the query you had previously so the result set only returns the count. Then get the value form the result set. I don't use that type of syntax, so I'm not 100% sure, but it should go something like this

 

 

$q = DB::getInstance()->query("SELECT COUNT(*) FROM records");
$row = $q->fetch_assoc();
$total = $row[0];
Link to comment
Share on other sites

I am following a different setup to do PDO queries.  Here is the class function for the 'query' that I am using in the above example.

	public function query($sql, $params = array()) {
		$this->_error = false;
		if($this->_query = $this->_pdo->prepare($sql)) {
			$x = 1;
			if(count($params)) {
				foreach($params as $param) {
					$this->_query->bindValue($x, $param);
					$x++;
				}
			}
			
			if($this->_query->execute()) {
				$this->_results = $this->_query->fetchAll(PDO::FETCH_OBJ);
				$this->_count = $this->_query->rowCount();
			} else {
				$this->_error = true;
			}
		}
		
		return $this;
	}

COUNT(*) or fetch_assoc() won't work with "query" function.

Edited by man5
Link to comment
Share on other sites

You've obviously missed my reply. You don't need a COUNT(*) query at all. Just use SQL_CALC_FOUND_ROWS.

 

Regarding your database class, I suggest you throw it away and switch to plain PDO. The class is way too trivial to be useful, and most of the time, it actually stands in the way (as you can see).

 

With PDO, this whole task is a matter of 5 minutes:

$images_stmt =  $database->prepare('
    SELECT SQL_CALC_FOUND_ROWS
        ...
    FROM
        ...
');
$images_stmt->execute(array(
	...
));

$total_images = $database->query('SELECT FOUND_ROWS()')->fetchColumn();

foreach ($images_stmt as $image)
{
    ...
}
Edited by Jacques1
Link to comment
Share on other sites

 

You've obviously missed my reply. You don't need a COUNT(*) query at all. Just use SQL_CALC_FOUND_ROWS.

 

Regarding your database class, I suggest you throw it away and switch to plain PDO. The class is way too trivial to be useful, and most of the time, it actually stands in the way (as you can see).

 

With PDO, this whole task is a matter of 5 minutes:

$images_stmt =  $database->prepare('
    SELECT SQL_CALC_FOUND_ROWS
        ...
    FROM
        ...
');
$images_stmt->execute(array(
	...
));

$total_images = $database->query('SELECT FOUND_ROWS()')->fetchColumn();

foreach ($images_stmt as $image)
{
    ...
}

 

Ahh I see now. 

 

Thanks for clearing it up for me!

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.