Zend Framework Database Query

Zend Framework Database Query

Database Manipulation

• Create a controller
• Call / Create(Write) Database configuration setting
• Request data object creation
• Assign message (Success / Failure / Already exists)
• Create a view page to see the message or to redirect to the previous page to fill it up again

Controller creation

public function processAction(){
}

Database configuration creation/call from registry

$registry = Zend_Registry::getInstance();
$DB = $registry['DB'];

Or

$params = array(‘host’=>’localhost’ , ‘username’ =>’root’ , ‘password’ =>” , ‘dbname’ =>’zend’ );
$DB = new Zend_Db_Adapter_Pdo_Mysql($params);

Insert Query

$sql = "INSERT INTO `user`
(`first_name` , `last_name` ,`user_name` ,`password`)
VALUES
(‘".$request->getParam(‘first_name’)."’, ‘".$request->getParam(‘last_name’)."’, ‘".$request->getParam(‘user_name’)."’, MD5(‘".$request->getParam(‘password’)."’))";$DB->query($sql);

OR

$data = array(‘first_name’ => $request->getParam(‘first_name’),
‘last_name’ => $request->getParam(‘last_name’),
‘user_name’ => $request->getParam(‘user_name’),
‘password’ => md5($request->getParam(‘password’))
);$DB->insert(‘user’, $data);

Fetch Query

$params = array(‘host’ =>’localhost’, ‘username’ =>’root’, ‘password’ =>”, ‘dbname’ =>’zend’ );
$DB = new Zend_Db_Adapter_Pdo_Mysql($params);$DB->setFetchMode(Zend_Db::FETCH_OBJ);
$sql = "SELECT * FROM `user` ORDER BY user_name ASC";
$result = $DB->fetchAssoc($sql);
$this->view->assign(‘data’,$result); // Send the array to the view page

Access a variable in the view page

$received_data = $this->data; // received the whole data array
Var_dump($ received_data); // print the whole array
?>

Update Query

$params = array(‘host’ =>’localhost’, ‘username’ =>’root’, ‘password’ =>”, ‘dbname’ =>’zend’ );
$DB = new Zend_Db_Adapter_Pdo_Mysql($params);$request = $this->getRequest();
$data = array(‘first_name’ => $request->getParam(‘first_name’),
‘last_name’ => $request->getParam(‘last_name’),
‘user_name’ => $request->getParam(‘user_name’),
‘password’ => md5($request->getParam(‘password’))
);
$DB->update(‘user’, $data,’id = ‘.$request->getParam(‘id’));

Delete Query

$params = array(‘host’ =>’localhost’, ‘username’ =>’root’, ‘password’ =>”, ‘dbname’ =>’zend’ );
$DB = new Zend_Db_Adapter_Pdo_Mysql($params);$request = $this->getRequest();
$DB->delete(‘user’, ‘id = ‘.$request->getParam(‘id’));

Complete controller
Table name : user ( Fields : id, first_name , last_name, user_name , password)
Input textbox names are (first_name , last_name , user_name , password)

public function processAction()
{$params = array(‘host’ => ‘localhost’ , ‘username’ =>’root’ , ‘password’ =>” , ‘dbname’ =>’zend’ );
$DB = new Zend_Db_Adapter_Pdo_Mysql($params);
$request = $this->getRequest(); //create a request object
$data = array(‘first_name’ => $request->getParam(‘first_name’),
‘last_name’ => $request->getParam(‘last_name’),
‘user_name’ => $request->getParam(‘user_name’),
‘password’ => md5($request->getParam(‘password’))
);
$DB->insert(‘user’, $data); // insertion code
$this->view->assign(‘title’,'Registration Process’);
$this->view->assign(‘description’,'Registration succes’);
}