Powerful & Flexible SQL Generation — without the Hassle

PHP is almost synonymous with databases, and it has been since the early versions. Yet creating SQL queries can still be a challenging task. What's the right tool to use? ORMs often feel like overkill, and creating queries by hand can be unmaintainable. In this session, I'll show you how to use the ZendDb library to generate SQL queries — from simple selects to more complex unions, filtered deletions, and updates. You'll learn how to use the library to create flexible, secure, maintainable, and reusable queries quickly and efficiently, saving you time and effort in the long term.

Transcript

1. Powerful & Flexible SQL Generation Without the Hassle! 1
2. Who Am I? • Software Developer, Technical Writer, 
 & Podcaster • @settermjd • matthew@matthewsetter.com 2
3. I Run This Blog 3
4. What We'll Cover • Zend\Db\Sql Introduction • Build Queries 4
5. What You'll Learn • Generate queries programmatically • Use Named & Positional Parameters • Run Hand Written SQL Queries 5
6. Why This Talk? Because Writing SQL Isn't Necessarily Easy 6
7. Writing SQL is Problematic 7
8. function buildSql()
 {
 return "SELECT name, email FROM employees";
 } 8
9. function buildSql($limit = null)
 {
 $sql = "SELECT name, email FROM employees ";
 if ($limit) {
 $sql .= "LIMIT 1, $limit";
 }
 return $sql;
 } 9
10. function buildSql($name = null, $limit = null)
 {
 $sql = "SELECT name, email FROM employees";
 
 if ($name) {
 $sql .= "WHERE 'name' LIKE '{$name}%'";
 }
 
 if ($limit) {
 $sql .= "LIMIT $limit";
 }
 
 return $sql;
 } 10
11. function buildSql($name = null, $email = null, $limit = null)
 {
 $sql = "SELECT name, email FROM employees";
 
 if ($name) {
 $sql .= "WHERE 'name' LIKE '{$name}%'";
 }
 if ($email) {
 if (!$name) {
 $sql .= "WHERE 'name' LIKE '{$name}%'";
 } else {
 $sql .= "AND 'email' = '$email'";
 }
 }
 if ($limit) {
 $sql .= "LIMIT $limit";
 }
 
 return $sql;
 } 11
12. How Do You Generate SQL Efficiently, even Hassle-Free? 12
13. How Do You Build Queries 
 Which Are • Object-Oriented • Flexible • Maintainable • Reusable 13
14. What is Zend\Db\Sql "Zend\Db\Sql is a SQL abstraction layer for building platform specific SQL queries via an object-oriented API." - http://framework.zend.com/manual/current/en/modules/zend.db.sql.html 14
15. Why Zend\Db\SQL • It's not an ORM • It's Easy to Start With • Build Flexible and Reusable Queries Programmatically • It's Light & Flexible • It Integrates with Zend Framework Components 15
16. What Can Zend\Db\Sql Do? 16
17. Everything You'd Expect! \ZEND\DB\SQL \ZEND\DB\SQL\DDL Selects Create Tables Inserts Alter Tables Updates Drop Tables Deletes 17
18. What Databases Does it Support? 18
19. Schema Creation 19
20. CREATE TABLE "super_hero" ( "id" INTEGER NOT NULL AUTO_INCREMENT, "universe_id" INTEGER NOT NULL, "name" VARCHAR(200) NOT NULL, "active" BOOLEAN NOT NULL DEFAULT '', CONSTRAINT "tblpk" PRIMARY KEY ("id"), CONSTRAINT "tbluniq_nameuniverse" UNIQUE ( "name", "universe_id" ), CONSTRAINT "con_universefk" FOREIGN KEY 
 REFERENCES "super_hero"("universe_id") 
 ON DELETE CASCADE 
 ON UPDATE CASCADE ); 20
21. CREATE TABLE "universe" ( "id" INTEGER NOT NULL AUTO_INCREMENT, "name" VARCHAR(20) NOT NULL, CONSTRAINT "tblpk" PRIMARY KEY ("id"), CONSTRAINT "tbluniq_universe" UNIQUE ("name") ); 21
22. use Zend\Db\Sql\Sql; use Zend\Db\Sql\Ddl; use Zend\Db\Sql\Ddl\Column; use Zend\Db\Sql\Ddl\Index; use Zend\Db\Sql\Ddl\Constraint; $adapter = new Zend\Db\Adapter\Adapter([ 'driver' => 'Pdo', 'dsn' => 'mysql:host=127.0.0.1;port=3306;dbname=mydb',
 'user' => 'username',
 'password' => 'password' ]); 22
23. $idColumn = new Column\Integer('id'); $idColumn->setOption('auto_increment', true); 
 $table = new Ddl\CreateTable('universe'); $table->addColumn($idColumn) ->addColumn(new Column\Varchar(
 'name', 200, false )) ->addConstraint( new Constraint\PrimaryKey(['id'], 'tblpk') ) ->addConstraint(new Constraint\UniqueKey( ['name'], 'tbluniq_universe' )); 23
24. $idColumn = new Column\Integer('id'); $idColumn->setOption('auto_increment', true); $table = new Ddl\CreateTable('super_hero'); $table->addColumn($idColumn) ->addColumn(new Column\Varchar('name', 200, false)) ->addColumn(new Column\Integer('universe_id')) ->addColumn(new Column\Boolean('active', false)) ->addConstraint( new Constraint\PrimaryKey(['id'], 'con_tblpk') ) ->addConstraint(new Constraint\UniqueKey( ['name', 'universe_id'], 'tbluniq_nameuniverse' )) ->addConstraint(new Constraint\ForeignKey( 'fk_universe', ['universe_id'], 'universe', ['id'], 'CASCADE', 'CASCADE' )); 24
25. Let's Run It! $sql = new Sql($adapter); $adapter->query( $sql->buildSqlString($table), $adapter::QUERY_MODE_EXECUTE ); 25
26. Schema Modification 26
27. ALTER TABLE super_hero ADD INDEX idx_name (name(10)); 27
28. $table = new Ddl\AlterTable('super_hero'); $table->addConstraint( new Index\Index( 'name, 'idx_name', 10 ) ); $adapter->query( $sql->buildSqlString($table), $adapter::QUERY_MODE_EXECUTE ); 28
29. Insert Some Super Heroes 29
30. INSERT INTO universe (name)
 VALUES ('DC'), ('Marvel'); 30
31. INSERT INTO super_hero (name, active, universe_id)
 VALUES ('The Incredible Hulk', true, 2),
 ('Spiderman', true, 2),
 ('Ironman', true, 2),
 ('Superman', true, 1),
 ('Batman', true, 1); 31
32. $insert = $sql->insert(); $insert->into('super_hero')->values([ 'name' => 'The Incredible Hulk', 'active' => true, 'universe_id' => 2 ]); 32
33. $insert = $sql->insert(); $insert->into('super_hero')
 ->columns(['name', 'active', 'universe_id'])
 ->values(['The Incredible Hulk', true, 2]); 33
34. $sql->prepareStatementForSqlObject($insert)
 ->execute(); 34
35. Update Records 35
36. UPDATE super_hero 
 SET active = false 
 WHERE universe_id = 2
 AND name = 'The Incredible Hulk'; 36
37. $update = $sql->update(); $update->table('super_hero') ->set(['active' => false]) ->where->equalTo('universe_id', 2) ->and->equalTo('name', 'The Incredible Hulk'); $statement = $sql->prepareStatementForSqlObject($update) ->execute(); 37
38. Delete Records 38
39. DELETE FROM super_hero 
 WHERE Name = 'The Incredible Hulk'
 AND universe_id = 2; 39
40. $delete = $sql->delete(); $delete->table('super_hero') ->where->equalTo('name', 'The Incredible Hulk') ->equalTo('universe_id', 2); $statement = $sql->prepareStatementForSqlObject($delete) ->execute(); 40
41. Quick Recap • Programmatically Build DDL and DML • Methods and Objects Map to Underlying SQL • All Rather Intuitive 41
42. SELECT name, active
 FROM super_hero; 42 Basic Select
43. $statement = $adapter->createStatement(
 "SELECT name, active FROM super_hero"
 );
 
 $results = $statement->execute(); 43
44. $select = $sql->select(); $select->from('super_hero') ->columns(['name', 'active']); $statement = $sql->prepareStatementForSqlObject($select); $results = $statement->execute(); 44
45. SELECT name, active
 FROM super_hero
 ORDER BY name DESC, active ASC
 LIMIT 1, 20; 45 Add an Order By & Limit Clause
46. $statement = $adapter->createStatement(
 "SELECT name, active FROM super_hero
 ORDER BY name DESC, active ASC
 LIMIT 1, 20"
 ); 46
47. $select = $sql->select(); $select->from('user') ->columns(['name', 'active', 'universe_id']) ->order('name DESC, active ASC') ->limit(20) ->offset(1); 47
48. $select = $sql->select(); $select->from('user') ->columns(['name', 'active', 'universe_id']) ->order(['name DESC', 'active ASC']) ->limit(20) ->offset(1); 48
49. $select = $sql->select(); $select->from('user') ->columns(['name', 'active', 'universe_id']) ->order(['name DESC']) ->order('active ASC') ->limit(20) ->offset(1); 49
50. SELECT 
 sh.name as "Hero, 
 sh.active, un.name as "Universe"
 FROM super_hero sh
 INNER JOIN universe un ON (sh.universe_id = un.id)
 WHERE un.name = 'DC'
 ORDER BY sh.name DESC, sh.active ASC
 LIMIT 1, 20; 50 Add a Join & a Where Clause
51. $statement = $adapter->createStatement(
 "SELECT 
 us.name as "Hero", us.active, 
 un.name as "Universe"
 FROM super_hero sh
 INNER JOIN universe un ON (
 sh.universe_id = un.id
 )
 WHERE un.name = 'DC'
 ORDER BY sh.name DESC, sh.active ASC
 LIMIT 1, 20"
 ); 51
52. $select = $sql->select(); $select->from(['sh' => 'super_hero']) ->columns(["Hero" => 'name', 'active', 'universe_id']) ->join(
 ['un' => 'universe'], 
 'un.id = sh.universe_id', 
 ["Universe" => 'name'] )
 ->where->equals('un.name', 'DC') ->order('sh.name DESC, sh.active ASC') ->limit(20) ->offset(1); 52
53. $statement = $adapter->createStatement(
 "SELECT 
 sh.name as "Hero, sh.active, 
 un.name as "Universe"
 FROM super_hero sh
 INNER JOIN universe un ON (sh.universe_id = un.id)
 WHERE un.name = :UniverseName
 ORDER BY sh.name DESC, sh.active ASC
 LIMIT 1, 20",
 [':UniverseName' => 'DC']
 ); 53
54. $statement = $adapter->createStatement(
 "SELECT 
 sh.name as "Hero, sh.active, un.name as "Universe"
 FROM super_hero sh
 INNER JOIN universe un ON (sh.universe_id = un.id)
 WHERE un.name = ? ORDER BY sh.name DESC, sh.active ASC
 LIMIT 1, 20",
 ['DC']
 ); 54
55. Reusable Predicates 55
56. $universePredicate = new Predicate(); $universePredicate->equalTo('un.name', 'DC'); $heroNamePredicate = new Predicate(); $heroNamePredicate->like('sh.name', '%Hulk'); 56
57. $predicate = new Predicate(); $predicate->addPredicate($universePredicate); $predicate->addPredicate($heroNamePredicate); 57
58. $predicate->addPredicates( [ $universePredicate, $someOtherPredicate ] ); 58
59. $select = $sql->select(); $select->from('super_hero') ->columns(['name', 'active', 'universe_id']) ->join(
 ['un' => 'universe'], 
 'un.id = u.universe_id', 
 ['name'] )
 ->where($predicate) ->order('name DESC') ->limit(20) ->offset(1); 59
60. Drop the Schema 60
61. DROP TABLE "super_hero"; DROP TABLE "universe"; 61
62. $table = new Ddl\DropTable('super_hero'); $adapter->query( $sql->buildSqlString($table), $adapter::QUERY_MODE_EXECUTE ); 62
63. Final Recap • Programmatically Build DDL and DML • Run SQL strings • Easy to Get Started • Reasonably Intuitive 63