PHP code example of mindplay / sql

    
        
<?php
require_once('vendor/autoload.php');

/* Start to develop here. Best regards https://php-download.com/ */

    

mindplay / sql example snippets

/**
 * @property-read Column $id
 * @property-read Column $first_name
 * @property-read Column $last_name
 */
class UserTable extends Table
{
    public function id($alias)
    {
        return $this->autoColumn(__FUNCTION__, IntType::class, $alias);
    }
    
    public function first_name($alias)
    {
        return $this->

/**
 * @property-read UserTable    $user
 * @property-read AddressTable $address
 */
class UserSchema extends Schema
{
    /**
     * @param string $alias
     * 
     * @return UserTable
     */
    public function user($alias)
    {
        return $this->createTable(UserTable::class, __FUNCTION__, $alias);
    }

    /**
     * @param string $alias
     * 
     * @return AddressTable
     */
    public function address($alias)
    {
        return $this->createTable(AddressTable::class, __FUNCTION__, $alias);
    }
}

$db = new MySQLDatabase();

/** @var UserSchema $schema */
$schema = $db->getSchema(UserSchema::class);

$connection = $db->createConnection(new PDO("mysql:dbname=foo;host=127.0.0.1", "root", "root"));

$user = $schema->user;

$query = $db->select($user)
    ->where("{$user->first_name} LIKE :name")
    ->bind("name", "%rasmus%")
    ->order("{$user->last_name} DESC, {$user->first_name} DESC")
    ->page(1, 20); // page 1, 20 records per page, e.g.: OFFSET 0 LIMIT 20

$query->bind("name", $value);

$query->bind("created", $timestamp, DateType::class);

$query->apply([
    "first_name" => $first_name,
    "last_name" => $last_name,
]);

$user = $schema->user;

$query = $db->select($user);

$user = $schema->user;

$query = $db
    ->select($user)
    ->columns([
        $user->first_name,
        $user->last_name,
    ]);

$user = $schema->user;

$query = $db
    ->select($user)
    ->columns([$user->id])
    ->value("CONCAT({$user->first_name}, ' ', {$user->last_name})", "full_name");

$user = $schema->user;

$query = $db
    ->select($user)
    ->table($user)
    ->value("DATEDIFF(hour, {$user->dob}, NOW()) / 8766", "age", IntType::class);

$query->having("age >= 21");

$user = $schema->user;

$query = $db
    ->select($user)
    ->columns([$user->country])
    ->groupBy($user->country)
    ->value("COUNT({$user})", "num_users");

$user = $schema->user;

$query = $db
    ->select($user)
    ->where([
        "{$user->first_name} LIKE :first_name",
        "{$user->last_name} LIKE :last_name",
    ])
    ->apply([
        "first_name" => "ras%",
        "last_name" => "sch%",
    ]);

$user = $schema->user;

$query = $db
    ->select($user)
    ->where(
        expr::any([
            "{$user->first_name} LIKE :first_name",
            "{$user->last_name} LIKE :last_name",
        ])
    )
    ->apply([
        "first_name" => "ras%",
        "last_name" => "sch%",
    ]);

$customer = $schema->customer;
$order = $schema->order;

$query = $db
    ->select($customer)
    ->table($customer)
    ->leftJoin($order, "{$order->customer_id} = {$customer->id}")
    ->value("COUNT({$order})", "num_orders")
    ->groupBy($customer->id);

$employee = $schema->employee;
$supervisor = $schema->employee("supervisor"); // e.g. "employee AS supervisor"

$query = $db
    ->select($employee)
    ->table($employee)
    ->leftJoin($supervisor, "{$supervisor->id} = {$emplyoee->supervisor_id}")
    ->columns($supervisor->name);

$user = $schema->user;

$query = $db
    ->insert($user)
    ->add([
        "first_name" => "Rasmus",
        "last_name" => "Schultz",
        "dob" => 951030427,
    ]);

$user = $schema->user;

$query = $db
    ->insert($user)
    ->add([
        $user->first_name->getName() => "Rasmus",
        $user->last_name->getName() => "Schultz",
        $user->dob->getName() => 951030427,
    ]);

$user = $schema->user;

$query = $db
    ->update($user)
    ->where("{$user->id} = :id")
    ->bind("id", 123)
    ->setValue($user->first_name, "Rasmus");

$query->assign([
    "first_name" => "Rasmus"
]);

$user = $schema->user;

$query = $db
    ->update($user)
    ->where("{$user->id} = :id")
    ->bind("id", 123)
    ->setExpr($user->last_logged_in, "NOW()");

$user = $schema->user;

$query = $db
    ->delete($user)
    ->where("{$user->id} = :id")
    ->bind("id", 123);

$user = $schema->user;

$query = $db
    ->sql("SELECT COUNT({$user}) as num_users FROM {$user} WHERE {$user->created} > :first_date")
    ->bind("first_date",  time() + 30*24*60*60, TimestampType::class);

$customer = $schema->customer;
$order = $schema->order;

$num_orders = $db
    ->select($order)
    ->value("COUNT({$order})")
    ->where([
        "{$order->total} > :min_total",
        "{$order->customer_id} = {$customer->id}",
    ]);

$query = $db
    ->select($customer)
    ->columns([
        $customer->id,
        $customer->first_name,
        $customer->last_name,
    ])
    ->value($num_orders, "num_orders")
    ->bind("min_total", 100);

$query = $db
    ->select($customer)
    ->columns([
        $customer->id,
        $customer->first_name,
        $customer->last_name,
    ])
    ->value(
        "SELECT COUNT({$order}) FROM {$order}"
        . " WHERE ({$order->total} > :min_total)"
        . " AND ({$order->customer_id} = {$customer->id})",
        "num_orders"
    )
    ->bind("min_total", 100);

$connection->execute(
    $db->sql("DELETE FROM order WHERE id = :id")->bind("id", 123)
);

$delete = $db->sql("DELETE FROM order WHERE id = :id")->bind("id", 123);

if ($connection->execute($delete)->getRowsAffected() !== 1) {
    // delete failed!
}

$query = $db->sql("SELECT * FROM user");

$result = $connection->fetch($query);

foreach ($result as $row) {
    var_dump($row["id"], $row["first_name"]);
}

$user = $schema->user;

$query = $db
    ->select($user)
    ->where("{$user->id} = :id")
    ->bind("id", 123);

$row = $connection->fetch($query)->firstRow();

var_dump($row["created"]); // => (int) 1553177264

$user = $schema->user;

$query = $db
    ->select($user)
    ->mapRecords(function (array $row) {
        return new User($row["id"], $row["first_name"], $row["last_name"]);
    });

$results = $connection->fetch($query);

foreach ($results as $result) {
    var_dump($result); // class User#1 (0) { ... }
}

class UserMapper implements Mapper
{
    public function map(array $rows)
    {
        foreach ($rows as $row) {
            yield new User($row["id"], $row["first_name"], $row["last_name"]);
        }
    }
}

$query = $db
    ->select($user)
    ->map(new UserMapper());

$query = $db
    ->select($user)
    ->map(new UserMapper());

$result = $connection->fetch($query, 100); // batches of 100

foreach ($result as $row) {
    // ...
}

$query = $db
    ->select($user)
    ->where("{$user->name} LIKE :name")
    ->bind("name", "%rasmus%")
    ->page($page_no, 20); // $page_no is the base-1 page number

$count = $connection->count($query);  // total number of matching results (for display)

$num_pages = ceil($count / 20);       // total number of pages (for display)

$result = $connection->fetch($query); // 20 records of the requested page number

$connection->transact(function () use ($connection, $db) {
    $connection->execute(
        $db->sql("UPDATE payment WHERE id = :payment_id SET paid = NOW()")->bind(...)
    );
    
    $connection->execute(
        $db->sql("INSERT INTO subscription (...) VALUES (...)")->bind(...);
    );
    
    return true; // COMMITS the transaction
});

$delete = $connection->prepare($db->sql("DELETE FROM order WHERE id = :id"));

foreach ($ids as $id) {
    $delete->bind("id", $id);
    $delete->execute();
}

$buffer = new BufferedPSRLogger();

$connection->addLogger($buffer);

// ... execute queries ...

$buffer->flushTo($psr_logger);