Getting Started with Prepared Statements in MySQL

Prepared statements are an essential tool for writing secure and efficient SQL queries. In this article, we will explore how to use prepared statements with mysqli, the MySQLi extension in PHP.

Syntax Error: Non-Object Execution

The error you encounter, "Fatal error: Call to a member function execute() on a non-object," typically indicates that the $stmt variable is not properly initialized or an object. Here's how to correct it:

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

Ensure that you have a valid connection to the database before proceeding.

Binding Parameters

Prepared statements use parameter markers (e.g., ?) to represent input values. These values need to be bound to PHP variables before executing the statement. Here's an example:

$name = 'one';
$age = 1;
$stmt->bind_param('si', $name, $age);

In this example, we bind the name parameter as a string ('s') and the age parameter as an integer ('i').

Executing the Statement

Once the parameters are bound, you can execute the prepared statement:

$stmt->execute();

Handling Errors

Prepared statements provide better error handling than direct SQL queries. Use the mysqli_stmt::error method to retrieve error messages:

if ($stmt->error) {
    die("Error: " . $stmt->error);
}

Complete Example

Here's a complete example of inserting, selecting, and handling errors:

// Establish connection
$mysqli = new mysqli("localhost", "root", "root", "test");

// Prepare and bind parameters
$stmt = $mysqli->prepare("INSERT INTO users (name, age) VALUES (?,?)");
$stmt->bind_param('si', $name, $age);

// Insert multiple rows
$name = 'one';
$age = 1;
$stmt->execute();
$name = 'two';
$age = 2;
$stmt->execute();

// Prepare and execute select statement
$stmt = $mysqli->prepare("SELECT * FROM users");
$stmt->execute();

// Bind result
$result = $stmt->get_result();

// Process results
while ($row = $result->fetch_assoc()) {
    echo $row['name'] . ", " . $row['age'] . "
\n";
}

// Handle errors
if ($stmt->error) {
    die("Error: " . $stmt->error);
}

By using prepared statements, you can prevent SQL injection attacks and write more robust and efficient SQL queries.