This article explores how to perform basic SELECT operations using MySQLi, covering both the object-oriented and procedural approaches. We will also discuss the importance of prepared statements for secure querying.
Prerequisites
Before we begin, ensure you have the following:
1. A web server with PHP installed (such as Apache or Nginx).
2. MySQL server installed and running.
3. Basic knowledge of PHP and MySQL.
4. A sample database and table to work with.
For demonstration purposes, let’s assume we have a database named test_db with a table users:
CREATE DATABASE test_db;
USE test_db;
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO users (username, email) VALUES
('john_doe', 'john@example.com'),
('jane_doe', 'jane@example.com');
Basic SELECT Queries Using MySQLi
1. Object-Oriented Approach
Step 1: Establish a Connection
First, establish a connection to the MySQL database. Create a config.php file to store your database credentials:
<?php
// config.php
define('DB_SERVER', 'localhost');
define('DB_USERNAME', 'root');
define('DB_PASSWORD', '');
define('DB_NAME', 'test_db');
?>
<?php
require_once 'config.php';
$mysqli = new mysqli(DB_SERVER, DB_USERNAME, DB_PASSWORD, DB_NAME);
if ($mysqli->connect_error) {
die("Connection failed: " . $mysqli->connect_error);
}
?>
Step 2: Perform a Basic SELECT Query
To fetch data from the users table, use the following code:
<?php
require_once 'config.php';
$mysqli = new mysqli(DB_SERVER, DB_USERNAME, DB_PASSWORD, DB_NAME);
if ($mysqli->connect_error) {
die("Connection failed: " . $mysqli->connect_error);
}
$sql = "SELECT id, username, email, created_at FROM users";
$result = $mysqli->query($sql);
if ($result->num_rows > 0) {
while ($row = $result->fetch_assoc()) {
echo "ID: " . $row['id'] . " - Name: " . $row['username'] . " - Email: " . $row['email'] . " - Created At: " . $row['created_at'] . "<br>";
}
} else {
echo "0 results";
}
$mysqli->close();
?>
Explanation
$sql: The SQL query string.
$result: The result of the query, which is an object of the mysqli_result class.
$result->num_rows: The number of rows in the result set.
$row = $result->fetch_assoc(): Fetches a result row as an associative array.
2. Procedural Approach
Step 1: Establish a Connection
Create a connection using mysqli_connect():
<?php
require_once 'config.php';
$conn = mysqli_connect(DB_SERVER, DB_USERNAME, DB_PASSWORD, DB_NAME);
if ($conn === false) {
die("ERROR: Could not connect. " . mysqli_connect_error());
}
?>
Step 2: Perform a Basic SELECT Query
Use mysqli_query() to execute the query and fetch results:
<?php
require_once 'config.php';
$conn = mysqli_connect(DB_SERVER, DB_USERNAME, DB_PASSWORD, DB_NAME);
if ($conn === false) {
die("ERROR: Could not connect. " . mysqli_connect_error());
}
$sql = "SELECT id, username, email, created_at FROM users";
$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0) {
while ($row = mysqli_fetch_assoc($result)) {
echo "ID: " . $row['id'] . " - Name: " . $row['username'] . " - Email: " . $row['email'] . " - Created At: " . $row['created_at'] . "<br>";
}
} else {
echo "0 results";
}
mysqli_close($conn);
?>
3. Using Prepared Statements
Prepared statements are essential for preventing SQL injection, especially when dealing with user input.
Object-Oriented Approach
<?php
require_once 'config.php';
$mysqli = new mysqli(DB_SERVER, DB_USERNAME, DB_PASSWORD, DB_NAME);
if ($mysqli->connect_error) {
die("Connection failed: " . $mysqli->connect_error);
}
$stmt = $mysqli->prepare("SELECT id, username, email, created_at FROM users WHERE username = ?");
$stmt->bind_param("s", $username);
$username = 'john_doe';
$stmt->execute();
$result = $stmt->get_result();
if ($result->num_rows > 0) {
while ($row = $result->fetch_assoc()) {
echo "ID: " . $row['id'] . " - Name: " . $row['username'] . " - Email: " . $row['email'] . " - Created At: " . $row['created_at'] . "<br>";
}
} else {
echo "0 results";
}
$stmt->close();
$mysqli->close();
?>
Procedural Approach
<?php
require_once 'config.php';
$conn = mysqli_connect(DB_SERVER, DB_USERNAME, DB_PASSWORD, DB_NAME);
if ($conn === false) {
die("ERROR: Could not connect. " . mysqli_connect_error());
}
$sql = "SELECT id, username, email, created_at FROM users WHERE username = ?";
$stmt = mysqli_prepare($conn, $sql);
mysqli_stmt_bind_param($stmt, "s", $username);
$username = 'john_doe';
mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);
if (mysqli_num_rows($result) > 0) {
while ($row = mysqli_fetch_assoc($result)) {
echo "ID: " . $row['id'] . " - Name: " . $row['username'] . " - Email: " . $row['email'] . " - Created At: " . $row['created_at'] . "<br>";
}
} else {
echo "0 results";
}
mysqli_stmt_close($stmt);
mysqli_close($conn);
?>
Best Practices for SELECT Queries
Use Prepared Statements: Always use prepared statements to protect against SQL injection.
Check Connection Errors: Ensure your script properly handles connection errors.
Close Connections: Always close your database connections to free up resources.
Handle No Results: Gracefully handle cases where no results are returned.
Conclusion
Performing SELECT operations in MySQLi is a fundamental task in PHP development. By following the examples and best practices outlined in this article, you can efficiently and securely retrieve data from your MySQL database. Whether you prefer the object-oriented or procedural approach, MySQLi provides the tools you need to interact with your database effectively. Remember to use prepared statements to safeguard your application against SQL injection and always handle connection errors gracefully.