Posts Tagged ‘ Database ’

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

How to Image Store and Update on database and view stored image from database using Cakephp ?


1> create a database table as follows.

— Table structure for table `users`

CREATE TABLE IF NOT EXISTS `users` (

`id` int(11) unsigned NOT NULL AUTO_INCREMENT,

`name` varchar(100) DEFAULT NULL,

`email` varchar(150) DEFAULT NULL,

`firstname` varchar(60) DEFAULT NULL,

`lastname` varchar(60) DEFAULT NULL,

`img_name` varchar(25) NOT NULL,

`image` blob NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 ;

2> create a model on your app/models folder

//model for the abave table

class User extends AppModel {

var $name = ‘User’;

var $hasMany = array(‘Post’);

}

3> create a controller as following on app/controllers folder.

//my TestUsers controller

class TestUsersController extends Controller {

var $name = ‘TestUsers’;

var $uses = array(‘User’);

var $helpers = array(‘Html’, ‘Form’);

var $components = null;

/*

* method: This method is for upload and store an image to database table.

* @arguments: null

* @return: null

*/

function add(){

if($this->data &&is_uploaded_file($this->data[‘File’][‘image’][‘tmp_name’]) ){

$this->data[‘User’][‘img_name’] = $this->data[‘File’][‘image’][‘name’];

$fileData = fread(

fopen($this->data[‘File’][‘image’][‘tmp_name’], “r”),

$this->data[‘File’][‘image’][‘size’]

);

$this->data[‘User’][‘image’] = $fileData;

if($this->User->save($this->data[‘User’])){

echo “File uploaded successfully”;

}else{

echo “File does not uploaded successfully”;

}

}

}

/*

* method: This method is for edit the uploaded and stored image to database table and show the upload image from database.

* @arguments: null

* @return: null

*/

function edit($id=null){

if($this->data &&is_uploaded_file($this->data[‘File’][‘image’][‘tmp_name’]) ){

$this->data[‘User’][‘img_name’] = $this->data[‘File’][‘image’][‘name’];

$fileData = fread(

fopen($this->data[‘File’][‘image’][‘tmp_name’], “r”),

$this->data[‘File’][‘image’][‘size’]

);

$this->data[‘User’][‘image’] = $fileData;

if($this->User->save($this->data[‘User’])){

echo “File uploaded successfully”;

}else{

echo “File does not uploaded successfully”;

}

}else{

$this->data = $this->User->read(null,$id);

}

}

/*

* method: this method is for retrive the image from database.

* this is action will be on your image sourse.

* (here it is : project_nam/TestUsers/image)

* @arguments: null

* @arguments: null

*/

function image($id){

$this->data = $this->User->read(null,$id);

echo $this->data[‘User’][‘image’];

die;

}

}

4> create view add.ctp on app/views/test_users folder.

<?php e($form->create(‘User’,array(‘url’=>array(‘controller’=>’test_users’, ‘action’=>’add’), ‘type’ => ‘file’, ‘name’=>’test_user_form’))); ?>

<?php e($form->input(‘name’)); ?>

<?php e($form->input(’email’)); ?>

<?php e($form->input(‘firstname’)); ?>

<?php e($form->input(‘lastname’)); ?>

<?php e($form->file(‘File.image’)); ?>

<?php e($form->submit(‘submit’, array(‘style’=>’float:left’, ‘width’=>’100px’))); ?>

<?php e($form->end()); ?>

4> create view edit.ctp on app/views/test_users folder.

<?php e($form->create(‘User’,array(‘url’=>array(‘controller’=>’test_users’, ‘action’=>’add’), ‘type’ => ‘file’, ‘name’=>’test_user_form’))); ?>

<?php if(isset ($this->data[‘User’][‘image’])):?>

<img alt=”img” src=”http://localhost/mycake/test_users/image/&lt;?php echo $this->data[‘User’][‘id’]; ?>” />

<?php endif; ?>

<?php e($form->input(‘name’)); ?>

<?php e($form->input(’email’)); ?>

<?php e($form->input(‘firstname’)); ?>

<?php e($form->input(‘lastname’)); ?>

<?php e($form->file(‘File.image’)); ?>

<?php e($form->submit(‘submit’, array(‘style’=>’float:left’, ‘width’=>’100px’))); ?>

<?php e($form->hidden(‘id’)); ?>

<?php e($form->end()); ?>

now for image insert use:

http://localhost/project_name/test_users/add

for edit and view database image use.

http://localhost/project_name/test_users/edit/4

(here 4 is the id of users table )

Thats all