How to fetch data from the database in PHP ?
Last Updated :
17 Mar, 2022
Database operations in PHP are a very crucial thing that is especially needed in CRUD (Create, Read, Update and Delete) operations.
In this article, we will discuss the Read part i.e. data fetching from database.
There are two ways to connect to a database using PHP. They are as follows.
- MySQLi ("i" stands for improved)
- PDO (PHP Data Objects)
MySQLi vs PDO: Both the ways are really good but there is only one difference between the two methods, PDO can work on 12 different database systems whereas MySQLi works with MySQL databases only.
Connecting to a Database:
MySQLi Object-OrientedÂ
$conn = new mysqli($servername, $username, $databasename)
MySQLi Procedural
$conn = mysqli_connect($servername,
$username, $password, $databasename);PDO
$conn = new PDO("mysql:host=$servername;dbname=myDB",
$username, $password, $databasename);
Executing Queries: After connecting to the database we need to run queries to fetch data. In Read operations, we will use only select queries to fetch data from the database.
Close Connection: After the fetching is performed, you should close the connection to the database using the close() function.
$conn->close();
Sample Database
Create Table in the database:
CREATE TABLE `Student Details` (
`Roll_No` int(11) NOT NULL,
`Name` varchar(255) NOT NULL,
`City` varchar(255) NOT NULL,
`Age` int(11) NOT NULL,
PRIMARY KEY (`Roll_No`)
);
Student Details
MySQLi Object-Oriented approach:
PHP Code:
PHP
<?php
$servername = "localhost";
$username = "root";
$password = "";
$databasename = "geeksforgeeks";
// CREATE CONNECTION
$conn = new mysqli($servername,
$username, $password, $databasename);
// GET CONNECTION ERRORS
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// SQL QUERY
$query = "SELECT * FROM `Student Details`;";
// FETCHING DATA FROM DATABASE
$result = $conn->query($query);
if ($result->num_rows > 0)
{
// OUTPUT DATA OF EACH ROW
while($row = $result->fetch_assoc())
{
echo "Roll No: " .
$row["Roll_No"]. " - Name: " .
$row["Name"]. " | City: " .
$row["City"]. " | Age: " .
$row["Age"]. "<br>";
}
}
else {
echo "0 results";
}
$conn->close();
?>
Output:
Roll No: 1 - Name: Ram | City: Delhi | Age: 18
Roll No: 2 - Name: Shyam | City: Mumbai | Age: 19
Roll No: 3 - Name: Rohit | City: Chennai | Age: 18
Roll No: 4 - Name: Suresh | City: Kolkata | Age: 20
MySQLi Procedural approach:
PHP Code:
PHP
<?php
$servername = "localhost";
$username = "root";
$password = "";
$databasename = "geeksforgeeks";
// CREATE CONNECTION
$conn = mysqli_connect($servername,
$username, $password, $databasename);
// GET CONNECTION ERRORS
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
// SQL QUERY
$query = "SELECT Roll_No, Name FROM `Student Details`;";
// FETCHING DATA FROM DATABASE
$result = mysqli_query($conn, $query);
if (mysqli_num_rows($result) > 0) {
// OUTPUT DATA OF EACH ROW
while($row = mysqli_fetch_assoc($result)) {
echo "Roll No: " . $row["Roll_No"]
. " - Name: " . $row["Name"]. "<br>";
}
} else {
echo "0 results";
}
$conn->close();
?>
Output:
Roll No: 1 - Name: Ram
Roll No: 2 - Name: Shyam
Roll No: 3 - Name: Rohit
Roll No: 4 - Name: Suresh
PDO Approach:
PHP Code:
PHP
<?php
$servername = "localhost";
$username = "root";
$password = "";
$databasename = "geeksforgeeks";
// CREATE CONNECTION
$conn = mysqli_connect($servername,
$username, $password, $databasename);
// GET CONNECTION ERRORS
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
// SQL QUERY
$query = "SELECT Roll_No, Name, City FROM `Student Details` WHERE Age > 18;";
try
{
$conn = new PDO(
"mysql:host=$servername;dbname=$databasename",
$username, $password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $conn->prepare($query);
// EXECUTING THE QUERY
$stmt->execute();
$r = $stmt->setFetchMode(PDO::FETCH_ASSOC);
// FETCHING DATA FROM DATABASE
$result = $stmt->fetchAll();
// OUTPUT DATA OF EACH ROW
foreach ($result as $row)
{
echo "Roll No: " . $row["Roll_No"]. " - Name: " .
$row["Name"]. " | City: " . $row["City"]. "<br>";
}
} catch(PDOException $e) {
echo "Error: " . $e->getMessage();
}
$conn->close();
?>
Output:
Roll No: 2 - Name: Shyam | City: Mumbai
Roll No: 4 - Name: Suresh | City: Kolkata
Similar Reads
How to Retrieve Data from Database in Rails?
In this article, we'll be creating a new Rails project, setting up a database, adding demo data, and retrieving that data to display in your application. Steps to Retrieve Data from DatabaseStep 1: Create a Project You can create a new Rails project using the following command in your terminal. rail
3 min read
How to retrieve data from MySQL database using PHP ?
There are steps to understand for retrieving the data from the MySQL database. Approach: Create the database, then create the table for data.Enter the rows in the table. You have to connect to the database. Now we understand each and every step as shown below.  Example 1: In this. we use PHPMyAdmin
2 min read
How to Run a Database Query in a JS File?
To run a database query in a JavaScript file, you typically use a Node.js environment along with a library or package that can communicate with your database. Here’s a basic example of how to set up and run a database query using Node.js and a popular database library, such as mysql2 for MySQL or pg
3 min read
How to Update Data in MySQL Database Table Using PHP?
Updating data in a MySQL database table using PHP is a fundamental aspect of web development, particularly in applications where user interactions involve modifying existing records. This guide delves into the process of updating data in a MySQL database table using PHP, covering database connection
3 min read
How to Create a New Database in phpMyAdmin?
We have seen many apps uses any specific backend database or services which are used to store data of the app within itself. In this article we will take a look on creating a new SQL database in phpMyAdmin so that we can perform CRUD operations using that database. In this article we will be simply
3 min read
How to Extract Data from an XML File Using PHP ?
XML, which stands for Extensible Markup Language, is a data storage format that is easily searchable and understandable. It simplifies the process of storing, retrieving, and displaying data in informative applications. It is widely used in web applications. In this article, we will learn how to ext
3 min read
How to Build a Calendar Table in PHP?
In PHP, by using the Date and DateTime Functions, we can build a dynamic Calendar Table based on user input. Below are the approaches to Build a Calendar Table in PHP: Table of Content Using PHP's Date FunctionsUsing PHP's DateTime ObjectUsing PHP's Date FunctionsIn this approach, we are using PHP's
3 min read
Create a drop-down list that options fetched from a MySQL database in PHP
In many scenarios, we may need to create a dropdown input that can display all the options consistent with the current state of the database. This form of input is used many times in real life and the following examples may help to understand the same.A set of students who have unique registration n
4 min read
How to Insert JSON data into MySQL database using PHP?
To insert JSON data into MySQL database using PHP, use the json_decode function in PHP to convert JSON object into an array that can be inserted into the database. Here, we are going to see how to insert JSON data into MySQL database using PHP through the XAMPP server in a step-by-step way. JSON Str
3 min read
How Does an API Work with A Database?
APIs define methods and protocols for accessing and exchanging data, allowing developers to integrate various services and functionalities into their applications seamlessly. On the other hand, databases store data in a structured manner, enabling efficient storage, retrieval, and management of info
5 min read