MySQLi query - Execute Query

Welcome to this detailed tutorial on the MySQLi query method in PHP. If you're looking to execute SQL queries directly through your PHP scripts, the query() method is a simple and effective way to do so using the MySQLi extension. This guide will walk you through the setup, usage examples, best practices, common mistakes, and interview questions—all specifically focused on the MySQLi query method.

Introduction to MySQLi query Method

The query() method is a core function of the MySQLi (MySQL Improved) extension in PHP, used to execute SQL statements against your MySQL database. It is especially useful for straightforward SQL queries such as SELECT, INSERT, UPDATE, and DELETE. The method returns a mysqli_result object for SELECT queries or TRUE/FALSE for other query types, enabling you to handle results or errors efficiently.

Prerequisites

• Basic understanding of PHP programming.
• MySQL server installed and running.
• A database created with relevant privileges to execute queries.
• MySQLi extension enabled in your PHP environment.

Setup Steps

1. Create a database: For this tutorial, assume a database named testdb exists.
2. Create a table: A simple table named users with fields id, name, and email.
3. Connect to the database: Use MySQLi procedural or OOP style to establish a connection.

Example Table Schema

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  email VARCHAR(50) NOT NULL UNIQUE
);

Using MySQLi query Method: Examples

1. Connecting to the MySQL Database

<?php
$servername = "localhost";
$username = "root";
$password = "your_password";
$dbname = "testdb";

$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";
?>

2. Executing a SELECT Query

<?php
$sql = "SELECT id, name, email FROM users";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // Output data of each row
    while ($row = $result->fetch_assoc()) {
        echo "ID: " . $row["id"] . " - Name: " . $row["name"] . " - Email: " . $row["email"] . "<br>";
    }
} else {
    echo "0 results";
}
?>

3. Executing an INSERT Query

<?php
$sql = "INSERT INTO users (name, email) VALUES ('John Doe', 'john@example.com')";

if ($conn->query($sql) === TRUE) {
    echo "New record created successfully";
} else {
    echo "Error: " . $conn->error;
}
?>

4. Executing UPDATE and DELETE Queries

<?php
// UPDATE example
$sql_update = "UPDATE users SET email='john.doe@example.com' WHERE name='John Doe'";
if ($conn->query($sql_update) === TRUE) {
    echo "Record updated successfully";
} else {
    echo "Error updating record: " . $conn->error;
}

// DELETE example
$sql_delete = "DELETE FROM users WHERE name='John Doe'";
if ($conn->query($sql_delete) === TRUE) {
    echo "Record deleted successfully";
} else {
    echo "Error deleting record: " . $conn->error;
}
?>

Best Practices

• Always Validate SQL: Sanitize user inputs to prevent SQL injection; avoid directly inserting variables into SQL queries.
• Check Return Values: Always check the return value of the query() method to handle errors gracefully.
• Use Prepared Statements for Complex or User-Input Queries: While query() is simple, use prepared statements with prepare() and bind_param() for security.
• Close Connections: Always close your database connections using $conn->close() to free resources.
• Handle Result Sets Properly: Free result objects when no longer needed with $result->free() to keep memory usage low.

Common Mistakes

• Not checking connection or query errors before using results.
• Using user input directly in queries without sanitization, opening doors to SQL injection attacks.
• Confusing the return types of query() for SELECT vs non-SELECT statements.
• Trying to use fetch_assoc() on a non-result boolean value when query returns TRUE or FALSE.
• Forgetting to close the MySQLi connection or free result sets, causing resource leaks.

Interview Questions

Junior-Level Questions

• Q1: What does the MySQLi query() method do?
  A: It executes an SQL query on the connected database and returns a mysqli_result object or boolean.
• Q2: What type of value does query() return for a SELECT statement?
  A: It returns a mysqli_result object on success.
• Q3: How do you handle an error returned by query() method?
  A: Check if the method returns FALSE and then use $conn->error to get the error message.
• Q4: Can query() execute INSERT or UPDATE queries?
  A: Yes, it executes any valid SQL query like INSERT, UPDATE, DELETE, etc.
• Q5: How do you connect to a MySQL database using MySQLi in PHP?
  A: By creating a new instance of mysqli with server, username, password, and database parameters.