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