MySQLi info - Get Query Information

The MySQLi info() method is a useful tool for developers who want to retrieve detailed information about the most recently executed query. This PHP method plays a crucial role in tracking and understanding how MySQL queries perform, offering insights into rows affected, warnings, and more. In this tutorial, you will learn how to use the info() method effectively to enhance your MySQLi-driven applications, track query performance, and debug effectively.

Prerequisites

Basic knowledge of PHP and MySQL.
MySQL database setup with appropriate credentials.
PHP installed with MySQLi extension enabled.
Access to a web server (local or remote) capable of running PHP scripts.
A sample table in your MySQL database to run queries against.

Setup Steps

Create a MySQL database and table for testing (if not already available):

CREATE DATABASE demo_db;

USE demo_db;

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100),
    age INT
);

INSERT INTO users (username, email, age) VALUES
('alice', 'alice@example.com', 28),
('bob', 'bob@example.com', 34),
('charlie', 'charlie@example.com', 22);

Connect to MySQL using MySQLi in PHP:

<?php
$mysqli = new mysqli('localhost', 'username', 'password', 'demo_db');

if ($mysqli->connect_error) {
    die('Connect Error (' . $mysqli->connect_errno . ') ' . $mysqli->connect_error);
}
?>

What is the MySQLi `info()` Method?

The info() method returns detailed information about the last executed query on the MySQLi connection. It especially provides data after queries modifying rows like UPDATE, DELETE, and INSERT. The exact content depends on the statement executed but often includes how many rows were matched, changed, warnings, etc.

Using the MySQLi `info()` Method: Step-by-step Example

Example: Tracking an UPDATE Query

<?php
// Execute UPDATE query
$sql = "UPDATE users SET age = age + 1 WHERE age < 30";
if ($mysqli->query($sql)) {
    // Get info about the last query
    $info = $mysqli->info;
    echo "Query Info: " . $info;
} else {
    echo "Error: " . $mysqli->error;
}
?>

What does this output show?

The `$mysqli->info` property returns a string similar to:

Rows matched: 2  Changed: 2  Warnings: 0

This indicates how many rows matched the query conditions, how many were actually changed, and how many warnings occurred.

Example: Using `info()` as a Method

The info() method is called without parentheses through the property $mysqli->info. However, in older PHP documentation, you might also see mysqli_info($mysqli) used as a function-style alternative with the same result.

Retrieve Information After DELETE Query

<?php
$sqlDelete = "DELETE FROM users WHERE age > 33";
if ($mysqli->query($sqlDelete)) {
    echo "Delete Query Info: " . $mysqli->info;
} else {
    echo "Error: " . $mysqli->error;
}
?>

Best Practices

Use $mysqli->info immediately after executing a query that changes data (UPDATE, DELETE, INSERT).
Check for errors with $mysqli->error before relying on info() output.
Document the meaning of info strings in your application logs for easier debugging.
Combine info() usage with other MySQLi methods like affected_rows for comprehensive analysis.
Avoid calling info() before any query has been executed or after SELECT queries, as the result may be empty or misleading.

Common Mistakes

Calling info() before executing any query returns NULL or an empty string.
Expecting info() to provide details after SELECT queries. It generally provides info only after data-changing queries.
Not checking for query errors before accessing info(), which can result in misinterpretation.
Using info() with multi-query statements without caution, as output might be inconsistent.
Confusing mysqli->info property with a method that requires parentheses (it is a property).

Interview Questions

Junior-Level Interview Questions

Q1: What does the mysqli->info property return?
A1: It returns information about the most recently executed data-changing query, such as rows matched, changed, or warnings.
Q2: Which types of queries show useful output from info()?
A2: Mainly UPDATE, DELETE, and INSERT queries.
Q3: Can you use info() to get details after a SELECT query?
A3: No, info() does not provide useful information after SELECT queries.
Q4: How do you access query info in MySQLi: with parentheses or directly?
A4: You access it as a property without parentheses: $mysqli->info.
Q5: Why is it important to check for errors before reading info()?
A5: Because if the query failed, info() may return misleading or empty info.

Mid-Level Interview Questions

Q1: How does mysqli->info differ from mysqli->affected_rows?
A1: affected_rows returns the number of rows affected; info returns a detailed string including matched rows, changed rows, and warnings.
Q2: How can you interpret the string returned by mysqli->info after an UPDATE?
A2: It usually shows "Rows matched", "Changed", and "Warnings", indicating how many rows matched the condition, how many changed, and if any warnings occurred.
Q3: Can you use info() after executing multiple queries with multi_query()?
A3: It’s not safe to rely on info() after multi_query() without processing each result separately.
Q4: What PHP property holds the last query’s info in procedural MySQLi usage?
A4: The function mysqli_info($link) returns this info in procedural style.
Q5: Is mysqli->info affected by client or server configurations?
A5: Yes, some MySQL server configurations or permissions can affect the level of detail returned by info().

Senior-Level Interview Questions

Q1: Describe how you would use mysqli->info to optimize batch updates.
A1: Use info() after batch UPDATEs to analyze how many rows matched vs. changed, identifying unnecessary updates to minimize.
Q2: How can the info() method assist in detecting silent warnings in SQL queries?
A2: The returned string includes a Warnings count; a value > 0 indicates warnings, prompting further investigation.
Q3: Can the format of the string returned by mysqli->info vary? How to handle it?
A3: Yes, it may vary with query type and server version; parsing should be flexible and consider different formats.
Q4: Discuss integration of info() output into logging systems for production environments.
A4: Capture and log info() results after critical updates/deletes to audit database state changes and diagnose performance issues.
Q5: How does mysqli->info interplay with transaction management?
A5: It shows info about queries within transactions, but you should ensure the transaction commits successfully to reflect final states.

FAQ

Q: Does info() work after INSERT queries?: A: Yes, it typically returns info about rows matched and warnings, but its usefulness is usually greatest for UPDATE and DELETE queries.
Q: What is the data type returned by $mysqli->info?: A: It returns a string containing details about the last executed query.
Q: Can I use info() with prepared statements?: A: info() reflects the last query executed, including prepared statements executed via MySQLi, but interpretation depends on the statement type.
Q: What happens if I call info() multiple times after one query?: A: The value remains the same until another query is executed on the connection.
Q: Is there an alternative to mysqli->info in PDO?: A: PDO does not provide an equivalent method; you rely on rowCount() and driver-specific attributes.

Conclusion

The MySQLi info() property is a practical way to gain granular insights into how your MySQL queries affect the database. It provides developers with valuable data on rows matched, changed, and warnings which are indispensable for debugging and optimizing SQL operations in PHP applications. By integrating this method correctly and following best practices, you can track query performance efficiently and maintain robust database interactions within your projects.

MySQLi info Method