SQL

The SQL module expose a shorthand for common database methods extending the PDO layer.

Bind to database

---

You can bind the SQL module to a database with a DSN (Data Source Name) string via the connect method. Connection is lazy-loaded at the first database access.

SQL::connect('mysql:host=localhost;dbname=test','root','password');

Note: SQL::connect comes already connected to an in-memory ephemeral database (SQLite3)

The event core.sql.connect si fired upon database connection.

Event::on('core.sql.connect',function($sql){
  $sql->exec('SET NAMES "UTF8"');
});

You can register several data sources via the register method.

SQL::register('production','mysql:host=database.mysite.com;dbname=production','www','******');
$localDB = SQL::register('local','mysql:host=localhost;dbname=development','root','');

You can now use the returned resource for executing SQL methods or access the wanted datasource via the using accessor.

$localDB->insert('users',[
  'email' => '[email protected]',
  'password' => 'kek',
]);

echo SQL::using('local')->value("SELECT password FROM users WHERE email=?",['[email protected]']);

kek

Normally the SQL::* methods are binded to the default connection that is registered on the default datasource.
If you want to change it you can with the defaultTo method :

// Setting the default datasource, this is the same as we used `SQL::register('default',...`
SQL::connect('mysql:host=database.mysite.com;dbname=production','www','******');

// Setting the `local` datasource
SQL::register('local','mysql:host=localhost;dbname=development','root','');

// Now we are using the `default` datasource as the default one
$users_a = SQL::each("SELECT * FROM users");

SQL::defaultTo("local");

// Now we are using the `local` datasource as the default one
$users_a = SQL::each("SELECT * FROM users");

Execute a SQL statement

---

You can execute a SQL statement with the exec method. The query will be prepared and you can pass optional binding parameters as last function argument.

SQL::exec('TRUNCATE TABLE `users`');

SQL::exec('DELETE FROM `users` WHERE `age` < 16');

Retrieve a single value

---

The value method executes the query, with the optional parameters and returns the first column of the first row of the results.

$total_users = SQL::value('SELECT COUNT(1) FROM `users`');

$user_is_registered = !!SQL::value('SELECT 1 FROM `users` WHERE username = :usr_name',[
  'usr_name' => $username
]);

Retrieve a single row

---

The single method executes the query, with the optional parameters and runs the passed callback with the current row object.

SQL::single('SELECT username, points FROM `rankings` LIMIT 1',function($rank){
  echo 'The Winner is : ',$rank->username,' with ',$rank->points,' points!';
});

$rank = SQL::single('SELECT username, points FROM `rankings` LIMIT 1',function($rank){
  $rank->username = strtoupper($rank->username);
  return $rank;
});

Retrieve an entire column

---

SQL::column($query, $params=[], $column_idx=0)

The column method executes the query, with the optional parameters and a third parameter which is the numeric 0-based index of the column or its label and returns a filtered array of values.

$emails = SQL::column('SELECT name, email FROM `users`', [], 1);

// or

$emails = SQL::column('SELECT name, email FROM `users`', [], 'email');

[email protected]
[email protected]

Retrieve rows

---

The each method executes the query, with the optional parameters and runs the passed callback with the current row object for every row of the results.

SQL::each('SELECT * FROM `users`',function($user){
  echo '<li><a href="mailto:', $user->email ,'">', $user->name ,'</a></li>';
});

Reduce rows

---

The reduce method works like array_reduce function with the query performed.

$users = SQL::reduce('SELECT id,group FROM `users`',function($results, $row){
  $results[$row->group][] = $row->id;
  return $results;
}, []);

Retrieve all results

---

The all method is used to retrieve all results in a single call.

echo json_encode( SQL::all('SELECT `name` , `email` FROM `users`') );

Insert a new row

---

The insert method is used to insert into a defined table a new row, passed as an associative array.

$inserted_item_id = SQL::insert('users',[
  'name'     => 'Stannis Baratheon',
  'password' => 'im_the_one_true_king',
]);

Update a single row

---

The update method is used to change a single row data, passed as an associative array.

SQL::update('users',[
  'id'       => 321,
  'name'     => 'King Stannis Baratheon',
]);

You can also override the name of the primary key column as the third function parameter, default is id

SQL::update('users',[
  'email'    => '[email protected]',
  'name'     => 'King Stannis Baratheon',
],'email');

Delete a single row

---

The delete method is used to remove a single row data.

SQL::delete( 'users', [ 321, 432 ] );

You can also override the name of the primary key column as the third function parameter, default is id

SQL::delete( 'users', [ '[email protected]', '[email protected]' ], 'email' );

Debug queries

---

You can bind a function to the core.sql.query event for listening every executed query.

Event::on('core.sql.query',function($query,$params,$statement){
  echo "SQL Query  : $query \n";
  echo "Parameters : ", print_r($params,true), "\n";
  echo "Success    : ", ($statement?'Yes':'No'), "\n";
});