The correct way to handle NULL values in stmt_init

M66 2025-05-29

When interacting with MySQL using PHP, the mysqli extension provides a convenient way to execute preprocessing statements, where mysqli::stmt_init is a common function used to initialize a preprocessing statement object. However, developers may encounter some challenges when handling NULL values in databases, because NULL values have special handling methods in MySQL and PHP.

1. Understand the mysqli::stmt_init function

First, mysqli::stmt_init is a function used to initialize the mysqli_stmt object and returns a mysqli_stmt instance. It is often used to create and execute preprocessing statements, which helps prevent SQL injection attacks and improve query efficiency.

 <?php
$mysqli = new mysqli("localhost", "user", "password", "database");
$stmt = $mysqli->stmt_init();
?>

2. Use preprocessing statements to bind parameters

The mysqli_stmt object created by mysqli::stmt_init can be used to bind input parameters using the bind_param function. When binding parameters, developers need to specify the type of parameter, common types include:

i represents an integer
d represents a floating point number
s means a string
b represents binary data

In some cases, the parameter value may be NULL , which needs special treatment at this time.

3. How to handle NULL values?

In MySQL, NULL is a special value, so you must be careful when passing NULL directly to the database. By default, when using bind_param , if a parameter value is NULL , it will not be automatically converted to SQL NULL . We need to perform special processing through bind_param .

3.1 Passing NULL value using bind_param

Suppose we need to insert a record and some fields have NULL values. When binding parameters, if the value of a field is NULL , we need to make sure that NULL is passed when using bind_param , and the type is set to i , d , or s , and an additional variable is used to explicitly indicate whether the parameter is NULL .

 <?php
// Initialize the connection
$mysqli = new mysqli("localhost", "user", "password", "database");

// Create a preprocessing statement
$stmt = $mysqli->stmt_init();

// PrepareSQLQuery
$sql = "INSERT INTO users (name, age, email) VALUES (?, ?, ?)";

if ($stmt->prepare($sql)) {
    $name = "John Doe";
    $age = null;  // NULL value
    $email = "john.doe@m66.net";

    // for NULL value使用特殊绑定
    $stmt->bind_param("sis", $name, $age, $email);

    // 执行Query
    $stmt->execute();
}
?>

3.2 Special handling of NULL values

To correctly pass NULL values in bind_param , PHP provides support for NULL parameters, but this is limited to certain data types such as strings ( s ) and integers ( i ) and so on. For NULL values, we have to make sure that they are passed to the function and that the type needs to be specified.

 <?php
// example：deal with NULL value的代码
$null_value = null;
$stmt->bind_param("s", $null_value);
$stmt->execute();
?>

4. Things to note

Variable type when passing NULL : Make sure that the NULL matches the corresponding SQL type when passing NULL . For example, if the field is of integer type, the type of bind_param should be i .
Use isset() to determine : you can use isset() to determine whether a variable is NULL , so that the bound value can be set according to different situations.

 <?php
$age = null;
if (isset($age)) {
    $stmt->bind_param("i", $age);
} else {
    $stmt->bind_param("i", $null_value);
}
?>

5. Summary

When using mysqli::stmt_init and bind_param , special attention is required to be paid to handling NULL values. It is crucial to pass NULL values correctly and match the appropriate SQL type. If left untreated, it may cause query failure or undesired behavior. I hope that through the introduction of this article, it can help you correctly handle NULL values in actual development.

The correct way to handle NULL values ​​in stmt_init