PHP code example of neat / database

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

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

    

neat / database example snippets

// Connecting is easy, just pass a PDO instance
$pdo = new PDO('mysql:host=localhost;dbname=test', 'username', 'password');
$db  = new Neat\Database\Connection($pdo);

$db = new Neat\Database\Connection(new PDO('...'));

// Get all users as associative arrays in an indexed array
$users = $db->query('SELECT * FROM users')->rows();

// Get a single user row as an associative array
$user = $db->query('SELECT * FROM users WHERE id = ?', 31)->row();

// Get the id of a user
$id = $db->query('SELECT id FROM users WHERE username = ?', 'john')->value();

// Get an array of user names
$names = $db->query('SELECT username FROM users')->values();

$db = new Neat\Database\Connection(new PDO('...'));

// Fetching all rows before iterating over them can consume a lot of memory.
foreach ($db->query('SELECT * FROM users')->rows() as $row) {
    var_dump($row);
}

// By calling the row() method repeatedly until you hit false, you store only
// one row at a time in memory
$result = $db->query('SELECT * FROM users');
while ($row = $result->row()) {
    var_dump($row);
}

// The same can be achieved by looping over the Result directly using foreach
foreach ($db->query('SELECT * FROM users') as $row) {
    var_dump($row);
}

$db = new Neat\Database\Connection(new PDO('...'));

// Get the fetched result first
$result = $db->fetch('SELECT * FROM users');

$count = $result->count();
$users = $result->rows();

$db = new Neat\Database\Connection(new PDO('...'));

// Update a user (execute returns the number of rows affected)
$rows = $db->execute('UPDATE users SET login_at = ?', new DateTime('now'));

// Delete all inactive users
$rows = $db->execute('DELETE FROM users WHERE active = 0');

// Insert a user and get the auto_increment id value
$rows = $db->execute('INSERT INTO users (username, password) VALUES (?, ?)',
                     'john', password_hash('secret', PASSWORD_DEFAULT));

$db = new Neat\Database\Connection(new PDO('...'));

// Welcome John! We'll now turn you into a database record.
if ($db->insert('users', ['username' => 'john', 'password' => '...'])) {
    $id = $db->insertedId();
}

// Update John's last login
$time = new DateTime('now');
$rows = $db->update('users', ['login_at' => $time], ['username' => 'john']);

// Delete all users that haven't logged in for a year or more
$rows = $db->delete('users', 'login_at < DATE_SUB(NOW(), INTERVAL 1 YEAR)');

$db = new Neat\Database\Connection(new PDO('...'));

// Look mama, without SQL!
$users = $db->select()->from('users')->where('active = 1')->query()->rows();

// Or just get the SQL... this prints "SELECT * FROM users"
echo $db->select()->from('users');

// Complex select statements are just as easy to build
$db->select('g.*, COUNT(1) as active_users')
   ->from('users', 'u')
   ->leftJoin('users_groups', 'ug', 'u.id = ug.user_id')
   ->leftJoin('groups', 'g', 'g.id = ug.group_id')
   ->where('users.active = ?', 1)
   ->groupBy('g.id')
   ->having('COUNT(u.id) > 1')
   ->orderBy('g.name')
   ->limit(25)
   ->query()
   ->rows();
   
// Mixing the order of your calls can be useful too
$query = $db->select('u.*')
            ->from('users', 'u')
            ->where('active = 1');
if (isset($searchGroup)) {
    $query->join('users_groups', 'ug', 'u.id = ug.user_id')
          ->join('groups', 'g', 'g.id = ug.group_id')
          ->where('g.name LIKE ?', "%$searchGroup%");
}

$db = new Neat\Database\Connection(new PDO('...'));

// A simple insert query
$db->insert('users')
   ->values(['username' => 'john', 'password' => '...'])
   ->execute();

// Or an update query
$db->update('users')
   ->set(['login_at' => new DateTime('now')])
   ->where(['username' => 'john'])
   ->execute();

$db = new Neat\Database\Connection(new PDO('...'));

// First escape and quote the user input into an SQL safe string literal
$quoted = $db->quote('%' . $_GET['search'] . '%');
$sql = "SELECT * FROM users WHERE lastname LIKE $quoted OR firstname LIKE $quoted";

// It also understands DateTime value objects
$date = $db->quote(new DateTime('last monday'));
$sql = "SELECT * FORM users WHERE login_at > $date";

// And NULL values (be sure to use the appropriate SQL syntax, eg IS instead of =)
$null = $db->quote(null); // store 'NULL' into $null

// Identifiers can also be quoted
$table = $db->quoteIdentifier('users'); // store '`users`' (note the backticks) into $table 
$sql = "SELECT * FROM $table";

$db = new Neat\Database\Connection(new PDO('...'));

// When the email could not be sent, rollback the transaction
$db->transaction(function () use ($db)
{
    $db->execute('UPDATE users SET active = 0 WHERE username = ?', 'john');
    if (!mail('[email protected]', 'Confirmation', 'Account terminated')) {
        throw new \RuntimeException('E-mail failure, please rollback!');
    }
});