This section shows how to use the factory and the instance as well as how to
execute some typical queries. For more information on how to perform queries using
the handlers, we recommend reading the PHP PDO documentation.
In order to get started, you need a database handler. The first example shows
how to create one using ezcDbFactory and how to store the handler in
ezcDbInstance so it can be easily retrieved later:
1. <?php
2.
3. $db = ezcDbFactory::create( 'mysql://user:password@host/database' );
4. ezcDbInstance::set( $db );
5.
6. // anywhere later in your program you can retrieve the db instance again using
7. $db = ezcDbInstance::get();
8.
9. ?>
Executing a simple query and returning the result right away can be done with the
PDO::query() method:
1. <?php
2.
3. $db = ezcDbInstance::get();
4.
5. $rows = $db->query( 'SELECT * FROM quotes' );
6.
7. // Iterate over the rows and print the information from each result.
8. foreach( $rows as $row )
9. {
10. print_r( $row );
11. }
12. ?>
Next, we show a simple example with statements and the use of bind. Binding
values can be very valuable both in terms of efficiency and security. The main
difference with normal queries is that the bound value will be transferred to
the SQL server independent of the main query. See the section 'Avoiding SQL
injection' below.
1. <?php
2.
3. $db = ezcDbInstance::get();
4. $stmt = $db->prepare( 'SELECT * FROM quotes where author = :author' );
5. $stmt->bindValue( ':author', 'Robert Foster' );
6.
7. $stmt->execute();
8. $rows = $stmt->fetchAll();
9.
10. ?>
Lazy initialization is a mechanism to load and configure a component, only
when it is really used in your application. This mechanism saves time for
parsing the classes and configuration, when the component is not used at all
during one request. You can find a description how you can use it for your
own components and how it works in the ezcBase tutorial. The keyword for
the database component is ezcInitDatabaseInstance.
1. <?php
2. require_once 'tutorial_autoload.php';
3.
4. class customLazyDatabaseConfiguration implements ezcBaseConfigurationInitializer
5. {
6. public static function configureObject( $instance )
7. {
8. switch ( $instance )
9. {
10. case false: // Default instance
11. return ezcDbFactory::create( 'mysql://user:password@host/database' );
12. case 'sqlite':
13. return ezcDbFactory::create( 'sqlite://:memory:' );
14. }
15. }
16. }
17.
18. ezcBaseInit::setCallback(
19. 'ezcInitDatabaseInstance',
20. 'customLazyDatabaseConfiguration'
21. );
22.
23. // Create and configure default mysql connection
24. $db = ezcDbInstance::get();
25.
26. // Create and configure additional sqlite connection
27. $sb = ezcDbInstance::get( 'sqlite' );
28. ?>
ezcBaseInit::setCallback accepts as a first parameter a component specific key,
which lets the component later request the right configuration callback. The
second parameter is the name of the class to perform the static callback on.
This class must implement the ezcBaseConfigurationInitializer class.
Each component's lazy initialization calls the static method configureObject()
on the referenced class.
This example shows a way to configure multiple database handlers, only when
they are really requested in your application. The example does basicaly the
same like the first example in this tutorial, but creates the connection not
before it is really required.
In line 24 the default database handler is first requested in this example,
which does not exist yet, so that the configuration class earlier referenced
through the setCallback() call will be asked for a new instance for the
current instance name, which is (bool) false for the default instance.
In the configureObject() method in line 8 we switch on the instance name and
create and return the right newly created database handler. Line 27 shows,
that this will also work with multiple database instances, creating an
additional SQLite connection.
This section gives you a basic introduction on how to build queries using the
query abstraction layer.
We will start out by recreating the first query example:
1. <?php
2.
3. $db = ezcDbInstance::get();
4.
5. $q = $db->createSelectQuery();
6. $q->select( '*' )->from( 'quotes' );
7.
8. $stmt = $q->prepare();
9. $stmt->execute();
10.
11. ?>
As you can see, building the query itself follows the build-up of a normal
query and is rather straightforward. The rest of the example is a bit more
verbose; this is mainly due to the fact that you need to fetch the query object
from the handler and that you are required to use prepared statements with the
query abstraction layer. The factory methods in the handler to fetch the query
object ensure that you get a query of the correct type, regardless of what
database you use.
The next example is based on the previous one, but builds a more complex query
and introduces the usage of bind parameters in the query:
1. <?php
2.
3. $db = ezcDbInstance::get();
4.
5. $q = $db->createSelectQuery();
6. $e = $q->expr; // fetch the expression object
7. $q->select( '*' )->from( 'quotes' )
8. ->where( $e->eq( 'author', $q->bindValue( 'Robert Foster' ) ) )
9. ->orderBy( 'quote' )
10. ->limit( 10, 0 );
11.
12. $stmt = $q->prepare();
13. $stmt->execute();
14.
15. ?>
The query will fetch the first ten quotes by Robert Foster, sorted by the quote itself.
Note that string parameters must either be bound using
ezcQuery::bindParam()/ezcQueryBindValue() or escaped and quoted manually.
As you can see, logical expressions are built up using the expression object of
the type ezcQueryExpression. Note that the methods for logical or and and are
named lOr and lAnd, respectively. This is because and and or are reserved names
in PHP and cannot be used in method names.
The next example shows that you can insert, update and delete rows from a table
using the query abstraction layer, in a similar way to the SELECT query.
The example below shows how to create and use basic INSERT, UPDATE and DELETE
query objects.
1. <?php
2.
3. $db = ezcDbInstance::get();
4.
5. // Insert
6. $q = $db->createInsertQuery();
7. $q->insertInto( 'quotes' )
8. ->set( 'id', 1 )
9. ->set( 'name', $q->bindValue( 'Robert Foster' ) )
10. ->set( 'quote', $q->bindValue( "It doesn't look as if it's ever used!" ) );
11. $stmt = $q->prepare();
12. $stmt->execute();
13.
14. // update
15. $q = $db->createUpdateQuery();
16. $q->update( 'quotes' )
17. ->set( 'quote', 'His skin is cold... Like plastic...' )
18. ->where( $q->expr->eq( 'id', 1 ) );
19. $stmt = $q->prepare();
20. $stmt->execute();
21.
22. // delete
23. $q = $db->createDeleteQuery();
24. $q->deleteFrom( 'quotes' )
25. ->where( $q->expr->eq( $q->bindValue( 'Robert Foster' ) ) );
26. $stmt = $q->prepare();
27. $stmt->execute();
28. ?>
The next examples show how to use multi-join syntax to build
queries with several joined tables using inner, right or left join.
The innerJoin(), rightJoin() and leftJoin() methods can be used in three forms:
- The first form takes two string arguments (table name and join condition)
and returns an ezcQuery object. Each invocation joins one table. You can
invoke the *Join() method several times.
1. <?php
2.
3. $db = ezcDbInstance::get();
4.
5. $q = $db->createSelectQuery();
6.
7. // Right join of two tables. Will produce SQL:
8. // "SELECT id FROM table1 RIGHT JOIN table2 ON table1.id = table2.id".
9. $q->select( 'id' )->from( 'table1' )->rightJoin( 'table2', $q->expr->eq( 'table1.id', 'table2.id' ) );
10.
11. $stmt = $q->prepare();
12. $stmt->execute();
13.
14. // Right join of three tables. Will produce SQL:
15. // "SELECT id FROM table1 RIGHT JOIN table2 ON table1.id < table2.id RIGHT JOIN table3 ON table2.id > table3.id".
16. $q->select( 'id' )
17. ->from( 'table1' )
18. ->rightJoin( 'table2', $q->expr->lt( 'table1.id', 'table2.id' ) )
19. ->rightJoin( 'table3', $q->expr->gt( 'table2.id', 'table3.id' ) );
20.
21. $stmt = $q->prepare();
22. $stmt->execute();
23. ?>
This is a simplified version of form 1, where the join condition is always
set to "equal".
rightJoin( 'table1', 'table1.id', 'table2.id' ) is a shorter equivalent
of rightJoin( 'table1', $this->expr->eq('table1.id', 'table2.id' ) );
1. <?php
2.
3. $db = ezcDbInstance::get();
4.
5. $q = $db->createSelectQuery();
6.
7. // Right join of three tables. Will produce SQL:
8. // "SELECT id FROM table1 RIGHT JOIN table2 ON table1.id = table2.id RIGHT JOIN table3 ON table2.id = table3.id".
9. $q->select( 'id' )
10. ->from( 'table1' )
11. ->rightJoin( 'table2', 'table1.id', 'table2.id' )
12. ->rightJoin( 'table3', 'table2.id', 'table3.id' );
13.
14. $stmt = $q->prepare();
15. $stmt->execute();
16.
17.
18. ?>
- This is a simple form that can join only two tables. It takes four
string arguments and returns an SQL string. This exists mainly for backwards
compatibility reasons.
1. <?php
2.
3. $db = ezcDbInstance::get();
4.
5. $q = $db->createSelectQuery();
6.
7. // $q->rightJoin( 'table1', 'table2', 'table1.id', 'table2.id' ) will produce
8. // string "table1 RIGHT JOIN table2 ON table1.id = table2.id"
9. // that should be added to FROM clause of query.
10. // resulting query is "SELECT id FROM table1 RIGHT JOIN table2 ON table1.id = table2.id".
11. $q->select( 'id' )->from( $q->rightJoin( 'table1', 'table2', 'table1.id', 'table2.id' ) );
12. $stmt = $q->prepare();
13. $stmt->execute();
14.
15. ?>
This final example shows how to build subselect queries inside SELECT:
1. <?php
2.
3. $name = 'IBM';
4. $q = new ezcQuerySelect( ezcDbInstance::get() );
5.
6. // Creating subselect object
7. $q2 = $q->subSelect();
8.
9. // $q2 will build the subquery "SELECT company FROM query_test WHERE
10. // company = :ezcValue1 AND id > 2". This query will be used inside the SQL for
11. // $q.
12. $q2->select( 'company' )
13. ->from( 'query_test' )
14. ->where( $q2->expr->eq( 'company', $q2->bindParam( $name ) ), 'id > 2' );
15.
16. // $q the resulting query. It produces the following SQL:
17. // SELECT * FROM query_test
18. // WHERE id >= 1 AND
19. // company IN ( (
20. // SELECT company FROM query_test
21. // WHERE company = :ezcValue1 AND id > 2
22. // ) )
23. $q->select('*')
24. ->from( 'query_test' )
25. ->where( ' id >= 1 ', $q->expr->in( 'company', $q2->getQuery() ) );
26.
27. $stmt = $q->prepare();
28. $stmt->execute();
29.
30. ?>
SQL injection is possibly the biggest single cause of major security problems
in web applications. SQL injections are caused when building SQL statements
that include untrusted data. If the untrusted data is
not escaped properly or checked for proper input, you are susceptible to SQL
injections.
With the introduction of bound values, it is possible to avoid SQL injection
altogether. Simply use bind to insert untrusted data into a query. This
is usually more efficient as well, since you do not need to escape the data and the
SQL server does not have to parse it as part of the query string.
Even though the query abstraction layer creates syntactically equal queries for the
supported databases, the results may still differ. This is due to a large
number of differences between the databases.
For example, SQLite does not support the rebinding of values. If you have an insert query
and want to reuse it as outlined below, an error will be returned:
$q->insertInto( 'query_test' )
->set( 'id', 1 )
->set( 'company', $q->bindValue( 'eZ systems' ) )
->set( 'section', $q->bindValue( 'Norway' ) )
->set( 'employees', 20 );
$stmt = $q->prepare();
$stmt->execute();
$q->insertInto( 'query_test' );
$q->set( 'id', 2 );
$q->set( 'company', $q->bindValue( 'Trolltech' ) );
$q->set( 'section', $q->bindValue( 'Norway' ) );
$q->set( 'employees', 70 );
$stmt = $q->prepare();
$stmt->execute();
Instead, you should use bindParameter() to achieve the same effect:
$company = 'eZ systems';
$section = 'Norway';
$q->insertInto( 'query_test' )
->set( 'id', 1 )
->set( 'company', $q->bindParam( $company ) )
->set( 'section', $q->bindParam( $section ) )
->set( 'employees', 20 );
$stmt = $q->prepare();
$stmt->execute();
$q->insertInto( 'query_test' );
$q->set( 'id', 2 );
$q->set( 'employees', 70 );
$company = 'Trolltech';
$section = 'Norway';
$stmt = $q->prepare();
$stmt->execute();
