Fetch Data from Database in PHP MySQL – Complete Guide

Introduction

In PHP, fetching data from a MySQL database is done by executing SQL SELECT queries. These queries allow developers to retrieve specific records from database tables and display them on a website.

After establishing a database connection, PHP can execute SELECT queries using functions such as mysqli_query(). The retrieved data can then be processed and displayed using loops.

What is Fetch Data from Database?

Fetching data from a database refers to the process of retrieving stored records from a database table using SQL queries.

In MySQL, data is organized into tables that contain rows and columns. Each row represents a record, and each column represents a specific field.

To retrieve data from these tables, developers use the SELECT statement in SQL.

Example SQL query:


SELECT * FROM users;

This query retrieves all records from the users table.

PHP can execute this query and store the result in a variable. The result set can then be processed using functions like:

  • mysqli_fetch_assoc()
  • mysqli_fetch_array()
  • mysqli_fetch_row()

These functions allow PHP scripts to access each row of data and display it on a webpage.

Why It Is Used

Fetching data from a database is a fundamental part of dynamic web development.

1. Display User Information

Applications retrieve user profiles, account details, or other stored information.

2. Show Blog Posts or Articles

Content management systems fetch posts from the database and display them on the website.

3. Display Product Listings

Online stores retrieve product information such as name, price, and description.

4. Search Results

Search systems retrieve matching records based on user queries.

5. Data Analysis

Applications can retrieve data for reporting or analytics.

Without the ability to fetch data from a database, websites would not be able to display stored content dynamically.

Syntax

The basic SQL syntax for retrieving data is:


SELECT column1, column2 FROM table_name;

To retrieve all columns:


SELECT * FROM table_name;

In PHP, the query is executed using mysqli_query().

Example:


$sql = "SELECT * FROM users";
$result = mysqli_query($conn, $sql);

To fetch rows from the result:


while($row = mysqli_fetch_assoc($result)){
  echo $row['name'];
}

Explanation:

  • mysqli_query() executes the SQL query.
  • mysqli_fetch_assoc() retrieves rows from the result set.
  • The loop processes each row of data.

Example

Below is a simple example of fetching data from a MySQL database using PHP.


$servername = "localhost";
$username = "root";
$password = "";
$database = "testdb";
$conn = mysqli_connect($servername, $username, $password, $database);
if (!$conn) {
  die("Connection failed: " . mysqli_connect_error());
}
$sql = "SELECT name, email FROM users";
$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0) {
  while($row = mysqli_fetch_assoc($result)) {
     echo "Name: " . $row["name"] . " - Email: " . $row["email"] . "
";
  }
} else {
  echo "No records found";
}

Explanation:

  1. The script connects to the database.
  2. A SELECT query retrieves records from the users table.
  3. mysqli_query() executes the query.
  4. mysqli_fetch_assoc() retrieves each row.
  5. The data is displayed on the webpage.

Real-Life Example

A common real-life example of fetching data from a database is a blog website.

In a blog system, all articles are stored in a database table called posts. Each record may contain fields such as:

  • id
  • title
  • content
  • author
  • publish_date

When a user visits the blog homepage, PHP retrieves the articles from the database and displays them.

Example process:

  1. The website connects to the database.
  2. A SELECT query retrieves blog posts.
  3. PHP processes the results using a loop.
  4. Each article is displayed on the webpage.

Another example is an online store, where product details are fetched from the database and displayed on product listing pages.

Common Mistakes

Beginners often make several mistakes when fetching data from databases.

1. Not Checking If Results Exist

If no records exist, the script may display errors.

Solution:


mysqli_num_rows($result)

2. Using Incorrect Column Names

Using incorrect column names will cause errors or empty results.

3. Forgetting to Use a Loop

SELECT queries often return multiple rows, so a loop is needed to display all results.

4. Not Handling Database Errors

Developers sometimes ignore database errors, making debugging difficult.

5. Fetching Data Inefficiently

Fetching large datasets without filters can slow down the application.

Conclusion

Fetching data from a MySQL database using PHP is a crucial part of building dynamic web applications. It allows developers to retrieve stored information and display it to users in real time.

By using SQL SELECT queries and PHP functions like mysqli_query() and mysqli_fetch_assoc(), developers can easily retrieve and process database records.

Related PHP Tutorials