PHP code example of gamernetwork / yolk-database

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

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

    

gamernetwork / yolk-database example snippets

use yolk\database\DSN;
use yolk\database\adapters\MySQLConnection;

// create a DSN
$dsn = DSN::fromString('mysql://localhost/mydb');

// create a connection instance
$db = new MySQLConnection($dsn);

// get some data
$user = $db->getAssoc("SELECT * FROM users WHERE user_id = ?", 123);

// update some data
$updated = $db->execute(
    "UPDATE users SET last_seen = :now WHERE id = :id",
    [
        'id'  => 123,
        'now' => date('Y-m-d H:i:s'),
    ]
);

$dsn = new DSN([
	'type' => 'mysql',
	'host' => 'localhost',
	'db'   => 'myapp',
]);

$dsn = DSN::fromString('mysql://root:[email protected]/myapp?charset=utf-8');

use yolk\database\ConnectionManager;
use yolk\database\adapters\SQLiteConnection;

// create a ConnectionManager instance
$m = new ConnectionManager();

// register a DSN
$m->add('mydb1', 'mysql://localhost/mydb');

// register an existing connection
$db = new SQLiteConnection('sqlite://var/www/myapp/myapp.db');
$m->add('mydb2', $db);

// determine if a connection with the specified name exists
$exists = $m->has('mydb1');

// retrieve a previously added connection
$db = $m->get('mydb1');

// remove a connection from the manager and return it
// NOTE: this does not disconnect the connection
$db = $m->remove('mydb1');

 // Execute a query and return the resulting PDO_Statement
$stmt = $db->query($statement, $params = []);

// Execute a query and return the number of affected rows
$rows = $db->execute($statement, $params = []);

// Execute a query and return all matching data as an array of associative arrays of matching rows
// Each row array has column names as keys
$db->getAll($statement, $params = []);

// Execute a query and return all matching data as an associative array,
// the first selected column is used as the array key
$db->getAssoc($statement, $params = []);

// Execute a query and return all matching data as a two-dimensioanl
// associative array, the first two selected columns are used as the array keys
$db->getAssocMulti($statement, $params = []);

// Execute a query and return the first matching row as an associative array
$db->getRow($statement, $params = []);

// Execute a query and return all values of the first selected column as an array
$db->getCol($statement, $params = []);

// Execute a query and return the value of the first column in the first array
$db->getOne($statement, $params = []);

$user_id = $db->getOne(
    "SELECT id FROM user WHERE type = :type AND name LIKE :name", 
    [
        'type' => 'NORMAL',
        'name' => 'Jim%',
    ]
);

$user_id = $db->getOne(
    "SELECT id FROM user WHERE type = ? AND name LIKE ?",
    ['NORMAL', 'Jim%']
);

$user_id = $db->getOne("SELECT id FROM user WHERE login = ?", 'jimbob');

// Returns the ID of the last inserted row or sequence value.
$id = $db->insertId($name = '');

// Escape/quote a value for use in a query string
$db->quote($value, $type = \PDO::PARAM_STR);

// Escape/quote an identifier name (table, column, etc)
// Allows reserved words to be used as identifiers.
$db->quoteIdentifier('key');

// Execute a raw SQL string and return the number of affected rows.
// Primarily used for DDL queries
$db->rawExec($sql);

// Begin a transaction
$db->begin();

// Commit the current transaction
$db->commit();

// Rollback the current transaction
$db->rollback();

// Determines if a transaction is currently active
$db->inTransaction();

$db->select()

   // accepts true (default) or false as argument
   ->distinct()

   // comma-separated list or array of column names
   ->cols('*')

   // table to select from
   ->from('table')

   // append a where clause - column, operator, value
   // multiple calls add additional clauses
   ->where('created', '>=', '2016-01-01')		

   // with two arguments, operator is assumed to be '='
   ->where('id', 123)

   // array of columns to group by
   ->groupBy(['type', 'status'])

   // second parameter specifies ascending (true) or descending (false)
   // multiple calls add additional clauses
   ->orderBy('column', true)

   // return result as associative array
   // can also use the other fetch* methods defined by DatabaseConnection
   ->fetchAssoc();

$db->insert()

   // accepts true (default) or false as argument
   ->ignore()

   // table to insert to
   ->into('table')

   // item to insert as an associative array of column names/values
   ->item([
       'col1' => 'value1',
       'col2' => 'value1',
   ])

   // run the query
   ->execute();

$db->insert()

   // accepts true (default) or false as argument
   ->ignore()

   // table to insert to
   ->into('table')

   // columns to update as an associative array of column names/values
   ->set([
       'col1' => 'value1',
       'col2' => 'value1',
   ])

   // same usage as for SELECT
   ->where('id', 123)

   // run the query
   ->execute();

$db->delete()

   // table to insert to
   ->from('table')

   // same usage as for SELECT
   ->where('id', 123)

   // run the query
   ->execute();