Linear Mode Tutor Query Builder for Mysql extension

Tujuan dari pembuatan program ini adalah untuk memudahkan dalam membuat query

Penggunaan

Code:

require_once('Mysql.php');

set DB

Code:

$config['host'] = 'host';
$config['user'] = 'user';
$config['passwd'] = 'passwd';
$config['dbname'] = 'dbname';
$config['prefix'] = 'prefix_';
$db = new QB_Mysql($config);

General Rules for the SELECT Statement

Code:

SELECT
[ALL | DISTINCT | DISTINCTROW ]
  [HIGH_PRIORITY]
  [MAX_STATEMENT_TIME = N]
  [STRAIGHT_JOIN]
  [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
  [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
select_expr [, select_expr ...]
[FROM table_references
  [PARTITION partition_list]
[WHERE where_condition]
[GROUP BY {col_name | expr | position}
  [ASC | DESC], ... [WITH ROLLUP]]
[HAVING where_condition]
[ORDER BY {col_name | expr | position}
  [ASC | DESC], ...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
[PROCEDURE procedure_name(argument_list)]
[INTO OUTFILE 'file_name'
    [CHARACTER SET charset_name]
    export_options
  | INTO DUMPFILE 'file_name'
  | INTO var_name [, var_name]]

Insert Query

Code:

$data = array("username" => "admin",
      "password" => md5('admin123')
      );
$stmt = $db->table('users')
        ->insert($data)
        ->get();
if($stmt !== FALSE)
{
    echo 'user was created';
}
//INSERT INTO users (username, password) VALUES (“admin”, “0192023a7bbd73250516f069df18b500”);

Update Query
Update untuk semua baris

Code:

$data = array('username'=>'webmin');
$stmt = $db->table('users')
        ->update($data)
        ->get();
//UPDATE SET users username = “webmin”;

Untuk baris tertentu

Code:

$data = array('username'=>'webmin');
$stmt = $db->table('users')
        ->update($data)
        ->where('id', '=', 1)
        ->get();
//UPDATE SET users username = “webmin” WHERE id = 1;

Select Query
mengeluarkan semua kolom dan baris

Code:

$stmt = $db->table('users')
        ->select()
        ->get();
//SELECT * FROM users;
print_r($stmt->fetchAll());

mengeluarkan 1 kolom dan 1 baris

Code:

$stmt = $db->table('users')
        ->select('username')
        ->where('id', '=', 1)
        ->get();
//SELECT username WHERE id = 1;

mengeluarkan 2 kolom dan 1 baris

Code:

$stmt = $db->table('users')
        ->select(['username', 'password'])
        ->where('id', '=', 1)
        ->get();
//SELECT username, password WHERE id = 1;

Menjalankan Query

Code:

$stmt = $db->query("SELECT * FROM users);

Dengan prepared statement

Code:

$stmt = $db->query("SELECT * FROM users WHERE id = ?", array(1));

Penggunaan Where

Code:

Note : di parameter kedua bisa digantikan dengan  >, <, <=, >=, !=
$stmt = $db->table('users')
        ->select()
        ->where('id', '=', 1)
        ->run();
//SELECT * FROM users WHERE id = 1;

OR WHERE

Code:

$stmt = $db->table('users')
        ->where('id', '=', 1)
        ->where('id', '=', 2, 'OR')
        ->run();
//SELECT * FROM users WHERE id = 1 OR id = 2;

BETWEEN / NOT BETWEEN
Note : untuk parameter ke 3 maksimal nilai dari arraynya adalah 2

Code:

$stmt = $db->table('users')
        ->select()
        ->where('id', 'BETWEEN', array(1,10))
        ->run();
//SELECT * FROM users WHERE id BETWENN 1 AND 10;

NOT BETWEEN

Code:

$stmt = $db->table('users')
        ->select()
        ->where('id', 'NOT BETWEEN', array(3,6))
        ->run();
//SELECT * FROM users WHERE id NOT BETWENN 3 AND 6;

IN / NOT IN
Note : nilai dari parameter ke 3 harus sebuah array(sama seperti BETWEEN/NOT BETWEEN)

Code:

$stmt = $db->table('users')
        ->select(['username', 'password'])
        -->where('id, 'IN', array(1,3,5,6))
        ->run();
//SELECT username, password WHERE id IN (1, 3, 5, 6);

NOT IN

Code:

$stmt = $db->table('users')
        ->select(['username', 'password'])
        ->where('id, 'NOT IN', array(8, 9, 13))
        ->run();
//SELECT username, password WHERE id NOT IN (8, 9, 13);

Having Method
Sama seperti WHERE METHOD, hanya saja tidak ada BETWEEN/NOT BETWEEN, IN/ NOT IN

Code:

$stmt = $db->table('info')
->select(['ordernumber', 'SUM(quantityordered) AS itemsCount', 'SUM(priceeach) AS total'])
            ->groupBy('ordernumber')
            ->having('total', '>', 300)
            ->having('itemsCount', '>', 12)
            ->run();
//SELECT ordernumber, SUM(quantityOrdered) AS itemsCount, SUM(priceeach) AS total FROM info GROUP BY ordernumber HAVING total > 300;

Delete Query
Cukuplah berhati-hati dalam penggunaannya

Untuk menghapus semua baris

Code:

$stmt = $db->table('users')
        ->delete()
        ->run();
//DELETE FROM users;

Menghapus 1 baris

Code:

$stmt = $db->table('users')
        ->delete()
        ->where('id', '=', 1)
        ->run();
//DELETE FROM users WHERE id = 1;

Ordering Method
note : nilai bawaan dari parameter ketiga adalah ASC, jika ingin mengurutkan dengan DESC berikan nilai DESC di parameter ketiga

Code:

$stmt = $db->table('pet_info')
        ->select(['name', 'owner'])
        ->orderBy('name')
        ->run();
//SELECT name, owner FROM pet_info ORDER BY name;

Dengan DESC

Code:

$stmt = $db->table('pet_info')
        ->select(['name', 'owner'])
        ->orderBy('name', null, 'DESC')
        ->run();
//SELECT name, owner FROM pet_info ORDER BY name DESC;

Mengurutkan dengan nilai(Ordering by the values)

Code:

$stmt = $db->table('pet_info')
            ->select(['name', 'owner'])
            ->orderBy('name', array('simpay', 'momo', 'jimbo', 'appa','cimot'))
            ->run();
//SELECT name, owner ORDER BY FIELD(name, 'simpay', 'momo', 'jimbo', 'appa','cimot');

Group By

Code:

$stmt = $db->table('pet_info')
            ->select(['gender', 'count(gender) AS total')
            ->groupBy('gender')
            ->run();

//SELECT gender, count(gender) AS total FROM pet_info GROUP BY gender;

Subquery
Note : diawali dengan subQuery() dan diakhiri dengan get()

Code:

$id = $db->subQuery()
        ->table('users')
        ->select('id')
        ->where('id', '=', 8)
        ->get();

$stmt = $db->table('users')
            ->select()
            ->where('id', '=', $id)
            ->run();

//SELECT * FROM users WHERE id = (SELECT id FROM users WHERE id = 8);