Use Zend_Db_Adapter to operate databases

Zend_Db_Adapter is the database abstraction layer API of zendframework. Based on pdo, you can use Zend_Db_Adapter to connect to and process multiple databases, including microsoft SQL Server, MySql, and SQLite. To instantiate a Zend_Db_Adapter object for different databases, you must take the adapter name and the parameter array describing the database connection as the parameter and call the Zend_Db: factory () method statically. For example, to connect to a local MySQL database named "test" with the username "root", you can perform the following operations:

<?phprequire_once 'Zend/Db.php';$params = array ('host' => 'localhost','username' => 'root','password' => '','dbname' => 'test');$db = Zend_Db::factory('PDO_MYSQL', $params);?>

Directly query data

Once you get a Zend_Db_Adapter instance, you can directly execute the SQL statement for query. Zend_Db_Adapter transfers these SQL statements to the underlying PDO objects, which are combined and executed by the PDO objects. A PDOStatement object is returned in the case of query results to process the results.

The database is as follows:

Id	Name
1	A
2	B
3	C
4	D

PHP code:

<? Phprequire_once 'zend/Db. php '; $ params = array ('host' => 'localhost', 'username' => 'root', 'Password' => '', 'dbname' => 'test'); $ db = Zend_Db: factory ('pdo _ mysql', $ params); // create a $ db object, then query the database // directly using the complete SQL statement. $ SQL = $ db-> quoteInto ('select * FROM test WHERE id =? ', '3'); $ result = $ db-> query ($ SQL ); // use the PDOStatement object $ result to put all the result data into an array $ rows = $ result-> fetchAll ();?> <Pre> <? Phpprint_r ($ rows);?> </Pre>

Program running result:

Array(    [0] => Array        (            [id] => 3            [name] => C        ))

You can automatically bind data to your query. This means that you can set multiple specified placeholders in the query, and then transmit an array of data to replace these placeholders. The replaced data is automatically enclosed by quotation marks, providing stronger security to prevent database attacks.

<? Php // create a $ db object and query the database. // this time, use the bound placeholder. $ result = $ db-> query ('select * FROM example WHERE date>: placeholder ', array ('placeholder' => '2017-01-01 ')); // use the PDOStatement object $ result to put all the result data into an array $ rows = $ result-> fetchAll ();?>

Alternatively, you can manually set SQL statements and bind data to SQL statements. This function uses the prepare () method to obtain a set PDOStatement object for direct database operations.

<? Php // create a $ db object and query the database. // this time, set a PDOStatement object for manual binding. $ stmt = $ db-> prepare ('select * FROM example WHERE date>: placeholder '); $ stmt-> bindValue ('placeholder', '2017-01-01 '); $ stmt-> execute (); // use the PDOStatement object $ result to put all the result data in an array $ rows = $ stmt-> fetchAll ();?>

Transaction Processing

By default, PDO (So Zend_Db_Adapter is also used) adopts the automatic commit mode. That is to say, the commit operation is performed when all database operations are executed. If you try to execute transaction processing, the simplest is to call the beginTransaction () method, and then select commit or rollback. Then, Zend_Db_Adapter will return to the automatic commit mode until you call the beginTransaction () method again.

<? Php // create a $ db object and start a transaction. $ db-> beginTransaction (); // try database operations. // if the operation is successful, commit the operation; // if the operation is roll back. try {$ db-> query (...); $ db-> commit ();} catch (Exception $ e) {$ db-> rollBack (); echo $ e-> getMessage () ;}?>

Insert data rows

For convenience, you can use the insert () method to bind the data to be inserted and create an insert statement (the bound data is automatically quoted to avoid database attacks) the returned value is not the id of the last inserted data. The reason is that some tables do not have an auto-increment field. On the contrary, the returned value is the number of changed data rows (usually 1 ). If you need the last inserted data id, you can call the lastInsertId () method after the insert operation.

<? Php /// insert into round_table // (noble_title, first_name, favorite_color) // VALUES ("King", "Arthur", "blue "); //// create a $ db object and then... // construct an insert array in the format of "column name" => "data" and insert a data row $ row = array ('noble _ title' => 'King ', 'First _ name' => 'Arthur ', 'favorite _ color' => 'blue',); // insert a data table $ table = 'round _ table '; // I insert data rows and return the number of rows $ rows_affected = $ db-> insert ($ table, $ row); $ last_insert_id = $ db-> lastInsertId ();?>

Update data rows

For convenience, you can use the update () method to determine the data to be updated and create an update statement (the determined data is automatically enclosed in quotation marks to avoid database attacks ). You can provide an optional where statement to describe the update condition (Note: The where statement is not a binding parameter, so you need to quote your data ).

<? Php // UPDATE round_table // SET favorite_color = "yellow" // WHERE first_name = "Robin"; // create a $ db object and then... // construct an update array in the format of "column name" => "data" and update the data row $ set = array ('favorite _ color' => 'yellow ',); // update the data table $ table = 'round _ table'; // where statement $ where = $ db-> quoteInto ('first _ name =? ', 'Robin'); // update table data, returns the number of updated rows $ rows_affected = $ db-> update ($ table, $ set, $ where);?>

Delete data rows

For convenience, you can use the delete () method to create a delete statement. You can also provide a where statement to describe the conditions for data deletion. (Note: The where statement is not a binding parameter, so you need to enclose the data with quotation marks ).

<? Php // the table to be deleted // WHERE first_name = "Patsy"; // create a $ db object and then... // set the table for which data to be deleted $ table = 'und _ table'; // where Condition Statement $ where = $ db-> quoteInto ('first _ name =? ', 'Patsy'); // The number of rows affected by data deletion $ rows_affected = $ db-> delete ($ table, $ where);?>

Retrieve query results

Although you can use the query () method to operate the database directly, you still need to select the data row and return the result. A series of methods starting with fetch can meet this requirement. For each fetch method, you need to transmit a select SQL statement. If you use the specified placeholder in the operation statement, you can also send an array of bound data to process and replace your operation statements. Methods of the Fetch series include:

• FetchAll ()
• FetchAssoc ()
• FetchCol ()
• FetchOne ()
• FetchPairs ()
• FetchRow ()

<? Php // create a $ db object and then... // retrieve the values of all fields in the result set and return $ result = $ db-> fetchAll ("SELECT * FROM round_table WHERE noble_title =: title" as a continuous array ", array ('title' => 'Sir '); // retrieves the values of all fields in the result set, return as an associated array // The first field as the Code $ result = $ db-> fetchAssoc ("SELECT * FROM round_table WHERE noble_title =: title ", array ('title' => 'Sir ')); // retrieve the first field name of all result rows $ result = $ db-> fetchCol ("SELECT first_name FROM round_table WHERE noble_title =: title", arra Y ('title' => 'Sir '); // retrieve only the first field value $ result = $ db-> fetchOne ("select count (*) FROM round_table WHERE noble_title =: title ", array ('title' => 'Sir '); // retrieves an array, the first field value is the code // The second field is the value $ result = $ db-> fetchPairs ("SELECT first_name, favorite_color FROM round_table WHEREnoble_title =: title ", array ('title' => 'Sir ')); // retrieve only the first row of the result set $ result = $ db-> fetchRow ("SELECT * FROM round_table WHERE first_name =: name", array ( 'Name' => 'lancelot');?>

Add quotation marks to prevent database attacks

You should handle the condition values that will be used in SQL statements, which is good for preventing SQL statement attacks. Zend_Db_Adapter (through pdo) provides two methods to help you manually add quotation marks to the condition value. The first method is the quote () method. This method will add appropriate quotation marks for the scalar according to the database adapter. If you try to quote an array, it will enclose each element in the array with quotation marks and return results separated. (This is helpful for functions with many parameters ).

<? Php // create a $ db object. Assume that the database adapter is mysql. // quote the scalar value $ value = $ db-> quote ('st John "s wort '); // $ value is now '"St John \" s Wort "' (note the quotation marks on both sides) // enclose the array with quotation marks $ value = $ db-> quote (array ('A', 'B', 'C '); // $ value is now '"a", "B", "c"' ("," separator string)?>

The second method is the quoteInto () method. You provide a basic string containing the question mark placeholder, and then add a scalar or array with quotation marks to this position. This method is helpful for building query SQL statements and condition statements as needed. The scalar and array returned results processed by quoteInto are the same as those returned by the quote () method.

<? Php // create a $ db object. Assume that the database adapter is mysql. // In the where statement, enclose the scalar with quotation marks $ where = $ db-> quoteInto ('Id =? ', 1); // $ where is now 'id = "1"' (note the quotation marks on both sides) // IN the where statement, enclose the array with quotation marks $ where = $ db-> quoteInto ('Id IN (?) ', Array (1, 2, 3); // $ where is now 'Id IN ("1", "2", "3 ") '(a comma-separated string)?>