Introduction
In this tutorial, we will learn how to create tables in MySQL and display them on a web page using PHP. Tables are an essential component of any database, as they allow you to organize and store data in a structured manner. By combining MySQL and PHP, you can create dynamic web pages that retrieve and display data from your tables.
Step 1: Setting up the Database
Before we can create tables, we need to set up a database in MySQL. Open your MySQL command-line client or any other MySQL management tool and execute the following command to create a new database:
CREATE DATABASE mydatabase;Replace mydatabase with the desired name for your database.
Step 2: Connecting to the Database
Once the database is created, we need to establish a connection between PHP and MySQL. Create a new PHP file and add the following code:
<?php
$servername = 'localhost';
$username = 'root';
$password = '';
$dbname = 'mydatabase';
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
die('Connection failed: ' . $conn->connect_error);
}
echo 'Connected successfully';
?>Make sure to update the $servername, $username, $password, and $dbname variables with your MySQL server details.
Step 3: Creating a Table
Now that we have a database and a connection, we can proceed to create a table. Add the following code after the connection code:
$sql = 'CREATE TABLE users (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(30) NOT NULL,
email VARCHAR(50) NOT NULL
)';
if ($conn->query($sql) === TRUE) {
echo 'Table created successfully';
} else {
echo 'Error creating table: ' . $conn->error;
}This code creates a table named ‘users’ with three columns: ‘id’, ‘name’, and ’email’.
Step 4: Displaying the Table on a Web Page
Finally, let’s retrieve the data from the table and display it on a web page. Add the following code after the table creation code:
$sql = 'SELECT * FROM users';
$result = $conn->query($sql);
if ($result->num_rows > 0) {
while ($row = $result->fetch_assoc()) {
echo 'ID: ' . $row['id'] . '<br>';
echo 'Name: ' . $row['name'] . '<br>';
echo 'Email: ' . $row['email'] . '<br><br>';
}
} else {
echo 'No results found';
}
$conn->close();This code retrieves all the rows from the ‘users’ table and displays the ‘id’, ‘name’, and ’email’ values on separate lines.
Conclusion
By following these steps, you can create tables in MySQL and display them on a web page using PHP. This allows you to store and retrieve data efficiently, providing a seamless user experience. Experiment with different table structures and queries to enhance the functionality of your web applications.
