Displaying Queries in Tables using PHP/MySQL
When fetching data from a MySQL database using PHP, we often want to display the results in a structured format like an HTML table. This ensures a neat and user-friendly representation of database records.
1. Steps to Display Query Results in an HTML Table
- Connect to MySQL Database
- Execute a SQL Query
- Fetch Results
- Loop through Results and Display in a Table
- Close the Connection
2. Example: Display Data in an HTML Table using MySQLi (Procedural Style)
<?php
// Step 1: Connect to the Database
$conn = mysqli_connect(“localhost”, “root”, “”, “my_database”);
// Check Connection
if (!$conn) {
die(“Connection failed: ” . mysqli_connect_error());
}
// Step 2: Write the SQL Query
$sql = “SELECT id, name, email FROM users”;
$result = mysqli_query($conn, $sql);
// Step 3: Display Data in a Table
if (mysqli_num_rows($result) > 0) {
echo “<table border=’1′ cellpadding=’10’ cellspacing=’0′>”;
echo “<tr><th>ID</th><th>Name</th><th>Email</th></tr>”;
while ($row = mysqli_fetch_assoc($result)) {
echo “<tr>”;
echo “<td>” . $row[“id”] . “</td>”;
echo “<td>” . $row[“name”] . “</td>”;
echo “<td>” . $row[“email”] . “</td>”;
echo “</tr>”;
}
echo “</table>”;
} else {
echo “No records found!”;
}
// Step 4: Close the Database Connection
mysqli_close($conn);
?>
3. Example: Display Data using MySQLi (Object-Oriented Style)
<?php
// Step 1: Establish Database Connection
$conn = new mysqli(“localhost”, “root”, “”, “my_database”);
// Check Connection
if ($conn->connect_error) {
die(“Connection failed: ” . $conn->connect_error);
}
// Step 2: Run the Query
$sql = “SELECT id, name, email FROM users”;
$result = $conn->query($sql);
// Step 3: Display Data in an HTML Table
if ($result->num_rows > 0) {
echo “<table border=’1′ cellpadding=’10’ cellspacing=’0′>”;
echo “<tr><th>ID</th><th>Name</th><th>Email</th></tr>”;
while ($row = $result->fetch_assoc()) {
echo “<tr>”;
echo “<td>” . $row[“id”] . “</td>”;
echo “<td>” . $row[“name”] . “</td>”;
echo “<td>” . $row[“email”] . “</td>”;
echo “</tr>”;
}
echo “</table>”;
} else {
echo “No records found!”;
}
// Step 4: Close Connection
$conn->close();
?>
4. Example: Display Data using PDO
<?php
// Step 1: Connect to the Database
try {
$conn = new PDO(“mysql:host=localhost;dbname=my_database”, “root”, “”);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// Step 2: Prepare and Execute Query
$stmt = $conn->query(“SELECT id, name, email FROM users”);
// Step 3: Display Data in an HTML Table
echo “<table border=’1′ cellpadding=’10’ cellspacing=’0′>”;
echo “<tr><th>ID</th><th>Name</th><th>Email</th></tr>”;
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
echo “<tr>”;
echo “<td>” . $row[“id”] . “</td>”;
echo “<td>” . $row[“name”] . “</td>”;
echo “<td>” . $row[“email”] . “</td>”;
echo “</tr>”;
}
echo “</table>”;
} catch (PDOException $e) {
echo “Error: ” . $e->getMessage();
}
// Step 4: Close Connection
$conn = null;
?>
5. Enhancing Table Styling using CSS
To make the table look more professional, you can apply CSS styles.
Updated Code with CSS
<?php
$conn = mysqli_connect(“localhost”, “root”, “”, “my_database”);
if (!$conn) {
die(“Connection failed: ” . mysqli_connect_error());
}
$sql = “SELECT id, name, email FROM users”;
$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0) {
echo “<style>
table {
width: 60%;
border-collapse: collapse;
margin: 20px 0;
font-size: 18px;
text-align: left;
}
th, td {
padding: 12px;
border-bottom: 1px solid #ddd;
}
th {
background-color: #f4b41a;
color: white;
}
tr:hover {
background-color: #f1f1f1;
}
</style>”;
echo “<table>”;
echo “<tr><th>ID</th><th>Name</th><th>Email</th></tr>”;
while ($row = mysqli_fetch_assoc($result)) {
echo “<tr>”;
echo “<td>” . $row[“id”] . “</td>”;
echo “<td>” . $row[“name”] . “</td>”;
echo “<td>” . $row[“email”] . “</td>”;
echo “</tr>”;
}
echo “</table>”;
} else {
echo “No records found!”;
}
mysqli_close($conn);
?>
6. Using Bootstrap for Responsive Table
You can also integrate Bootstrap for a responsive table layout.
Bootstrap Example
<?php
$conn = mysqli_connect(“localhost”, “root”, “”, “my_database”);
if (!$conn) {
die(“Connection failed: ” . mysqli_connect_error());
}
$sql = “SELECT id, name, email FROM users”;
$result = mysqli_query($conn, $sql);
echo ‘<link rel=”stylesheet” href=”https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css”>’;
if (mysqli_num_rows($result) > 0) {
echo ‘<div class=”container mt-4″>’;
echo ‘<table class=”table table-bordered table-striped”>’;
echo ‘<thead class=”table-dark”><tr><th>ID</th><th>Name</th><th>Email</th></tr></thead>’;
echo ‘<tbody>’;
while ($row = mysqli_fetch_assoc($result)) {
echo “<tr>”;
echo “<td>” . $row[“id”] . “</td>”;
echo “<td>” . $row[“name”] . “</td>”;
echo “<td>” . $row[“email”] . “</td>”;
echo “</tr>”;
}
echo ‘</tbody></table>’;
echo ‘</div>’;
} else {
echo “No records found!”;
}
mysqli_close($conn);
?>
7. Summary
- Basic Table: Fetches and displays data in an HTML table.
- Enhanced Table: Adds CSS for a better appearance.
- Bootstrap Table: Makes it responsive and styled.
Best Practices
✔ Always sanitize and validate user input when using SQL queries.
✔ Use prepared statements to prevent SQL injection.
✔ Implement CSS/Bootstrap for a better UI.
✔ Close the database connection after query execution.