dimasdwib / siquery

siQuery is a simple SQL builder based on PDO for PHP

siquery

siQuery is a simple SQL builder based on PDO for PHP

Usage

Include siquery class;

require_once 'src/siquery.php';

Select Statement

$db = new siquery();
$users = $db->from('users')
            ->orderBy('id DESC')
            ->get();

Executed Query :

SELECT *
FROM users
ORDER BY id DESC

Another example

$users = $db->select('username, email')
            ->from('users')
            ->orderBy('id DESC')
            ->get();

Executed Query :

SELECT username, email
FROM users
ORDER BY id DESC

Join Statement

$users = $db->from('users')
          ->innerJoin('posts', 'user_id')
          ->where('user_id',1)
          ->get();

Executed Query :

SELECT * 
FROM users
INNER JOIN posts
USING(user_id)
WHERE user_id = 1

Select first row

$users = $db->from('users')
          ->where('user_id', 1)
          ->first();

Select one column

$username = $db->from('users')
          ->where('user_id', 1)
          ->fetch('username');

Support paginate

$users = $db->from('users')
          ->paginate(20);

HTML for pagination will generate by function $db->getPagenav(); , so you can use it for pagination link in your view

Search

$books = $db->from('books')
          ->search('title, author')
          ->get();

It will use $_GET['search'] variable by default to get a keyword for search, you can set search variable like this

$db = new siquery();
$db->searchVar = 'MySearchVariable';

##Data Manipulation Languange ###Insert

$insert = $db->insert('users', ['username'=>'John', 'email'=>'[email protected]'])
          ->execute();

###Update

$update = $db->update('users', ['username'=>'Dale', 'email'=>'[email protected]'])
          ->where('user_id', 2)
          ->execute();

###Delete

$delete = $db->delete('users')
          ->where('user_id', 2)
          ->execute();

or

$delete = $db->delete('users', 'user_id = 2')
          ->execute()

Transaction

###Example :

$db = new siquery();

$db->TRANSACTION(); //Begin Transaction

  $insert = $db->insert('users', ['username' => 'John'])
            ->execute();
  
  $lastId = $db->lastId();
  
  $update = $db->update('books', ['user_id' => $lastId])
              ->where('books_id', 4)
              ->execute();

if(empty($db->getError())){
    
    $db->COMMIT(); //Commit transaction

} else {

    $db->ROLLBACK(); //Rollback transaction

}