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.
-
MySQLi Object-Oriented
$conn->query($query);
-
MySQLi Procedural
mysqli_query($conn, $query)
-
PDO
$stmt = $conn->prepare($query); $stmt->execute();
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 = "neveropen"; Ā Ā Ā Ā // 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 = "neveropen"; Ā Ā Ā Ā // 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 = "neveropen"; Ā Ā Ā Ā // 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
