Application of mysqli_stmt::__construct in executing complex SQL queries

In PHP, using the mysqli_stmt::__construct constructor can create a preprocessed statement object, which can perform SQL queries more safely and efficiently, especially when facing complex SQL statements. This article will introduce in detail how to use mysqli_stmt::__construct and its related methods to implement practical techniques and applications in complex SQL queries to improve the performance and security of the code.

1. Introduction to mysqli_stmt::__construct

mysqli_stmt::__construct is used to initialize a preprocessing statement object. Its constructor is defined as follows:

 public mysqli_stmt::__construct(mysqli $mysql, string $query)

• $mysql : mysqli connection object

• $query : SQL query statement, usually with placeholders

After calling, a prepared mysqli_stmt object will be returned, which will facilitate subsequent binding parameters and execution statements.

2. Advantages of preprocessing statements under complex SQL queries

• Prevent SQL injection : Use placeholders and parameter binding to avoid the risks brought by splicing strings.

• Improve performance : Preprocessing statement execution plan cache to reduce SQL parsing overhead.

• Clear logic : Structured SQL and parameter separation, making the code easier to maintain.

• Supports multiple parameter types : dynamically bind different data types to improve flexibility.

3. Practical skills and application demonstrations

Suppose we have a complex SQL query that contains multiple table JOINs, multiple conditional filtering, and requires dynamic parameters to be passed in.

3.1 Preprocessing complex queries and binding multiple parameters

 $mysqli = new mysqli("gitbox.net", "user", "password", "database");

$sql = "
    SELECT u.id, u.username, p.title, p.created_at 
    FROM users u
    JOIN posts p ON u.id = p.user_id
    WHERE u.status = ? AND p.created_at > ? AND p.category IN (?, ?, ?)
    ORDER BY p.created_at DESC
    LIMIT ?
";

$stmt = new mysqli_stmt($mysqli, $sql);

// Bind parameters：s = string, i = integer
// Parameter order corresponds toSQLPlaceholder：u.status (string), p.created_at (stringdate), p.category (3A string), limit (int)
$status = 'active';
$date = '2024-01-01';
$cat1 = 'tech';
$cat2 = 'news';
$cat3 = 'life';
$limit = 10;

$stmt->bind_param("ssssssi", $status, $date, $cat1, $cat2, $cat3, $limit);

// Execute a query
$stmt->execute();

// Get results
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
    echo $row['username'] . " - " . $row['title'] . " (" . $row['created_at'] . ")\n";
}

3.2 Dynamic IN parameter processing

mysqli_stmt does not support directly binding array type parameters. If the number of parameters in the IN statement changes dynamically, placeholders need to be constructed dynamically:

 $categories = ['tech', 'news', 'life', 'sports'];
$placeholders = implode(',', array_fill(0, count($categories), '?'));

$sql = "
    SELECT * FROM posts 
    WHERE category IN ($placeholders)
";

$stmt = new mysqli_stmt($mysqli, $sql);

// Dynamically generate type strings，All are string types
$types = str_repeat('s', count($categories));

// use“Reference call”Bind parameters
$stmt->bind_param($types, ...$categories);
$stmt->execute();
$result = $stmt->get_result();

This method flexibly deals with the situation where the number of IN condition parameters is not fixed.

4. Error handling and debugging

When mysqli_stmt::__construct fails, you can get detailed error information through $mysqli->error and $stmt->error .

 if (!$stmt) {
    die("Preprocessing statement creation failed: " . $mysqli->error);
}

if (!$stmt->execute()) {
    die("Execution failed: " . $stmt->error);
}

In addition, enable the mysqli debugging function:

 mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

Helps quickly locate problems in complex SQL.

5. Integrate data consistency in transaction management

In complex SQL queries, especially when multiple statements are updated or queried, ensuring atomicity in combination with transactions is an important means.

 $mysqli->begin_transaction();

try {
    $stmt1 = new mysqli_stmt($mysqli, "UPDATE accounts SET balance = balance - ? WHERE id = ?");
    $stmt1->bind_param("di", $amount, $from_id);
    $stmt1->execute();

    $stmt2 = new mysqli_stmt($mysqli, "UPDATE accounts SET balance = balance + ? WHERE id = ?");
    $stmt2->bind_param("di", $amount, $to_id);
    $stmt2->execute();

    $mysqli->commit();
} catch (Exception $e) {
    $mysqli->rollback();
    echo "Transaction failed: " . $e->getMessage();
}

6. Summary

• Building preprocessing statements using mysqli_stmt::__construct is a best practice for performing complex SQL queries.

• By binding parameters and dynamically constructing placeholders, you can flexibly handle multiple conditions and dynamic parameters.

• Combining error handling and transaction management ensures robust code and data security.

• Turn on mysqli debugging in time is conducive to quickly locate problems in complex SQL.

Mastering the above skills can help PHP developers handle complex database operations efficiently and securely.