How to use mysqli prepared statements?

Question

I am trying out prepared statements, but the below code is not working. I am getting the error:

Fatal error: Call to a member function execute() on a non-object in /var/www/prepared.php on line 12

<?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 one 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 a complete example on prepared statements from connection to insertion to selection with error handling?

Answer 1

From the mysqli::prepare docs:

The parameter markers must be bound to application variables using mysqli_stmt_bind_param() and/or mysqli_stmt_bind_result() before executing the statement or fetching rows.

bind_param docs.

i.e.:

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

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

// bind parameters. I'm guessing 'string' & 'integer', but read documentation.
$stmt->bind_param('si', $name, $age);

// *now* we can execute
$stmt->execute();

Answer 2

Also do I need to use mysqli for prepared statement. Can anyone point me to a complete example on prepared statement from connection to insertion to selection with error handling

You can also use PDO which I much prefer. In fact, it looks like you're confusing PDO and Mysqli in your code example.

$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 with mysqli you don't have to call a separate binding function, although that feature is available if you prefer/want/need to use it.

Another fun thing about PDO is named placeholders which can be much 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));