Topic 36 of 56 · Full Stack Advanced

Topic 3 : PHP with Select, Fetch

Lesson TL;DRTopic 3: PHP with Select, Fetch 📖 5 min read · 🎯 beginner · 🧭 Prerequisites: nodeprojects, reactstructure Why this matters Up until now, you've been building pages with data you typed by hand — sta...
5 min read·beginner·php · mysql · mysqli · sql

Topic 3: PHP with Select, Fetch

📖 5 min read · 🎯 beginner · 🧭 Prerequisites: node-projects, react-structure

Why this matters

Up until now, you've been building pages with data you typed by hand — static, fixed, always the same. But real web apps don't work that way. A product listing, a user profile, a news feed — all of that lives in a database, and your PHP code needs to go fetch it. That's exactly what we're covering here: how to write a PHP script that connects to MySQL, runs a SELECT query, and then loops through every row using fetch_assoc to display it live on the page. This is the moment your app starts feeling alive.

What You'll Learn

  • Establish a PHP-to-MySQL connection using mysqli
  • Write a SQL SELECT query inside a PHP script
  • Use fetch_assoc(), fetch_row(), fetch_array(), and fetch_object() to retrieve rows
  • Build a complete, end-to-end script that creates a database, inserts sample data, and displays it in the browser

The Analogy

Think of yourself as the curator of a digital museum. Your database is the underground vault full of artifacts — student records, product listings, blog posts. PHP is the retrieval cart you send down into the vault: you write a manifest (the SQL query) describing exactly which artifacts you want, the cart comes back loaded, and you unpack each item one by one onto the gallery floor (the web page). The fetch_* methods are simply different ways to label each artifact as you unpack it — by name, by number, or both at once.

Chapter 1: Setting Up Your Local Environment

Before any PHP script can talk to MySQL, you need a local server that runs both. Three popular options exist for this:

  • XAMPP — cross-platform (Windows, Mac, Linux), bundles Apache + MySQL + PHP
  • WAMP — Windows-only stack, similar bundle
  • MAMP — Mac-first (also has a Windows version), same idea

Install any one of these, start the Apache and MySQL services, and place your .php files inside the htdocs (XAMPP/WAMP) or Applications/MAMP/htdocs folder. Visiting http://localhost/yourfile.php in a browser will execute the script.

Chapter 2: Connecting PHP to MySQL

Every database-driven PHP script starts the same way: open a connection, check it succeeded, then proceed.

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

mysqli (MySQL Improved) is PHP's built-in extension for communicating with MySQL. Passing all four credentials to new mysqli() opens the connection. If connect_error is non-null, die() halts the script and prints the error message — essential feedback during development.

Chapter 3: Writing the SELECT Query

With a live connection in hand, build a SQL SELECT string and hand it to $conn->query().

<?php
$sql    = "SELECT id, name, age FROM students";
$result = $conn->query($sql);
?>

$result is a mysqli_result object. It holds the server's response but hasn't yet moved any rows into PHP memory — that's what the fetch methods do.

Chapter 4: Fetching the Results

mysqli_result exposes four fetch methods, each returning the same row data in a different shape:

  1. fetch_assoc() — returns the row as an associative array keyed by column name ($row["name"])
  2. fetch_row() — returns the row as a zero-indexed numeric array ($row[1])
  3. fetch_array() — returns the row as both an associative and a numeric array simultaneously
  4. fetch_object() — returns the row as an object with column names as properties ($row->name)

Each call advances the internal pointer to the next row and returns null when there are no more rows — which is why a while loop works so naturally here.

The most common pattern uses fetch_assoc():

<?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 the query matched before you start looping — a useful guard against processing an empty result set. Always call $conn->close() when you're done to release the database connection.

Chapter 5: Putting It All Together

Here is the complete, self-contained PHP script that connects, queries, fetches, and closes:

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

Save this as fetch_students.php inside your server's web root and open it in a browser to see each student printed line by line.

🧪 Try It Yourself

Task: Create the school database, populate a students table, and display every student with a PHP script.

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

CREATE DATABASE school;
USE school;

CREATE TABLE students (
    id   INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age  INT NOT NULL
);

Step 2 — Insert sample data:

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

Step 3 — Save the PHP script as fetch_students.php and run it:

<?php
$servername = "localhost";
$username   = "root";
$password   = "";
$dbname     = "school";

$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$sql    = "SELECT id, name, age FROM students";
$result = $conn->query($sql);

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

Success criterion: Your browser should display three lines like:

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

🔍 Checkpoint Quiz

Q1. What does $result->num_rows tell you, and why is it useful before entering the fetch loop?

Q2. Given the following snippet, what will be printed if the students table is empty?

<?php
$result = $conn->query("SELECT id, name, age FROM students");
if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        echo $row["name"] . "<br>";
    }
} else {
    echo "0 results";
}
?>

A) Nothing is printed
B) 0 results
C) A PHP error is thrown
D) An empty <br> tag

Q3. Which fetch method returns a row where you can access columns both by name ($row["age"]) and by index ($row[2])?

A) fetch_assoc()
B) fetch_row()
C) fetch_array()
D) fetch_object()

Q4. You need to show each student's name as $row->name (object property syntax) instead of $row["name"]. Which single-line change to the while loop achieves this?

A1. $result->num_rows returns the total count of rows matched by the query. Checking it before the loop lets you branch to a "no data" message instead of silently rendering nothing — improving both debuggability and user experience.

A2. B) 0 results — because $result->num_rows is 0, the else branch runs and echoes that string.

A3. C) fetch_array() — it populates both associative keys and numeric indices in the returned array, so both access styles work on the same row.

A4. Replace $result->fetch_assoc() with $result->fetch_object() and update references to use $row->id, $row->name, $row->age instead of bracket notation.

🪞 Recap

  • Use new mysqli($host, $user, $pass, $db) to open a PHP-to-MySQL connection and always check connect_error before proceeding.
  • $conn->query($sql) executes a SQL SELECT and returns a mysqli_result object.
  • fetch_assoc() returns each row as an associative array; fetch_row() uses numeric indices; fetch_array() provides both; fetch_object() exposes columns as object properties.
  • $result->num_rows tells you how many rows were matched — guard your fetch loop with it.
  • Always call $conn->close() when you're done to free the database connection.

📚 Further Reading

Like this topic? It’s one of 56 in Full Stack Advanced.

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

Course detailsBlock your seat →