[PHP] Zend_Db (Zend Framework)

[PHP] Zend_Db (Zend Framework)

• 1.
  Zend Database Buzoo PHP Lecture By: Tya Herlina
• 2.
  Definition Zend_Db and itsrelated classes provide a simple SQL database interface for Zend Framework.
• 3.
  Zend_Db_Adapter Zend_Db_Adapter is thebasic class you use to connect your PHP application to an RDBMS. There is a different Adapter class for each brand of RDBMS.
• 4.
  Zend_Db_Adapter (cont’d) RDBMS Adapter IBM DB2 Pdo_ibm MariaDB Pdo_mysql MySQL Pdo_mysql MicrosoftSQL Server Pdo_dblib Oracle Pdo_oci PostgreSQL Pdo_pgsql SQLite Pdo_sqlite
• 5.
  Set Connection 1. 2. 3. Using aZend_Db Adapter Constructor Using the Zend_Db Factory Using Zend_Config with the Zend_Db Factory
• 6.
  1. Using aZend_Db Adapter Constructor $db = new Zend_Db_Adapter_Pdo_Mysql(array( ’host’ => ’buzoo.biz’, ’username’ => ’root’, ’password’ => ’’, ’dbname’ => ’app_geshucloud’ ));
• 7.
  2. Using theZend_Db Factory $db = Zend_Db::factory('Pdo_Mysql', array( ’host’ => ’buzoo.biz’, ’username’ => ’root’, ’password’ => ’’, ’dbname’ => ’app_geshucloud’ ));
• 8.
  3. Using Zend_Configwith the Zend_Db Factory database.host database.username database.password database.dbname = = = = “buzoo.biz“ “root“ “ “app_geshucloud“ $config = new Zend_Config_Ini(“path/to/config.ini“); $db = Zend_Db::factory(‘Pdo_Mysql‘, $config->database);
• 9.
  Set Connection Zend_Db_Table_Abstract::setDefaultAdapter ($db); Get Connection publicfunction db() { return Zend_Db_Table_Abstract::getDefaultAdapter(); }
• 10.
  Reading Query Results 1. 2. 3. 4. 5. 6. Fetchinga Complete Result Set Fetching a Single Row from a Result Set Fetching a Single Scalar from a Result Set Fetching a Result Set as an Associative Array Fetching Key-Value Pairs from a Result Set Fetching a Single Column from a Result Set
• 11.
  1. Fetching aComplete Result Set $models = $this->db()->fetchAll( “SELECT * FROM `dtb_customer`” ); print_r($models); echo $models[0][`customerID`]; //44 Array ( [0] => Array ( [customerID] [customerName] [customerAddr] [customerPhone] [create_date] [update_date] => => => => => => 44 Adisti Prihartini Maleo 345 Bintan 2390554 2012-10-30 14:29:36 2012-11-27 16:04:45 [customerID] [customerName] [customerAddr] [customerPhone] [create_date] [update_date] => => => => => => 45 Angela Nayoan Van Heutz Boulevard 53 Batavia 2140 2012-10-30 14:29:36 2012-11-27 16:04:45 ) [1] => Array ( ) )
• 12.
  2. Fetching aSingle Row from a Result Set $models = $this->db()->fetchRow( “SELECT * FROM `dtb_customer` LIMIT 1” ); print_r($models); echo $models[`customerID`]; //44 Array ( [customerID] [customerName] [customerAddr] [customerPhone] [create_date] [update_date] ) => => => => => => 44 Adisti Prihartini Maleo 345 Bintan 2390554 2012-11-05 10:09:14 2012-11-21 10:35:45
• 13.
  3. Fetching aSingle Scalar from a Result Set $models = $this->db()->fetchOne( “SELECT `customerID` FROM `dtb_customer` LIMIT 1” ); print_r($models); echo $models[`customerID`]; 44
• 14.
  Modifying Data tothe Database 1. 2. 3. Inserting Data Updating Data Deleting Data
• 15.
  1. Inserting Data $this->db()->insert(‘dtb_room_facility‘,array( ‘room_id‘ => 99, ‘facility_id‘ => 99 )); echo $this->db()->lastInsertId(); //5 $model = new Dao_RoomFacility(); $new_id = $model->insert(array( ‘room_id‘ => 99, ‘facility_id‘ => 99 )); echo $new_id; //5
• 16.
  2. Updating Data $update_id= $this->db()->update('dtb_room_facility', array( 'room_id' => 999, 'facility_id' => 999 ), 'id = 999'); echo $update_id; //1 $model = new Dao_RoomFacility(); $update_id = $model->update( array( 'room_id' => 899, 'facility_id' => 899 ), 'id = 899'); echo $update_id; //1
• 17.
  3. Deleting Data $delete_id= $this->db()->delete( '`dtb_room_facility`', '`id` = 999' ); echo $delete_id; //1 $model = new Dao_RoomFacility(); $delete_id = $model->delete( '`id` = 899'); echo $delete_id; //1
• 18.
  Preventing SQL Injection $name= "O'Reilly"; $sql = "SELECT * FROM `dtb_customer` WHERE `customerName` = '$name'"; echo $sql; // SELECT * FROM `dtb_customer` WHERE `customerName` = 'O'Reilly'
• 19.
  Quoting Values andIdentifiers 1. 2. 3. Using quote() Using quoteInto() Using quoteIdentifier()
• 20.
  1. Using quote() $name= $this->db()->quote("O'Reilly"); $sql = "SELECT * FROM `dtb_customer` WHERE `customerName` = $name"; echo $sql; // SELECT * FROM `dtb_customer` WHERE `customerName` = 'O'Reilly' $phone = $this->db()->quote("1234", "INTEGER"); $sql = "SELECT * FROM `dtb_customer` WHERE `customerPhone` = $phone"; echo $sql; // SELECT * FROM `dtb_customer` WHERE `customerPhone` = 1234
• 21.
  2. Using quoteInto() $name= "O'Reilly"; $sql = $this->db()->quoteInto( "SELECT * FROM `dtb_customer` WHERE `customerName` = ?", $name ); echo $sql; // SELECT * FROM `dtb_customer` WHERE `customerName` = 'O'Reilly'
• 22.
  Notes  Always store yourlogic query in Models/Logic/your_logic.php  Minimizing the possibility of SQL injection with quoting values  When creating logic, please reduce the possibility of errors  Always return your logic result value  Always check the existing logic before you make yours
• 23.
  Thank you~  Question? Share?  Critics?  Advice?