Archive for the ‘ Database ’ Category

Simple solution to complex where clause brace problen in Code-igniter


In Code-Igniter How do you make a query with where clause with a brace like following —

SELECT * FROM props 
WHERE `active` = 1
AND (rent >= 25 OR price >=25) AND 1=  1
AND `props`.`id` = '1'

Code-Igniter active record pattern does not generally support this type of query as far i searched.

I found a surprising solution for this type of query. for this query i make the where clause like following —

$this->db->where('(rent >= 25 OR price >=25) AND 1= ',1); 
$this->db->where('props.id',1); 

Which does the tricks.

I don’t actually know this is the fault of the developers or a feature but it is good for my work to move on.

so the total query for this SQL becomes —

$this->db->where('(rent >= 25 OR price >=25) AND 1= ',1); 
$this->db->where('props.id',1); 
$this->db->get('props');

cheers………

Advertisements

Simple steps to install Apache, MySql , PHP (LAMP) and PhpMyAdmin On Ubuntu Server


Install mysql :

First we install MySQL 5 like this:

sudo apt-get install mysql-server mysql-client

You will be asked to provide a password for the MySQL root user – this password is valid for the user root@localhost as well as root@server1.example.com, so we don’t have to specify a MySQL root password manually later on:

New password for the MySQL “root” user: <– yourrootsqlpassword
Repeat password for the MySQL “root” user: <– yourrootsqlpassword

Install apache:

Apache2 is available as an Ubuntu package, therefore we can install it like this:


sudo apt-get install apache2

Now direct your browser to http://localhost/ and you should see the Apache2 placeholder page (It works!):

Apache Test

Apache Test

Apache’s default document root is /var/www on Ubuntu, and the configuration file is /etc/apache2/apache2.conf. Additional configurations are stored in subdirectories of the /etc/apache2 directory such as /etc/apache2/mods-enabled (for Apache modules), /etc/apache2/sites-enabled (for virtual hosts), and /etc/apache2/conf.d.

Install php:

We can install PHP5 and the Apache PHP5 module as follows:


sudo apt-get install php5 libapache2-mod-php5

We must restart Apache afterwards:


sudo /etc/init.d/apache2 restart

Testing PHP5 / Getting Details About Your PHP5 Installation

The document root of the default web site is /var/www. We will now create a small PHP file (info.php) in that directory and call it in a browser. The file will display lots of useful details about our PHP installation, such as the installed PHP version.


sudo gedit /var/www/info.php

and write —

<?php
phpinfo();
?>

Now we call that file in a browser (e.g. http://localhost/info.php):

PHP Test

PHP Test

As you see, PHP5 is working, and it’s working through the Apache 2.0 Handler, as shown in the Server API line. If you scroll further down, you will see all modules that are already enabled in PHP5. MySQL is not listed there which means we don’t have MySQL support in PHP5 yet.

Getting MySQL Support In PHP5

To get MySQL support in PHP, we can install the php5-mysql package. It’s a good idea to install some other PHP5 modules as well as you might need them for your applications. You can search for available PHP5 modules like this:

sudo aptitude search php5

Pick the ones you need and install them like this:

sudo apt-get install php5-mysql php5-curl php5-gd php5-idn php-pear php5-imagick php5-imap php5-mcrypt php5-memcache php5-mhash php5-ming php5-ps php5-pspell php5-recode php5-snmp php5-sqlite php5-tidy php5-xmlrpc php5-xsl php5-json

Now restart Apache2:

sudo /etc/init.d/apache2 restart

Now reload http://localhost/info.php in your browser and scroll down to the modules section again. You should now find lots of new modules there, including the MySQL module.

Install php gd library :


sudo apt-get install php5-gd

Enable rewrite module

sudo a2enmod rewrite

Now restart Apache2:


sudo /etc/init.d/apache2 restart

Cnage apache settings —

Run the followint command

sudo gedit /etc/apache2/sites-enabled/000-default

then change –


<Directory /var/www/>

Options Indexes FollowSymLinks MultiViews

AllowOverride None

Order allow,deny

allow from all

</Directory>

to


<Directory /var/www/>

Options Indexes FollowSymLinks MultiViews

AllowOverride all

Order allow,deny

allow from all

</Directory>

install  phpMyAdmin

PhpMyAdmin  is a web interface through which you can manage your MySQL databases. It’s a good idea to install it:

sudo apt-get install phpmyadmin

You will see the following questions:

Web server to reconfigure automatically: <– apache2
Configure database for phpmyadmin with dbconfig-common? <– No

Afterwards, you can access phpMyAdmin under http://localhost/phpmyadmin/:

Phpmyadmin Test

Phpmyadmin Test

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