Select Data in MySQL Using PHP

In this tutorial, we will learn how to select data from a MySQL database using PHP. Retrieving data from a database is one of the most essential operations when developing dynamic and data-driven web applications.

PHP provides multiple ways to fetch data from a MySQL database table:

• MySQLi Procedural method
• MySQLi Object-Oriented method
• PDO (PHP Data Objects) method

These methods allow us to execute SELECT queries, fetch single or multiple records from the table, and display the results dynamically on web pages. Let us understand first what SELECT statement.

Introduction to SELECT Query

The SQL provides the SELECT statement, which is used to retrieve data from one or more database tables. PHP executes this query and returns the result in the form of arrays or objects, which you can then display in HTML.

To select specific columns from a table, use the following syntax:

SELECT column1, column2, column3 FROM table_name

To select all columns from a table, use the * character like this:

SELECT * FROM table_name

Example:

SELECT id, name, email FROM users;

Requirements Before Selecting Data from Table

Before selecting data from a MySQL table using PHP, make sure the following requirements are met:

• A MySQL database must be created.
• PHP must be successfully connected to the MySQL database.
• The table must exist in the selected database.
• The table should contain record to retrieve. This is optional, but needed to see results.
• The database user has permission to access the table.

We hope you have met all the basic requirements before selecting data from a MySQL table.
[blocksy-content-block id=”12371″]

Select Data Using MySQLi

Inside the htdocs folder, create a new file with name select_data.php and paste this code into it.

Example 1: Procedural Method

<?php
// Include database connection.
require_once "connect.php";

$sql = "SELECT id, name, email, age FROM students";
// Execute the SQL query.
$result = mysqli_query($conn, $sql);

// Process the result set.
if (mysqli_num_rows($result) > 0) {

 // Output data of each row.
    while($row = mysqli_fetch_assoc($result)) {
        echo "ID: " . $row["id"] . "<br>";
        echo "Name: " . $row["name"] . "<br>";
        echo "Email: " . $row["email"] . "<br>";
        echo "Age: " . $row["age"] . "<br><br>";
    }
} else {
     echo "No records found";
}
// Close connection.
mysqli_close($conn);
?>

Now save this file using Ctrl + S.

Now open the browser and go to http://localhost/select_data.php. If the record exists into the table, you will see the following output in your browser.

Output:

Connected successfully.
ID: 11123
Name: John
Email: john@gmail.com
Age: 22

ID: 12332
Name: Tripti
Email: tripti@gmail.com
Age: 26

[blocksy-content-block id=”12121″]

Explanation of PHP Code

• The line require_once "connect.php"; includes the database connection file. This file contains the code that connects PHP to the MySQL database.
• Without this connection, PHP cannot communicate with the database.
• Then, we created a SQL statement and stored it inside the variable named $sql.
• The SQL SELECT statement retrieves data from the students table.
• The mysqli_query() function executes the SQL query. This function takes two parameters:
  • $conn → database connection
  • $sql → SQL query
• The result is stored in the $result variable.
• The mysqli_num_rows() function defined inside if statement returns the number of rows in the result.
• If rows exist (i.e. greater than 0), the condition becomes true. Then, the program processes the data.
• If no records exist inside the table, it goes to the else block to display “No records found”.
• We have used while loop to iterate over multiple records inside the table.
• The mysqli_fetch_assoc() function fetches one record at a time and returns the record as an associative array. The loop runs until all records are fetched.
• The line mysqli_close($conn); closes the database connection. This frees up server resources.
• It is good practice to close the connection after completing the database task.

Example 2: Object-Oriented Method

<?php
// Include database connection.
require_once "connect.php";

$sql = "SELECT * FROM students";
// Execute the SQL query.
$result = $conn->query($sql);

// Process the result set.
if ($result->num_rows > 0) {
  
  // Output data of each row.
     while($row = $result->fetch_assoc()) {
         echo "ID: " . $row["id"] . "<br>";
         echo "Name: " . $row["name"] . "<br>";
         echo "Email: " . $row["email"] . "<br>";
         echo "Age: " . $row["age"] . "<br><br>";
     }
} else {
     echo "No records found";
}
// Close connection.
$conn->close();
?>

[blocksy-content-block id=”12153″]

Explanation of PHP Code

In this example:

• The line $result->num_rows is a property in MySQLi object-oriented method that returns the total number of rows returned by the SELECT query.
• This property is commonly used when you want to check whether the SELECT query returned any records or not.
• The line $row = $result->fetch_assoc(); fetch a single row of data from a database result set as an associative array and moves the internal pointer to the next row.
• The fetch_assoc() function fetches one row at a time and returns the row as an associative array. Associative array means column names are used as keys.
• This function is commonly used inside a loop to fetch and display multiple rows from a database.

Select Data Using PDO Method

Inside the htdocs folder, create a new file with name select_data_pdo.php and paste this code into it.

Example 3: Select Data with PDO Method

<?php
// Include database connection.
require_once "config.php";

try {
 // SQL SELECT query.
    $sql = "SELECT * FROM students";
 // Execute the SQL query.
    $result = $conn->query($sql);

// Check if records exist.
   if ($result->fetch() > 0) {
   // Fetch and display each row.
      while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
          echo "ID: " . $row["id"] . "<br>";
          echo "Name: " . $row["name"] . "<br>";
          echo "Email: " . $row["email"] . "<br>";
          echo "Age: " . $row["age"] . "<br><br>";
      }
   } else {
       echo "No records found.";
   }
} catch(PDOException $e) {
  // Display error message
     echo "Error: " . $e->getMessage();
}
// Close connection
$conn = null;
?>

Explanation of PHP Code

In this example:

• The line $sql = “SELECT * FROM students”; selects all columns from the students table.
• The line $result = $conn->query($sql); executes the SELECT query and returns a PDOStatement object.
• The variable named $result stores the result set, which can be used to fetch the records.
• The line if ($result->fetch() > 0) checks whether records exist in the result set or not. If records exist, the loop executes and fetches the rows. Otherwise, the else block executes.
• The fetch() method retrieves one row at a time from the PDOStatement object,
• The PDO::FETCH_ASSOC returns the row as an associative array, where column names are used as array keys.

Conclusion

PHP provides three main methods to select data from a MySQL database table. They are:

• MySQLi Procedural → Uses mysqli_query() and mysqli_fetch_assoc() functions to execute SELECT queries and retrieve records.
• MySQLi Object-Oriented → Uses $conn->query() and $result->fetch_assoc() methods to fetch records in an object-oriented way.
• PDO (PHP Data Objects) method → Uses $conn->query() and $stmt->fetch() methods to retrieve records.

These methods allow you to execute SELECT queries, fetch single or multiple rows from a table, and display the data dynamically on web pages.

We hope this tutorial helped you understand how to select data from a MySQL database using PHP and practiced all examples.