Complete Guide to Displaying Data from MySQL Database to HTML Table Using PHP

Introduction

In web development, retrieving data from databases and displaying it on web pages is a common requirement. PHP, as a server-side scripting language, provides multiple ways to interact with MySQL databases. This article systematically introduces how to connect to MySQL database using PHP, execute query operations, and present results to users in HTML table format.

Database Connection Fundamentals

PHP offers several methods to connect to MySQL databases, including the traditional mysql extension, improved mysqli extension, and more modern PDO (PHP Data Objects). Each method has its characteristics and applicable scenarios.

Using mysql Extension (Deprecated)

Although the mysql extension was widely used in early versions, it was marked as deprecated starting from PHP 5.5.0 and completely removed in PHP 7.0.0. Understanding its basic usage helps comprehend the development history:

$connection = mysql_connect('localhost', 'root', '');
mysql_select_db('hrmwaitrose');

$query = "SELECT * FROM employee";
$result = mysql_query($query);

echo "<table>";
while($row = mysql_fetch_array($result)) {
    echo "<tr><td>" . htmlspecialchars($row['name']) . "</td><td>" . htmlspecialchars($row['age']) . "</td></tr>";
}
echo "</table>";

mysql_close();

This code demonstrates the basic connection, query, and result processing flow. It's important to note that the mysql_fetch_array function has been deprecated and should be replaced with more modern alternatives in actual projects.

Using mysqli Extension

The mysqli (MySQL Improved) extension provides both object-oriented and procedural programming styles, supports prepared statements and transaction processing, and is currently the recommended approach.

Object-Oriented Approach

$servername = "localhost";
$username = "root";
$password = "";
$dbname = "hrmwaitrose";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$sql = "SELECT * FROM employee";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    echo "<table border='1'><tr><th>Name</th><th>Age</th></tr>";
    while($row = $result->fetch_assoc()) {
        echo "<tr><td>" . htmlspecialchars($row['name']) . "</td><td>" . htmlspecialchars($row['age']) . "</td></tr>";
    }
    echo "</table>";
} else {
    echo "No records found";
}
$conn->close();

Procedural Approach

$servername = "localhost";
$username = "root";
$password = "";
$dbname = "hrmwaitrose";

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

// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

$sql = "SELECT * FROM employee";
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {
    echo "<table border='1'><tr><th>Name</th><th>Age</th></tr>";
    while($row = mysqli_fetch_assoc($result)) {
        echo "<tr><td>" . htmlspecialchars($row['name']) . "</td><td>" . htmlspecialchars($row['age']) . "</td></tr>";
    }
    echo "</table>";
} else {
    echo "No records found";
}
mysqli_close($conn);

Using PDO Extension

PDO provides a data access abstraction layer that can support multiple databases, offering better security and portability.

echo "<table style='border: solid 1px black;'>";
echo "<tr><th>Name</th><th>Age</th></tr>";

$servername = "localhost";
$username = "root";
$password = "";
$dbname = "hrmwaitrose";

try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    $stmt = $conn->prepare("SELECT name, age FROM employee");
    $stmt->execute();
    
    // Set result set as associative array
    $stmt->setFetchMode(PDO::FETCH_ASSOC);
    
    foreach($stmt->fetchAll() as $row) {
        echo "<tr><td>" . htmlspecialchars($row['name']) . "</td><td>" . htmlspecialchars($row['age']) . "</td></tr>";
    }
} catch(PDOException $e) {
    echo "Error: " . $e->getMessage();
}
$conn = null;
echo "</table>";

Security Considerations

When displaying database data, security considerations are essential:

• SQL Injection Protection: Using prepared statements can effectively prevent SQL injection attacks
• XSS Protection: Using the htmlspecialchars() function to escape output data prevents cross-site scripting attacks
• Error Handling: In production environments, database connection and query errors should be properly handled to avoid leaking sensitive information

Best Practice Recommendations

1. Choose Appropriate Extension: Recommend using mysqli or PDO, avoid using deprecated mysql extension
2. Use Prepared Statements: Especially when handling user input, prepared statements provide better security
3. Proper Error Handling: Implement comprehensive error handling mechanisms for easier debugging and maintenance
4. Resource Management: Close database connections promptly to release resources
5. Data Validation: Perform appropriate validation and sanitization before displaying data

Conclusion

This article provides a detailed introduction to various methods of retrieving data from MySQL database using PHP and displaying it in HTML tables. From the basic mysql extension to modern mysqli and PDO extensions, each method has its applicable scenarios. In actual development, appropriate technical solutions should be chosen based on project requirements and security needs. By following best practices, secure and efficient database-driven web applications can be built.