PHPackages benclerc/datamanagement - PHPackages - PHPackages [Skip to content](#main-content)[PHPackages](/)[Directory](/)[Categories](/categories)[Trending](/trending)[Leaderboard](/leaderboard)[Changelog](/changelog)[Analyze](/analyze)[Collections](/collections)[Log in](/login)[Sign up](/register) 1. [Directory](/) 2. / 3. [Database & ORM](/categories/database) 4. / 5. benclerc/datamanagement ActiveLibrary[Database & ORM](/categories/database) benclerc/datamanagement ======================= PHP Class providing simple and compact database requests. Useful for small projects without ORM. 1.6(5y ago)255MITPHPPHP >=7.3.0 Since Mar 5Pushed 5y ago2 watchersCompare [ Source](https://github.com/benclerc/DataManagement)[ Packagist](https://packagist.org/packages/benclerc/datamanagement)[ Docs](https://github.com/benclerc/DataManagement)[ RSS](/packages/benclerc-datamanagement/feed)WikiDiscussions master Synced 6d ago READMEChangelogDependenciesVersions (7)Used By (0) Data Management =============== [](#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 ----------------- [](#table-of-contents) - [Getting started](#getting-started) - [Documentation](#documentation) - [connector()](#connector) - [beginTransaction()](#begintransaction) - [commit()](#commit) - [rollback()](#rollback) - [debug()](#debug) - [select()](#select) - [customSelect()](#customselect) - [insert()](#insert) - [customInsert()](#custominsert) - [update()](#update) - [delete()](#delete) - [count()](#count) - [sum()](#sum) - [customSQL()](#customsql) Getting started --------------- [](#getting-started) 1. Get [Composer](http://getcomposer.org/) 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 ------------- [](#documentation) You can find a full documentation [here](https://benclerc.github.io/DataManagement/). ### connector() [](#connector) This method returns the PDO object connected to the database. ### beginTransaction() [](#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() [](#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() [](#rollback) This method ends a SQL transaction by rolling back the changes. Nothing done during the transaction will be applied. ### debug() [](#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 * FROM BOOKS;" $db->debug()->select('BOOKS'); // "SELECT * FROM BOOKS WHERE BOOKS.books_id = :whereBOOKSbooks_id;" $db->debug()->select('BOOKS', NULL, NULL, ['BOOKS'=>['books_id'=>42]]); // "SELECT * FROM BOOKS INNER JOIN AUTHORS ON BOOKS.books_refauthor = AUTHORS.authors_id ORDER BY books_name ASC;" $db->debug()->select('BOOKS', ['books_name'], ['AUTHORS'=>['INNER', 'books_refauthor', 'authors_id']]); // "INSERT INTO BOOKS (books_name, books_refauthor) VALUES (:books_name, :books_refauthor);" $db->debug()->insert('BOOKS', ['books_name'=>htmlentities('Super book'), 'books_refauthor'=>42]); // "UPDATE BOOKS SET books_name=:books_name WHERE BOOKS.books_id = :whereBOOKSbooks_id;" $db->debug()->update('BOOKS', ['books_name'=>htmlentities('Super book 2')], ['books_id'=>42]); // "DELETE FROM BOOKS WHERE BOOKS.books_id = :whereBOOKSbooks_id;" $db->debug()->delete('BOOKS', ['books_id'=>42]); // "SELECT COUNT(books_id) FROM BOOKS WHERE BOOKS.books_isavailable IS NULL;" $db->debug()->count('BOOKS', 'books_id', ['BOOKS'=>['books_isavailable'=>TRUE]]); // "SELECT SUM(books_pages) FROM BOOKS WHERE BOOKS.books_isavailable IS NULL;" $db->debug()->sum('BOOKS', 'books_pages', ['BOOKS'=>['books_isavailable'=>TRUE]]); ``` ### select() [](#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 : ``` // Get all books $res = $db->select('BOOKS'); // Get one book, id = 42 $res = $db->select('BOOKS', NULL, NULL, ['BOOKS'=>['books_id'=>42]])[0]; // Note the NULL values because we do not want order or join. And also note [0] because we know we have only one result so we can select it directly. // Get all books with their authors, results ordered on the book name from A to Z $res = $db->select('BOOKS', ['books_name'], ['AUTHORS'=>['INNER', 'books_refauthor', 'authors_id']]); // Get all books with the reference in the list + their authors, results ordered on the book name from A to Z and author name from Z to A, limit to 10 results with an offset of 10 (page 2) $referenceList = [37483, 27949, 49303, 20438]; $res = $db->select('BOOKS', ['books_name', 'authors_name'=>'DESC'], ['AUTHORS'=>['INNER', 'books_refauthor', 'authors_id']], ['BOOKS'=>['books_reference'=>$referenceList]], 10, 10); // Get all books with their subcategories and categories $res = $db->select('BOOKS', NULL, ['SUBCATEGORIES'=>['INNER', 'books_refsubcategory', 'subcategories_id'], 'CATEGORIES'=>['INNER', 'subcategories_refcategory', 'categories_id', 'SUBCATEGORIES']]); // Note the fourth element in the element 'CATEGORIES' in the join array. ``` ### customSelect() [](#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 : ``` // For request with subqueries for example $res = $db->customSelect('SELECT * FROM BOOKS WHERE books_id IN (SELECT orders_refbook FROM ORDERS WHERE orders_refclient = :id);', ['id'=>42]); // Or to filter using an other operator than = $res = $db->customSelect('SELECT * FROM BOOKS WHERE books_release > 2000-01-01'); ``` ### insert() [](#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 : ``` // Simple insert, without transaction if ($db->insert('BOOKS', ['books_name'=>htmlentities('Super book'), 'books_refauthor'=>42])['raw']) { echo('Success'); } else { echo('Error'); } // Simple insert, with transaction $db->beginTransaction(); $resAuthor = $db->insert('AUTHORS', ['authors_name'=>htmlentities('Super Author')]); if ($resAuthor['raw']) { if ($db->insert('BOOKS', ['books_name'=>htmlentities('Super book'), 'books_refauthor'=>$resAuthor['lastInsertId']])['raw']) { $db->commit(); echo('Success'); } else { $db->rollback(); echo('Error when inserting book.'); } } else { $db->rollback(); echo('Error when inserting author.'); } ``` ### customInsert() [](#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 : ``` // For insert using SQL functions for example $res = $db->customInsert('INSERT INTO BOOKS (books_name, books_release) VALUES (?, NOW());', [htmlentities('Super book')]); ``` ### update() [](#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 : ``` // Simple update, without transaction if ($db->update('BOOKS', ['books_name'=>htmlentities('Super book 2')], ['books_id'=>42])) { echo('Success'); } else { echo('Error'); } // Simple update, with transaction $db->beginTransaction(); $resBook1 = $db->update('BOOKS', ['books_name'=>htmlentities('Super book 1')], ['books_id'=>41]); if ($resBook1) { if ($db->update('BOOKS', ['books_name'=>htmlentities('Super book 2')], ['books_id'=>42])) { $db->commit(); echo('Success'); } else { $db->rollback(); echo('Error when updating book 2.'); } } else { $db->rollback(); echo('Error when updating book 1.'); } ``` ### delete() [](#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 : ``` // Simple delete, without transaction if ($db->delete('BOOKS', ['books_id'=>42])) { echo('Success'); } else { echo('Error'); } // Simple delete, with transaction $db->beginTransaction(); $resBook1 = $db->delete('BOOKS', ['books_id'=>41]); if ($resBook1) { if ($db->delete('BOOKS', ['books_id'=>42])) { $db->commit(); echo('Success'); } else { $db->rollback(); echo('Error when deleting book 2.'); } } else { $db->rollback(); echo('Error when deleting book 1.'); } ``` ### count() [](#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 : ``` // Simple count $res = $db->count('BOOKS', 'books_id', ['books_isavailable'=>TRUE]); // Count with join $res = $db->count('BOOKS', 'authors_id', ['books_isavailable'=>TRUE], ['AUTHORS'=>['INNER', 'books_refauthor', 'authors_id']]); // Return the number of authors who have at least one book available. ``` ### sum() [](#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 : ``` // Simple sum $res = $db->sum('BOOKS', 'books_pages', ['books_isavailable'=>TRUE]); // Return the total number of pages of available books. ``` ### customSQL() [](#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 : ``` // For uncommon SQL queries $res = $db->customSQL('UPDATE BOOKS SET books_isold IS TRUE WHERE books_release < 2000-01-01'); ``` ### Health Score 25 — LowBetter than 37% of packages Maintenance20 Infrequent updates — may be unmaintained Popularity11 Limited adoption so far Community8 Small or concentrated contributor base Maturity52 Maturing project, gaining track record Bus Factor1 Top contributor holds 100% of commits — single point of failure How is this calculated?**Maintenance (25%)** — Last commit recency, latest release date, and issue-to-star ratio. Uses a 2-year decay window. **Popularity (30%)** — Total and monthly downloads, GitHub stars, and forks. Logarithmic scaling prevents top-heavy scores. **Community (15%)** — Contributors, dependents, forks, watchers, and maintainers. Measures real ecosystem engagement. **Maturity (30%)** — Project age, version count, PHP version support, and release stability. ### Release Activity Cadence Every ~2 days Total 6 Last Release 1889d ago PHP version history (2 changes)1.0PHP >=7.4.0 1.4PHP >=7.3.0 ### Community Maintainers ![](https://www.gravatar.com/avatar/3b52a5a1fcfa2dc90b63bb0d58114fe8bae432ede9797ef52cb5579859910b44?d=identicon)[benclerc](/maintainers/benclerc) --- Top Contributors [![benclerc](https://avatars.githubusercontent.com/u/79925489?v=4)](https://github.com/benclerc "benclerc (20 commits)") --- Tags datadatabasedatamanagementmysqlpostgresqlsqlsql-transaction ### Embed Badge ![Health badge](/badges/benclerc-datamanagement/health.svg) ``` [![Health](https://phpackages.com/badges/benclerc-datamanagement/health.svg)](https://phpackages.com/packages/benclerc-datamanagement) ``` ### Alternatives [doctrine/orm Object-Relational-Mapper for PHP 10.2k285.3M6.2k](/packages/doctrine-orm)[jdorn/sql-formatter a PHP SQL highlighting library 3.9k115.1M102](/packages/jdorn-sql-formatter)[illuminate/database The Illuminate Database package. 2.8k52.4M9.4k](/packages/illuminate-database)[mongodb/mongodb MongoDB driver library 1.6k64.0M546](/packages/mongodb-mongodb)[ramsey/uuid-doctrine Use ramsey/uuid as a Doctrine field type. 90340.3M211](/packages/ramsey-uuid-doctrine)[reliese/laravel Reliese Components for Laravel Framework code generation. 1.7k3.4M16](/packages/reliese-laravel) PHPackages © 2026 [Directory](/)[Categories](/categories)[Trending](/trending)[Changelog](/changelog)[Analyze](/analyze)