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:

<?php
  
  $sql = "SELECT * FROM products WHERE id = :id";
  $stmt = $pdo->prepare($sql);
  $stmt->bindParam(':id', $productId, PDO::PARAM_INT);
  $productId = 1;
  $stmt->execute();

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

Fetching Data as Objects

You can fetch data as objects, which can be more convenient in some cases:

<?php
  
  class Product
  {
    public $id;
    public $name;
    public $description;
    public $price;
  }

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

  while ($row = $stmt->fetchObject('Product')) {
    echo "Product Name: " . $row->name . "<br>";
  }

Conclusion

Retrieving and selecting data from a MySQL database using PHP and PDO is a fundamental skill for web developers. With PDO’s flexibility and security features, you can build powerful applications that interact seamlessly with databases.

In this guide, we covered how to connect to a MySQL database, fetch all rows, fetch a single row, and fetch data as objects. Armed with this knowledge, you can now start building database-driven web applications with confidence.