基于MySQL的PHP SQLite3 Paginator给出了错误

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.

我正在使用SQLite3,我正在尝试调整我发现的Paginator脚本。在更改看起来像是MYSQL命令之后,它有点起作用,但是它没有显示正确数量的项目,并且似乎与它给出的结果不同。

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

注意:未定义的索引:第27行的C:\ xampp \ htdocs \ Projects \ index.php中的视频

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.

感谢建议修复删除“$ results [] = [];”索引页面现在显示没有错误,但它没有按预期工作。

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:

我所期望的是,由于$ limit设置为10,它将列出10个字符串并显示«1»分页器按钮,如果$ limit设置为5则显示5和“1,2”作为按钮但是当前它只显示1个字符串:

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.

同时单击下一个和页面按钮会使其产生意外结果,而不是作为页面列表的末尾,如果给出一些随机的。

3 个解决方案

#1

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

除了$ results [] = []位(在返回的结果中添加了一个空行)之外,代码中存在一个非常非常小的问题:

When method getData() is invoked from index.php, we have:

当从index.php调用方法getData()时,我们有:
```
$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) :

这意味着,即使看起来$ limit参数设置为至少获取4条记录,它也只能默认为1条记录。可以交换的地方肯定由一个人自行决定。但是,我碰巧在定义中做了修复。另外,在方法本身的开头声明$ 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;
}

#2

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.

注意到您的更新。请看看Dhruv的答案,他似乎已经解决了其余的问题。

#3

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.

当用户翻阅页面时添加/删除记录时,使用OFFSET的分页存在固有问题。此外,当数据集很大时,OFFSET效率很低。

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

所以,我建议放弃任何使用OFFSET的东西。相反,“记住你离开的地方”并使用类似“下一页”的内容:

WHERE id > $left_off
ORDER BY id
LIMIT 10

More details

#1

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

除了$ results [] = []位(在返回的结果中添加了一个空行)之外,代码中存在一个非常非常小的问题:

When method getData() is invoked from index.php, we have:

当从index.php调用方法getData()时,我们有:
```
$results    = $Paginator->getData( $page, $limit );
```
Whereas, at the definition, it is:

然而,在定义中,它是:
```
public function getData( $limit = 10, $page = 1 ){
}
```

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

#2

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.

注意到您的更新。请看看Dhruv的答案,他似乎已经解决了其余的问题。

#3

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.

当用户翻阅页面时添加/删除记录时,使用OFFSET的分页存在固有问题。此外,当数据集很大时,OFFSET效率很低。

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

所以,我建议放弃任何使用OFFSET的东西。相反,“记住你离开的地方”并使用类似“下一页”的内容:

WHERE id > $left_off
ORDER BY id
LIMIT 10

More details

秒客网

基于MySQL的PHP SQLite3 Paginator给出了错误

3 个解决方案

#1

#2

#3

#1

#2

#3

相关文章

基于MySQL的PHP​​ SQLite3 Paginator给出了错误

3 个解决方案

#1

#2

#3

#1

#2

#3

相关文章

基于MySQL的PHP SQLite3 Paginator给出了错误