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;">

Advertisements
    • Clister Nitish
    • June 25th, 2012

    nice effort:)

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: