turkmvc / php-mysqli-database-class

Wrapper for a PHP MySQL class, which utilizes MySQLi and prepared statements.

To utilize this class, first import Mysqldbi.php into your project, and require it.

require_once('Mysqlidb.php');

After that, create a new instance of the class.

$db = new Mysqlidb('host', 'username', 'password', 'databaseName');

Next, prepare your data, and call the necessary methods.

Insert Query

$data = array(
	'login' => 'admin',
	'firstName' => 'John',
	'lastName' => 'Doe',
);

$id = $db->insert('users', $data)
if($id)
    echo 'user was created. Id='.$id;

Select Query

$users = $db->get('users'); //contains an array of all users 
$users = $db->get('users', 10); //contains an array 10 users

or select with custom columns set. Functions also could be used

$stats = $db->getOne ("users", null, "sum(id), count(*) as cnt");
echo "total ".$stats['cnt']. "users found";

$cols = Array ("id, name, email");
$users = $db->get ("users", null, $cols);
foreach ($users as $user) { 
    print_r ($user);
}

or select just one row

$db->where ("id", 1);
$user = $db->getOne ("users");
echo $user['id'];

Update Query

$data = array (
	'firstName' => 'Bobby',
	'lastName' => 'Tables'
);
$db->where('id', 1);
if($db->update('users', $data)) echo 'successfully updated'; 

Delete Query

$db->where('id', 1);
if($db->delete('posts')) echo 'successfully deleted'; 

Generic Query Method

$users = $db->rawQuery('SELECT * from users');
foreach ($users as $user) {
    print_r ($user);
}

Raw Query Method

$params = array(1, 'admin');
$users = $db->rawQuery("SELECT id, firstName, lastName FROM users WHERE id = ? AND login = ?", $params);
print_r($users); // contains array of returned rows

// will handle any SQL query
$params = array(10, 1, 10, 11, 2, 10);
$resutls = $db->rawQuery("(SELECT a FROM t1 WHERE a = ? AND B = ? ORDER BY a LIMIT ?) UNION(SELECT a FROM t2 WHERE a = ? AND B = ? ORDER BY a LIMIT ?)", $params);
print_r($results); // contains array of returned rows

Where Method

This method allows you to specify the parameters of the query.

Regular == operator:

$db->where('id', 1);
$db->where('login', 'admin');
$results = $db->get('users');
// Gives: SELECT * FROM users WHERE id=1 AND login='admin';

Custom Operators:

$db->where('id', array('>=' => 50));
$results = $db->get('users');
// Gives: SELECT * FROM users WHERE id >= 50;

BETWEEN:

$db->where('id', array('between' => array(4, 20) ) );
$results = $db->get('users');
// Gives: SELECT * FROM users WHERE id BETWEEN 4 AND 20

IN:

$db->where('id', array( 'in' => array(1, 5, 27, -1, 'd') ) );
$results = $db->get('users');
// Gives: SELECT * FROM users WHERE id IN (1, 5, 27, -1, 'd');

Optionally you can use method chaining to call where multiple times without referencing your object over an over:

$results = $db
	->where('id', 1)
	->where('title', 'MyTitle')
	->get('users');

Ordering method

$db->orderBy("id","asc");
$db->orderBy("login","Desc");
$results = $db->get('users');
// Gives: SELECT * FROM users ORDER BY id ASC,login DESC;

Grouping method

$db->groupBy("name");
$results = $db->get('users');
// Gives: SELECT * FROM users GROUP BY name;

Join table products with table users with LEFT JOIN by tenantID

JOIN method

$db->join("users u", "p.tenantID=u.tenantID", "LEFT");
$db->where("u.id", 6);
$products = $db->get ("products p", "u.name, p.productName");
print_r ($products);