php - 使用 php pdo mysql 分页

Question

我试图为一个简单的数据库表设置分页，该表从数据库表中选择所有内容并将其显示在用户的表中，但是我一直遇到这个问题。我遵循了一个说明如何设置分页的教程，但我一直遇到 SQLSTATE[42000]: Syntax error or access violation: 1327 Undeclared variable: $startrow 错误，我不确定为什么。

这是代码:

<?php

        include "db_conx.php";

            //check if the starting row variable was passed in the URL or not
if (!isset($_GET['startrow']) or !is_numeric($_GET['startrow'])) {
  //we give the value of the starting row to 0 because nothing was found in URL
  $startrow = 0;
//otherwise we take the value from the URL
} else {
  $startrow = (int)$_GET['startrow'];
}
        try {

            $db_conx = new PDO("mysql:host=$mysql_hostname;dbname=$mysql_dbname", $mysql_username, $mysql_password);

            $db_conx->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            $stmt = $db_conx->prepare('SELECT * FROM role_type LIMIT $startrow, 3');
            $stmt->execute();
            $roles = $stmt->fetchAll(PDO::FETCH_ASSOC);
        }
        catch(Exception $e)
        {
            die ("Could not connect to the database $mysql_dbname :" . $e->getMessage());
        }
        ?>

        <h4><center>Manage Role Types</center></h4>

        <div class="container">
            <div class = "container-fluid">
                <div id = "table_container" style="width:auto; margin-top:50px;" class="mainbox col-md-6">
                    <div class="row clearfix">
                        <div class="col-md-12">
                            <table class="table table-bordered table-hover" id="tab_logic">
                                <thead>
                                    <tr >
                                        <th class="text-center">
                                            Role Type Code
                                        </th>
                                        <th class="text-center">
                                            Role Title
                                        </th>
                                    </tr>
                                </thead>
                                <tbody>
                                    <!-- populating the table with information from mysql database -->
                                    <?php foreach ($roles as $row) {
                                        echo "<tr><td>";
                                        echo $row['role_type_code'];
                                        echo "</td><td>";
                                        echo $row['role_title'];
                                        echo "</td><td>";
                                        echo '<p data-placement="top"
                                        data-toggle="tooltip"
                                        style="margin-left:5px" 
                                        title="Edit">';
                                        echo '<button class="btn btn-primary btn-xs" 
                                        data-title="Edit" 
                                        data-toggle="modal" 
                                        data-id="'; 
                                        echo $row['role_type_code']; 
                                        echo '" data-role="'; 
                                        echo $row['role_title']; 
                                        echo '" data-target="#editModal">';
                                        echo '<span class="glyphicon glyphicon-edit" />';
                                        echo '</button></p>';

                                        echo "</td>";
                                        echo "</td><td>";
                                        echo '<p data-placement="top"
                                        data-toggle="tooltip"
                                        style="margin-left:5px"
                                        title="Delete">';
                                        echo '<button class="btn btn-danger btn-xs" 
                                        data-title="Delete" 
                                        data-toggle="modal" 
                                        data-id="'; 
                                        echo $row['role_type_code']; 
                                        echo '" data-role="'; 
                                        echo $row['role_title']; 
                                        echo '" data-target="#deleteModal">';
                                        echo '<span class="glyphicon glyphicon-trash" />';
                                        echo '</button></p>';
                                        echo "</tr>"; }
                                        ?>
                                    </tbody>
                                </table>

                                <?PHP
//now this is the link..
                            echo '<a href="'.$_SERVER['PHP_SELF'].'?startrow='.($startrow+10).'">Next</a>';
                            $prev = $startrow - 10;

//only print a "Previous" link if a "Next" was clicked
                            if ($prev >= 0)
                                echo '<a href="'.$_SERVER['PHP_SELF'].'?startrow='.$prev.'">Previous</a>';
                            ?>

                            </div>
                        </div>

不知道为什么。我试过使用一个值代替变量，但它不起作用。任何人都知道我该如何克服这个问题。

谢谢

Answer 1

您不能在单引号字符串中嵌入 php 变量 http://php.net/language.types.string

所以你的查询是错误的

$stmt = $db_conx->prepare('SELECT * FROM role_type LIMIT $startrow, 3');

应该是

$stmt = $db_conx->prepare("SELECT * FROM role_type LIMIT $startrow, 3");