A Database Class with Create, Read, Update And Delete operation using PHP Data Object ( PDO ) with Prepared Statement.
PDO stands for PHP Data Objects. It is developed to provide a lightweight interface for different database engines. And one of the very good features of PDO is that it works like a Data Access Layer so that you can use the same function names for all database engines. You can connect to different databases using DSN (Data Source Name) strings.
And some advantages of using prepared statements:
1>Better Performance
2>Prevention of SQL injection
3>Saving memory while handling blobs
<!––
– Create Database: `news`
– Then run the following sql
–
CREATE TABLE IF NOT EXISTS `articles` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`title_en` varchar(250) DEFAULT NULL,
`title_bn` varchar(250) DEFAULT NULL,
`description_en` text,
`description_bn` text,
`image` varchar(50) DEFAULT NULL,
`published` timestamp NULL DEFAULT NULL,
`created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`active` tinyint(1) NOT NULL DEFAULT ‘1′ COMMENT ‘0=>retire,
1=>active’,
`category_id` int(11) unsigned NOT NULL COMMENT ‘refer to id field of
categories table’,
`user_id` int(11) unsigned NOT NULL COMMENT ‘refer to id field of user
table’,
PRIMARY KEY (`id`),
KEY `published` (`published`,`active`,`category_id`),
KEY `user_id` (`user_id`),
KEY `fk_articles_categories_relation` (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
–
– Dumping data for table `articles`
–
INSERT INTO `articles` (`id`, `title_en`, `title_bn`,
`description_en`, `description_bn`, `image`, `published`, `created`,
`active`, `category_id`, `user_id`) VALUES
(1, ‘test’, ‘test’, ‘test’, ‘test’, ‘test’, ‘2010-03-10 00:16:50′,
‘2010-03-10 00:16:50′, 1, 1, 1),
(2, ‘test2′, ‘test2′, ‘test-desc2′, ‘test-desc2′, ‘test2′, ‘2010-03-19
19:21:39′, ‘2010-03-19 19:21:39′, 1, 1, 1),
(3, ‘Upsate WOW Sunny Morning22!!!’, ‘Update WOW Dark Morning22!!!’,
‘description_en’, ‘description_bn’, ‘image’, ‘2010-03-10 00:16:50′,
‘2010-03-10 00:16:50′, 1, 1, 1);
–>
<?php
/*
* in my config file i defined following things
*/
//Database Connection
/*
* @DSN: It means Data Sourse Name . Here i am connecting my database to
mysql database so my DSN is defined as following.
* few properties are —
*
*
* @host: default localhost. if it it on live server then use your
liveserver hostnam
* such as https://ruzdi.wordpress.com/
* @dbname: name of your database.
*/
define(‘DSN’,'mysql:host=localhost;dbname=news’);
/*
*
* @USERNAME : name of database user here my databasee user name is root.
*/
define(‘USERNAME’,'root’);
/*
* @PASSWORD : name of database user password here my databasee user
password is root.
*/
define(‘PASSWORD’,'root’);
?>
<?php
/*
* @author Ruzdi
*
*
* @class : This is the Abstract DB class this clss is responsible for
connecting with database .
*/
abstract class DB{
/*
*@function: This is the the function which is responsible for connecting
with database.
*
* @arguments: null
* @return : connection link.
*/
public function connectMySql(){
$dsn = DSN;
$user = USERNAME;
$password = PASSWORD;
try{
$pdoLink = new PDO($dsn, $user, $password);
}catch(PDOException $e){
echo ‘Connection Failed :: ‘.$e->getMessage();
return false;
}
return $pdoLink;
}
}
/*
* @author Ruzdi
*
*
* @class : This is the Articles class this clss is responsible for
create, read, update and delete funtionalities with database using PHP
Data Object ( PDO ) with Prepared Statement.
*/
class Articles extends DB {
private $pdo;
public $result;
public function __construct(){}
/*
* @function: This is the the function which is responsible for viewing a
single articles.
*
* @arguments: $id: id of the article
* @return : single stdClass Object array.
*/
public function showArticle($id){
try {
$this->pdo = $this->connectMySql();
$query = ‘SELECT
id, title_en, title_bn,
description_en, description_bn,
image, published, created, active,
category_id, user_id
FROM
articles
WHERE
id =:id’;
$stmt = $this->pdo->prepare($query);
$stmt->bindParam(‘:id’,$id, PDO::PARAM_INT);
if(!$stmt->execute()){
return false;
}
$nart = $stmt->rowCount();
if ($nart == 0) {
return “No Article found on id “.$id;
}
$this->result = $stmt->fetch(PDO::FETCH_OBJ);
$this->pdo = null;
return $this->result;
} catch (PDOException $e) {
print “Error!: ” . $e->getMessage() . “<br/>”;
}
return NULL;
}
/*
* @function: This is the the function which is responsible for viewing
all articles.
*
* @arguments: null
* @return : array of stdClass Object array.
*/
public function showArticles(){
try {
$this->pdo = $this->connectMySql();
$query = ‘SELECT
id, title_en, title_bn,
description_en, description_bn,
image, published, created, active,
category_id, user_id
FROM
articles
ORDER BY
id DESC’;
$stmt = $this->pdo->prepare($query);
if(!$stmt->execute()){
return false;
}
$nart = $stmt->rowCount();
if ($nart == 0) {
return “No Article Found”;
}
$this->result = array(‘count’=>$nart,
‘result’=>$stmt->fetchAll(PDO::FETCH_OBJ));
$this->pdo = null;
return $this->result;
} catch (PDOException $e) {
print “Error!: ” . $e->getMessage() . “<br/>”;
}
return NULL;
}
/*
* @function: This is the the function which is responsible for inserting
articles.
*
* @arguments: $params: it is an array where fields are as key of the
array and the value of the array are corresponding fields value.
* example: $params['title_en']=”Title” here “title_en”
is the field name of the table and “Title” is the value of the field.
* @return : boolean true for successfully inseerted false for insertion
failed.
*/
public function insertArticle($params){
try {
$this->pdo = $this->connectMySql();
$query = ‘INSERT
INTO articles
SET
title_en = :title_en,
title_bn = :title_bn,
description_en = :description_en,
description_bn = :description_bn,
image = :image,
published=:published,
created=:created,
active=:active,
category_id=:category_id,
user_id=:user_id’;
$stmt = $this->pdo->prepare($query);
$stmt->bindParam(‘:title_en’,$params['title_en'],
PDO::PARAM_STR); // PDO::PARAM_STR is used for treating
$params['title_en'] as String
$stmt->bindParam(‘:title_bn’,$params['title_bn'], PDO::PARAM_STR);
$stmt->bindParam(‘:description_en’,$params['description_en'],
PDO::PARAM_STR);
$stmt->bindParam(‘:description_bn’,$params['description_bn'],
PDO::PARAM_STR);
$stmt->bindParam(‘:image’,$params['image'], PDO::PARAM_STR);
$stmt->bindParam(‘:published’,$params['published'], PDO::PARAM_STR);
$stmt->bindParam(‘:created’,$params['created'], PDO::PARAM_STR);
$stmt->bindParam(‘:active’,$params['active'], PDO::PARAM_INT);
// PDO::PARAM_INT is used for treating $params['active'] as Integer
$stmt->bindParam(‘:category_id’,$params['category_id'],
PDO::PARAM_INT);
$stmt->bindParam(‘:user_id’,$params['user_id'], PDO::PARAM_INT);
if(!$stmt->execute()){
return false;
}
$this->pdo = null;
return true;
} catch (PDOException $e) {
print “Article insertion is failed !: ” . $e->getMessage() .
“<br/>”;
return false;
}
}
/*
* @function: This is the the function which is responsible for updating
articles.
*
* @arguments: $params: it is an array where fields are as key of the
array and the value of the array are corresponding fields value.
* AND $params['id'] is the id of table which need to be
updated.
* example: $params['title_en']=”Title” here “title_en”
is the field name of the table and “Title” is the value of the field.
* @return : boolean true for successfully updated false for update
failed.
*/
public function updateArticle($params){
try {
$this->pdo = $this->connectMySql();
$query = “UPDATE
articles
SET
title_en = :title_en,
title_bn = :title_bn
WHERE
id=:id” ;
$stmt = $this->pdo->prepare($query);
$stmt->bindParam(‘:id’,$params['id'], PDO::PARAM_INT);
$stmt->bindParam(‘:title_en’,$params['title_en'], PDO::PARAM_STR);
$stmt->bindParam(‘:title_bn’,$params['title_bn'], PDO::PARAM_STR);
if(!$stmt->execute()){
return false;
}
$this->pdo = null;
return true;
} catch (PDOException $e) {
print “Article update failed !: ” . $e->getMessage() . “<br/>”;
return false;
}
}
/*
* @function: This is the the function which is responsible for deleting
articles.
*
* @arguments: $id: This is the id of the row which need to be deleted .
* @return : boolean true for successfully deleted false for delete
failed.
*/
public function deleteArticle($id){
try {
$this->pdo = $this->connectMySql();
$query = “DELETE FROM
articles
WHERE
id=:id” ;
$stmt = $this->pdo->prepare($query);
$stmt->bindParam(‘:id’,$id, PDO::PARAM_INT);
if(!$stmt->execute()){
return false;
}
$this->pdo = null;
} catch (PDOException $e) {
print “Article delete failed !: ” . $e->getMessage() . “<br/>”;
return false;
}
return true;
}
}
$articles = new Articles();
/*
* For showing all articles
*/
/*
*
$result = $articles->showArticles();
echo “<pre>”;
print_r($result);
echo “</pre>”;
echo $result['result'][0]->title_bn;
*
*/
/***
* For single article
*/
/*
*
*
$result = $articles->showArticle(5);
echo “<pre>”;
print_r($result);
echo “</pre>”;
echo $result->title_en;
*/
/*
*
* For inserting article
*/
/*
$params = array(
‘title_en’ => ‘WOW Sunny Morning3!!!’,
‘title_bn’ => ‘WOW Dark Morning3!!!’,
‘description_en’ => ‘description_en’,
‘description_bn’ => ‘description_bn’,
‘image’ =>’image’,
‘published’=>’2010-03-10 00:16:50′,
‘created’=>’2010-03-10 00:16:50′,
‘active’=>1,
‘category_id’=>1,
‘user_id’=>1
);
if ($articles->insertArticle($params)) {
echo “Successfully Article Inserted”;
}else{
echo “Article Insertion is Failed “;
}*/
/*
*
* For updating article
*/
/*
$params = array(
‘id’=>3,
‘title_en’ => ‘Upsate WOW Sunny Morning22!!!’,
‘title_bn’ => ‘Update WOW Dark Morning22!!!’
);
if ($articles->updateArticle($params)) {
echo “Successfully Article Updated”;
}else{
echo “Article Update is Failed “;
}
*/
/*
*
* For delete an article
*/
/*if($articles->deleteArticle(1)){
echo “Successfully article deleted”;
}else{
echo “Article delete failed “;
}*/
<span style="font-family: Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif; line-height: 19px; white-space: normal; font-size: 13px;">
<pre style="font: normal normal normal 12px/18px Consolas, Monaco, 'Courier New', Courier, monospace;">
