Welcome to the next stage of our journey, where we combine the power of PHP with SQL to retrieve and display data from our databases. Think of PHP as a magical script that not only casts SQL spells but also brings the results to life on your web pages. Let’s delve into this fascinating aspect of web development.
Setting the Scene
Imagine you’re the curator of a digital museum. Your job is to retrieve artifacts (data) from the storage (database) and display them in the gallery (web page). PHP helps you fetch these artifacts efficiently and present them beautifully.
First, ensure you have a working environment with PHP and MySQL set up. You can use tools like XAMPP, WAMP, or MAMP to create a local server environment.
Step 1: Connect to the Database
Before retrieving data, you need to establish a connection between PHP and your MySQL database. Here’s how you do it:
php
Copy code
<?php
$servername = “localhost”;
$username = “root”;
$password = “”;
$dbname = “your_database_name”;
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die(“Connection failed: ” . $conn->connect_error);
}
echo “Connected successfully”;
?>
Step 2: Writing the Select Query
Now, let’s write a SQL SELECT query to fetch data from a table named students.
php
Copy code
<?php
$sql = “SELECT id, name, age FROM students”;
$result = $conn->query($sql);
?>
Step 3: Fetching the Results
Using the fetch methods, you can retrieve and display the data. Here are the common methods:
We’ll use fetch_assoc() for this example to retrieve data as an associative array.
php
Copy code
<?php
if ($result->num_rows > 0) {
// Output data of each row
while($row = $result->fetch_assoc()) {
echo “ID: ” . $row[“id”]. ” – Name: ” . $row[“name”]. ” – Age: ” . $row[“age”]. “<br>”;
}
} else {
echo “0 results”;
}
$conn->close();
?>
Here’s the complete PHP script that connects to the database, selects data from the students table, and displays it.
php
Copy code
<?php
$servername = “localhost”;
$username = “root”;
$password = “”;
$dbname = “your_database_name”;
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die(“Connection failed: ” . $conn->connect_error);
}
// SQL query to select data
$sql = “SELECT id, name, age FROM students”;
$result = $conn->query($sql);
// Fetch and display data
if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
echo “ID: ” . $row[“id”]. ” – Name: ” . $row[“name”]. ” – Age: ” . $row[“age”]. “<br>”;
}
} else {
echo “0 results”;
}
$conn->close();
?>
Create a Database and Table:
SQL
Copy code
CREATE DATABASE school;
USE school;
CREATE TABLE students (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
age INT NOT NULL
);
1. Insert Sample Data:
SQL
Copy code
INSERT INTO students (name, age) VALUES (‘Alice’, 23), (‘Bob’, 25), (‘Carol’, 22);
2. PHP Script to Fetch and Display Data: Save the following PHP script in a file named fetch_students.php and run it on your server.
php
Copy code
<?php
$servername = “localhost”;
$username = “root”;
$password = “”;
$dbname = “school”;
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die(“Connection failed: ” . $conn->connect_error);
}
// SQL query to select data
$sql = “SELECT id, name, age FROM students”;
$result = $conn->query($sql);
// Fetch and display data
if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
echo “ID: ” . $row[“id”]. ” – Name: ” . $row[“name”]. ” – Age: ” . $row[“age”]. “<br>”;
}
} else {
echo “0 results”;
}
$conn->close();
?>
In this chapter, you’ve learned how to connect PHP with MySQL to retrieve and display data using SQL SELECT queries and various fetch methods. This powerful combination allows you to create dynamic, data-driven web applications. Keep experimenting with different queries and data structures, and soon you’ll master the art of fetching and presenting data elegantly. Happy coding!