We use cookies to ensure that we give you the best experience on our website.

fetch data from MySQL database using PHP

How to fetch data from MySQL database using PHP?

In this tutorial, you will learn how to fetch data from MySQL database using PHP.

Before getting the data, obviously, you first need to know how to write the SQL query to fetch data, and then you’re ready to move on.

PHP fetch data from MySQL database

In the “PHP insert data into MySQL DB” chapter, we setup our database and then we inserted some records (data) into that database.

Here are the records that we are going to fetch using PHP.

MySQL DB dummy user records

1. PHP MySQLi: fetch data from MySQL database

Below is an example of fetching all data from the database.

<?php
$db_host = 'localhost';
$db_user = 'root';
$db_password = '';
$db_name = 'my_test_db';

# Database Connection
$db_connection = mysqli_connect($db_host, $db_user, $db_password, $db_name);

$sql = "SELECT * FROM `users`";
$query = mysqli_query($db_connection, $sql);

# mysqli_num_rows function returns number of rows (records)
if (mysqli_num_rows($query) > 0) {
  
  # fetching all the records
  $allUsers = mysqli_fetch_all($query, MYSQLI_ASSOC);

  # showing each record through foreach loop
  foreach ($allUsers as $user) {
    echo "{$user['name']} | {$user['age']} | {$user['email']} \n";
  }
}
[Running] php "c:\xampp\htdocs\php\index.php"
Akash | 22 | [email protected] 
John Doe | 52 | [email protected] 
Rahul | 25 | [email protected] 
BabuRao | 62 | [email protected] 
Shyam | 38 | [email protected] 
Raju | 36 | [email protected]

In the above code, You can see MYSQLI_ASSOC as the second parameter (mode) of the mysqli_fetch_all function.

You can skip the MYSQLI_ASSOC. In that case, the default value (MYSQLI_NUM) will be considered.

MYSQLI_ASSOC VS MYSQLI_NUM

When you get data from a database, the data is received as a numeric array (MYSQLI_NUM), but if you want to receive the data as an associative array you have to pass MYSQLI_ASSOC.

There is one more constant you can pass which is MYSQLI_BOTH, It will return the data in both formats (ASSOC & NUM).

2. PHP MySQLi OOP: fetch data

Here is the above code but in the form of MySQLi OOP.

<?php
$db_host = 'localhost';
$db_user = 'root';
$db_password = '';
$db_name = 'my_test_db';

# Database Connection
$db_connection = new mysqli($db_host, $db_user, $db_password, $db_name);

$sql = "SELECT * FROM `users`";
$query = $db_connection->query($sql);

# $query->num_rows gives the number of rows (records)
if ($query->num_rows > 0) {

  # fetching all the records
  $allUsers = $query->fetch_all(MYSQLI_ASSOC);

  # showing each record through foreach loop
  foreach ($allUsers as $user) {
    echo "{$user['name']} | {$user['age']} | {$user['email']} \n";
  }
}

3. PHP PDO: fetch data

It’s time to write the Fetch Data code in PDO format.

<?php
$db_host = 'localhost';
$db_user = 'root';
$db_password = '';
$db_name = 'my_test_db';

try {
  # Database Connection
  $dsn = "mysql:host=$db_host;dbname=$db_name;charset=utf8";
  $db_connection = new PDO($dsn, $db_user, $db_password);
  $db_connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

  $sql = "SELECT * FROM `users`";
  $query = $db_connection->query($sql);

  # $query->rowCount() counts the number of rows (records)
  if($query->rowCount() > 0){
    # fetching all the records
    $allUsers = $query->fetchAll(PDO::FETCH_ASSOC);

    # showing each record through foreach loop
    foreach ($allUsers as $user) {
      echo "{$user['name']} | {$user['age']} | {$user['email']} \n";
    }
  }

} catch(PDOException $e) {
  echo $e->getMessage();
}

If you want to fetch data dynamically, you should use prepared statements to perform queries in a safe and efficient manner.

Related Posts