Welcome back to our journey through PHP and MySQL! In this chapter, we’ll dive deeper into selecting and fetching data from a MySQL database using PHP. These techniques will allow you to retrieve and display data dynamically on your web pages. Let’s get started.
Setting Up the Environment
Ensure your XAMPP server is running, with both Apache and MySQL modules active. If not, open the XAMPP control panel and start them.
First, let’s make sure we have a database and a table to work with. We’ll use the same mydatabase and users table from the previous chapter. If you haven’t set them up yet, here’s a quick recap:
Run the following SQL query to create a users table:
SQL
Copy code
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
password VARCHAR(255) NOT NULL
);
2. Insert Sample Data:
SQL
Copy code
INSERT INTO users (username, password) VALUES (‘admin’, ‘admin123’), (‘user’, ‘user123’);
Now, let’s create PHP scripts to select and fetch data from the users table.
Step 1: Connecting to the Database
First, we’ll write a PHP script to connect to the MySQL database.
1. Create a PHP File: Open a text editor and create a new file named connect.php.
2. Write PHP Code:
php
Copy code
<?php
$servername = “localhost”;
$username = “root”;
$password = “”;
$dbname = “mydatabase”;
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die(“Connection failed: ” . $conn->connect_error);
}
echo “Connected successfully<br>”;
?>
3. Save the File: Save the file in the htdocs directory (e.g., C:\xampp\htdocs\connect.php).
Step 2: Selecting Data from the Database
Next, we’ll write a PHP script to select data from the users table.
1. Create a PHP File: Open your text editor and create a new file named select.php.
2. Write PHP Code:
php
Copy code
<?php
// Include the database connection file
include ‘connect.php’;
// SQL query to select data
$sql = “SELECT id, username FROM users”;
$result = $conn->query($sql);
if ($result->num_rows > 0) {
// Output data of each row
while($row = $result->fetch_assoc()) {
echo “id: ” . $row[“id”]. ” – Username: ” . $row[“username”]. “<br>”;
}
} else {
echo “0 results”;
}
$conn->close();
?>
3. Save the File: Save the file in the htdocs directory (e.g., C:\xampp\htdocs\select.php).
Step 3: Fetching Data Using Different Methods
PHP provides several methods to fetch data from a result set:
Let’s see examples of each.
fetch_assoc() Example:
php
Copy code
<?php
// Include the database connection file
include ‘connect.php’;
// SQL query to select data
$sql = “SELECT id, username FROM users”;
$result = $conn->query($sql);
if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
echo “id: ” . $row[“id”]. ” – Username: ” . $row[“username”]. “<br>”;
}
} else {
echo “0 results”;
}
$conn->close();
?>
fetch_row() Example:
php
Copy code
<?php
// Include the database connection file
include ‘connect.php’;
// SQL query to select data
$sql = “SELECT id, username FROM users”;
$result = $conn->query($sql);
if ($result->num_rows > 0) {
while($row = $result->fetch_row()) {
echo “id: ” . $row[0]. ” – Username: ” . $row[1]. “<br>”;
}
} else {
echo “0 results”;
}
$conn->close();
?>
fetch_array() Example:
php
Copy code
<?php
// Include the database connection file
include ‘connect.php’;
// SQL query to select data
$sql = “SELECT id, username FROM users”;
$result = $conn->query($sql);
if ($result->num_rows > 0) {
while($row = $result->fetch_array()) {
echo “id: ” . $row[“id”]. ” – Username: ” . $row[“username”]. “<br>”;
}
} else {
echo “0 results”;
}
$conn->close();
?>
fetch_object() Example:
php
Copy code
<?php
// Include the database connection file
include ‘connect.php’;
// SQL query to select data
$sql = “SELECT id, username FROM users”;
$result = $conn->query($sql);
if ($result->num_rows > 0) {
while($row = $result->fetch_object()) {
echo “id: ” . $row->id. ” – Username: ” . $row->username. “<br>”;
}
} else {
echo “0 results”;
}
$conn->close();
?>
1. Create a Database and Table:
SQL
Copy code
CREATE DATABASE mydatabase;
USE mydatabase;
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
password VARCHAR(255) NOT NULL
);
INSERT INTO users (username, password) VALUES (‘admin’, ‘admin123’), (‘user’, ‘user123’);
2. Create and Run PHP Scripts:
connect.php:
php
Copy code
<?php
$servername = “localhost”;
$username = “root”;
$password = “”;
$dbname = “mydatabase”;
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die(“Connection failed: ” . $conn->connect_error);
}
echo “Connected successfully<br>”;
?>
select.php (using fetch_assoc()):
php
Copy code
<?php
// Include the database connection file
include ‘connect.php’;
// SQL query to select data
$sql = “SELECT id, username FROM users”;
$result = $conn->query($sql);
if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
echo “id: ” . $row[“id”]. ” – Username: ” . $row[“username”]. “<br>”;
}
} else {
echo “0 results”;
}
$conn->close();
?>
3. Run the Script: Open your web browser and go to http://localhost/select.php. You should see the list of users.
In this chapter, you’ve learned how to use PHP to select and fetch data from a MySQL database. You explored different methods to fetch data, such as fetch_assoc(), fetch_row(), fetch_array(), and fetch_object(). These techniques allow you to retrieve and display data dynamically on your web pages, providing a solid foundation for building dynamic web applications. Keep practicing, and soon you’ll master the art of data retrieval with PHP and MySQL. Happy coding!