Select All Employees

function buildSql()
{

  return "SELECT name, email FROM employees";

}

Add A Limit Clause

function buildSql($limit = null)
{

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

  if ($limit) {

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

  }

  return $sql;

}

Add A Where Clause

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;

}

Further Complicate The Query

function buildSql($name = null, $email = null, $limit = null)
{

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


    if ($name !== null) {

        $sql .= "WHERE 'name' LIKE '{$name}%'";

    }

    if ($email) {

        if ($name === null) {

            $sql .= "WHERE 'email' LIKE '{$email}%'";

        } else {

            $sql .= "AND 'email' = '$email'";

        }

    }

    if ($limit) {

        $sql .= "LIMIT $limit";

    }



    return $sql;

}

The Universe Table

CREATE TABLE "universe" (
    "id" INTEGER NOT NULL AUTO_INCREMENT,
    "name" VARCHAR(20) NOT NULL,
    CONSTRAINT "tblpk" PRIMARY KEY ("id"),
    CONSTRAINT "tbluniq_universe" UNIQUE ("name")
);

The Super Hero Table

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
);

Create a Database Adapter

use Zend\Db\Sql\Sql;
use Zend\Db\Sql\Ddl;
use Zend\Db\Sql\Ddl\{Column,Index,Constraint};

$adapter = new Zend\Db\Adapter\Adapter([
    'driver'   => 'Pdo',
    'dsn'      => 'mysql:host=127.0.0.1;port=3306;dbname=mydb',

    'user'     => 'username',

    'password' => 'password'
]);

Creating the Universe Table

$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'
    ));

Create the Super Hero Table

$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'
    ));

Let's Run It

$sql = new Sql($adapter);

$adapter->query(
    $sql->buildSqlString($table),
    $adapter::QUERY_MODE_EXECUTE
);

The Query

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

The Code

$table = new Ddl\AlterTable('super_hero');

$table->addConstraint(
    new Index\Index('name', 'idx_name', 10)
);

$adapter->query(
    $sql->buildSqlString($table),
    $adapter::QUERY_MODE_EXECUTE
);

Insert Some Universes

INSERT INTO universe (name)

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

Insert Some Super Heroes

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);

The Code

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

The Code

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

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

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

Run The Code

$sql->prepareStatementForSqlObject($insert)
    ->execute();

The Query

UPDATE super_hero 

SET active = false 

WHERE universe_id = 2

AND name = 'The Incredible Hulk';

The Code

$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();

The Query

DELETE FROM super_hero 

WHERE Name = 'The Incredible Hulk'

AND universe_id = 2;

The Code

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

$statement = $sql->prepareStatementForSqlObject($delete)
                 ->execute();

Basic Select

SELECT name, active
FROM super_hero;

Run a SQL String

$statement = $adapter->createStatement(
    "SELECT name, active FROM super_hero"
);


$results = $statement->execute();

Be Object-Oriented

$select = $sql->select();
$select->from('super_hero')
    ->columns(['name', 'active']);

$statement = $sql->prepareStatementForSqlObject($select);
$results = $statement->execute();

Add An Order By & Limit Clause

SELECT name, active

FROM super_hero

ORDER BY name DESC, active ASC

LIMIT 1, 20;

Augment The Query String

$statement = $adapter->createStatement(

    "SELECT name, active FROM super_hero

    ORDER BY name DESC, active ASC

    LIMIT 1, 20"

);

Augment The Query Code

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

Call It a Different Way

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

…Or Yet Another Way

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

Add a Join & a Where Clause

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;

Augment The Query String

$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"

);

Refactor The Query

$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);

Create Predicate Objects

$universePredicate = new Predicate();
$universePredicate->equalTo('un.name', 'DC');

$heroNamePredicate = new Predicate();
$heroNamePredicate->like('sh.name', '%Hulk');

Use The Predicates

$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);

Create Compound Objects

$predicate = new Predicate();

$predicate->addPredicate($universePredicate);
$predicate->addPredicate($heroNamePredicate);

Create Compound Objects

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

Use The Compound Objects

$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);

The Drop Table Query

DROP TABLE "super_hero";
DROP TABLE "universe";

The Drop Table Code

$table = new Ddl\DropTable('super_hero');

$adapter->query(
    $sql->buildSqlString($table),
    $adapter::QUERY_MODE_EXECUTE
);