Categories
PHP

PHP PDO CRUD Example

Connecting to database

<?php

try {
$dbh = new PDO("mysql:host=localhost;dbname=DATABASE_NAME", "DATABASE_USER","DATABASE_PASSWORD");
}
catch (PDOException $e) {
    print "Error!: " . $e->getMessage() . "<br/>";
    die();
}

$dbh = null;

Create

$sth = $dbh->prepare("INSERT INTO fruits (name,colour) VALUES (?,?)");

$sth->bindParam(1, "Apple", PDO::PARAM_STR);
$sth->bindParam(2, "red", PDO::PARAM_STR);

$sth->execute();

// OR

$sth = $dbh->prepare("INSERT INTO fruits (name,colour) VALUES (:name,:colour)");

$sth->bindParam(":name", "Apple", PDO::PARAM_STR);
$sth->bindParam(":colour", "red", PDO::PARAM_STR);

$sth->execute();

Read

 $sth = $dbh->prepare("SELECT name FROM fruits WHERE colour=:colour");
 $sth->bindParam(":colour", "red", PDO::PARAM_STR);
 $sth->execute();
 foreach ($sth->fetchAll() as $row) {
 echo $row['name'];             
 }

Update

 $sth = $dbh->prepare("UPDATE fruits SET colour=:colour WHERE name=:name");
 $sth->bindParam(":colour", "green", PDO::PARAM_STR);
 $sth->bindParam(":name", "kiwi", PDO::PARAM_STR);
 $sth->execute();

Delete

 $sth = $dbh->prepare("DELETE FROM fruits WHERE colour = :colour");
 $sth->bindParam(":colour", "green", PDO::PARAM_STR);
 $sth->execute();