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
   

   View Slide

2. Who Am I?
   • Software Developer, Technical Writer, 

   & Podcaster
   • @settermjd
   • matthew@matthewsetter.com
   2
   

   View Slide

3. I Run This Blog
   3
   

   View Slide

4. What We'll Cover
   • Zend\Db\Sql Introduction
   • Build Queries
   4
   

   View Slide

5. What You'll Learn
   • Generate queries programmatically
   • Use Named & Positional Parameters
   • Run Hand Written SQL Queries
   5
   

   View Slide

6. Why This Talk?
   Because Writing SQL Isn't Necessarily Easy
   6
   

   View Slide

7. Writing SQL is Problematic
   7
   

   View Slide

8. function buildSql()

   {

   return "SELECT name, email FROM employees";

   }
   8
   

   View Slide

9. function buildSql($limit = null)

   {

   $sql = "SELECT name, email FROM employees ";

   if ($limit) {

   $sql .= "LIMIT 1, $limit";

   }

   return $sql;

   }
   9
   

   View Slide

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
    

    View Slide

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
    

    View Slide

12. How Do You Generate SQL
    Efficiently, even Hassle-Free?
    12
    

    View Slide

13. How Do You Build Queries 

    Which Are
    • Object-Oriented
    • Flexible
    • Maintainable
    • Reusable
    13
    

    View Slide

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
    

    View Slide

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
    

    View Slide

16. What Can Zend\Db\Sql Do?
    16
    

    View Slide

17. Everything You'd Expect!
    \ZEND\DB\SQL \ZEND\DB\SQL\DDL
    Selects Create Tables
    Inserts Alter Tables
    Updates Drop Tables
    Deletes
    17
    

    View Slide

18. What Databases Does it Support?
    18
    

    View Slide

19. Schema Creation
    19
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

25. Let's Run It!
    $sql = new Sql($adapter);
    $adapter->query(
    $sql->buildSqlString($table),
    $adapter::QUERY_MODE_EXECUTE
    );
    25
    

    View Slide

26. Schema Modification
    26
    

    View Slide

27. ALTER TABLE super_hero
    ADD INDEX idx_name (name(10));
    27
    

    View Slide

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
    

    View Slide

29. Insert Some Super Heroes
    29
    

    View Slide

30. INSERT INTO universe (name)

    VALUES ('DC'), ('Marvel');
    30
    

    View Slide

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
    

    View Slide

32. $insert = $sql->insert();
    $insert->into('super_hero')->values([
    'name' => 'The Incredible Hulk',
    'active' => true,
    'universe_id' => 2
    ]);
    32
    

    View Slide

33. $insert = $sql->insert();
    $insert->into('super_hero')

    ->columns(['name', 'active', 'universe_id'])

    ->values(['The Incredible Hulk', true, 2]);
    33
    

    View Slide

34. $sql->prepareStatementForSqlObject($insert)

    ->execute();
    34
    

    View Slide

35. Update Records
    35
    

    View Slide

36. UPDATE super_hero 

    SET active = false 

    WHERE universe_id = 2

    AND name = 'The Incredible Hulk';
    36
    

    View Slide

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
    

    View Slide

38. Delete Records
    38
    

    View Slide

39. DELETE FROM super_hero 

    WHERE Name = 'The Incredible Hulk'

    AND universe_id = 2;
    39
    

    View Slide

40. $delete = $sql->delete();
    $delete->table('super_hero')
    ->where->equalTo('name', 'The Incredible Hulk')
    ->equalTo('universe_id', 2);
    $statement = $sql->prepareStatementForSqlObject($delete)
    ->execute();
    40
    

    View Slide

41. Quick Recap
    • Programmatically Build DDL and DML
    • Methods and Objects Map to Underlying SQL
    • All Rather Intuitive
    41
    

    View Slide

42. SELECT name, active

    FROM super_hero;
    42
    Basic Select
    

    View Slide

43. $statement = $adapter->createStatement(

    "SELECT name, active FROM super_hero"

    );

    

    $results = $statement->execute();
    43
    

    View Slide

44. $select = $sql->select();
    $select->from('super_hero')
    ->columns(['name', 'active']);
    $statement = $sql->prepareStatementForSqlObject($select);
    $results = $statement->execute();
    44
    

    View Slide

45. SELECT name, active

    FROM super_hero

    ORDER BY name DESC, active ASC

    LIMIT 1, 20;
    45
    Add an Order By & Limit Clause
    

    View Slide

46. $statement = $adapter->createStatement(

    "SELECT name, active FROM super_hero

    ORDER BY name DESC, active ASC

    LIMIT 1, 20"

    );
    46
    

    View Slide

47. $select = $sql->select();
    $select->from('user')
    ->columns(['name', 'active', 'universe_id'])
    ->order('name DESC, active ASC')
    ->limit(20)
    ->offset(1);
    47
    

    View Slide

48. $select = $sql->select();
    $select->from('user')
    ->columns(['name', 'active', 'universe_id'])
    ->order(['name DESC', 'active ASC'])
    ->limit(20)
    ->offset(1);
    48
    

    View Slide

49. $select = $sql->select();
    $select->from('user')
    ->columns(['name', 'active', 'universe_id'])
    ->order(['name DESC'])
    ->order('active ASC')
    ->limit(20)
    ->offset(1);
    49
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

55. Reusable Predicates
    55
    

    View Slide

56. $universePredicate = new Predicate();
    $universePredicate->equalTo('un.name', 'DC');
    $heroNamePredicate = new Predicate();
    $heroNamePredicate->like('sh.name', '%Hulk');
    56
    

    View Slide

57. $predicate = new Predicate();
    $predicate->addPredicate($universePredicate);
    $predicate->addPredicate($heroNamePredicate);
    57
    

    View Slide

58. $predicate->addPredicates(
    [
    $universePredicate,
    $someOtherPredicate
    ]
    );
    58
    

    View Slide

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
    

    View Slide

60. Drop the Schema
    60
    

    View Slide

61. DROP TABLE "super_hero";
    DROP TABLE "universe";
    61
    

    View Slide

62. $table = new Ddl\DropTable('super_hero');
    $adapter->query(
    $sql->buildSqlString($table),
    $adapter::QUERY_MODE_EXECUTE
    );
    62
    

    View Slide

63. Final Recap
    • Programmatically Build DDL and DML
    • Run SQL strings
    • Easy to Get Started
    • Reasonably Intuitive
    63
    

    View Slide