Topic 27 of 48 · Full Stack Essentials

PHP with Select, Fetch

Lesson TL;DRTopic 3: PHP with Select, Fetch 📖 13 min read · 🎯 beginner · 🧭 Prerequisites: iframeembedalbumsembedvideoembedmapsinputtagintroduction, javascriptobjects Why this matters Up until now, you've learn...
13 min read·beginner·php · mysql · mysqli · select-query

Topic 3: PHP with Select, Fetch

📖 13 min read · 🎯 beginner · 🧭 Prerequisites: iframe-embed-albums-embed-video-embed-maps-input-tag-introduction, javascript-objects

Why this matters

Up until now, you've learned how to store data in a database — great. But a website that only writes data and never reads it back is pretty useless. Think about any site you've used: a product listing, a news feed, a student result page. Every one of those is pulling records out of a database and showing them on screen. That's exactly what we're building today. We're going to use PHP's SELECT query and fetch to grab rows from MySQL and render them live on a webpage — the moment things start to feel real.

What You'll Learn

  • How to connect PHP to a MySQL database using mysqli
  • How to write and execute a SELECT SQL query from PHP
  • How to iterate over results using fetch_assoc(), fetch_row(), fetch_array(), and fetch_object()
  • How to build a complete, working PHP script that displays database records in a browser

The Analogy

Picture yourself as the curator of a digital museum. Deep in the basement sits a vast storage vault (the MySQL database) packed with labeled artifact crates (rows of data). Your job is to write a retrieval order (the SELECT query), hand it to the retrieval bot (PHP + mysqli), and wait while it wheels the matching crates up to the gallery floor (the web page) one at a time. The bot knows exactly which shelf to visit because you gave it the vault's address, your access badge, and the exact crate labels you need — that's your connection string and column names. Without the retrieval order, the crates sit in the dark forever.

Chapter 1: Setting Up Your Local Environment

Before a single query can run, PHP and MySQL must both be reachable from your machine. The quickest path is one of these all-in-one local server stacks:

  • XAMPP — cross-platform (Windows, macOS, Linux), most popular for beginners
  • WAMP — Windows-only, lightweight
  • MAMP — macOS-focused, clean interface

Install any one of them, start the Apache and MySQL services from the control panel, and you have a fully functional PHP + MySQL environment running at http://localhost. All .php files go inside the htdocs (XAMPP/WAMP) or htdocs/www folder to be served by Apache.

Chapter 2: Connecting PHP to MySQL

Every database operation starts with a connection. PHP's built-in mysqli extension (MySQL Improved) handles this with a single constructor call.

<?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";
?>

Key points:

  • $servername is "localhost" when MySQL runs on the same machine as Apache.
  • $username defaults to "root" in local XAMPP/WAMP/MAMP installations.
  • $password is an empty string "" unless you set one in phpMyAdmin.
  • $dbname must exactly match an existing database.
  • $conn->connect_error holds the error string if the connection fails; die() halts the script and prints it, preventing silent failures.

Chapter 3: Writing the SELECT Query

Once connected, you compose a SQL SELECT statement as a plain PHP string and pass it to $conn->query().

<?php
$sql    = "SELECT id, name, age FROM students";
$result = $conn->query($sql);
?>
  • $sql is just a string — you can build it dynamically, add WHERE clauses, ORDER BY, etc.
  • $conn->query($sql) sends the query to MySQL and returns a mysqli_result object on success, or false on failure.
  • $result holds the full result set in memory, ready to be iterated.

Chapter 4: Fetching Results

mysqli_result objects expose four fetch methods, each returning one row at a time from the result set:

MethodReturns
fetch_assoc()Associative array — keys are column names ($row["name"])
fetch_row()Numeric array — keys are column index numbers ($row[1])
fetch_array()Both associative AND numeric — combines both access styles
fetch_object()stdClass object — properties are column names ($row->name)

The most readable choice for beginners is fetch_assoc(), which lets you reference columns by their actual names.

<?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();
?>
  • $result->num_rows tells you how many rows matched the query before you start looping — a fast sanity check.
  • The while loop calls fetch_assoc() on each iteration; when all rows are exhausted, it returns null and the loop ends.
  • $conn->close() releases the database connection. Always call this when you're done.
sequenceDiagram
    participant Browser
    participant PHP
    participant MySQL

    Browser->>PHP: HTTP request for fetch_students.php
    PHP->>MySQL: new mysqli(...) — connect
    MySQL-->>PHP: connection OK
    PHP->>MySQL: SELECT id, name, age FROM students
    MySQL-->>PHP: result set (rows)
    loop Each row
        PHP->>PHP: fetch_assoc() → $row
        PHP->>Browser: echo row data
    end
    PHP->>MySQL: $conn->close()
    PHP-->>Browser: full HTML response

Chapter 5: Putting It All Together

Here is the complete, self-contained PHP script that connects, queries, fetches, displays, and closes — all in one file.

<?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();
?>

Chapter 6: Practical Exercise — The Full Workflow

Follow these steps in order. Each step builds on the previous one.

Step 1 — Create the database and table in phpMyAdmin (or the MySQL CLI):

CREATE DATABASE school;

USE school;

CREATE TABLE students (
    id   INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age  INT NOT NULL
);
  • AUTO_INCREMENT means MySQL assigns id values automatically — you never insert them manually.
  • NOT NULL ensures name and age cannot be left empty.

Step 2 — Insert sample data:

INSERT INTO students (name, age) VALUES ('Alice', 23), ('Bob', 25), ('Carol', 22);

This inserts three rows in a single statement. MySQL assigns id values 1, 2, and 3 automatically.

Step 3 — Create fetch_students.php:

Save the following script as fetch_students.php inside your XAMPP/WAMP/MAMP htdocs folder:

<?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();
?>

Step 4 — Run it: Open http://localhost/fetch_students.php in your browser. You should see:

ID: 1 – Name: Alice – Age: 23
ID: 2 – Name: Bob – Age: 25
ID: 3 – Name: Carol – Age: 22

🧪 Try It Yourself

Task: Add a fourth student named Dave, age 27, to the school database, then modify fetch_students.php so it only returns students older than 22 by changing the SQL query to use a WHERE clause.

Starter snippet — updated query line:

$sql = "SELECT id, name, age FROM students WHERE age > 22";

Success criterion: When you reload http://localhost/fetch_students.php, you should see only Alice (23), Bob (25), and Dave (27) — Carol (22) should not appear because 22 > 22 is false.

🔍 Checkpoint Quiz

Q1. What does $result->num_rows represent, and why check it before the while loop?

Q2. Given the following snippet, what is printed on the first iteration of the loop?

<?php
// Assume the students table has: id=1, name='Alice', age=23
while ($row = $result->fetch_assoc()) {
    echo $row["name"] . " is " . $row["age"];
    break;
}
?>

A) 1 is Alice B) Alice is 23 C) name is age D) Nothing — fetch_assoc() returns null on the first call

Q3. What is the difference between fetch_assoc() and fetch_row()?

A) fetch_assoc() returns an object; fetch_row() returns an array B) fetch_assoc() uses column names as keys; fetch_row() uses integer indexes C) fetch_row() is faster and always preferred D) They return identical data structures

Q4. You have a table called products with columns product_id, product_name, and price. Write the PHP line that executes a query to select all three columns from that table and stores the result in $result.

A1. num_rows is the count of rows returned by the query. Checking it before the loop ensures you only attempt to fetch when there is actual data — otherwise you'd enter the loop with nothing to iterate, or in some MySQL versions cause an error on a null result object.

A2. B) Alice is 23 — fetch_assoc() returns an associative array keyed by column name, so $row["name"] is 'Alice' and $row["age"] is 23.

A3. B) fetch_assoc() returns an array where keys are column names (e.g., $row["name"]). fetch_row() returns a numerically indexed array (e.g., $row[1]). Use fetch_assoc() when readability matters; fetch_row() when you know the exact column order and prefer index access.

A4.

$result = $conn->query("SELECT product_id, product_name, price FROM products");

🪞 Recap

  • Use new mysqli($servername, $username, $password, $dbname) to open a database connection and always check connect_error.
  • $conn->query("SELECT ...") executes the query and returns a mysqli_result object.
  • fetch_assoc() retrieves one row at a time as an associative array keyed by column name; loop with while until all rows are consumed.
  • fetch_row(), fetch_array(), and fetch_object() are alternative fetch styles for numeric, combined, or object-based access respectively.
  • Always call $conn->close() after you're done to release the connection.

📚 Further Reading

Like this topic? It’s one of 48 in Full Stack Essentials.

Block your seat for ₹2,500 and join the next cohort.

Course detailsBlock your seat →