This chapter shows how to use the factory and the instance as well as how to
execute some typical queries. For more details 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 easily be 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 get 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 transfered to
the SQL server independently from the main query. See the chapter '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. ?>
This chapter gives you a basic introduction on how to build queries using the
query abstraction layer.
We will start out with 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 pretty straight forward. 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 ensures that you get a query of the correct type regardless of what
database you use.
The next example builds 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 ten first quotes by Robert Foster sorted by the quote itself.
Note that string parameters must be either 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 can not be used in method names.
The next example shows that you in a similar way to the SELECT query can
insert, update and delete rows from a table using the query abstraction layer.
The example 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() method 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() methods multiple 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. ?>
Simplified version of 1. where 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. ?>
- Simple form, could be used to join only two tables.
Takes 4 string arguments and return SQL string. This way does mainly exist
for BC 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. ?>
The 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 and
parts of the statement is built up of untrusted data. If the untrusted data is
not escaped properly or checked for proper input you will have a possible SQL
injection problem.
With the introduction of bound values it is possible to avoid SQL injection
altogether. Simply always use bind to insert untrusted data into a query. This
is usually also more efficient since you don't need to escape the data and the
SQL server does not have to parse it as part of the query string.
