Download the PHP package benclerc/datamanagement without Composer

Data Management

PHP Class providing simple and compact database requests. Useful for small projects without ORM. With this class you can :

• Select, insert, update, delete data
• Count rows
• Sum rows
• Use SQL transaction
• Execute custom SQL request

This class was not tested on PHP version < 7.3, thus it is not recommended to use this class on < 7.3 PHP projects.

Table of contents

• Getting started
• Documentation
  • connector()
  • beginTransaction()
  • commit()
  • rollback()
  • debug()
  • select()
  • customSelect()
  • insert()
  • customInsert()
  • update()
  • delete()
  • count()
  • sum()
  • customSQL()

Getting started

1. Get Composer
2. Install the library using composer composer require benclerc/datamanagement.
3. Add the following to your application's main PHP file require 'vendor/autoload.php';.
4. Instanciate the class with the database's connection information $db = \DataManagement\DataManagement('pgsql', 'localhost', 5432, 'myDb', 'myUser', 'myUserPassword');.
5. Start using the library $books = $db->select('BOOKS');.

Documentation

You can find a full documentation here.

connector()

This method returns the PDO object connected to the database.

beginTransaction()

This method starts a SQL transaction. Every call to other methods following this one will be in the transaction until you end it with commit() or rollback().

commit()

This method ends a SQL transaction by applying the changes. Be careful, this method won't return the state of the commit ; even if it returns TRUE it does not mean the commit was successful, it means the commit was successfully sent to the database. You must check the state of every request you do during the transaction if you want to know if your transaction was successful.

rollback()

This method ends a SQL transaction by rolling back the changes. Nothing done during the transaction will be applied.

debug()

This method is used to enable debug mode for the next request (only works on methods forging SQL request like select(), insert(), update(), delete(), count(), sum()). instead of executing the forged request, it will be returned as a string.

Parameters :

• $state bool : Set the value for debug state, default is TRUE.

Return value : itself.

Examples :

select()

This method is used to retrieve data from the database. It can be a very simple request like getting a whole table or a more complex request with ordering, table joins, filters, limits and offsets.

Parameters :

• $table string : Table name.
• $order array (optional) : Array of column name and wanted order e.g. ['column' => 'ASC/DESC']. If no value is passed then default value is used : 'ASC'.
• $join array (optional) : Array with wanted join table name as key and array of needed values as values e.g. ['table' => [type(inner, left, right ...), 'foreignkey', 'primarykey', /*from table*\]]. From table argument is optionnal, if not set $table will be used instead.
• $where array (optional) : Array with table name as key and array as value with column name and filter value e.g. ['table'=>['columnname'=>'data']]. 'data' has reserved values for nulls and booleans : 'NULL', '!NULL' 'TRUE', 'FALSE'. 'data' can also be an array of values.
• $limit int (optional) : Number of max rows e.g. 50.
• $offset int (optional) : Offset for returned rows e.g. 100.
• $columns array (optional) : Array of column name.

Return value : If debug set to TRUE : return forged SQL request, else returns the fetchAll results.

Examples :

customSelect()

This method is used to retrieve data from the database using a custom SQL request.

Parameters :

• $sql string : SQL request.
• $data array (optional) : Array of data e.g. ['columnname'=>'data'] or if you use ? in the request : ['data1', 'data2'].

Return value : Array of the fetchAll results.

Examples :

insert()

This method is used to insert data in the database. It is highly recommended to use transaction when inserting data.

Parameters :

• $table string : Table name.
• $data array : Array of data e.g. ['columnname'=>'data'].

Return value : If debug set to TRUE : return forged SQL request, else returns array with 2 rows : 'raw' => the database's raw response, 'lastInsertId' => the last insert id.

Examples :

customInsert()

This method is used to insert data in the database using a custom SQL request.

Parameters :

• $sql string : SQL request.
• $data array (optional) : Array of data e.g. ['columnname'=>'data'] or if you use ? in the request : ['data1', 'data2'].

Return value : an array with 2 rows : 'raw' => the database's raw response, 'lastInsertId' => the last insert id.

Example :

update()

This method is used to update data in the database. It is highly recommended to use transaction when updating data.

Parameters :

• $table string : Table name.
• $data array : Array of data e.g. ['columnname'=>'data'].
• $where array : Array of data pointing the row to update e.g. ['columnname'=>'data']. 'data' has reserved values for nulls and booleans : 'NULL', '!NULL' 'TRUE', 'FALSE'. 'data' can also be an array of values.

Return value : If debug set to TRUE : return forged SQL request, else returns request's status as boolean.

Example :

delete()

This method is used to delete data from the database. It is highly recommended to use transaction when deleting data.

Parameters :

• $table string : Table name.
• $where array : Array of data pointing the row to update e.g. ['columnname'=>'data']. 'data' has reserved values for nulls and booleans : 'NULL', '!NULL' 'TRUE', 'FALSE'. 'data' can also be an array of values.

Return value : If debug set to TRUE : return forged SQL request, else returns request's status as boolean.

Example :

count()

This method is used to count how many rows match the criterias.

Parameters :

• $table string : Table name.
• $column string : Column name.
• $where array : Array with table name as key and array as value with column name and filter value e.g. ['table'=>['columnname'=>'data']]. 'data' has reserved values for nulls and booleans : 'NULL', '!NULL' 'TRUE', 'FALSE'. 'data' can also be an array of values.
• $join array (optional) : = Array with wanted join table name as key and array of needed values as values e.g. ['table' => [type(inner, left, right ...), 'foreignkey', 'primarykey', /*from table*\]].

Return value : If debug set to TRUE : return forged SQL request, else returns request's status as boolean on fail or int on success.

Example :

sum()

This method is used to get the sum of several rows matching criterias.

Parameters :

• $table string : Table name.
• $column string : Column name.
• $where array : Array with table name as key and array as value with column name and filter value e.g. ['table'=>['columnname'=>'data']]. 'data' has reserved values for nulls and booleans : 'NULL', '!NULL' 'TRUE', 'FALSE'. 'data' can also be an array of values.
• $join array (optional) : = Array with wanted join table name as key and array of needed values as values e.g. ['table' => [type(inner, left, right ...), 'foreignkey', 'primarykey', /*from table*\]].

Return value : If debug set to TRUE : return forged SQL request, else returns request's status as boolean on fail or int on success.

Example :

customSQL()

This method is used to execute a custom SQL request.

Parameters :

• $sql string : SQL request.
• $data array (optional) : Array of data e.g. ['columnname'=>'data'] or if you use ? in the request : ['data1', 'data2'].

Return value : the array of the raw response.

Example :