This page looks best with JavaScript enabled

PDO in PHP

 ·  β˜• 3 min read  ·  πŸ€– Arjit Sharma

PDO stands for PHP Data Objects (Object Oriented). It is a PHP Extension to access database.Unlike mysqli it works with multiple databases. There are 3 main Classes are :
β‡’ PDO - Connection between PHP and DB
β‡’ PDOStatement - Prepared Statement
β‡’ PDOException - Represent error raised by PDO

PDO Basics

We create PDO Object,execute a query and fetch result,either in associated array format(FETCH_ASSOC) or object format(FETCH_OBJ )
Line 11 : - Creating PDO Object ( $pdo )
Line 14 : - Executing Query using $pdo→query( sql )
Line 17 : - Fetching result using fetch() method
Line 18 : - As FETCH_ASSOC class is used so we get the query result in associated array format and that is why column ‘title’ is accessed using array referrencing.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
<?php
$host = "localhost";
$user = "root";
$password = "";
$dbname = "test";

//Set DSN = Data Source Name,describes connection to database
$dsn = "mysql:hosts=".$host.";dbname=".$dbname;

//Creating PDO Instance
$pdo = new PDO($dsn, $user, $password);

//PDO Query
$stmt = $pdo->query("SELECT * FROM posts");

//Fetching Rows in Associated array format
while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
    echo $row['title'] . "";
}
?>

Fetching rows in Object Format

PDO::FETCH_OBJ can be used to get query result in form of objects then in order to access columns we will use β†’ symbol

1
2
3
while($row = $stmt->fetch(PDO::FETCH_OBJ)){
    echo $row->title . "";
}

Set FETCH_OBJ as default

After specifying the default format of result we wont need to provide argument in fetch method.

1
2
3
$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE,PDO::FETCH_OBJ)
while($row = $stmt->fetch())//Now this will work
{--}

Prepared Statements

Why to use ?

  • Helps write parametric queries,i.e same query can be used again and again.
  • Faster
  • Prevents SQL injections

Using indexed parameters

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
$author = "Arjit";
$sql = "SELECT * FROM posts WHERE author = ?";
$stmt = $pdo->prepare($sql);

//array of variables is sent in execute
$stmt->execute([$author]);

//fetchall fetches all result, while fetch gives 1
$posts = $stmt->fetchAll(PDO::FETCH_OBJ);

foreach($posts as $post){
    echo $post->title. "";
}

Using named parameters

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
$author = "Arjit";
$is_published = true;

$sql = "SELECT * FROM posts WHERE author = :author and is_published = :is_published";
$stmt = $pdo->prepare($sql);

$stmt->execute(['author' => $author,"is_published"=>$is_published]);

$posts = $stmt->fetchAll(PDO::FETCH_OBJ);

foreach($posts as $post){
    echo $post->title. "";
}

Basic Operations in PDO

Counting no. of rows

1
2
3
4
5
$author = "Arjit";
$stmt = $pdo->prepare("SELECT * FROM posts WHERE author = ?");
$stmt->execute([$author]);
$count = $stmt->rowCount();
echo $count;

Inserting in database

1
2
3
4
5
6
7
8
$title = "Post 4";
$body = "This is a post 4";
$author = "Singham";

$sql = "INSERT INTO posts(title,body,author) VALUES(?,?,?)";
$stmt = $pdo->prepare($sql);
$stmt->execute([$title,$body,$author]);
echo "Post Added";

Updating in database

1
2
3
4
5
6
7
$title = "Post 4 : Updated";
$id = "4";

$sql = "UPDATE posts SET title = ? WHERE id = ?";
$stmt = $pdo->prepare($sql);
$stmt->execute([$title,$id]);
echo "Post Updated";

Deleting in database

1
2
3
4
5
6
$id = "4";

$sql = "DELETE FROM posts WHERE id = ?";
$stmt = $pdo->prepare($sql);
$stmt->execute([$id]);
echo "Post Deleted";

Searching in database

1
2
3
4
5
6
7
8
9
$search = "%title%";
$sql = "SELECT * FROM posts WHERE title LIKE ?";
$stmt = $pdo->prepare($sql);
$stmt->execute([$search]);
$posts = $stmt->fetchAll(PDO::FETCH_OBJ);

foreach($posts as $post){
    echo $post->title. "";
}
Share on

Arjit Sharma
WRITTEN BY
Arjit Sharma
Yo, I am a CS enthusiast or am I ? Just kidding.


What's on this Page