In the world of web development, PHP and MySQL are two powerful tools that work hand in hand to create dynamic and interactive websites. One of the most common tasks while working with these technologies is retrieving data from a MySQL database and displaying it on a webpage. In this article, we will explore how to loop through a MySQL query using PHP's PDO (PHP Data Objects) extension.
Before we dive into the code, let's first understand what PDO is. PDO is a PHP extension that provides a consistent interface for working with databases. It supports multiple database drivers, including MySQL, PostgreSQL, and SQLite, making it a popular choice among developers. PDO also offers a secure and efficient way to interact with databases, making it a preferred option for database operations.
Now, let's move on to the main topic of this article - looping through a MySQL query via PDO in PHP. To demonstrate this, we will create a simple web application that displays a list of users from a MySQL database.
Step 1: Creating a Database
To get started, we need to create a database and a table to store our user data. Open your MySQL command-line or a GUI tool like phpMyAdmin and run the following SQL query:
CREATE DATABASE users;
This will create a database named "users". Next, let's create a table called "users_info" with the following fields:
- id (INT, Primary Key, Auto Increment)
- name (VARCHAR(50))
- email (VARCHAR(100))
- age (INT)
- country (VARCHAR(50))
INSERT INTO users_info (name, email, age, country) VALUES ('John Doe', 'john@example.com', 25, 'USA'), ('Jane Smith', 'jane@example.com', 28, 'Canada'), ('Mark Johnson', 'mark@example.com', 30, 'UK');
This will insert three sample users into our table. Now, our database setup is complete, and we can move on to the PHP code.
Step 2: Connecting to the Database
To connect to the MySQL database using PDO, we need to provide our database credentials such as host, username, password, and database name. Create a new PHP file named "index.php" and add the following code:
<?php
// Database credentials
$host = "localhost";
$username = "root";
$password = "";
$dbname = "users";
// Create a new PDO instance
$conn = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
// Set PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
?>
Here, we have used the PDO constructor to create a new PDO instance and provided the necessary database credentials. We have also set the error mode to "exception" to handle any errors that may occur during database operations.
Step 3: Fetching Data from Database
Next, we need to fetch the data from the "users_info" table and display it on our webpage. To do this, we will use the "SELECT" statement in SQL. Add the following code to your "index.php" file:
<?php
// Fetch data from database
$stmt = $conn->query("SELECT * FROM users_info");
?>
Here, we have used the "query()" method to execute the SQL query and store the result in the $stmt variable.
Step 4: Looping through the Data
Now, we can use a loop to iterate through the $stmt variable and display the data on our webpage. Update your "index.php" file with the following code:
<?php
// Fetch data from database
$stmt = $conn->query("SELECT * FROM users_info");
// Loop through the data
while ($row = $stmt->fetch()) {
echo "<p>Name: " . $row['name'] . "</p>";
echo "<p>Email: " . $row['email'] . "</p>";
echo "<p>Age: " . $row['age'] . "</p>";
echo "<p>Country: " . $row['country'] . "</p>";
}
?>
In the above code, we have used the "fetch()" method to retrieve one row at a time from the $stmt variable and stored it in the $row variable. Then, we have used HTML tags to display the user's information on the webpage.
Step 5: Run the Code
Save all the changes and open your webpage in a browser. You should see a list of users with their names, email, age, and country displayed on the page. Congratulations, you have successfully looped through a MySQL query via PDO in PHP!
Conclusion
In this article, we have learned how to use PHP's PDO extension to loop through a MySQL query and display the results on a webpage. PDO offers a secure and efficient way to interact with databases, making it a popular choice among developers. You can now use this knowledge
