What are the best practices for handling and formatting data retrieved from a MySQL database in PHP for display in HTML?

When retrieving data from a MySQL database in PHP for display in HTML, it is important to properly handle and format the data to ensure it is displayed correctly. This includes sanitizing the data to prevent SQL injection attacks, escaping special characters to prevent XSS attacks, and formatting the data appropriately for display (e.g., dates in a human-readable format). One common practice is to use prepared statements to safely retrieve data from the database and then use PHP functions like htmlentities() or htmlspecialchars() to escape special characters before displaying the data in HTML. 

// Establish a connection to the MySQL database
$connection = new mysqli("localhost", "username", "password", "database");

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

// Retrieve data from the database
$sql = "SELECT * FROM table";
$result = $connection->query($sql);

// Display the data in HTML
if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        $formattedData = htmlentities($row["column_name"], ENT_QUOTES, 'UTF-8');
        echo "<p>" . $formattedData . "</p>";
    }
} else {
    echo "0 results";
}

// Close the connection
$connection->close();

Keywords

MySQL PHP data handling formatting HTML display

Related Questions