Geek Answers Handbook

MySQL-based PHP SQLite3 Paginator gives errors

I am using SQLite3 and trying to adapt the found Paginator script. After changing commands like MYSQL, it works, but does not display the correct number of elements and seems to differ in the results it gives.

I also get the following error, which I'm not sure how to fix:

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

The code I use 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 a database and check the added data, 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 is it correct to work with this code?

Edit:

Thanks to the proposed removal fix, "$ results [] = [];" The index page now displays correctly, but it doesn’t work properly.

What I expected was that since the $ limit is set to 10, it will list 10 lines and show the “1” paginator buttons, and if the $ limit was set to 5, then 5 displays and “1,2” as buttons, however, it currently only displays one line, for example:

nwpj9_hrK_A

In addition, clicking the next button and the page button gives unexpected results, and not the end of the page list if it gives something random.

+7
php mysql pagination sqlite3
source share
3 answers

The reason you get an undefined index error is because of this code bit

  while ( $row = $rs->fetchArray() ) { $results[] = $row; } $results[] = []; /** this line here **/

You discard the results. Delete this line. Regarding the rest of the question, please provide additional information.

Your update has been marked. Please see the Answer from Dhruv , it seems to have addressed the rest of the problem.

+5
source share

Besides the bit $results[] = [] (which attached one empty line in the returned results), there is a very small problem with the code: very :

  • When the getData() method is called from index.php , we have:

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


  • While in the definition it is:

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

This means that while it seems that the $limit parameter is set to retrieve at least 4 records, by default only record 1 . Where it can be replaced, it is certainly left to the discretion. However, I managed to correct this definition. In addition, it would be nice to have a $results declaration at the beginning of the method itself (otherwise, we again get an undefined error when passing past the last page of displayed 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; }
+5
source share

Pagination using OFFSET has inherent problems when entries are added / deleted when the user views the pages. In addition, OFFSET quite inefficient when the data set is large.

So, I recommend giving up everything that uses OFFSET . Instead, “remember where you left off” and using something like the “next” page:

 WHERE id > $left_off ORDER BY id LIMIT 10

More details

+4
source share

More articles:


All Articles