Introduction

This tutorial aims to guide you on how to retrieve data from a MySQL database and display it on your webpage. By the end of this tutorial, you will be able to create a simple webpage that fetches data from a MySQL database and displays it on the webpage dynamically.

Prerequisites:

• Basic knowledge of HTML & CSS
• Basic understanding of PHP
• MySQL database and basic SQL commands

Step-by-Step Guide

Database Connection

The first step in retrieving data from a MySQL database is to establish a connection to the database. In PHP, this can be done using the mysqli_connect() function.

Fetching Data

Once a connection is established, you can fetch data from your database using SQL queries. In PHP, this can be done using the mysqli_query() function.

Displaying Data

After fetching the data, it can be displayed onto your webpage using HTML and CSS. Here, PHP is embedded within the HTML to dynamically display the fetched data.

Code Examples

Database Connection

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

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

// Check connection
if (!$conn) {
  die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully";
?>

This script will try to connect to the database. If the connection is successful, it will print "Connected successfully". If the connection fails, it will print "Connection failed" along with the error message.

Fetching and Displaying Data

<?php
$sql = "SELECT id, firstname, lastname FROM MyGuests";
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {
  // output data of each row
  while($row = mysqli_fetch_assoc($result)) {
    echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>";
  }
} else {
  echo "0 results";
}
mysqli_close($conn);
?>

This script first executes an SQL query that selects all data from the 'MyGuests' table. If the query returns any rows, it will loop through each row and print the data. If no rows are returned, it will print "0 results".

Summary

In this tutorial, we learned how to connect to a MySQL database, fetch data using SQL queries, and display the data on a webpage using HTML and CSS embedded with PHP.

Next, you could learn about data insertion and deletion from a database. You could also learn about securing your database connections and queries.

Practice Exercises

1. Create a webpage that displays all the usernames from the 'Users' table in your database.
2. Create a webpage that displays a user's full name and email when provided with a username.
3. Create a webpage that displays all users ordered by their last login date.

Remember to always close your database connections after you're done with them to free up resources. Happy coding!