Displaying MySQL Database Table Data in HTML Tables Using PHP

Introduction

Dynamic display of database content is a fundamental and crucial functionality in modern web development. By combining PHP with MySQL, developers can easily retrieve data from databases and present it in tabular format on web pages. This article systematically presents a complete implementation approach for this process.

Database Connection Configuration

Establishing a connection to the MySQL database is the first step in data presentation. PHP provides several extensions for this purpose, with MySQLi being one of the most commonly used options.

<?php
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$database = "your_database";

// Create database connection
$connection = mysqli_connect($servername, $username, $password, $database);

// Check connection success
if (!$connection) {
    die("Database connection failed: " . mysqli_connect_error());
}
?>

In practical applications, it's recommended to store database connection information in configuration files to improve code maintainability and security. Additionally, proper error handling mechanisms should be implemented to provide clear error messages when connections fail.

Data Query and Retrieval

After establishing the connection, the next step is to execute SQL queries to retrieve the required data. For displaying entire table data, SELECT statements are typically used.

<?php
// Execute query to get all data from tickets table
$query = "SELECT submission_id, formID, IP, name, email, message FROM tickets";
$result = mysqli_query($connection, $query);

// Get all data rows
$data = mysqli_fetch_all($result, MYSQLI_ASSOC);

// Free result set
mysqli_free_result($result);
?>

Using the mysqli_fetch_all() function allows retrieving all result rows at once, which is more efficient than fetching row by row. The MYSQLI_ASSOC parameter ensures associative arrays are returned, facilitating subsequent processing.

HTML Table Construction

After retrieving data, HTML tables need to be constructed to display this information. Tables should include both header and body sections.

<table border="1" cellspacing="0" cellpadding="8" style="border-collapse: collapse; width: 100%;">
    <thead>
        <tr>
            <th>Submission ID</th>
            <th>Form ID</th>
            <th>IP Address</th>
            <th>Name</th>
            <th>Email</th>
            <th>Message</th>
        </tr>
    </thead>
    <tbody>
        <?php foreach ($data as $row): ?>
        <tr>
            <td><?= htmlspecialchars($row['submission_id']) ?></td>
            <td><?= htmlspecialchars($row['formID']) ?></td>
            <td><?= htmlspecialchars($row['IP']) ?></td>
            <td><?= htmlspecialchars($row['name']) ?></td>
            <td><?= htmlspecialchars($row['email']) ?></td>
            <td><?= htmlspecialchars($row['message']) ?></td>
        </tr>
        <?php endforeach ?>
    </tbody>
</table>

Security Measures

When outputting user data, security considerations are essential, particularly for preventing XSS (Cross-Site Scripting) attacks.

<?php
// Safe output function
function safe_output($value) {
    return htmlspecialchars($value, ENT_QUOTES, 'UTF-8');
}

// Using safe output in loops
foreach ($data as $row) {
    echo '<td>' . safe_output($row['field_name']) . '</td>';
}
?>

The htmlspecialchars() function converts special characters to HTML entities, effectively preventing XSS attacks. It's recommended to apply proper escaping to all user-provided data.

Complete Implementation Example

The following is a complete implementation example demonstrating the full workflow from database connection to data presentation.

<?php
// Database connection
$connection = mysqli_connect("localhost", "username", "password", "database_name");
if (!$connection) {
    die("Connection failed: " . mysqli_connect_error());
}

// Execute query
$result = mysqli_query($connection, "SELECT * FROM tickets");
$data = mysqli_fetch_all($result, MYSQLI_ASSOC);

// Close connection
mysqli_close($connection);
?>

<!DOCTYPE html>
<html>
<head>
    <title>Ticket Data Display</title>
    <style>
        table {
            border-collapse: collapse;
            width: 100%;
            margin: 20px 0;
        }
        th, td {
            border: 1px solid #ddd;
            padding: 12px;
            text-align: left;
        }
        th {
            background-color: #f2f2f2;
            font-weight: bold;
        }
        tr:nth-child(even) {
            background-color: #f9f9f9;
        }
    </style>
</head>
<body>
    <table>
        <thead>
            <tr>
                <th>Submission ID</th>
                <th>Form ID</th>
                <th>IP Address</th>
                <th>Name</th>
                <th>Email</th>
                <th>Message</th>
            </tr>
        </thead>
        <tbody>
            <?php if (!empty($data)): ?>
                <?php foreach ($data as $row): ?>
                <tr>
                    <td><?= htmlspecialchars($row['submission_id']) ?></td>
                    <td><?= htmlspecialchars($row['formID']) ?></td>
                    <td><?= htmlspecialchars($row['IP']) ?></td>
                    <td><?= htmlspecialchars($row['name']) ?></td>
                    <td><?= htmlspecialchars($row['email']) ?></td>
                    <td><?= htmlspecialchars($row['message']) ?></td>
                </tr>
                <?php endforeach ?>
            <?php else: ?>
                <tr>
                    <td colspan="6" style="text-align: center;">No data found</td>
                </tr>
            <?php endif ?>
        </tbody>
    </table>
</body>
</html>

Performance Optimization Recommendations

When dealing with large amounts of data, performance optimization becomes particularly important. Here are some practical optimization suggestions:

<?php
// Limit query result quantity
$query = "SELECT * FROM tickets LIMIT 100";

// Select only required fields
$query = "SELECT submission_id, name, email FROM tickets";

// Implement pagination
$page = isset($_GET['page']) ? (int)$_GET['page'] : 1;
$limit = 50;
$offset = ($page - 1) * $limit;
$query = "SELECT * FROM tickets LIMIT $limit OFFSET $offset";
?>

By limiting query results, selecting specific fields, and implementing pagination functionality, application performance and user experience can be significantly improved.

Error Handling and Debugging

Comprehensive error handling mechanisms are crucial for production environments.

<?php
// Enable error reporting
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

try {
    $connection = mysqli_connect("localhost", "username", "password", "database_name");
    $result = mysqli_query($connection, "SELECT * FROM tickets");
    
    if (mysqli_num_rows($result) === 0) {
        echo "<p>No ticket data currently available.</p>";
    } else {
        $data = mysqli_fetch_all($result, MYSQLI_ASSOC);
        // Process data display
    }
} catch (Exception $e) {
    error_log("Database error: " . $e->getMessage());
    echo "<p>System temporarily unable to access data, please try again later.</p>";
}
?>

Conclusion

By combining PHP with MySQL, developers can efficiently implement dynamic display of database data. The methods introduced in this article are not only functionally complete but also consider multiple aspects including security, performance, and user experience. In actual development, it's recommended to make appropriate adjustments and optimizations based on specific requirements while maintaining constant attention to security issues.