SimplePDOClass

Database Credentials

Open config.php and enter credentials

Calling the Dbconnect class

$db = new Dbconnect();

Select

$db->Select[$table](  );

$table must be capitalized. Example you have a table named users, you should do $db->SelectUsers( ).

Select with conditions

$conditions = array(
  'id' => 1,
  'created >' => '2016-04-25'
);
$db->SelectUsers( $conditions );

Above will produce the query string

Select * from users where id = '1' and created > '2016-04-25'

Update

$conditions = array(
  'id' => 1,
  'status' => 2
);
$db->UpdateUsers( $conditions );

Above will produce the query string

update users set status = '2' where id = '1'

Update[$table]() always need an id to work

Update where

$columns = array(
  'name' => 'John Doe',
  'email' => '[email protected]'
);
$conditions = array(
  'id' => 1,
  'status !=' => 2
);
$db->UpdatewhereUsers( $columns , $conditions );

Above will produce the query string

update users set name = 'John Doe', email = '[email protected]' where id = '1' and status != 2

Delete

  $conditions = array(
    'id' => 1,
    'status' => 2
  );
  $db->DeleteUsers( $conditions );

Above will produce the query string

Delete from users where status = '2' and id = '1'

Insert

$columns = array(
  'status' => 1,
  'name' => 'John Doe',
  'email' => '[email protected]',
  'created' => '2016-04-25'
);
$db->InsertUsers( $columns );

Above will produce the query string

Insert into users (status,name,email,created) values ('1','John Doe','[email protected]','2016-04-25')

$db->InsertUsers( $columns ); will return the last inserted id

Join

$db
->joins(array(
  'type' => 'left',
  'table' => 'images',
  'on' => 'images.user_id = users.id'
))
->SelectUsers(  );

Above will produce the query string

Select * from users left join images on images.user_id = users.id

Default type is left so no need to add it in the options. You can also add an alias to the joins option like

$db
->joins(array(
  'type' => 'right',
  'table' => 'images',
  'as' => 'im',
  'on' => 'im.user_id = users.id'
))
->SelectUsers( array(
  'im.type' => 3
) );

Above will produce the query string

Select * from users right join images as im on im.user_id = users.id where im.type = '3'

Select with specific columns

$db
->columns(array(
  'users.*',
  'images.url',
  'images.type'
))
->joins(array(
  'type' => 'left',
  'table' => 'images',
  'on' => 'images.user_id = users.id'
))
->SelectUsers(  );

Above will produce the query string

Select users.*, images.url, images.type from users left join images on images.user_id = users.id

Group

$db
->group('images.type')
->columns(array(
  'users.*',
  'images.url',
  'images.type'
))
->joins(array(
  'type' => 'left',
  'table' => 'images',
  'on' => 'images.user_id = users.id'
))
->SelectUsers(  );

Above will produce the query string

Select users.*, images.url, images.type from users left join images on images.user_id = users.id group by images.type

Order

$db
->order('images.created desc')
->group('images.type')
->columns(array(
  'users.*',
  'images.url',
  'images.type'
))
->joins(array(
  'type' => 'left',
  'table' => 'images',
'on' => 'images.user_id = users.id'
))
->SelectUsers(  );

Above will produce the query string

Select users.*, images.url, images.type from users left join images on images.user_id = users.id order by images.created group by images.type

Limit

$db
->limit(5)
->order('images.created desc')
->group('images.type')
->columns(array(
  'users.*',
  'images.url',
  'images.type'
))
->joins(array(
  'type' => 'left',
  'table' => 'images',
  'on' => 'images.user_id = users.id'
))
->SelectUsers();

Above will produce the query string

Select users.*, images.url, images.type from users left join images on images.user_id = users.id order by images.created group by images.type limit 5

Paging

$page = 1;  // will get page 1

$db
->limit( 5 , $page )
->order('images.created desc')
->group('images.type')
->columns(array(
  'users.*',
  'images.url',
  'images.type'
))
->joins(array(
  'type' => 'left',
  'table' => 'images',
  'on' => 'images.user_id = users.id'
))
->SelectUsers(  );

You can get the pagination information after doing like this

$db->$pgntion

Or condition

$conditions = array(
'type' => 2,
'or' => array(
    'name like' => '%John%',
    'email like' => '%[email protected]%',
    'id in' => '(1,3,5)'
  )
);
$db->SelectUsers( $conditions );

Above will produce the query string

Select * from users where type = '2' and (name like '%John%' or email like '%[email protected]%' or id in (1,3,5))

Side note

Some cases are not considered thats why you can use the normal prepared statements

Insert

$query = "Insert into users ( name , email , type ) values ( :name , :email , :type )";
$data = array(
  'name' => 'John Doe',
  'email' => '[email protected]',
  'type' => 2
);
$db->insertRow( $query , $data );

Select

$query = "Select * users where id = :id";
$data = array(
  'id' => 1
);
$db->getRow( $query , $data ); // for single result
$db->getRows( $query , $data ); // for multiple results

Update

$query = "Update users set name = :name where type = :type";
$data = array(
  'name' => 'John Doe',
  'type' => 2
);
$db->updateRow( $query , $data );

Delete

$query = "Delete from users where type = :type";
$data = array(
  'type' => 2
);
$db->deleteRow( $query , $data );