Using MySQL with the Zend Framework

excitingwonderlakeInternet and Web Development

Dec 13, 2013 (3 years and 4 months ago)

60 views

Copyright © 2007, Zend Technologies Inc.

Using MySQL with the
Zend Framework

Bill Karwin

Product Engineering Manager,

Zend Technologies

Now, the world's most popular web
programming language gets even
better with an easy to use framework
for developing the next generation of
web applications.

2007
-
04
-
25

Using MySQL with the Zend Framework

| Page
2

What is the Zend Framework?


PHP 5 class library for web development


Free, open source, BSD license (like PHP)


140,000 lines of code


Emphasis on quality: 90%+ unit test coverage


Solutions are “extremely simple”


solving the 80%
usage case, and allowing for extensibility


182,000 downloads



http://framework.zend.com/

2007
-
04
-
25

Using MySQL with the Zend Framework

| Page
3

What’s in the Zend Framework?


MVC web app framework

(model
-
view
-
controller)


Database access


Lucene
-
compatible Search
engine


Input filtering and validation


Authentication


Access control


Session handling


I18N, locales, translation


PDF file reading and writing


HTTP client


XmlRpc


REST web services


RSS and Atom feeds


Google Gdata client


Logging


Mail reading and sending


Caching


Configuration files


Command
-
line option
parsing


Simple, convenient object
-
oriented solutions to common
web application development tasks:

2007
-
04
-
25

Using MySQL with the Zend Framework

| Page
4

What’s in the Zend Db component?


Lightweight Adapter to multiple PHP database
extensions, including:


Mysqli
, Oracle OCI8, IBM DB2


PDO:
Mysql
, MS SQL Server, Oracle, PostgreSQL, SQLite


Basic functions for CRUD operations


Functions to quote SQL identifiers and values,

to encourage secure application code


Profiler to measure the time of SQL queries


SELECT query builder interface


Object
-
oriented interface to operations on
tables and rows

2007
-
04
-
25

Using MySQL with the Zend Framework

| Page
5

Connecting to MySQL


Use the Zend_Db class factory() method:


<?php


require_once ‘Zend/Db.php’;


$db = Zend_Db::factory(‘Mysqli’,


array(



‘host’

=> ’localhost’,



‘dbname’

=> ’test’,



‘username’=> ’webappuser’,



‘password’

=> ’xxxxxxxx’)


);



Returns an object of Zend_Db_Adapter_Mysqli

2007
-
04
-
25

Using MySQL with the Zend Framework

| Page
6

Running a query


The fetchAll() method returns an array of rows,
each of which is an associative array:


$data = $db
-
>fetchAll(‘SELECT * FROM bugs’);


foreach ($data as $row) {


echo $row[‘bug_description’];

}

2007
-
04
-
25

Using MySQL with the Zend Framework

| Page
7

Running a prepared query


Use the query() method to create a Statement:


$stmt = $db
-
>query(


‘SELECT * FROM bugs


WHERE bug_status = ?’);


Execute it once, giving a parameter value:


$stmt
-
>execute(array(‘OPEN’));


$openBugs = $stmt
-
>fetchAll();


Execute it a second time, giving another value:


$stmt
-
>execute(array(‘CLOSED’));


$closedBugs = $stmt
-
>fetchAll();

2007
-
04
-
25

Using MySQL with the Zend Framework

| Page
8

Inserting data


Use the insert() method.


Pass the table name, and

an array mapping columns to values:


$db
-
>insert( ‘bugs’,


array(



‘bug_description’

=> ‘help me’,



‘bug_status’

=> ‘NEW’


)

);


Get the generated primary key value:


$bugId = $db
-
>lastInsertId();


2007
-
04
-
25

Using MySQL with the Zend Framework

| Page
9

Updating data


Use the update() method.


Pass the table name,

an array mapping columns to new values, and

an expression for the WHERE clause:


$n = $db
-
>update( ‘bugs’,


array(‘bug_status’ => ‘NEW’),


‘bug_id = 123’

);



Returns the number of rows affected.

2007
-
04
-
25

Using MySQL with the Zend Framework

| Page
10

Deleting data


Use the delete() method.


Pass the table name, and

an expression for the WHERE clause:


$n = $db
-
>delete(‘bugs’, ‘bug_id = 321’);



Returns the number of rows affected.

2007
-
04
-
25

Using MySQL with the Zend Framework

| Page
11

Retrieving table metadata


Use the describeTable() method:


$bugsMetadata = $db
-
>describeTable(‘bugs’);


Returns an array indexed by column name:


array(


‘bug_id’

=> array(…),


‘bug_description’

=> array(…),


‘bug_status’

=> array(…),


‘created_on’

=> array(…),


‘updated_on’

=> array(…),


‘reported_by’

=> array(…),


‘assigned_to’

=> array(…),


‘verified_by’

=> array(…),

)

2007
-
04
-
25

Using MySQL with the Zend Framework

| Page
12

Retrieving table metadata (cont’d)


The value of each entry is an associative array

of metadata for the respective column:


array(


‘bug_id’ => array(




‘TABLE_NAME’


=> ‘bugs’



‘COLUMN_NAME’


=> ‘bug_id’



‘COLUMN_POSITION’

=> 1



‘DATA_TYPE’


=> ‘INTEGER’



‘DEFAULT’


=> null



‘NULLABLE’


=> false



‘LENGTH’


=> null



‘SCALE’


=> null



‘PRECISION’


=> null



‘UNSIGNED’


=> null



‘PRIMARY’


=> true



‘PRIMARY_POSITION’

=> 1


),


…entries for other columns follow…

)

2007
-
04
-
25

Using MySQL with the Zend Framework

| Page
13

Quoting SQL identifiers and values


Important when interpolating strings and PHP
variables into SQL


Use the quote() method to turn a string or
variable into a quote SQL string:


$stmt = $db
-
>query(


‘SELECT * FROM bugs WHERE bug_reporter = ’


. $db
-
>quote(“O’Reilly”));


Note
: unlike mysql_real_escape_string(), the quote() method
returns a string with quotes.


mysql_real_escape_string(“O’Reilly”) returns:

O
\
’Reilly


$db
-
>quote(“O’Reilly”) returns:


‘O
\
’Reilly’

2007
-
04
-
25

Using MySQL with the Zend Framework

| Page
14

Quoting SQL identifiers and values (cont’d)


Use the quoteInto() method to substitute a scalar
into a SQL string:


$whereExpr = $db
-
>quoteInto(


‘bug_reporter = ?’, “O’Reilly”);


$stmt = $db
-
>query(


“SELECT * FROM bugs WHERE $whereExpr”);


Results in the following query:


SELECT * FROM bugs WHERE bug_reporter = ‘O
\
’Reilly’

2007
-
04
-
25

Using MySQL with the Zend Framework

| Page
15

Quoting SQL identifiers and values (cont’d)


Use the quoteIdentifier() method :


$table = $db
-
>quoteIdentifier(


‘table
-
with
-
special characters’);


$stmt = $db
-
>query(“SELECT * FROM $table WHERE …”);


Results in the following query:


SELECT * FROM `table
-
with
-
special characters`

WHERE …

2007
-
04
-
25

Using MySQL with the Zend Framework

| Page
16

Profiling queries


Useful for development/debugging



An Adapter object has a Zend_Db_Profiler
object, which is disabled by default


$prof = $db
-
>getProfiler()


$prof
-
>setEnabled(true);



While the profiler is enabled, SQL queries are
recorded, with the time it takes them to run.

2007
-
04
-
25

Using MySQL with the Zend Framework

| Page
17

Profiling queries (cont’d)


Example: find the longest
-
running query:


// run one or more queries with profiler enabled


$db
-
>query( … );

. . .


$max = 0;


foreach ($prof
-
>getQueryProfiles() as $q) {


if ($q
-
>getElapsedSecs() > $max) {



$max = $q
-
>getElapsedSecs();



$longestQuery = $q
-
>getQuery();


}

}


echo “The longest query ran $max seconds
\
n”;


echo “SQL = $longestQuery
\
n”;

2007
-
04
-
25

Using MySQL with the Zend Framework

| Page
18

Profiling queries (cont’d)


Filtering queries to be recorded


// Report only queries that take more than 5 seconds:

$prof
-
>setFilterElapsedSecs(5);


// Report only SELECT and UPDATE queries:

$prof
-
>setFilterQueryType(


Zend_Db_Profiler::SELECT | Zend_Db_Profiler::UPDATE);



Filtering queries to be reported


$prof
-
>getQueryProfiles(Zend_Db_Profiler::SELECT);

2007
-
04
-
25

Using MySQL with the Zend Framework

| Page
19

Building a SELECT query


Use the Zend_Db_Select class to build a SELECT
statement piece by piece:


$select = $db
-
>select();


$select
-
>from(‘bugs’);


$select
-
>where(‘bug_status = ?’, ‘NEW’);


Then execute the query:


$data = $db
-
>fetchAll($select);


The Adapter knows to render the SQL string for a
Select object, or optionally you can do it:


echo $select
-
>__toString();

2007
-
04
-
25

Using MySQL with the Zend Framework

| Page
20

Building a SELECT query (cont’d)


A fluent interface can be convenient:


$select = $db
-
>select()



-
>from(‘bugs’)



-
>where(‘bug_status = ?’, ‘NEW’);



You can mix fluent and traditional usage.

You can add SQL clauses in any order.


$select = $db
-
>select()
-
>from(‘bugs’)



-
>order(‘bug_id’);


if ($condition == true) {



$select
-
>joinNatural(‘other_table’);

}

2007
-
04
-
25

Using MySQL with the Zend Framework

| Page
21

Building a SELECT query (cont’d)


Additional functions:


DISTINCT modifier


Specify columns


Table correlation names


Specify SQL expressions


Column aliases


Joins to other tables


GROUP BY clause


HAVING clause


ORDER BY clause


LIMIT clause


Additional functions:


DISTINCT modifier


Specify columns


Table correlation names


Specify SQL expressions


Column aliases


Joins to other tables


GROUP BY clause


HAVING clause


ORDER BY clause


LIMIT clause

$select = $db
-
>select()

-
>distinct()

-
>from(array(‘a’=>‘accounts’),


array(‘account_name’,



‘num_bugs’=>‘COUNT(*)’))

-
>joinLeft(array(‘b’=>‘bugs’),


‘b.reported_by =



a.account_name’)

-
>group(‘a.account_name’)

-
>having(‘num_bugs < 5’)

-
>order(‘a.account_name ASC’)

-
>limit(10, 20);

2007
-
04
-
25

Using MySQL with the Zend Framework

| Page
22

Table Data Gateway


Simple object
-
oriented interface to common
tasks for tables and rows


Table Data Gateway
and
Row Data Gateway
are based on Martin Fowler’s “Patterns of
Enterprise Architecture.”


CRUD operations


Fetch operations for related tables


Extensible classes for Table, Row, and Rowset
allow you to define some business logic

2007
-
04
-
25

Using MySQL with the Zend Framework

| Page
23

Table Data Gateway: define a table


Start by defining a class for each table:


class Bugs extends Zend_Db_Table_Abstract

{


protected $_name = ‘bugs’;

}


Set the default Adapter for all tables:


Zend_Db_Table_Abstract::setDefaultAdapter($db);


Create an instance of the Bugs class:


$bugsTable = new Bugs();


Optionally, specify the database adapter:


$bugsTable = new Bugs( array(‘db’=>$db) )

2007
-
04
-
25

Using MySQL with the Zend Framework

| Page
24

Table Data Gateway: query from table


Fetch a Rowset object:


$rowset = $bugsTable
-
>fetchAll(“bug_status = ‘OPEN’”);



A Rowset object is iterable and countable.


A Rowset is a collection of Row objects.


A Row has an accessor for each column.


foreach ($rowset as $row) {


echo “$row
-
>bug_id: $row
-
>bug_description
\
n”;

}

2007
-
04
-
25

Using MySQL with the Zend Framework

| Page
25

Table Data Gateway: find by primary key


Use the find() method with a primary key value
or an array of values:


$bugsTable = new Bugs();


$rowset1 = $bugsTable
-
>find(123);


$rowset2 = $bugsTable
-
>find( array(123, 321) );



$rowset1
contains 0 or 1 Row


$rowset2

contains up to 2 Rows

2007
-
04
-
25

Using MySQL with the Zend Framework

| Page
26

Table Data Gateway: UPDATE


Get a Rowset with the Row you want to update:


$bugsTable = new Bugs();


$rowset = $bugsTable
-
>find(123);


$row = $rowset
-
>current();


Set a column value using the accessor:


$row
-
>bug_description = ‘New description’;


Save the change to the database:


$row
-
>save();

2007
-
04
-
25

Using MySQL with the Zend Framework

| Page
27

Table Data Gateway: INSERT


Use the fetchNew() method to get a blank Row:


$bugsTable = new Bugs();


$newRow = $bugsTable
-
>fetchNew();


Set Row fields:


$newRow
-
>bug_description = ‘help me’;


$newRow
-
>bug_status = ‘NEW’;


Save new Row to the database:


$newRow
-
>save();

2007
-
04
-
25

Using MySQL with the Zend Framework

| Page
28

Table Data Gateway: DELETE


Get a Row you want to delete:


$bugsTable = new Bugs();


$row = $bugsTable
-
>find(123)
-
>current();


Delete the row from the database:


$row
-
>delete();

2007
-
04
-
25

Using MySQL with the Zend Framework

| Page
29

Table Data Gateway: relationships


Assume an Entity
-
Relationship like this:






Get a Row from the Accounts table:


$accountsTable = new Accounts();


$account = $accountsTable
-
>find(‘bill’)
-
>current();


Find a Rowset in related table Bugs:


$reportedBugs = $account
-
>findBugs();

reported by

bugs

accounts

2007
-
04
-
25

Using MySQL with the Zend Framework

| Page
30

Table Data Gateway: relationships (cont’d)


You also can get the parent row


Start with the dependent row in Bugs:


$bugsTable = new Bugs();


$bug = $bugsTable
-
>find(123)
-
>current();


Find the parent Row in related table Accounts:


$reporter = $bug
-
>findParentAccounts();

2007
-
04
-
25

Using MySQL with the Zend Framework

| Page
31

Table Data Gateway: relationships (cont’d)


Declare table relationships in the table class:


class Accounts extends Zend_Db_Table_Abstract

{


protected $_name = ‘accounts’;

}


class Bugs extends Zend_Db_Table_Abstract

{


protected $_name = ‘bugs’;



protected $_referenceMap = array(


‘Reporter’ => array(



‘columns’

=> array(‘reported_by’),



‘refTableClass’

=> ‘Accounts’


)

);

}


}

2007
-
04
-
25

Using MySQL with the Zend Framework

| Page
32

Table Data Gateway: customization


You can add custom logic to a table class:


class Bugs extends Zend_Db_Table_Abstract

{


protected $_name = ‘bugs’;



public

function

insert(array $data)


{



if

(empty($data['created_on']))

{




$data['created_on']

=

time();



}



return

parent::insert($data);


}

}

2007
-
04
-
25

Using MySQL with the Zend Framework

| Page
33

Recap of Zend Db


Adapter classes for PHP database extensions


Quoting SQL identifiers and values


Query profiler


SELECT query builder


Table Data Gateway / Row Data Gateway

Copyright © 2007, Zend Technologies Inc.

Thanks

http://framework.zend.com/