- Notifications
You must be signed in to change notification settings - Fork1
A php class for using pdo class for your database queries
License
eosobande/pdo-database-class
Folders and files
| Name | Name | Last commit message | Last commit date | |
|---|---|---|---|---|
Repository files navigation
pdodbDb -- Simple PDO wrapper with prepared statements
Initialization
Insert Query
Update Query
Select Query
Delete Query
Running raw SQL queries
Where Conditions
Joining Tables
Subqueries
Prepared Queries
To utilize this class, first import sql_builder.php into your project, and require it then import pdodb.php into your project, and require it.
require_once ('sql_builder.php');require_once ('pdodb.php');
Simple initialization: make sure the following constants are defined DB_SERVER,DB_NAME,DB_USER,DB_PASS, the initialization is automated with variable name $db
$db =newPDOdb(DB_SERVER,DB_NAME,DB_USER,DB_PASS);
SQLITE initialization: provide the filepath to your sqlite db file as the first param only, the file does not have to exist to be passed
$db =newPDOdb('mysqlitedb');
Also it is possible to reuse already connected pdodb object:
$pdodb =newpdodb ('host','username','password','databaseName');$db =newpdodbDb ($pdodb);
If you need to get already created pdodb object from another class or function use
Class myclass {use PDOdbRef;function__construct() {$this->__db_instance_ref();// public attribute $this->db has be created } }...functionmyfunc () {global$db;// global $db var imported to local }
Simple example
$db->insert('users', [$username,$email],'username,email');if ($db->execute())echo'user was created. Id=' .$db->last_insert_id();elseecho'insert failed:' .$db->error_info();
Insert with ignore
$values = [$username,$email];$cols ='username,email';$db->insert('users',$values,$cols,true);$db->execute();
$data = ['username'=>'emmanuel','email'=>'eosobande@gmail.com' ];$db->where(['id',1]);OR$db->where('id=1');$db->update ('users',$data);if ($db->execute())echo$db->row_count() .' records were updated';elseecho'update failed:' .$db->error_info();
update() also support limit parameter:
$db->update('users',$data,5);// Gives: UPDATE users SET ... LIMIT 5
After any select function calls amount or returned rows is stored in $count variable
$db->select('users');$users =$db->fetch();// contains an Array of all users$users =$db->select('users',10);//contains an Array 10 users
or select with custom columns set. Functions also could be used
$cols ="id,name,email";$db->select("users",$cols);$users =$db->fetch();
or select just one row
$db->where (["id",1]);$db->select('users');$user =$db->fetch(PDOdb::_FETCH_ONE);echo$user['id'];
or select one column value or function result
$db->select("users","count(*)");$count =$db->fetch(PDOdb::_FETCH_ONE_FIELD);echo"{$count} users found";
or select one column with direct array acces:
$db->select("users","username");$usernames =$db->fetch(PDOdb::_FETCH_FIRST_FROM_EACH_ROW);foreach ($usernamesas$username) {echo$username.'<br>';}
or select with keywords:
$keywords = ['limit'=>5,'order_by'=>'username','offset'=>10,'group_by'=>'','having'=>'']$db->select("users","login",$keywords);$users =$db->fetch();
or select distinct
$db->select("users","login",null,true);$users =$db->fetch();
$db->raw_query('SELECT * from users');$users =$db->fetch();
each $db->where() paramenter must be either an array or a string
Regular = operator with variables:
$db->where (['id',1]);$db->select ('users');// Gives: SELECT * FROM users WHERE id=1 AND login='admin';
$db->where (['id',1], ['username','emmanuel','=']);$results =$db->select ('users');// Gives: SELECT * FROM users WHERE id=1 AND username='emmanuel';
$db->where ('id=1', ['username','emmanuel','=','or']);$results =$db->select ('users');// Gives: SELECT * FROM users WHERE id=1 OR username='emmanuel';
BETWEEN / NOT BETWEEN:
$db->where (['id', [4,20],'between']);$db->select('users');$db->execute();// Gives: SELECT * FROM users WHERE id BETWEEN 4 AND 20
IN / NOT IN:
$db->where (['id', [4,1,2,3,620],'in']);$db->select('users');$db->execute();// Gives: SELECT * FROM users WHERE id IN (1, 5, 27, -1, 'd');
Also you can use raw where conditions:
$db->where ("DATE(created) = DATE(lastLogin)");$db->select("users");$db->execute();
$db->where(['id',1]);$db->delete('users',1);if($db->execute())echo'successfully deleted';
Join table products with table users with LEFT JOIN by user_id
$tables = [ ['products','as'='p'], ['users','as=>'u','join'=>'left','on'=>'p.user_id=u.user_id']];$db->select($tables);$products =$db->fetch();print_r ($products);
$sq =$db->sub_query();$sq->select("users",'user_id');$db->where(['user_id',$sq,'in']);$db->select('products');var_dump($db->fetch());
$db->select('users');$users =$db->fetch();$db->where('user_id=?');$db->select('products','COUNT(*)');$db->prepare();foreach ($usersas$key =>$user) {$db->execute([$user['user_id']],TRUE);$users[$key]['product_count'] =$db->fetch(PDOdb::_FETCH_ONE_FIELD,FALSE); }print_r($users);