stmt_init with type binding: How to correctly use type strings in bind_param

In PHP, the mysqli extension provides powerful capabilities to interact with MySQL databases. stmt_init and bind_param are two commonly used functions for prepared statements and parameter binding. However, many developers are prone to common errors when using these two functions, especially when binding parameter types. This article will dive into how to use these functions correctly, especially how to ensure the correct use of type strings to avoid potential errors.

1. Basic usage of mysqli::stmt_init and bind_param

The mysqli::stmt_init method is used to initialize a preprocessing statement, which returns a mysqli_stmt object through which query and binding parameters can be executed. The bind_param method is used to bind variables to placeholders in preprocessing statements (usually ? ). When binding, you need to specify the data type for each parameter.

Sample code:

 <?php
$mysqli = new mysqli("localhost", "username", "password", "database");

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

$stmt = $mysqli->stmt_init();

if ($stmt->prepare("INSERT INTO users (name, age) VALUES (?, ?)")) {
    $name = "John Doe";
    $age = 25;

    // Bind parameters
    $stmt->bind_param("si", $name, $age); // 's' Represents a string，'i' Indicates integers

    // Execution statement
    $stmt->execute();
    
    echo "Record inserted successfully!";
} else {
    echo "Error: " . $stmt->error;
}

$stmt->close();
$mysqli->close();
?>

In this example, we use bind_param to bind the $name and $age variables to the placeholder in the SQL query. The type string "si" means that $name is a string ( s ) and $age is an integer ( i ).

2. Correct usage of binding type strings

The first parameter of bind_param is a string that specifies the type of each bound parameter. Common types are:

• i : integer

• d : double precision floating point number (double)

• s : string (string)

• b : Binary data (blob)

It is important to make sure you specify the correct type for each parameter, as wrong type binding can cause query failures or data to be incorrectly inserted into the database.

Example error:

 $stmt->bind_param("si", $age, $name); // mistake，The order is wrong

In this example, the binding order is wrong, because $age is an integer that should be used with type 'i' , and $name is a string that should be used with type 's' .

3. Resolve common type binding errors

1. Type mismatch: MySQL returns an error if you try to bind a variable of string type to an integer type placeholder, or bind an integer to a string type placeholder. Make sure you understand the data type for each parameter and use the correct type string in bind_param .

2. Initialize variables before using bind_param : Before using bind_param to bind variables, make sure you have initialized all variables. Uninitialized variables will cause PHP to report an error.

3. Prevent SQL Injection: Using preprocessed statements and parameter bindings not only prevent SQL injection, but also ensures the correctness of data types. In this way, MySQL can handle it correctly even if the data entered by the user does not meet the expected type.

4. m66.net example and further optimization

In actual development, we often pass URLs and other data as parameters into SQL queries. Here is an example of processing URLs, where we replace all domain names with m66.net to ensure data consistency.

Sample code: