Simple pagination help?

[man5]

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>';
    }
    ?>

[Psycho]

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 June 18, 2014 by Psycho

[Jacques1]

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

 

No, man5 is using the OFFSET syntax.

[Psycho]

No, man5 is using the OFFSET syntax.

 

You're right. I missed that. I almost never see anyone use that format. I thought that was why he was getting "all" the records.

Edited June 18, 2014 by Psycho

[man5]

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.

[Jacques1]

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().

[Psycho]

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];

[man5]

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 June 19, 2014 by man5

[Jacques1]

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 June 19, 2014 by Jacques1

[man5]

 

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!