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 );

emmandev / simplepdoclass Goto Github PK

simplepdoclass's Introduction

SimplePDOClass

Database Credentials

Calling the Dbconnect class

Select

Select with conditions

Update

Update where

Delete

Insert

Join

Select with specific columns

Group

Order

Limit

Paging

Or condition

Side note

Insert

Select

Update

Delete

simplepdoclass's People

Contributors

Watchers

Recommend Projects

Recommend Topics

Recommend Org