PHP SQLite3 Paginator based on MySQL giving errors

Question

I am using SQLite3 and am trying to adapt a Paginator script i found. After changing what seems to be MYSQL commands it kind of works but it does not display the correct amount of items and seems to differ with the results it gives.

I am also getting the following error which i am not sure how to fix:

Notice: Undefined index: video in C:\xampp\htdocs\Projects\index.php on line 27

The code i am using is:

Paginator.php

<?php

class Paginator {

    private $_conn;
    private $_limit;
    private $_page;
    private $_query;
    private $_total;

    public function __construct( $conn, $query ) {
        $this->_conn = $conn;
        $this->_query = $query;
        $rs= $this->_conn->query( $this->_query );
        $this->_total = count($rs);
    }

    public function getData( $limit = 10, $page = 1 ) {
        $this->_limit   = $limit;
        $this->_page    = $page;

        if ( $this->_limit == 'all' ) {
            $query      = $this->_query;
        } else {
            $query      = $this->_query . " LIMIT " . ( ( $this->_page - 1 ) * $this->_limit ) . ", $this->_limit";
        }
        $rs             = $this->_conn->query( $query );

        while ( $row = $rs->fetchArray() ) {
            $results[]  = $row;
        }

        $results[] = [];
        $result         = new stdClass();
        $result->page   = $this->_page;
        $result->limit  = $this->_limit;
        $result->total  = $this->_total;
        $result->data   = $results;
        return $result;
    }

    public function createLinks( $links, $list_class ) {
        if ( $this->_limit == 'all' ) {
            return '';
        }

        $last       = ceil( $this->_total / $this->_limit );
        $start      = ( ( $this->_page - $links ) > 0 ) ? $this->_page - $links : 1;
        $end        = ( ( $this->_page + $links ) < $last ) ? $this->_page + $links : $last;
        $html       = '<ul class="' . $list_class . '">';
        $class      = ( $this->_page == 1 ) ? "disabled" : "";
        $html       .= '<li class="' . $class . '"><a href="?limit=' . $this->_limit . '&page=' . ( $this->_page - 1 ) . '">&laquo;</a></li>';

        if ( $start > 1 ) {
            $html   .= '<li><a href="?limit=' . $this->_limit . '&page=1">1</a></li>';
            $html   .= '<li class="disabled"><span>...</span></li>';
        }

        for ( $i = $start ; $i <= $end; $i++ ) {
            $class  = ( $this->_page == $i ) ? "active" : "";
            $html   .= '<li class="' . $class . '"><a href="?limit=' . $this->_limit . '&page=' . $i . '">' . $i . '</a></li>';
        }

        if ( $end < $last ) {
            $html   .= '<li class="disabled"><span>...</span></li>';
            $html   .= '<li><a href="?limit=' . $this->_limit . '&page=' . $last . '">' . $last . '</a></li>';
        }

        $class      = ( $this->_page == $last ) ? "disabled" : "";
        $html       .= '<li class="' . $class . '"><a href="?limit=' . $this->_limit . '&page=' . ( $this->_page + 1 ) . '">&raquo;</a></li>';
        $html       .= '</ul>';
        return $html;
    }

}

?>

index.php

<?php
    require_once 'Paginator.php';

    $db = new SQLite3('latest.db');

    $limit      = ( isset( $_GET['limit'] ) ) ? $_GET['limit'] : 4;
    $page       = ( isset( $_GET['page'] ) ) ? $_GET['page'] : 1;
    $links      = ( isset( $_GET['links'] ) ) ? $_GET['links'] : 4;
    $query      = "SELECT ID, video FROM latest";

    $Paginator  = new Paginator( $db, $query );

    $results    = $Paginator->getData( $page, $limit );
?>

<!DOCTYPE html>
    <head>
        <title>PHP Pagination</title>
        <link rel="stylesheet" href="css/stylesheet.css">
    </head>
    <body>
        <div class="container">
                <div class="col-md-10 col-md-offset-1">
                <h1>PHP Pagination</h1>

<?php for( $i = 0; $i < count( $results->data ); $i++ ) : ?>
    <p><?php echo $results->data[$i]['video']; ?></p>
<?php endfor; ?>

<p><?php echo $Paginator->createLinks($links, 'pagination pagination-sm'); ?></p>

                </div>
        </div>
        </body>
</html>

To create the database and check the data is added i ran this script once:

<?php

// Create the Database
$db = new SQLite3('latest.db');

// Create the Table
$db->query('CREATE TABLE IF NOT EXISTS latest (ID INTEGER PRIMARY KEY ASC, video STRING)');

// Insert the data
$db->query('INSERT INTO latest (video) VALUES ("XoiEkEuCWog")');
$db->query('INSERT INTO latest (video) VALUES ("jsbeemdD2rQ")');
$db->query('INSERT INTO latest (video) VALUES ("hv44srAsAo4")');
$db->query('INSERT INTO latest (video) VALUES ("nwpj9_hrK_A")');
$db->query('INSERT INTO latest (video) VALUES ("sY3rIlrTTh8")');
$db->query('INSERT INTO latest (video) VALUES ("QpbQ4I3Eidg")');
$db->query('INSERT INTO latest (video) VALUES ("M0it_zMP-EM")');
$db->query('INSERT INTO latest (video) VALUES ("6X_C9E55CfM")');
$db->query('INSERT INTO latest (video) VALUES ("cNw8A5pwbVI")');
$db->query('INSERT INTO latest (video) VALUES ("J-gYJBsln-w")');

echo '<h1>The Following Data Was Created</h1>';

// Get the data
$results = $db->query('SELECT ID, video FROM latest');
while ($row = $results->fetchArray()) {
    echo '<b>ID:</b> ' . $row['ID'] . ', <b>Video:</b> ' . $row['video'] . '<br>';
}

?>

How can i fix the error and get this code working correctly?

Edit:

Thanks to the suggested fix of removing "$results[] = [];" the index page now displays without errors however it is not working as expected.

What i expected was that since the $limit is set at 10 it would list 10 strings and show the «1» paginator buttons and if the $limit was set to 5 then 5 display and «1,2» as the buttons however currently it only shows 1 string like:

nwpj9_hrK_A

Also clicking the next and page buttons makes it give unexpected results rather than being the end of the page list if will give something random.

Dhruv Saxena · Accepted Answer

Aside from the $results[] = [] bit (which tacked one empty row in the returned results), there's a very, very small problem with the code:

When method getData() is invoked from index.php, we have:
```
$results    = $Paginator->getData( $page, $limit );
```

Whereas, at the definition, it is:

public function getData( $limit = 10, $page = 1 ){
}

This means, even though it apparently looks like the $limit parameter is set to fetch at least 4 records, it is defaulted to 1 record only. Where it may be swapped is surely left to one's discretion. However, I happened to make the fix at the definition. Additionally, it'd be nice to have the declaration of $results at the start of the method itself (or else, we get an undefined error again when traversing beyond the last page of displayable results) :

public function getData($page = 1, $limit = 10) {
    $this->_limit   = $limit;
    $this->_page    = $page;
    $results        = array();

    if ( $this->_limit == 'all' ) {
        $query      = $this->_query;
    } 
    else {
        $query      = $this->_query . " LIMIT " . ( ( $this->_page - 1 ) * $this->_limit ) . ", $this->_limit";
    }
    $rs             = $this->_conn->query( $query );

    while ( $row = $rs->fetchArray() ) {
        $results[]  = $row;
    }

    //$results[] = [];
    $result         = new stdClass();
    $result->page   = $this->_page;
    $result->limit  = $this->_limit;
    $result->total  = $this->_total;
    $result->data   = $results;
    return $result;
}

e4c5 · Answer

The reason that you are getting the undefined index error is because of this bit of code

    while ( $row = $rs->fetchArray() ) {
        $results[]  = $row;
    }

    $results[] = [];  /** this line here **/

You are resetting results. Please remove that line. As for the rest of the question, please provide more information.

Noted your update. Please see the answer by Dhruv he seems to have addressed the rest of the problem.

Rick James · Answer

Pagination using OFFSET has inherent problems when records are being added/deleted as the user is flipping through the pages. Also, OFFSET is quite inefficient when the data set is large.

So, I recommend abandoning anything that uses OFFSET. Instead, "remember where you left off" and using something like for the "next" page:

WHERE id > $left_off
ORDER BY id
LIMIT 10

More details

PHP SQLite3 Paginator based on MySQL giving errors

Tags:

php

sqlite

mysql

pagination

zeddex

3 Answers

Dhruv Saxena

e4c5

Rick James

Recent Activity

Donate For Us

PHP SQLite3 Paginator based on MySQL giving errors

Tags:

php

sqlite

mysql

pagination

zeddex

3 Answers

Dhruv Saxena

e4c5

Rick James

Related questions

Recent Activity

Donate For Us