การเชื่อมต่อฐานข้อมูล MYSQL Zend Framework

[zend_framework]

การเชื่อมต่อฐานข้อมูล MYSQL Zend Framework
1. สร้าง table member

โค้ด: เลือกทั้งหมด

CREATE TABLE `zf`.`member` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`name` VARCHAR( 200 ) NOT NULL ,
`surname` VARCHAR( 200 ) NOT NULL ,
`company` VARCHAR( 200 ) NOT NULL ,
`zip` VARCHAR( 5 ) NOT NULL
) ENGINE = InnoDB CHARACTER SET tis620 COLLATE tis620_thai_ci;

2. create file "application/config.ini"

โค้ด: เลือกทั้งหมด

[zf]
db.adapter = PDO_MYSQL
db.params.host = localhost
db.params.username = zend15
db.params.password = 15
db.params.dbname = member

3. create directory "application/models"


0.4.0 create your class name "Mydb" in models/Mydb.php details as below.

โค้ด: เลือกทั้งหมด

<?php
class Mydb extends Zend_Db_Table
{
    protected $_name = 'mydb' ;

    function insert_items($mytable, $data)
    {
      $config = new Zend_Config_Ini('../application/config.ini', 'zf');
      $db = new Zend_Db_Adapter_Pdo_Mysql(array(
                'host'     => $config->db->params->host ,
                'username' => $config->db->params->username,
                'password' => $config->db->params->password,
                'dbname'   =>$config->db->params->dbname,
                'driver_options'  => array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES \'tis620\'')
     ));
        $n = $db->insert($mytable, $data);

        return $n ; // 1 success , 0 fail;

    }//end_insert_items
}
 

4. create file "application/models/Mydb.php"

โค้ด: เลือกทั้งหมด

<?php
class Mydb extends Zend_Db_Table
{
    protected $_name = 'mydb' ; 

    function Searchm123($mytable, $keysearch, $mysearch)
    {
      $config = new Zend_Config_Ini('../application/config.ini', 'zf');
      $db = new Zend_Db_Adapter_Pdo_Mysql(array(
                'host'     => $config->db->params->host ,
                'username' => $config->db->params->username,
                'password' => $config->db->params->password,
                'dbname'   =>$config->db->params->dbname,
                'driver_options'  => array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES \'tis620\'')
     ));
    //mysql_db_query('p_data_nmrsw2',"SET NAMES tis620");
        $sql = "SELECT * FROM  $mytable WHERE $keysearch='$mysearch' ";
        $result = $db->fetchAll($sql, 2);

    return $result ;
    }//end_func_Search

    function insert_items($mytable, $data)
    {
      $config = new Zend_Config_Ini('../application/config.ini', 'zf');
      $db = new Zend_Db_Adapter_Pdo_Mysql(array(
                'host'     => $config->db->params->host ,
                'username' => $config->db->params->username,
                'password' => $config->db->params->password,
                'dbname'   =>$config->db->params->dbname,
                'driver_options'  => array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES \'tis620\'')
     ));
        $n = $db->insert($mytable, $data);

        return $n ; // 1 success , 0 fail;

    }//end_insert_items

    function update_items($mytable, $data, $where)
    {
      $config = new Zend_Config_Ini('../application/config.ini', 'zf');
      $db = new Zend_Db_Adapter_Pdo_Mysql(array(
                'host'     => $config->db->params->host ,
                'username' => $config->db->params->username,
                'password' => $config->db->params->password,
                'dbname'   =>$config->db->params->dbname,
                'driver_options'  => array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES \'tis620\'')
     ));
        $n = $db->update($mytable, $data, $where);

        return $n ; // 1 success , 0 fail;

    }//end_insert_items


    function deleteitems($mytable, $where)
    {
      $config = new Zend_Config_Ini('../application/config.ini', 'zf');
      $db = new Zend_Db_Adapter_Pdo_Mysql(array(
                'host'     => $config->db->params->host ,
                'username' => $config->db->params->username,
                'password' => $config->db->params->password,
                'dbname'   =>$config->db->params->dbname,
                'driver_options'  => array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES \'tis620\'')
     ));
        $n = $db->delete($mytable, $where);

        return $n ; // 1 success , 0 fail;

    }//end_func_delete




}
?>

6. create file "IndexController.php"

โค้ด: เลือกทั้งหมด

  public function updateAction()
    {

    //$this->_helper->layout->setLayout('contact_layout');
        if ($this->_request->isPost()) {
            $edit = Zend_Filter::filterStatic($this->getRequest()->getPost('edit'), 'StripTags');
        $editid = Zend_Filter::filterStatic($this->getRequest()->getPost('editid'), 'StripTags');
        $this->view->assign('editid', $editid) ;
    //echo $editid ; 

    if($edit) {
    $Mydata = new Mydb();
    $get_data_list = $Mydata->Smember2("member" , "id" , "$editid");
        $this->view->assign('member2', $get_data_list) ;

        $update = Zend_Filter::filterStatic($this->getRequest()->getPost('update'), 'StripTags');
    }else{
          $name = Zend_Filter::filterStatic($this->getRequest()->getPost('name'), 'StripTags');
        $surname = Zend_Filter::filterStatic($this->getRequest()->getPost('surname'), 'StripTags');
        $company = Zend_Filter::filterStatic($this->getRequest()->getPost('company'), 'StripTags');
        $zip = Zend_Filter::filterStatic($this->getRequest()->getPost('zip'), 'StripTags');

    //echo "update on: ".$editid ;
    $where = "id = " . $editid ;
    $data = array(
    "name" => "$name",
    "surname" => "$surname",
    "company" => "$company",
    "zip" => "$zip",
    );

    $myins = new Mydb();
    $do_ins = $myins->update_items("member", $data, $where);
    }


        }//POST
 

7. create file "application/views/index/update.phtml"

โค้ด: เลือกทั้งหมด

<div id="header">
    <img src="stage-logo.gif" width="187" height="29" alt="Stage & Screen" class="logo" />
    <span class="strapline">theatre and film reviews</span>
</div>
<div id="content">
    <h1>สวัสดี Welcome to Stage & Screen</h1>
    <p>
<span class="titles"><a href="#" rel="bookmark" title="Scripts Basic Form">ใบสมัครสมาชิก</a></span>

<div align="center">
<?php 
if  ( $this->name ) { 
echo "สวัสดีคุณ::$this->name $this->surname<br>" ; 
echo ":: $this->email</span>" ; 
} 

?>
<br><br>


<?php 
if ( count($this->member2) >0  ) {
foreach($this->member2 as $k) { 
?>

<form method="POST" action="/index/update" name="mainform">

<table>
  <tbody>
    <tr>
      <td> ชื่อ: </td>
      <td><input name="name" <?php if( $k['name'] ){ ?> value="<?php echo $k['name'] ; }else{ echo "" ; }?>" > </td>
    </tr>
    <tr>
      <td>นามสกุล:</td>
      <td><input name="surname" <?php if( $k['surname'] ){ ?> value="<?php echo $k['surname'] ; }else{ echo "" ; }?>" > </td>
    </tr>
    <tr>
      <td>บริษัท:</td>
      <td><input name="company" <?php if( $k['company'] ){ ?> value="<?php echo $k['company'] ; }else{ echo "" ; }?>" > </td>
    </tr>
    </tr>
      <td>zip:</td>
      <td><input name="zip" <?php if( $k['zip'] ){ ?> value="<?php echo $k['zip'] ; }else{ echo "" ; }?>" > </td>
    </tr>
    <tr>
      <td align="center" colspan="2">
    <input type="hidden" name="editid" value="<?php echo $this->editid;?>">
    <input type="Submit" name="update" id="submit" value="update" >
    </td>
    </tr>
  </tbody>
</table>
</form>

<?php } ?>
<?php }else{ ?>

Update OK !!!

<?php }?>
</div>

8 สร้าง function delete

โค้ด: เลือกทั้งหมด

    public function deleteAction()
    {
    //$this->_helper->layout->setLayout('contact_layout');
        if ($this->_request->isPost()) {
    $deleteid = Zend_Filter::filterStatic($this->getRequest()->getPost('deleteid'), 'StripTags');
    $where = "id = " . $deleteid ;
    $mydelete = new Mydb();
    $do_del = $mydelete->deleteitems("member", $where);
    
    }//if
    }//delete