Php Displaying Data In Html Tables Complete Guide

 Last Update:2025-06-22T00:00:00     .NET School AI Teacher - SELECT ANY TEXT TO EXPLANATION.    7 mins read      Difficulty-Level: beginner

Understanding the Core Concepts of PHP Displaying Data in HTML Tables

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 http://localhost/display_employees.php.

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

Conclusion

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

📥 Download Code ▶️ Watch Online 📖 Read Description

Login to post a comment.