Prepared statements in MySQLi prevent SQL injection and improve efficiency by separating SQL logic from data inputs. To use them effectively: 1) connect to the database, 2) prepare the SQL statement with placeholders, 3) bind parameters correctly by type (s for string, i for integer, etc.), 4) execute the statement, 5) fetch results safely using bind_result() for SELECT queries, and 6) close the statement and connection properly. Common mistakes include incorrect parameter binding, failing to check if prepare() returns false, and not resetting variables during loops, but following best practices ensures secure and maintainable code.

當你're working with databases in PHP, using prepared statements with MySQLi is one of the best ways to keep your application secure and efficient. They help prevent SQL injection by separating SQL logic from data inputs.

Here’s how to use prepared statements with mysqli effectively.

What Are Prepared Statements?

Prepared statements are a feature in MySQLi that allows you to write a SQL query once and then execute it multiple times with different parameters. Instead of building queries by concatenating strings (which can be dangerous), you prepare a template and bind variables to placeholders.

This method ensures that user input is always treated as data, not executable code.

How to Use Prepared Statements with MySQLi

Using prepared statements involves a few clear steps:

1. Connect to the database
2. Prepare the SQL statement
3. Bind parameters
4. Execute the statement
5. Get results (if needed)
6. Close the statement

Let's walk through a basic example:

// 1. Connect to the database
$mysqli = new mysqli("localhost", "username", "password", "database");

// Check connection
if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}

// 2. Prepare the SQL statement
$stmt = $mysqli->prepare("INSERT INTO users (name, email) VALUES (?, ?)");

// 3. Bind parameters (s for string, s for string)
$stmt->bind_param("ss", $name, $email);

// 4. Set values and execute
$name = "John Doe";
$email = "john@example.com";
$stmt->execute();

echo "Record inserted successfully.";

// 6. Close the statement and connection
$stmt->close();
$mysqli->close();

You can also use this approach for SELECT, UPDATE, and DELETE operations.

Binding Parameters Correctly

One of the most important parts of using prepared statements is binding your variables correctly. The bind_param() function takes a type string followed by the variables.

• "s" for string
• "i" for integer
• "d" for double
• "b" for blob

Make sure the number and types of variables match the placeholders in your SQL query.

For example:

$stmt = $mysqli->prepare("SELECT id FROM users WHERE name = ? AND age > ?");
$stmt->bind_param("si", $name, $age);

In this case, the first parameter ($name) is a string, and the second ($age) is an integer.

If you get this wrong, your query might not behave as expected or could fail silently.

Fetching Results Safely

When retrieving data with a SELECT query, you’ll need to bind the result variables before fetching them. Here’s how:

$stmt = $mysqli->prepare("SELECT id, name FROM users WHERE id = ?");
$stmt->bind_param("i", $user_id);
$stmt->execute();
$stmt->bind_result($id, $name);

while ($stmt->fetch()) {
    echo "ID: $id, Name: $name <br>";
}

$stmt->close();

The bind_result() method binds the result columns to variables so you can access them safely in a loop.

This keeps everything clean and avoids messy array handling like with regular mysqli_query() calls.

Common Mistakes to Avoid

There are a few pitfalls people often run into when using prepared statements:

• Forgetting to check if prepare() returns false
• Not closing statements after use
• Binding incorrect types or too many/few variables
• Using the same variable names across multiple executions accidentally

A good practice is to always check the return value of prepare():

if (!$stmt = $mysqli->prepare("SELECT ...")) {
    die("Prepare failed: " . $mysqli->error);
}

Also, if you’re looping through multiple sets of data, make sure to reset or re-bind variables properly each time.

That's basically how to work with prepared statements using MySQLi. It’s a bit more code than old-school methods, but it's much safer and easier to maintain in the long run.

The above is the detailed content of How to use prepared statements with mysqli. For more information, please follow other related articles on the PHP Chinese website!