PHP Data Objects (PDO)

[google-translator]

Should I Use MySQLi or PDO?

Both MySQLi and PDO have their advantages:
PDO will work on 12 different database systems, whereas MySQLi will only work with MySQL databases.
So, if you have to switch your project to use another database, PDO makes the process easy. You only have to change the connection string and a few queries.With MySQLi, you will need to rewrite the entire code – queries included.
Both are object-oriented, but MySQLi also offers a procedural API.
Both support Prepared Statements. Prepared Statements protect from SQL injection, and are very important for web application security.

Database Connection

<?php 
try{
                $server = “localhost”;
                $user =”root”;
                $password=””;
                $db=”pdophp”;


                $db = new PDO(” mysql:host=$server; dbname=$db”, $user, $password );
}catch(PDOException $e){
                echo ‘Error: ‘. $e->getMessage();
}


The object operator, ->, is used in object scope to access methods and properties of an object.


$dbcon->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_OBJ);

Insert Query

$sqlquery = ” insert into studentstable(name,age,class,gender) values(‘sabbir’,21,11,’male’) “;
$dbcon->query($sqlquery);$dbcon->exec($sqlquery);
echo ‘inserted successful’;

Select Query

$sqlquery = ” select * from studentstable where id=4 “;
//unsafe Sql injection$stmt = $dbcon->query($sqlquery);
print_r($stmt);
$result = $stmt->fetch();
echo “<br> <pre>”;
print_r($result);
echo “<pre>  <br>”;
echo  “My name is ” .$result[‘name’];

PDO::FETCH_ASSOC vs PDO::FETCH_NUM vs PDO::FETCH_OBJ

https://www php.net/manual/en/pdostatement.fetch.php
https://www.php.net/manual/en/pdo.setattribute.php




$sqlquery = ” select * from studentstable where id=4 “;
//unsafe Sql injection
$stmt = $dbcon->query($sqlquery);


print_r($stmt);
// $result = $stmt->fetch(PDO::FETCH_ASSOC);
// $result = $stmt->fetch(PDO::FETCH_NUM);
// $result = $stmt->fetch(PDO::FETCH_OBJ);


$result = $stmt->fetch();


echo “<br> <pre>”;
print_r($result);
echo “<pre>  <be>”;


// echo  “My name is ” .$result[‘name’];
echo  “My name is ” .$result->class;

PREPARED STATEMENTS (Prepare, BindParam and Execute)Named Placeholder(Parameter) vs Positional parameterFetch() vs FetchAll()

In Fetch_Assoc we have to use FetchAll method to fetch the data
GET ROW COUNT   while loop vs forEach loop

Execute a prepared statement with a bound variable

$insertquery = ” insert into studentstable(name,age,class,gender) values(:name, :age, :class, :gender) “;
$stmt = $dbcon->prepare($insertquery);
$stmt->bindparam(‘:name’, $name);
$stmt->bindparam(‘:age’, $age);
$stmt->bindparam(‘:class’, $class);
$stmt->bindparam(‘:gender’, $gender);




//  Insert multiple values
$name=’Sabbir’;
$age = 26;
$class = 15;
$gender = ‘male’;
$stmt->execute();


//  Insert multiple values
$name=’Shakib’;
$age = 26;
$class = 15;
$gender = ‘male’;
$stmt->execute();


//  Insert multiple values
$name=’Rasel’;
$age = 26;
$class = 15;
$gender = ‘male’;
$stmt->execute();

Execute a prepared statement with an array of insert values

$name=’Sabbir’;
$age = 26;
$class = 15;
$gender = ‘male’;


$insertquery = ” insert into studentstable(name,age,class,gender) values(:name, :age, :class, :gender) “;
$stmt = $dbcon->prepare($insertquery);


// The double arrow operator, =>, is used as an access mechanism for arrays. This means that what is on the left side of it will have a corresponding value of what is on the right side of it in array context


// It is case sensitive Name != to name


$stmt->execute([‘name’ => $name, ‘age’=>$age, ‘class’=>$class, ‘gender’=>$gender]);

SELECT with multiple conditions

$name = ‘Sabbir’;
$age = 26;


// $selectquery = ” select * from studentstable where name=:name && age=:age “;
// $stmt->execute([‘name’=>$name, ‘age’=>$age]);




$selectquery = ” select * from studentstable where name=? && age=? “;
$stmt = $dbcon->prepare($selectquery);
$stmt->execute([$name, $age]);


// $result = $stmt->fetchAll();
// print_r($result);


while($result = $stmt->fetch()){
                echo “<br>”.$result->name;
}

UPDATE 

$id = 48;
$name = ‘Sabbir’;
$updatequery = ” update studentstable set name=:name where id=:id “;
$stmt = $dbcon->prepare($updatequery);
$stmt->execute([‘name’=>$name, ‘id’=>$id]);

DELETE

$id = 48;
$deletequery = ” delete from studentstable where id=:id “;
$stmt = $dbcon->prepare($deletequery);
$stmt->execute([‘id’=>$id]);

Source: https://www.thapatechnical.com/

Keep Connected With https://sabbir.xyz/blog

Leave a Comment

Your email address will not be published. Required fields are marked *