PHP code example of thefinaloutpost / database

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

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

    

thefinaloutpost / database example snippets

use Cake\Database\Connection;
use Cake\Database\Driver\Mysql;

$driver = new Mysql([
	'database' => 'test',
	'username' => 'root',
	'password' => 'secret'
]);
$connection = new Connection([
	'driver' => $driver
]);

use Cake\Database\Connection;

$connection = new Connection([
	'driver' => Cake\Database\Driver\Sqlite::class,
	'database' => '/path/to/file.db'
]);

$statement = $connection->execute('SELECT * FROM articles');

while($row = $statement->fetch('assoc')) {
	echo $row['title'] . PHP_EOL;
}

$statement = $connection->execute('SELECT * FROM articles WHERE id = :id', ['id' => 1], ['id' => 'integer']);
$results = $statement->fetch('assoc');

$statement = $connection->prepare('SELECT * from articles WHERE id != :id');
$statement->bind(['id' => 1], ['id' => 'integer']);
$results = $statement->fetchAll('assoc');

$statement = $connection->prepare('SELECT * from articles WHERE id = :id');
$statement->bind(['id' => 1], ['id' => 'integer']);
$results = $statement->fetchAll('assoc');

$statement->bind(['id' => 1], ['id' => 'integer']);
$results = $statement->fetchAll('assoc');

$connection->update('articles', ['title' => 'New title'], ['id' => 1]);

$connection->update(
	'articles',
	['title' => 'New title'],
	['created >=' => new DateTime('-3 day'), 'created <' => new DateTime('now')],
	['created' => 'datetime']
);

$connection->delete('articles', ['created <' => DateTime('now')], ['created' => 'date']);

$connection->insert(
	'articles',
	['title' => 'My Title', 'body' => 'Some paragraph', 'created' => new DateTime()],
	['created' => 'datetime']
);

$query = $connection->newQuery();

$query->select(['id', 'title', 'body']);

// Results in SELECT id AS pk, title AS aliased_title, body ...
$query->select(['pk' => 'id', 'aliased_title' => 'title', 'body']);

// Use a closure
$query->select(function ($query) {
	return ['id', 'title', 'body'];
});

// WHERE id = 1
$query->where(['id' => 1]);

// WHERE id > 2
$query->where(['id >' => 1]);

$query->where(['id >' => 1])->andWhere(['title' => 'My Title']);

// Equivalent to
$query->where(['id >' => 1, 'title' => 'My title']);

$query->where(['OR' => ['id >' => 1, 'title' => 'My title']]);

$query->where(function ($exp) {
        return $exp
            ->eq('author_id', 2)
            ->eq('published', true)
            ->notEq('spam', true)
            ->gt('view_count', 10);
    });

$query->where(function ($exp) {
        $orConditions = $exp->or(['author_id' => 2])
            ->eq('author_id', 5);
        return $exp
            ->not($orConditions)
            ->lte('view_count', 10);
    });

// Results in SELECT COUNT(*) count FROM ...
$query->select(['count' => $query->func()->count('*')]);

$concat = $query->func()->concat([
    'title' => 'literal',
    ' NEW'
]);
$query->select(['title' => $concat]);

// Iterate the query
foreach ($query as $row) {
    // Do stuff.
}

// Get the statement and fetch all results
$results = $query->execute()->fetchAll('assoc');

sql
UPDATE articles SET title = 'New Title' WHERE created >= '2014-10-10 00:00:00' AND created < '2014-10-13 00:00:00';

sql
SELECT * FROM articles
WHERE
	author_id = 2
	AND published = 1
	AND spam != 1
	AND view_count > 10

sql
SELECT *
FROM articles
WHERE
	NOT (author_id = 2 OR author_id = 5)
	AND view_count <= 10