This article explores various techniques for fetching and displaying data using MySQLi, including examples and best practices to ensure efficient and secure data handling.
Setting Up the Environment
Before diving into fetching and displaying data, ensure you have a MySQL database and a PHP environment set up. For this example, let’s assume you have a database named mydatabase and a table named users with the following schema:
Database Table Schema
CREATE DATABASE mydatabase;
USE mydatabase;
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
);
Establishing a MySQLi Connection
To interact with the MySQL database, you need to establish a connection using MySQLi. This connection will be used to execute queries and fetch results.
Example: Connecting to MySQLi
<?php
// Database connection parameters
$servername = "localhost";
$username = "root";
$password = "";
$database = "mydatabase";
// Create a new MySQLi instance
$conn = new mysqli($servername, $username, $password, $database);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
?>
Fetching Data from the Database
Once you have established a connection, you can execute queries to retrieve data from the database. MySQLi provides several methods to fetch query results, including mysqli_query(), mysqli_fetch_assoc(), and mysqli_fetch_all().
Using mysqli_query() and mysqli_fetch_assoc()
Example: Fetching and Displaying Data
<?php
// Include the database connection
include 'db_connect.php'; // Assumes db_connect.php contains the connection code
// Query to fetch all users
$sql = "SELECT id, username, email, created_at FROM users";
$result = $conn->query($sql);
// Check if there are results
if ($result->num_rows > 0) {
// Output data of each row
while($row = $result->fetch_assoc()) {
echo "ID: " . $row["id"]. " - Username: " . $row["username"]. " - Email: " . $row["email"]. " - Created At: " . $row["created_at"]. "<br>";
}
} else {
echo "0 results";
}
// Close the connection
$conn->close();
?>
Using mysqli_fetch_all()
If you prefer to fetch all results at once and process them later, use mysqli_fetch_all().
Example: Fetching All Data
<?php
// Include the database connection
include 'db_connect.php';
// Query to fetch all users
$sql = "SELECT id, username, email, created_at FROM users";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
// Fetch all results as an associative array
$data = $result->fetch_all(MYSQLI_ASSOC);
// Display the data
foreach ($data as $row) {
echo "ID: " . $row["id"]. " - Username: " . $row["username"]. " - Email: " . $row["email"]. " - Created At: " . $row["created_at"]. "<br>";
}
} else {
echo "0 results";
}
// Close the connection
$conn->close();
?>
Displaying Data in HTML Tables
Displaying data in a tabular format improves readability and user experience. Here’s how you can present query results in an HTML table.
Example: Displaying Data in an HTML Table
<?php
// Include the database connection
include 'db_connect.php';
// Query to fetch all users
$sql = "SELECT id, username, email, created_at FROM users";
$result = $conn->query($sql);
echo "<table border='1'>
<tr>
<th>ID</th>
<th>Username</th>
<th>Email</th>
<th>Created At</th>
</tr>";
// Check if there are results
if ($result->num_rows > 0) {
// Output data of each row
while($row = $result->fetch_assoc()) {
echo "<tr>
<td>" . $row["id"]. "</td>
<td>" . $row["username"]. "</td>
<td>" . $row["email"]. "</td>
<td>" . $row["created_at"]. "</td>
</tr>";
}
} else {
echo "<tr><td colspan='4'>0 results</td></tr>";
}
echo "</table>";
// Close the connection
$conn->close();
?>
Handling Pagination
For large datasets, pagination helps manage and display data in smaller chunks. Here’s how you can implement pagination.
Implementing Pagination Logic
Example: Pagination Implementation
<?php
// Include the database connection
include 'db_connect.php';
// Pagination parameters
$limit = 10; // Number of records per page
$page = isset($_GET['page']) ? (int)$_GET['page'] : 1;
$offset = ($page - 1) * $limit;
// Query to fetch users with pagination
$sql = "SELECT id, username, email, created_at FROM users LIMIT ? OFFSET ?";
$stmt = $conn->prepare($sql);
$stmt->bind_param("ii", $limit, $offset);
$stmt->execute();
$result = $stmt->get_result();
// Display data
echo "<table border='1'>
<tr>
<th>ID</th>
<th>Username</th>
<th>Email</th>
<th>Created At</th>
</tr>";
if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
echo "<tr>
<td>" . $row["id"]. "</td>
<td>" . $row["username"]. "</td>
<td>" . $row["email"]. "</td>
<td>" . $row["created_at"]. "</td>
</tr>";
}
} else {
echo "<tr><td colspan='4'>0 results</td></tr>";
}
echo "</table>";
// Pagination links
$total_result = $conn->query("SELECT COUNT(*) AS total FROM users")->fetch_assoc();
$total_pages = ceil($total_result['total'] / $limit);
for ($i = 1; $i <= $total_pages; $i++) {
echo "<a href='?page=$i'>$i</a> ";
}
// Close the connection
$stmt->close();
$conn->close();
?>
<?php
// Include the database connection
include 'db_connect.php';
// Search and pagination parameters
$limit = 10;
$page = isset($_GET['page']) ? (int)$_GET['page'] : 1;
$offset = ($page - 1) * $limit;
$search = isset($_GET['search']) ? $_GET['search'] : '';
// Query to fetch users with search and pagination
$sql = "SELECT id, username, email, created_at FROM users WHERE username LIKE ? LIMIT ? OFFSET ?";
$stmt = $conn->prepare($sql);
$search_term = "%$search%";
$stmt->bind_param("sii", $search_term, $limit, $offset);
$stmt->execute();
$result = $stmt->get_result();
// Display data
echo "<table border='1'>
<tr>
<th>ID</th>
<th>Username</th>
<th>Email</th>
<th>Created At</th>
</tr>";
if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
echo "<tr>
<td>" . $row["id"]. "</td>
<td>" . $row["username"]. "</td>
<td>" . $row["email"]. "</td>
<td>" . $row["created_at"]. "</td>
</tr>";
}
} else {
echo "<tr><td colspan='4'>0 results</td></tr>";
}
echo "</table>";
// Search form
echo '<form method="get">
<input type="text" name="search" value="' . htmlspecialchars($search) . '" placeholder="Search by username">
<button type="submit">Search</button>
</form>';
// Pagination links
$total_result = $conn->query("SELECT COUNT(*) AS total FROM users WHERE username LIKE '$search_term'")->fetch_assoc();
$total_pages = ceil($total_result['total'] / $limit);
for ($i = 1; $i <= $total_pages; $i++) {
echo "<a href='?page=$i&search=" . urlencode($search) . "'>$i</a> ";
}
// Close the connection
$stmt->close();
$conn->close();
?>
Implementing Search Functionality
Adding a search feature allows users to find specific data easily. Here’s how to integrate search functionality with pagination.
Example: Search and Pagination
<?php
// Include the database connection
include 'db_connect.php';
// Search and pagination parameters
$limit = 10;
$page = isset($_GET['page']) ? (int)$_GET['page'] : 1;
$offset = ($page - 1) * $limit;
$search = isset($_GET['search']) ? $_GET['search'] : '';
// Query to fetch users with search and pagination
$sql = "SELECT id, username, email, created_at FROM users WHERE username LIKE ? LIMIT ? OFFSET ?";
$stmt = $conn->prepare($sql);
$search_term = "%$search%";
$stmt->bind_param("sii", $search_term, $limit, $offset);
$stmt->execute();
$result = $stmt->get_result();
// Display data
echo "<table border='1'>
<tr>
<th>ID</th>
<th>Username</th>
<th>Email</th>
<th>Created At</th>
</tr>";
if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
echo "<tr>
<td>" . $row["id"]. "</td>
<td>" . $row["username"]. "</td>
<td>" . $row["email"]. "</td>
<td>" . $row["created_at"]. "</td>
</tr>";
}
} else {
echo "<tr><td colspan='4'>0 results</td></tr>";
}
echo "</table>";
// Search form
echo '<form method="get">
<input type="text" name="search" value="' . htmlspecialchars($search) . '" placeholder="Search by username">
<button type="submit">Search</button>
</form>';
// Pagination links
$total_result = $conn->query("SELECT COUNT(*) AS total FROM users WHERE username LIKE '$search_term'")->fetch_assoc();
$total_pages = ceil($total_result['total'] / $limit);
for ($i = 1; $i <= $total_pages; $i++) {
echo "<a href='?page=$i&search=" . urlencode($search) . "'>$i</a> ";
}
// Close the connection
$stmt->close();
$conn->close();
?>
Best Practices and Considerations
Secure Your Code
Always use prepared statements to prevent SQL injection. Validate and sanitize user inputs to ensure data integrity and security.
Handle Errors Gracefully
Implement error handling to manage and log database connection issues or query errors.
Example: Error Handling
if (!$result) {
die("Error: " . $conn->error);
}
Optimize Queries
Optimize your SQL queries for performance, especially when dealing with large datasets. Indexes can significantly speed up search and retrieval operations.
Ensure Cross-Browser Compatibility
Ensure that your HTML and CSS code is compatible with different browsers for consistent user experiences.
Conclusion
Displaying query results on a web page using MySQLi involves fetching data from a MySQL database and presenting it in a user-friendly format. By following the techniques outlined in this article, including connection handling, data fetching, pagination, search functionality, and best practices, you can build robust and interactive web applications that efficiently manage and display data. Whether you are building a simple data display or a complex application, mastering these techniques will help you create effective and secure web solutions.