A Complete Guide - PHP Displaying Data in HTML Tables

Last Updated: 03 Jul, 2025   
  YOU NEED ANY HELP? THEN SELECT ANY TEXT.

PHP Displaying Data in HTML Tables

Importance of Displaying Data in HTML Tables

  1. Organization and Clarity: Tables provide a clear, structured way to present data, which makes it easier for users to read and understand the information.
  2. Ease of Access: By using HTML tables, developers can easily organize data into rows and columns, improving accessibility.
  3. Dynamic Content: PHP allows you to generate table content dynamically based on database queries or other sources, making your web application more flexible.
  4. Aesthetic Appeal: With proper styling using CSS, tables can enhance the looks and feel of your website.
  5. Interactivity and Functionality: Combining HTML tables with JavaScript adds interactivity, such as sorting, searching, and pagination, to improve the user experience.

Steps to Display Data in an HTML Table with PHP

Step 1: Connect to the Database

To start, connect to your database using PHP. You can use MySQLi or PDO. Here’s an example using MySQLi:

$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database_name"; // Create connection
$conn = new mysqli($servername, $username, $password, $dbname); // Check connection
if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error);
}
Step 2: Retrieve Data from the Database

Use a SQL query to fetch the data you want to display in the table. For example:

$sql = "SELECT id, name, email FROM users";
$result = $conn->query($sql);
Step 3: Start the HTML Table

Before filling it with data, start an HTML table structure:

<table border="1"> <tr> <th>ID</th> <th>Name</th> <th>Email</th> </tr>
Step 4: Populate the Table with Data

If the database query returns results, loop through them and create a row for each record:

if ($result->num_rows > 0) { // Output data of each row while($row = $result->fetch_assoc()) {
?>
<tr> <td><?php echo htmlspecialchars($row["id"]); ?></td> <td><?php echo htmlspecialchars($row["name"]); ?></td> <td><?php echo htmlspecialchars($row["email"]); ?></td>
</tr>
<?php }
} else { echo "<tr><td colspan='3'>No records found</td></tr>";
}
?>
Step 5: End the HTML Table

After inserting all the data, close the table tag.

</table>
Step 6: Close the Database Connection

Always remember to close the database connection after fetching and displaying the data.

$conn->close();

Adding Styling and Interactivity

Styling

Enhance the appearance by using CSS:

table { width: 50%; border-collapse: collapse; font-family: Arial, sans-serif;
} th, td { border: 1px solid #ddd; padding: 8px;
} th { background-color: #f3f3f3; text-align: left;
}
Interactivity

Add JavaScript for features like sorting:

<script> function sortTable(n) { var table, rows, switching, i, x, y, shouldSwitch, dir, switchcount = 0; table = document.querySelector('table'); switching = true; // Set the sorting direction to ascending: dir = "asc"; /* Make a loop that will continue until no switching has been done: */ while (switching) { // Start by saying: no switching is done: switching = false; rows = table.rows; /* Loop through all table rows (except the first, which contains table headers): */ for (i = 1; i < (rows.length - 1); i++) { // Start by saying there should be no switching: shouldSwitch = false; /* Get the two elements you want to compare, one from current row and one from the next: */ x = rows[i].getElementsByTagName("TD")[n]; y = rows[i + 1].getElementsByTagName("TD")[n]; /* Check if the two rows should switch place, based on the direction, asc or desc: */ if (dir === "asc") { if (x.innerHTML.toLowerCase() > y.innerHTML.toLowerCase()) { // If so, mark as a switch and break the loop: shouldSwitch = true; break; } } else if (dir === "desc") { if (x.innerHTML.toLowerCase() < y.innerHTML.toLowerCase()) { // If so, mark as a switch and break the loop: shouldSwitch = true; break; } } } if (shouldSwitch) { // If a switch has been marked, make the switch and mark that a switch has been done: rows[i].parentNode.insertBefore(rows[i + 1], rows[i]); switching = true; // Each time a switch is done, increase this count by 1: switchcount ++; } else { /* If no switching has been done AND the direction is "asc", set the direction to "desc" and run the while loop again. */ if (switchcount === 0 && dir === "asc") { dir = "desc"; switching = true; } } } }
</script>

Include clickable headers in your HTML:

Online Code run

🔔 Note: Select your programming language to check or run code at

💻 Run Code Compiler

Step-by-Step Guide: How to Implement PHP Displaying Data in HTML Tables

Prerequisites:

  1. You should have a local development environment with PHP, MySQL, and a web server like Apache or Nginx.
  2. You should have a MySQL database and a table with some sample data. For this example, let’s assume you have a database named sample_db and a table named employees with the following columns: id, name, position, and salary.

Step-by-Step Guide:

Step 1: Set Up the Database

First, let's create a sample database and table if you don’t have one already.

  1. Open your database management tool like phpMyAdmin or MySQL Workbench.
  2. Create a new database named sample_db.
  3. Inside sample_db, create a new table named employees with the following structure:
CREATE TABLE employees ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), position VARCHAR(255), salary DECIMAL(10, 2)
);
  1. Insert some sample data into the employees table:
INSERT INTO employees (name, position, salary) VALUES
('John Doe', 'Software Engineer', 75000.00),
('Jane Smith', 'Project Manager', 85000.00),
('Mike Johnson', 'UX Designer', 78000.00);

Step 2: Create the PHP Script to display the data in an HTML table

Now, let's create a PHP script to fetch the data from the employees table and display it in an HTML table.

  1. Create a new PHP file named display_employees.php in your webroot directory (typically htdocs for XAMPP).

  2. Open the display_employees.php file in a text editor and add the following code:

<?php
// Database connection settings
$servername = "localhost"; // Change if your MySQL server is hosted elsewhere
$username = "root"; // Default username for XAMPP
$password = ""; // Default password for XAMPP
$dbname = "sample_db"; // Create connection
$conn = new mysqli($servername, $username, $password, $dbname); // Check connection
if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error);
} // SQL query to fetch all employees
$sql = "SELECT * FROM employees";
$result = $conn->query($sql); if ($result->num_rows > 0) { // Start the table echo "<table border='1'> <tr> <th>ID</th> <th>Name</th> <th>Position</th> <th>Salary</th> </tr>"; // Output data of each row while($row = $result->fetch_assoc()) { echo "<tr> <td>" . $row["id"] . "</td> <td>" . $row["name"] . "</td> <td>" . $row["position"] . "</td> <td>" . $row["salary"] . "</td> </tr>"; } // Close the table echo "</table>";
} else { echo "0 results";
} // Close the connection
$conn->close();
?>

Step 3: Explanation of the PHP Script

  • Database Connection: The script starts by setting the database connection parameters ($servername, $username, $password, and $dbname). It then creates a new connection to the MySQL server using the mysqli class.

  • Error Handling: The script checks if the connection was successful. If not, it terminates the script and outputs an error message.

  • Query Execution: The script executes a SELECT SQL query to fetch all rows from the employees table.

  • Result Handling: If there are rows in the result set, it outputs an HTML table with a header row and populates the table with the data from each row in the result set.

  • Closing the Connection: Finally, the script closes the database connection.

Step 4: Run the Script

  1. Save the display_employees.php file.
  2. Open your web browser and navigate to

You should see a table displaying the data from the employees table in your database.

Conclusion

 YOU NEED ANY HELP? THEN SELECT ANY TEXT.

Top 10 Interview Questions & Answers on PHP Displaying Data in HTML Tables

1. How do I connect to a MySQL database using PHP?

Answer: You can connect to a MySQL database using PHP by utilizing the mysqli_connect() function (for MySQLi extension) or PDO class (for PDO extension). Here's an example using mysqli_connect():

$host = "localhost"; // Your host
$username = "root"; // Your username
$password = ""; // Your password
$dbname = "my_database"; // Your database name // Create connection
$conn = mysqli_connect($host, $username, $password, $dbname); // Check connection
if (!$conn) { die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully";

2. How can I retrieve data from a MySQL database using PHP?

Answer: Once you've connected to your database, you can use mysqli_query() to execute SQL queries that retrieve data. Here's an example:

$sql = "SELECT id, name, email FROM users";
$result = mysqli_query($conn, $sql); if (mysqli_num_rows($result) > 0) { // Output data of each row while($row = mysqli_fetch_assoc($result)) { echo "id: " . $row["id"]. " - Name: " . $row["name"]. " - Email: " . $row["email"]. "<br>"; }
} else { echo "0 results";
}

3. How do I display this retrieved data in an HTML table?

Answer: Loop through the result set and echo HTML table tags around the data:

echo "<table border='1'>
<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>";

4. How can I style this HTML table with CSS?

Answer: Use CSS to style your HTML table. Here’s a basic example:

table { width: 100%; border-collapse: collapse; margin: 20px 0;
} th, td { padding: 12px 15px; text-align: left; border-bottom: 1px solid #ddd;
} th { background-color: #f2f2f2; color: black;
} tr:hover { background-color: #f5f5f5;
}

You would typically place the CSS in a <style> tag within the <head> section of your HTML document or link to an external stylesheet.

5. Is there a way to use prepared statements to fetch data securely?

Answer: Yes, using prepared statements helps prevent SQL Injection. Here's how you can use mysqli_prepare() and mysqli_stmt_execute() with prepared statements:

$stmt = $conn->prepare("SELECT id, name, email FROM users");
$stmt->execute();
$stmt->bind_result($id, $name, $email); echo "<table border='1'>
<tr>
<th>ID</th>
<th>Name</th>
<th>Email</th>
</tr>";
while ($stmt->fetch()) { echo "<tr>"; echo "<td>" . $id . "</td>"; echo "<td>" . $name . "</td>"; echo "<td>" . $email . "</td>"; echo "</tr>";
}
echo "</table>"; $stmt->close();
$conn->close();

Or, using a PDO approach:

try { $stmt = $pdo->query("SELECT id, name, email FROM users"); echo "<table border='1'> <tr> <th>ID</th> <th>Name</th> <th>Email</th> </tr>"; while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { echo "<tr>"; echo "<td>" . htmlspecialchars($row['id']) . "</td>"; echo "<td>" . htmlspecialchars($row['name']) . "</td>"; echo "<td>" . htmlspecialchars($row['email']) . "</td>"; echo "</tr>"; } echo "</table>"; } catch (PDOException $e) { echo "Query failed: " . $e->getMessage();
}

6. What’s the importance of sanitizing or escaping data before displaying it in HTML?

Answer: Sanitizing and escaping data is crucial to prevent Cross-Site Scripting (XSS) attacks. To escape output, PHP provides the htmlspecialchars() function which converts special characters to HTML entities, preventing execution.

echo "<td>" . htmlspecialchars($row['email']) . "</td>";

7. Can I add sorting functionality to the columns of my HTML table?

Answer: Yes, you can add sorting by sending the column name via URL parameters and sorting the table accordingly in the backend query:

Here’s a simple example:

$sort_by = isset($_GET['sort_by']) ? $_GET['sort_by'] : 'id'; // Default sort by ID $sql = "SELECT id, name, email FROM users ORDER BY $sort_by";
$result = mysqli_query($conn, $sql); echo "<table border='1'>
<tr>
<th><a href='?sort_by=id'>ID</th>
<th><a href='?sort_by=name'>Name</th>
<th><a href='?sort_by=email'>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>";

8. How can I implement pagination in my HTML table?

Answer: Pagination involves splitting your data into multiple pages and showing one page at a time. Here's a simple example using LIMIT in SQL and handling it in PHP:

$records_per_page = 5;
$page = isset($_GET['page']) ? $_GET['page'] : 1;
$start_from = ($page-1) * $records_per_page; $sql = "SELECT id, name, email FROM users LIMIT $start_from, $records_per_page";
$result = mysqli_query($conn, $sql); // Display the result set here as usual... $total_records_sql = "SELECT COUNT(*) FROM users";
$total_records_result = mysqli_query($conn,$total_records_sql);
$total_records = mysqli_fetch_array($total_records_result)[0];
$total_pages = ceil($total_records / $records_per_page); for ($i=1; $i<=$total_pages; $i++) { echo "<a href='?page=".$i."'>".$i."</a> "; // Print links for all pages
}

9. What are some common pitfalls when displaying MySQL data in HTML tables?

Answer: Some common pitfalls include:

  • SQL Injection: Always use prepared statements.
  • XSS Vulnerability: Escape output using functions like htmlspecialchars().
  • Column Sorting: Ensure sanitized input if used as part of SQL queries.
  • Pagination: Handle edge cases such as invalid page numbers or too many pages.
  • Performance Issues: Limit the number of records fetched from the database to avoid long loading times.

10. How do I handle large datasets efficiently in a PHP HTML table?

Answer: For large datasets, consider:

  • Limiting the Number of Rows: Use OFFSET and LIMIT clauses in SQL.
  • Data Compression: Enable Gzip compression in your server settings.
  • Caching: Utilize caching mechanisms like Memcached or Redis to reduce database load.
  • Lazy Loading: Load additional data only when required by the user (using AJAX).
  • Efficient Queries: Optimize your SQL queries to fetch only necessary data (using indexes).

You May Like This Related .NET Topic

Login to post a comment.