Learn how to use mysqli prepared statements for database operations

Question

I'm trying to use prepared statements, but the code below doesn't work. I received the following error:

Fatal error: Calling non-object member function execute() on line 12 of /var/www/prepared.php

<?php

    $mysqli = new mysqli("localhost", "root", "root", "test");
    if ($mysqli->connect_errno) {
        echo "Failed to connect to MySQL:" . $mysqli->connect_error;
    }

    $stmt = $mysqli->prepare("INSERT INTO users (name, age) VALUES (?,?)");

    //Insert a row
    $stmt->execute(array('one',1));

    // Insert another row with different values
    $stmt->execute(array('two',1));
?>

Also, do I need to use mysqli for prepared statements? Can anyone point me to an example of a complete prepared statement, including joins, inserts and selects, and error handling?

Answer 1

You can also use PDO which I prefer. In fact, in your code example, you seem to be confusing PDO and Mysqli.

$db = new PDO($dsn, $user, $pass);
$stmt = $db->prepare("INSERT INTO users (name, age) VALUES (?,?)");
$stmt->execute(array($name1, $age1));
$stmt->execute(array($name2, $age2));

Unlike mysqli, you don't need to call a separate bind function, although the functionality is available if you like/want/need to use it.

Another interesting thing about PDO is named placeholders, which may be less confusing in complex queries:

$db = new PDO($dsn, $user, $pass);
$stmt = $db->prepare("INSERT INTO users (name, age) VALUES (:name,:age)");
$stmt->execute(array(':name' => $name1, ':age' => $age1));
$stmt->execute(array(':name' => $name2, ':age' => $age2));

Answer 2

Frommysqli::prepareDocumentation:

bind_paramDocumentation.

Right now:

$name = 'one';
$age  = 1;

$stmt = $mysqli->prepare("INSERT INTO users (name, age) VALUES (?,?)");

// 绑定参数。我猜测是'string'和'integer'，但请阅读文档。
$stmt->bind_param('si', $name, $age);

// *现在*我们可以执行
$stmt->execute();