PHP code example of mrjgreen / database

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

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

    

mrjgreen / database example snippets

$factory = new \Database\Connectors\ConnectionFactory();

$connection = $factory->make(array(
    'driver'    => 'mysql',
    'host'      => 'localhost',
    'username'  => 'root',
    'password'  => 'password',
    'charset'   => 'utf8',
    'collation' => 'utf8_unicode_ci',

    // Don't connect until we execute our first query
    'lazy'      => true,

    // Set PDO attributes after connection
    'options' => array(
        PDO::MYSQL_ATTR_LOCAL_INFILE    => true,
        PDO::ATTR_EMULATE_PREPARES      => true,
    )
));

$connection->query("SELECT id, username FROM customers");

$factory = new \Database\Connectors\ConnectionFactory();

$connection = $factory->make(array(
    'driver'    => 'mysql',
    'host'      => 'localhost',
    'username'  => 'root',
    'password'  => 'password',
    'charset'   => 'utf8',
    'collation' => 'utf8_unicode_ci',
));

$connection->fetchAll("SELECT id, username FROM customers");

$connection->table('customers')
	   ->find(12);

$connection->table('customers')
	   ->join('products', 'customer.id', '=', 'customer_id')
	   ->where('favourites', '=', 1)
	   ->get();

$connection = $factory->make(array(
    'driver'    => 'sqlite',
    'database' => '/path/to/sqlite.db',
));

PDO::ATTR_CASE              => PDO::CASE_NATURAL,
PDO::ATTR_ERRMODE           => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_ORACLE_NULLS      => PDO::NULL_NATURAL,
PDO::ATTR_STRINGIFY_FETCHES => false,
PDO::ATTR_EMULATE_PREPARES  => false,

$resolver = new Database\ConnectionResolver(array(
    'local' => array(
        'driver'    => 'mysql',
        'host'      => 'localhost',
        'username'  => 'root',
        'password'  => 'password',
        'charset'   => 'utf8',
        'collation' => 'utf8_unicode_ci',
    ),
    'archive' => array(
        'driver'    => 'mysql',
        'host'      => '1.2.3.456',
        'username'  => 'root',
        'password'  => 'password',
        'charset'   => 'utf8',
        'collation' => 'utf8_unicode_ci',
    ),
));

$dbLocal = $resolver->connection('local');

// Use it
$dbLocal->table('users')->get();


$dbArchive = $resolver->connection('archive');
// Etc...

$resolver->setDefaultConnection('local');

// Returns the `local` connection
$resolver->connection();

$statement = $connection->query('SELECT * FROM users WHERE name = ?', array('John Smith'));

// PDOStatement
$statement->rowCount();
$statement->fetchAll();

$firstRow = $connection->fetch('SELECT * FROM users WHERE name = ?', array('John Smith'));

$allRows = $connection->fetchAll('SELECT * FROM users WHERE name = ?', array('John Smith'));

$firstColumnFirstRow = $connection->fetchOne('SELECT COUNT(*) FROM users WHERE name = ?', array('John Smith'));

$rows = $connection->table('users')->query();

$rows = $connection->table('users')->get();

$row = $connection->table('users')->first();

$row = $connection->table('users')->find(6);

$connection->table('users')->find(3, array('user_id', 'name', 'email'), 'user_id');

$rows = $connection->table('users')->select('name')->addSelect('age', 'dob')->get();

$connection->table('users')->offset(100)->limit(10);

$connection->table('user')
    ->where('username', '=', 'jsmith')
    ->whereNotIn('age', array(10,20,30))
    ->orWhere('type', '=', 'admin')
    ->orWhereNot('name', 'LIKE', '%Smith%')
    ->get();

$connection->table('users')
            ->where('age', '>', 10)
            ->orWhere(function($subWhere)
                {
                    $subWhere
                        ->where('animal', '=', 'dog')
                        ->where('age', '>', 1)
                });

SELECT * FROM `users` WHERE `age` > 10 or (`age` > 1 and `animal` = 'dog')`.

$users = $connection->table('users')
                    ->orderBy('name', 'desc')
                    ->groupBy('count')
                    ->having('count', '>', 100)
                    ->get();

$connection->table('users')
    ->join('products', 'user_id', '=', 'users.id')
    ->get();
/*
    ->leftJoin()
    ->rightJoin()
*/

->join('products', function($table)
    {
        $table->on('users.id', '=', 'products.user_id');
        $table->on('products.price', '>', 'users.max_price');
    })

$query = $connection->table('users')
            ->selectSub(function($subQuery){
            	$subQuery
            	->from('customer')
            	->select('name')
            	->where('id', '=', $subQuery->raw('users.id'));
            }, 'tmp');

$count = $connection->table('users')->count();

$count = $connection->table('users')->min('age');

$count = $connection->table('users')->max('age');

$count = $connection->table('users')->avg('age');

$count = $connection->table('users')->sum('age');

$connection
	->table('users')
	->select('*')
	->where('bar', '=', 'baz')
	->intoOutfile('filename', function(\Database\Query\OutfileClause $out){
		$out
		->enclosedBy(".")
		->escapedBy("\\")
		->linesTerminatedBy("\n\r")
		->fieldsTerminatedBy(',');
	})->query();

$data = array(
    'username' = 'jsmith',
    'name' = 'John Smith'
);
$connection->table('users')->insert($data);
// Returns PDOStatement

`->insertGetId($data)` method returns the insert id instead of a PDOStatement

$data = array(
    'username' = 'jsmith',
    'name' = 'John Smith'
);
$connection->table('users')->insertIgnore($data);

$data = array(
    'username' = 'jsmith',
    'name' = 'John Smith'
);
$connection->table('users')->replace($data);

$data = array(
	array(
	    'username' = 'jsmith',
	    'name' = 'John Smith'
	),
	array(
	    'username' = 'jbloggs',
	    'name' = 'Joe Bloggs'
	),
);
$connection->table('users')->insert($data);

$data = array(
    'username' = 'jsmith',
    'name' = 'John Smith'
);

$now = $connection->raw('NOW()');

$connection->table('users')->insertUpdate(
    array('username' => 'jsmith', 'active' => $now), // Insert this data
    array('active' => $now)                          // Or partially update the row if it exists
);

//insertOnDuplicateKeyUpdate() is an alias of insertUpdate

$pdoStatement = $mainServer->table('users')->query(); // Returns a PDOStatement (which implements the `Traversable` interface)

// Will be inserted in batches of 1000 as it reads from the rowset iterator.
$backupServer->table('users')->buffer(1000)->insertIgnore($pdoStatement);

$data = array(
    'username' = 'jsmith123',
    'name' = 'John Smith'
);

$connection->table('users')->where('id', 123)->update($data);

$connection->table('users')->where('last_active', '>', 12)->delete();

$connection->table('users')
            ->select($connection->raw('DATE(activity_time) as activity_date'))
            ->where('user', '=', 123)
            ->get();

$query = $connection->table('users')->find(1)->toSql();
$query->toSql();
// SELECT * FROM users where `id` = ?

$query->getBindings();
// array(1)

$connection->getPdo();