How to Retrieve and Display Database Data in PHP/HTML Tables?

Displaying SQL Database Data in PHP/HTML Tables

Query:

How to retrieve and display data from a MySQL table in a PHP/HTML table?

Codebase:

PHP provides a wide range of functions for connecting to a MySQL database and executing queries. Here's an example of how to connect and fetch data from the "employee" table using PHP and display it in an HTML table:

<code class="php"><?php
$connection = mysql_connect('localhost', 'root', ''); // Assuming there's no password
mysql_select_db('hrmwaitrose');

$query = "SELECT * FROM employee"; // Note: No semicolon ";" in SQL query
$result = mysql_query($query);

echo "<table>"; // Start HTML table

while($row = mysql_fetch_array($result)){   // Loop through result rows
    echo "<tr><td>" . htmlspecialchars($row['name']) . "</td><td>" . htmlspecialchars($row['age']) . "</td></tr>";  //$row['index'] denotes field name
}

echo "</table>"; // End HTML table

mysql_close(); // Close database connection
?></code>

Explanation:

• mysql_connect() establishes a connection to the database.
• mysql_select_db() selects the specified database.
• The query string selects all columns (*) from the "employee" table and is executed using mysql_query().
• A while loop iterates through the result set, fetching each row as an associative array using mysql_fetch_array().
• Within the loop, each field from the current row is echoed into a table row using HTML.
• htmlspecialchars() is used to prevent cross-site scripting (XSS) attacks.
• Finally, the connection to the database is closed using mysql_close().

Note:

mysql_fetch_array() is deprecated in PHP 7.0.0. Consider using mysqli_fetch_array() instead.

The above is the detailed content of How to Retrieve and Display Database Data in PHP/HTML Tables?. For more information, please follow other related articles on the PHP Chinese website!