Database handling in PHP

There are three method for handling database in PHP

1. MySQLi- object-oriented
2. MySQLi - procedural-oriented
3. PDO - PHP Database Object

First create variable required

$servername = "localhost";

$username = "root";

$password = "password";

$dbname = "sample_db";

Method 1 : - MySQLi - object-oriented

Create connection : -

$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {

die("Connection failed: " . $conn->connect_error);

}

Fetch the records from table : -
$sql = "SELECT * FROM users";

$result = $conn->query($sql);

get rows count : -

$count = $result->num_rows;

iterate through records : -

while($row = $result->fetch_assoc()) {

//print single row details

print_r($row);

}

close connection : -

$conn->close();

Method 2 :- MySQLi - procedural-oriented

Create connection

$conn = mysqli_connect($servername, $username, $password, $dbname);

if (!$conn) {

die("Connection failed: " . mysqli_connect_error());
}

Fetch the records from table : -
$sql = "SELECT * FROM users";

$result = mysqli_query($conn, $sql);

get rows count : -

mysqli_num_rows($result)

iterate through records : -

while($row = mysqli_fetch_assoc($result)) {

//print single row details

print_r($row);
}

close connection:-

mysqli_close($conn);

Method 3:- PDO - PHP Database Objects
Create connection
Set DSN :-
$dsn = "mysql:host=$servername;dbname=$dbname";
Set options:-
$options = array (PDO::ATTR_PERSISTENT => true, PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION);
Create a new PDO instanace:
try {
$conn = new PDO($dsn, $username, $password, $options);
} catch (PDOException $e) {
echo $e->getMessage();

}

Fetch the records from table : -

$sql = "SELECT * FROM users";
$stmt = $conn->prepare($sql);
$stmt->execute();
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

get rows count : -

$stmt->rowCount();

iterate through records : -