如何在MySQLI中使用预备语句?

$mysqli = new mysqli("localhost", "root", "", "test");
$mysqli->query('PREPARE mid FROM "SELECT name FROM test_user WHERE id = ?"');
// working code start
//$res = $mysqli->query('PREPARE mid FROM "SELECT name FROM test_user"  ');
//$res = $mysqli->query( 'EXECUTE mid;') or die(mysqli_error($mysqli));
// working code end..
$res = $mysqli->query( 'EXECUTE mid 1;') or die(mysqli_error($mysqli));

while($resu = $res->fetch_object()) {
    echo '<br>' .$resu->name;
}

错误:

您的SQL语法有误;检查与您的MySQL服务器版本相对应的手册,以在第1行的'1'附近使用正确的语法

my php version is PHP Version 5.3.0 and mysql

mysqlnd 5.0.5-dev - 081106 - $Revision: 1.3.2.27 $ 

I got the correct result with out using the where clause

评论
  • 释(怀
    释(怀 回复

    http://dev.mysql.com/doc/refman/5.1/en/execute.html

    您需要在EXECUTE语句中具有using子句。因此,您可以执行以下操作:

    $mysqli = new mysqli("localhost", "root", "", "test");
    $mysqli->query('PREPARE mid FROM "SELECT name FROM test_user WHERE id = ?"');
    $mysqli->query('SET @var1 = 1;');
    $res = $mysqli->query( 'EXECUTE mid USING @var1;') or die(mysqli_error($mysqli));
    
  • yalias
    yalias 回复

    对SELECT查询使用prepare函数:

    http://php.net/manual/en/mysqli.prepare.php

    /* create a prepared statement */
    if ($stmt = $mysqli->prepare("SELECT District FROM City WHERE Name=?")) {
    
        /* bind parameters for markers */
        $stmt->bind_param("s", $city);
    
        /* execute query */
        $stmt->execute();
    
        /* bind result variables */
        $stmt->bind_result($district);
    
        /* fetch value */
        $stmt->fetch();
    
        /* close statement */
        $stmt->close();
    }