Using Zend_db_adapter to manipulate databases _php tutorial

Zend_db_adapter is the Zendframework database abstraction Layer API, based on PDO, you can use Zend_db_adapter to connect and process multiple databases, including Microsoft SQL Server,mysql,sqlite Wait a minute. To instantiate a Zend_db_adapter object for a different database, you need to call the Zend_db::factory () method statically by Adapter name and parameter array that describes the database connection. For example, to connect to a local MySQL database with a database name of "test" and a user named "root", you can do the following:

 
  ' localhost ', ' username ' = ' root ', ' password ' = ', ' dbname ' = ' test '); $db = Zend_db::factory (' Pdo_mysql ', $ params);? >

Querying data directly

Once you have a zend_db_adapter instance, you can directly execute the SQL statement to query. Zend_db_adapter transmits these SQL statements to the underlying PDO object, which is combined and executed by the PDO object, and returns a Pdostatement object in case of a query result to process the result.

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//Use the full SQL statement to query directly. $sql = $db->quoteinto (' SELECT * FROM test WHERE id =? ', ' 3 '); $result = $db->query ($sql);//Use Pdostatement object $result to place all result data in an array $rows = $result->fetchall (); >
  
   

Program Run Result:

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

You can automatically bind the data to your query. This means that you can set more than one specified placeholder in your query, and then transfer an array of data in place of those placeholders. These replaced data are automatically quoted, providing greater security against database attacks.

 
  Query (' SELECT * from example WHERE date >:p laceholder ', Array (' placeholder ' = ' 2006-01-01 '));//Use Pdostatement object $ Result puts all the result data into an array $rows = $result->fetchall ();? >

Alternatively, you can manually set the SQL statement and bind the data to the SQL statement. This function uses the prepare () method to obtain a set of Pdostatement objects for direct database operation.

 
  Prepare (' SELECT * from example WHERE date >:p laceholder '); $stmt->bindvalue (' placeholder ', ' 2006-01-01 '); $stmt- >execute ();//Use Pdostatement object $result to place all result data in an array $rows = $stmt->fetchall ();? >

Transaction processing

By default, PDO (and therefore zend_db_adapter) uses automatic commit mode. In other words, all database operations are executed with a commit operation. If you are trying to perform a transaction, the simplest is to call the BeginTransaction () method and choose commit or rollback. After that, Zend_db_adapter will return to auto commit mode until you call the BeginTransaction () method again.

 
  BeginTransaction ();//try the database operation.//If successful, commit the operation;//If, roll back.try {$db->query (...); $db->commit ();} catch (Exception $e) {$db->rollback (); Echo $e->getmessage ();}? >

Inserting data rows

For convenience, you can use the Insert () method to bind the data you want to insert and create an INSERT statement (the bound data is automatically quoted to avoid a database attack) and the return value is not the ID of the last inserted data, because some tables do not have an increment field Instead, the return value of this insert is the number of data rows changed (typically 1). If you need the last inserted data ID, you can call the Lastinsertid () method after insert execution.

 
  The format of "data" constructs an insert array, inserting data rows $row = Array (' noble_title ' = ' King ', ' first_name ' = ' Arthur ', ' favorite_color ' = ' blue ') ,);//data table inserted data $table = ' round_table ';//I insert a data row 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 that needs to be update and create an UPDATE statement (the determined data is automatically quoted to avoid database attacks). You can provide an optional where statement explaining the condition of the update (note: The WHERE statement is not a binding parameter, so you need to quote your own data).

 
  The format of the "data" is constructed to update the array, updating the data row $set = Array (' favorite_color ' = = ' yellow ',);//updated data table $table = ' round_table ';//WHERE statement $where = $db ->quoteinto (' first_name =? ', ' Robin ');//Update table data, return the updated number of rows $rows_affected = $db->update ($table, $set, $where);? >

Delete data rows

For convenience, you can create a DELETE statement using the Delete () method; You can also provide a where statement that describes the deletion criteria for the data. (Note: The WHERE statement is not a binding parameter, so you need to do your own data-quote processing).

 
  Quoteinto (' first_name =? ', ' Patsy ');//The number of rows to delete data and get affected $rows_affected = $db->delete ($table, $where);? >

Retrieving query results

Although you can use the query () method to manipulate the database directly, it is often still necessary to select the data rows and return the results. This requirement can be achieved by a series of methods that begin with fetch. For each fetch series method, you need to transfer a select SQL statement, and if you use the specified placeholder in the action statement, you can also transfer an array of bound data to handle and replace your action statement. The 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 value of all fields in the result set as an associative array return//First Fields as code $result = $db->FETCHASSOC ("select * from round_table WHERE noble_title =: title", Array (' title ' = ' Sir '));//Take Back to the first field name of all result rows $result = $db->fetchcol ("Select first_name from round_table WHERE noble_title =: title", Array (' title ' =& Gt ' Sir ');//Retrieve only the first field value $result = $db->fetchone ("Select COUNT (*) from round_table WHERE noble_title =: title", Array (' The title ' = ' Sir ');//Retrieve a related array, the first field value is the Code//second field is a 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 the SQL statement, which is good for preventing SQL statement attacks. Zend_db_adapter (via PDO) provides two ways to help you manually enclose the conditional value in quotation marks. The first type is the quote () method. This method will add the appropriate quotation marks according to the database adapter, and if you attempt to do a quote operation on an array, it will enclose each element in the arrays in quotation marks and return with a "," separator. (This is helpful for functions with many parameters).

 
  Quote (' St John ' s wort ');//$value now becomes ' "St John\" s Wort "' (note the quotation marks on both sides)//array with quotes $value = $db->quote (Array (' A ', ' B ', ' C '); /$value now becomes ' a ', ' B ', ' C ' ' ("," delimited string)?>

The second is the Quoteinto () method, which you provide a base string that contains a question mark placeholder, and then add a quoted scalar or array at that location. This method is useful for building query SQL statements and conditional statements on demand. The scalar and array results that are processed using Quoteinto are the same as the quote () method.

 
  Quoteinto (' id =? ', 1);//$where is now ' id = ' 1 ' (note the quotation marks on both sides)//The array is quoted in the WHERE statement $where = $db->quoteinto (' ID in (?) ', Array (1 , 2, 3));//$where is now ' ID in (' 1 ', ' 2 ', ' 3 ') ' (a comma-delimited string)?>

http://www.bkjia.com/PHPjc/752453.html www.bkjia.com true http://www.bkjia.com/PHPjc/752453.html techarticle Zend_db_adapter is the Zendframework database abstraction Layer API, based on PDO, you can use Zend_db_adapter to connect and process multiple databases, including Microsoft SQL Server,mysql, SQ ...

