Tutorial on using Zend_Db_Adapter to operate database _ PHP

Use Zend_Db_Adapter to operate the database. 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 a variety of databases, including microsoftSQLServer, MySql, and SQ Zend_Db_Adapter. it is the database abstraction layer API of zendframework, you can use Zend_Db_Adapter to connect to and process a variety of 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:

  '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:

 'Localhost', 'username' => 'root', 'password' => '', 'dbname' => 'test'); $ db = Zend_Db :: factory ('pdo _ mysql', $ params); // create a $ db object, and 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 ();?>
  

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.

 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.

 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 into 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.

 BeginTransaction (); // tries 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.

 "Data" format structure insert array, insert data row $ row = array ('noble _ title' => 'King', 'First _ name' => 'Arthur ', 'favorite _ color' => 'blue',); // data table inserted $ 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 ).

 "Data" format structure update array, update 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 ).

 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 ()

 FetchAll ("SELECT * FROM round_table WHERE noble_title =: title", array ('title' => 'sir '); // Retrieve 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 ", array ('title' => 'sir '); // retrieve only the first field value $ result = $ db-> fetchOne ("SELE Ct 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 ).

 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.

 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)?>

Http://www.bkjia.com/PHPjc/752453.htmlwww.bkjia.comtruehttp://www.bkjia.com/PHPjc/752453.htmlTechArticleZend_Db_Adapter is zendframework database abstraction layer API, based on pdo, you can use Zend_Db_Adapter to connect and process a variety of databases, including microsoft SQL Server, MySql, SQ...