php mysqli 准备好的语句

Question

嘿嘿，我有一个快点的。有什么方法可以将变量包含到准备好的查询中吗？例如：

$sql = "SELECT id, title, author, LEFT(description, 40) AS excerpt, 
               image_small, image_med, date 
        FROM posts 
        ORDER BY id DESC 
        LIMIT $start, $postsPerPage";

$result = $connect->prepare($sql) or die ('error');
$result->execute();
$result->bind_result($id, $title, $author, $excerpt, $image_small, $image_med, $date);

谢谢！

Answer 1

您需要以下内容：

$start = 1; $postsPerPage = 1;
$sql = "SELECT id, title, author, LEFT(description, 40) AS excerpt, 
               image_small, image_med, date 
        FROM posts 
        ORDER BY id DESC 
        LIMIT ?, ?";

$stmt = $connect->prepare($sql) or die ('error');
$stmt->bind_param('ii', $start, $postsPerPage);
$stmt->execute();
$stmt->bind_result($id, $title, $author, $excerpt, $image_small, $image_med, $date);

while($stmt->fetch()) {
  printf('<h1>%s</h1><p>%s <small> by %s on %s</small></p>',
    htmlspecialchars($title),
    htmlspecialchars($excerpt),
    htmlspecialchars($author),
    htmlspecialchars($date)
  );
}

这会将两个问号绑定到 $start 和 $postsPerPage 的整数 (i) 值。不要在准备好的语句中直接使用变量，因为这会破坏准备好的语句的全部目的（除了消除解析时间之外）

Answer 2

• 在 SQL 中您想要变量值的位置使用问号作为占位符。
• 使用 mysqli_stmt::bind_param 将值绑定到占位符。

Answer 3

如果我没记错的话，您必须使用 bindParam 并将查询中的变量替换为问号，

$sql = "SELECT id, title, author, LEFT(description, 40) AS excerpt, 
               image_small, image_med, date 
        FROM posts 
        ORDER BY id DESC 
        LIMIT ?, ?";

$result = $connect->prepare($sql) or die ('error');
$result->bindParam(1, $start);
$result->bindParam(2, $postsPerPage);

您可以在 http://php.net/manual/en/pdo.prepared-statements.php