Fetching and Selecting Data in MySQL Using PHP with PDO

One of the fundamental tasks in web development is interacting with databases. Whether you're building a simple blog or a complex e-commerce platform, you'll need to retrieve data from a MySQL database. In this guide, we'll explore how to fetch and select data from a MySQL database using PHP and PDO (PHP Data Objects).

Prerequisites

Before we dive into the code, make sure you have the following:

• A web server with PHP support.

• MySQL installed and running.

• Basic knowledge of SQL (Structured Query Language).

Setting Up the Database

To follow along, let's create a simple products table in a MySQL database. Here's the SQL code to create the table:

CREATE TABLE products (
 id INT AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(255) NOT NULL,
 description TEXT,
 price DECIMAL(10, 2) NOT NULL
);

We'll use this table to demonstrate how to fetch and select data with PHP and PDO.

Connecting to the Database

To get started, establish a connection to your MySQL database using PDO:

<?php
 $host = 'localhost';
 $dbname = 'your_database_name';
 $username = 'your_username';
 $password = 'your_password';

 try {
 $pdo = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
 $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
 echo "Connected successfully";
 } catch (PDOException $e) {
 die("Connection failed: " . $e->getMessage());
 }
?>

Replace your_database_name, your_username, and your_password with your database details.

Fetching Data with PDO

1. Fetch All Rows

To retrieve all rows from the products table, you can use the following code:

<?php

 $sql = "SELECT * FROM products";
 $stmt = $pdo->query($sql);

 while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
 echo "Product Name: " . $row['name'] . "<br>";
 }

2. Fetch a Single Row

If you only need one row, you can use fetch instead of fetchAll: