PHP code example of faslatam / sparrow

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

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

    

faslatam / sparrow example snippets

// Include the library
instance
$db = new Sparrow;

// Select a table
$db->from('user');

// Build a select query
$db->select();

// Display the SQL
echo $db->sql();

echo $db->from('user')->select()->sql();

echo $db->from('user')
  ->where('id', 123)
  ->select()
  ->sql();

echo $db->from('user')
  ->where('id', 123)
  ->where('name', 'bob')
  ->select()
  ->sql();

$where = array('id' => 123, 'name' => 'bob');

echo $db->from('user')
  ->where($where)
  ->select()
  ->sql();

echo $db->from('user')
  ->where('id = 99')
  ->select()
  ->sql();

echo $db->from('user')
  ->where('id >', 123)
  ->select()
  ->sql();

echo $db->from('user')
  ->where('id <', 10)
  ->where('|id >', 20)
  ->select()
  ->sql();

echo $db->from('user')
  ->where('name %', '%bob%')
  ->select()
  ->sql();

echo $db->from('user')
  ->where('name !%', '%bob%')
  ->select()
  ->sql();

echo $db->from('user')
  ->where('id @', array(10, 20, 30))
  ->select()
  ->sql();

echo $db->from('user')
  ->where('id !@', array(10, 20, 30))
  ->select()
  ->sql();

echo $db->from('user')
  ->select(array('id','name'))
  ->sql();

echo $db->from('user')
  ->limit(10)
  ->offset(20)
  ->select()
  ->sql();

echo $db->from('user')
  ->select('*', 50, 10)
  ->sql();

echo $db->from('user')
  ->distinct()
  ->select('name')
  ->sql();

echo $db->from('user')
  ->join('role', array('role.id' => 'user.id'))
  ->select()
  ->sql();

echo $db->from('user')
  ->join('role', array('role.id' => 'user.id', 'role.id >' => 10))
  ->select()
  ->sql();

echo $db->from('user')
  ->sortDesc('id')
  ->select()
  ->sql();

echo $db->from('user')
  ->sortAsc(array('rank','name'))
  ->select()
  ->sql();

echo $db->from('user')
  ->groupBy('points')
  ->select(array('id','count(*)'))
  ->sql();

$data = array('id' => 123, 'name' => 'bob');

echo $db->from('user')
  ->insert($data)
  ->sql();

$data = array('name' => 'bob', 'email' => '[email protected]');
$where = array('id' => 123);

echo $db->from('user')
  ->where($where)
  ->update($data)
  ->sql();

echo $db->from('user')
  ->where('id', 123)
  ->delete()
  ->sql();

$db->setDb('mysql://admin:hunter2@localhost/mydb');

$db->setDb(array(
  'type' => 'mysql',
  'hostname' => 'localhost',
  'database' => 'mydb',
  'username' => 'admin',
  'password' => 'hunter2'
));

$mysql = new mysqli('localhost', 'admin', 'hunter2');

$mysql->select_db('mydb');

$db->setDb($mysql);

$db->setDb('pdomysql://admin:hunter2@localhost/mydb');

$pdo = new PDO('mysql:host=localhost;dbname=mydb', 'admin', 'hunter2');

$db->setDb($pdo);

$rows = $db->from('user')
  ->where('id >', 100)
  ->many();

array(
  array('id' => 101, 'name' => 'joe'),
  array('id' => 102, 'name' => 'ted');
)

$row = $db->from('user')
  ->where('id', 123)
  ->one();

array('id' => 123, 'name' => 'bob')

$username = $db->from('user')
  ->where('id', 123)
  ->value('username');

$row = $db->from('user')
  ->where('id', 123)
  ->select(array('id', 'name'))
  ->one();

$db->from('user')
  ->where('id', 123)
  ->delete()
  ->execute();

$posts = $db->sql('SELECT * FROM posts')->many();

$user = $db->sql('SELECT * FROM user WHERE id = 123')->one();

$db->sql('UPDATE user SET name = 'bob' WHERE id = 1')->execute();

$name = "O'Dell";

printf("SELECT * FROM user WHERE name = %s", $db->quote($name));

// Last query executed
$db->last_query;

// Number of rows returned
$db->num_rows;

// Last insert id
$db->insert_id;

// Number of affected rows
$db->affected_rows;

$count = $db->from('user')->count();

$min = $db->from('employee')->min('salary');

$max = $db->from('employee')->max('salary');

$avg = $db->from('employee')->avg('salary');

$avg = $db->from('employee')->sum('salary');

$mysql = $db->getDb();

$mysql->info;

$db->setCache('memcache://localhost:11211');

$cache = new Memcache;
$cache->addServer('localhost', 11211);

$db->setCache($cache);

$key = 'all_users';

$users = $db->from('user')->many($key);

$db->setCache('apc');

$db->setCache('/usr/local/cache');

$db->setCache('./cache');

$key = 'top_users';
$expire = 600;

$users = $db->from('user')
  ->sortDesc('score')
  ->limit(100)
  ->many($key, $expire);

$memcache = $db->getCache();

echo $memcache->getVersion();

$db->store('id', 123);

$id = $db->fetch('id');

$db->clear('id');

$db->flush();

class User {
  // Class properties
  public $id;
  public $name;
  public $email;

  // Class configuration
  static $table = 'user';
  static $id_field = 'id';
  static $name_field = 'name';
}

$db->using('User');

$user = $db->find(123);

$user = $db->find('Bob');

$user = $db->find(
  array('email' => '[email protected]')
);

$user = new User();
$user->name = 'Bob';
$user->email = '[email protected]';

$db->save($user);

$user = new User();
$user->id = 123;
$user->name = 'Bob';
$user->email = '[email protected]';

$db->save($user);

// Fetch an object from the database
$user = $db->find(123);

// Update the object
$user->name = 'Fred';

// Update the database
$db->save($user);

$db->save($user, array('email'));

$user = $db->find(123);

$db->remove($user);

$db->using('User')
  ->where('id >', 10)
  ->sortAsc('name')
  ->find();

$db->using('User')
  ->sql('SELECT * FROM user WHERE id > 10')
  ->find();

$db->stats_enabled = true;

$stats = $db->getStats();

array(6) {
  ["queries"]=>
  array(2) {
    [0]=>
    array(4) {
      ["query"]=>
        string(38) "SELECT * FROM user WHERE uid=1"
      ["time"]=>
        float(0.00016617774963379)
      ["rows"]=>
        int(1)
      ["changes"]=>
        int(0)
    }
    [1]=>
    array(4) {
      ["query"]=>
        string(39) "SELECT * FROM user WHERE uid=10"
      ["time"]=>
        float(0.00026392936706543)
      ["rows"]=>
        int(0)
      ["changes"]=>
        int(0)
    }
  }
  ["total_time"]=>
    float(0.00043010711669922)
  ["num_queries"]=>
    int(2)
  ["num_rows"]=>
    int(2)
  ["num_changes"]=>
    int(0)
  ["avg_query_time"]=>
    float(0.00021505355834961)
}

$db->show_sql = true;

sql
SELECT * FROM user WHERE id=123 AND name='bob'

sql
SELECT * FROM user WHERE id>123;

sql
SELECT * FROM user WHERE id<10 OR id>20

sql
UPDATE user SET name='bob',email='[email protected]' WHERE id=123