• +91-9142290450

  • Hajipur, Bihar, 844101

☰
❮
❯

MySQL Select Data

← Previous Next →

After inserting data into your MySQL database, the next essential step is to retrieve it. Selecting data allows you to display records, perform calculations, and make decisions in your PHP applications.

This tutorial explains how to select data from MySQL tables using SQL queries, PHP MySQLi, and PDO, along with filtering, ordering, and best practices to manage and display your results efficiently.

What are SELECT Statements?

The SELECT statement is used to fetch data from a database table. You can select all columns or specific columns, retrieve a single row or multiple rows, and combine it with conditions, sorting, and limits.

Basic Syntax

SELECT column1, column2 FROM table_name;

To select all columns:

SELECT * FROM students;

Example Table: students

id name age email
1 Neha 16 neha@example.com
2 Anika 17 anika@example.com
3 Riya 16 riya@example.com 
SELECT name, email FROM students;

This query will return only the name and email columns.

Filtering Data with WHERE

The WHERE clause allows you to select only the rows that meet specific conditions.

SELECT * FROM students WHERE age = 16;

This query retrieves all students who are 16 years old.

Using Operators

  • = : Equals

  • > : Greater than

  • < : Less than

  • >= : Greater than or equal

  • <= : Less than or equal

  • != or <> : Not equal

  • LIKE : Pattern matching

SELECT * FROM students WHERE name LIKE 'R%';

This selects all students whose name starts with 'R'.

Ordering Results

The ORDER BY clause sorts the results:

SELECT * FROM students ORDER BY age ASC;
SELECT * FROM students ORDER BY name DESC;

  • ASC : Ascending order

  • DESC : Descending order

You can also sort by multiple columns:

SELECT * FROM students ORDER BY age ASC, name DESC;

Limiting Results

The LIMIT clause restricts the number of rows returned:

SELECT * FROM students LIMIT 3;

You can also use offset:

SELECT * FROM students LIMIT 1, 2;

This skips the first row and returns the next two rows.

Selecting Data Using PHP MySQLi

Procedural Style

<?php
$conn = mysqli_connect("localhost", "root", "", "school_db");
if (!$conn) die("Connection failed: " . mysqli_connect_error());

$sql = "SELECT id, name, email FROM students WHERE age = 16 ORDER BY name ASC";
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {
    while ($row = mysqli_fetch_assoc($result)) {
        echo "ID: " . $row['id'] . " - Name: " . $row['name'] . " - Email: " . $row['email'] . "<br>";
    }
} else {
    echo "No records found.";
}

mysqli_close($conn);
?>

Object-Oriented Style

<?php
$conn = new mysqli("localhost", "root", "", "school_db");
if ($conn->connect_error) die("Connection failed: " . $conn->connect_error);

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

if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        echo "ID: {$row['id']}, Name: {$row['name']}, Age: {$row['age']}, Email: {$row['email']}<br>";
    }
} else {
    echo "No records found.";
}

$conn->close();
?>

Using PDO to Select Data

PDO offers a more flexible approach and supports multiple databases.

<?php
try {
    $conn = new PDO("mysql:host=localhost;dbname=school_db", "root", "");
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $stmt = $conn->query("SELECT id, name, age, email FROM students ORDER BY name ASC");

    $students = $stmt->fetchAll(PDO::FETCH_ASSOC);

    if ($students) {
        foreach ($students as $student) {
            echo "ID: {$student['id']}, Name: {$student['name']}, Age: {$student['age']}, Email: {$student['email']}<br>";
        }
    } else {
        echo "No records found.";
    }
} catch(PDOException $e) {
    echo "Error: " . $e->getMessage();
}

$conn = null;
?>

Explanation:

  • fetchAll(PDO::FETCH_ASSOC) retrieves all results as an associative array.

  • query() is used for simple SELECT statements.

  • prepare() and execute() can be used for dynamic queries with placeholders.

Best Practices

  1. Always filter data using WHERE to avoid retrieving unnecessary rows.

  2. Use LIMIT when fetching only a few rows to improve performance.

  3. Use prepared statements for dynamic queries involving user input.

  4. Use meaningful column names to make results readable.

  5. Close connections and free results after use.

Common Errors

Error Cause Solution
Connection failed Incorrect credentials Verify host, username, password
Unknown column Typo in column name Check table structure
Empty results No matching rows Verify WHERE conditions
Fetch errors Using wrong fetch method Use fetch_assoc(), fetchAll(), or fetch_row correctly

Real-World Example

Suppose you want to display all students aged 17 in a school management system:

$sql = "SELECT name, email FROM students WHERE age = 17 ORDER BY name ASC";

This query fetches only relevant records and displays them sorted alphabetically, improving performance and usability.

Summary of the Tutorial

  • SELECT is used to retrieve data from MySQL tables.

  • Combine it with WHERE, ORDER BY, and LIMIT to filter, sort, and restrict results.

  • Use PHP MySQLi or PDO to fetch results efficiently.

  • Prepared statements are recommended for dynamic queries with user input.

  • Proper filtering, ordering, and limiting improve performance and maintainability.

Selecting data correctly is essential for creating dynamic PHP applications like student dashboards, e-commerce listings, and reporting tools. Understanding and implementing these techniques ensures your applications remain efficient, secure, and scalable.

Practice Questions

  1. Write a PHP MySQLi procedural script to fetch all student records from the students table and display their names and emails.

  2. Write a PHP MySQLi object-oriented script to select only students aged 16 and display their ID, name, and age.

  3. Write a PDO PHP script to retrieve all students and order them by name in ascending order.

  4. Write a SQL query to fetch only the name and email columns from the students table.

  5. Write a PHP script to fetch the first 5 students using the LIMIT clause.

  6. Write a SQL query using the WHERE clause to select students whose name starts with 'R'.

  7. Modify a PDO script to fetch students older than 17 and display their details.

  8. Write a PHP MySQLi script to count how many students are in the table.

  9. Write a SQL query to fetch all students ordered by age descending and then by name ascending.

  10. Explain a real-world scenario where selecting specific data with conditions is useful, such as displaying users in a dashboard or filtering orders in an e-commerce system.

← Previous Next →

Try a Short Quiz.

coding learning websites codepractice

No quizzes available.

PHP

online coding class codepractice

PHP Basics

PHP Data

Control Flow

PHP Functions

PHP Arrays

PHP Forms

PHP Advanced

Cookies & Sessions

Filters & JSON

PHP Exceptions

PHP OOP

MySQL with PHP

PHP and XML

AJAX with PHP

codepractice logo
Login
Go Back Top