Retrieving and Displaying Table Rows from MySQL Database Using PHP

Introduction

In web development, retrieving data from a database and displaying it in a user-friendly format is a common task. Many developers encounter issues when using PHP, such as fetching only one row from a query result instead of all rows, often due to outdated or incorrect functions. This article aims to address this problem by providing efficient and secure solutions using modern PHP extensions.

Using mysqli Extension to Retrieve Data

PHP offers multiple MySQL extensions, with mysqli (MySQL Improved) being the recommended one today due to its support for prepared statements and better error handling. First, establish a database connection. Below is a basic example demonstrating how to connect to a MySQL database and execute a query.

// Assume database connection parameters are defined
$host = "localhost";
$username = "your_username";
$password = "your_password";
$dbname = "your_database";

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

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

// Define the query statement
$sql = "SELECT * FROM MY_TABLE";

// Execute the query
$result = mysqli_query($conn, $sql);

// Check if query is successful
if (!$result) {
    echo "Query error: " . mysqli_error($conn);
    mysqli_close($conn);
    exit;
}

This code first establishes a connection and then executes a SELECT query. The mysqli_query() function returns a result set object that can be used for subsequent data traversal. If the connection or query fails, the code outputs an error message and terminates execution.

Iterating Through the Result Set and Outputting a Table

To fetch all rows, use a loop structure such as a while loop in combination with the mysqli_fetch_assoc() function. This function returns each row from the result set as an associative array, and returns NULL when no more rows are available, thus ending the loop. The following code demonstrates how to iterate through the result set and output data as an HTML table.

// Assume $result is obtained from the previous section
if ($result) {
    echo "<table border='1'>";
    while ($row = mysqli_fetch_assoc($result)) {
        echo "<tr>";
        foreach ($row as $field => $value) {
            // Use htmlspecialchars to escape output, preventing XSS attacks
            echo "<td>" . htmlspecialchars($value, ENT_QUOTES, 'UTF-8') . "</td>";
        }
        echo "</tr>";
    }
    echo "</table>";
} else {
    echo "No data to display";
}

// Free result set and close connection
mysqli_free_result($result);
mysqli_close($conn);

In this example, the while loop continuously calls mysqli_fetch_assoc() until all rows are processed. Each row's data is traversed using a foreach loop over the associative array key-value pairs, with htmlspecialchars() used to escape the output, ensuring HTML characters such as < and > are safely displayed and preventing cross-site scripting (XSS) attacks. The code also includes error handling and resource cleanup for robustness.

Adding a Header Row

Referencing other answers, the table can be improved to include a header row that displays column names. This is achieved by outputting the column names during the first iteration, using the keys from the associative array as headers. The following code demonstrates this enhancement.

$first_row = true;
echo "<table border='1'>";
while ($row = mysqli_fetch_assoc($result)) {
    if ($first_row) {
        $first_row = false;
        // Output header row
        echo "<tr>";
        foreach ($row as $column_name => $value) {
            echo "<th>" . htmlspecialchars($column_name, ENT_QUOTES, 'UTF-8') . "</th>";
        }
        echo "</tr>";
    }
    // Output data row
    echo "<tr>";
    foreach ($row as $column_name => $value) {
        echo "<td>" . htmlspecialchars($value, ENT_QUOTES, 'UTF-8') . "</td>";
    }
    echo "</tr>";
}
echo "</table>";

Here, a boolean variable $first_row is used to detect the first row and output the header. The header uses <th> tags to visually distinguish it from data rows. This approach makes the table more readable and suitable for displaying database table structures.

Security Considerations

Security is crucial when outputting user data. The htmlspecialchars() function is used to escape HTML special characters such as <, >, &, and ", preventing them from being interpreted as HTML code. This helps mitigate XSS attacks, where malicious scripts might be injected via user input. In code, it is recommended to always escape output unless the data source is completely trusted.

Alternative Methods

Besides the mysqli extension, PHP also supports the outdated mysql extension, but it has been deprecated since PHP 5.5.0 and removed in PHP 7.0.0. Code using the mysql extension, such as mysql_fetch_array(), may pose security risks and compatibility issues, so it is not recommended for new projects. Developers should migrate to mysqli or PDO extensions.

Conclusion

This article systematically introduces methods for retrieving data from a MySQL database using PHP and outputting it as an HTML table. Through the mysqli extension, combined with loops and escape functions, efficient and secure data display can be achieved. Core concepts include database connection, query execution, result traversal, and secure output. Developers should prioritize modern extensions and always consider security to ensure application robustness.