Just another PDO database library

Last update: Jul 6, 2022

PDO

Latest Stable Version Total Downloads Latest Unstable Version License

Just another PDO database library

Installation

Use Composer

$ composer require faapz/pdo 

Usage

Examples selecting, inserting, updating and deleting data from or into users table.

"your_new_password"]) ->table("users") ->where(new FaaPz\PDO\Clause\Conditional("id", "=", 1234)); if (($result = $insert->execute()) !== false) { $affectedRows = $result->rowCount(); } // DELETE FROM users WHERE id = ? $delete = $database->delete() ->from("users") ->where(new FaaPz\PDO\Clause\Conditional("id", "=", 1234)); if (($result = $delete->execute()) !== false) { $affectedRows = $result->rowCount(); }">
require_once 'vendor/autoload.php';

$dsn = 'mysql:host=your_db_host;dbname=your_db_name;charset=utf8';
$usr = 'your_db_username';
$pwd = 'your_db_password';

$database = new FaaPz\PDO\Database($dsn, $usr, $pwd);

// SELECT * FROM users WHERE id = ?
$select = $database->select()
                   ->from('users')
                   ->where(new FaaPz\PDO\Clause\Conditional('id', '=', 1234));

if ($insert->execute()) {
    $data = $stmt->fetch();
}

// INSERT INTO users (id , username , password) VALUES (? , ? , ?)
$insert = $database->insert(
                       'id',
                       'username',
                       'password'
                   )
                   ->into('users')
                   ->values(
                       1234,
                       'user',
                       'passwd'
                   );

if ($insert->execute()) {
    $insertId = $database->lastInsertId();
}

// UPDATE users SET pwd = ? WHERE id = ?
$update = $database->update(["pwd" => "your_new_password"])
                   ->table("users")
                   ->where(new FaaPz\PDO\Clause\Conditional("id", "=", 1234));

if (($result = $insert->execute()) !== false) {
    $affectedRows = $result->rowCount();
}

// DELETE FROM users WHERE id = ?
$delete = $database->delete()
                   ->from("users")
                   ->where(new FaaPz\PDO\Clause\Conditional("id", "=", 1234));

if (($result = $delete->execute()) !== false) {
    $affectedRows = $result->rowCount();
}

The sqlsrv extension will fail to connect when using error mode PDO::ERRMODE_EXCEPTION (default). To connect, you will need to explicitly pass array(PDO::ATTR_ERRMODE => PDO::ERRMODE_WARNING) (or PDO::ERRMODE_SILENT) into the constructor, or override the getDefaultOptions() method when using sqlsrv.

Documentation

See DOCUMENTATION

Changelog

See CHANGELOG

License

See LICENSE

GitHub

https://github.com/FaaPz/Slim-PDO
Comments
  • 1. Uncaught TypeError: Argument 1 passed to FaaPz\PDO\DatabaseException::__construct() must be of the type string, null given

    php -S 0.0.0.0:8080 -t public [Wed Oct 28 22:23:19 2020] PHP 7.4.11 Development Server (http://0.0.0.0:8080) started [Wed Oct 28 22:23:22 2020] 127.0.0.1:60524 Accepted [Wed Oct 28 22:23:22 2020] PHP Fatal error: Uncaught TypeError: Argument 1 passed to FaaPz\PDO\DatabaseException::__construct() must be of the type string, null given, called in /Users/nixon/PhpstormProjects/my-blog/vendor/faapz/pdo/src/AbstractStatement.php on line 40 and defined in /Users/nixon/PhpstormProjects/my-blog/vendor/faapz/pdo/src/DatabaseException.php:23 Stack trace: #0 /Users/nixon/PhpstormProjects/my-blog/vendor/faapz/pdo/src/AbstractStatement.php(40): FaaPz\PDO\DatabaseException->__construct(NULL, 'HY093') #1 /Users/nixon/PhpstormProjects/my-blog/vendor/faapz/pdo/src/Statement/Update.php(155): FaaPz\PDO\AbstractStatement->execute() #2 /Users/nixon/PhpstormProjects/my-blog/app/Models/User.php(73): FaaPz\PDO\Statement\Update->execute() #3 /Users/nixon/PhpstormProjects/my-blog/app/Services/Auth.php(94): App\Models\User->update('id', 6, Array) #4 /Users/nixon/PhpstormProjects/my-blog/src/dependencies.php(33): App\Services\Auth->user() #5 [internal function]: {closure}(Object(DI\Container)) #6 /Users/nixon/PhpstormProjects/celco in /Users/nixon/PhpstormProjects/my-blog/vendor/faapz/pdo/src/DatabaseException.php on line 23 [Wed Oct 28 22:23:22 2020] 127.0.0.1:60524 [200]: GET /partners - Uncaught TypeError: Argument 1 passed to FaaPz\PDO\DatabaseException::__construct() must be of the type string, null given, called in /Users/nixon/PhpstormProjects/my-blog/vendor/faapz/pdo/src/AbstractStatement.php on line 40 and defined in /Users/nixon/PhpstormProjects/my-blog/vendor/faapz/pdo/src/DatabaseException.php:23 Stack trace: #0 /Users/nixon/PhpstormProjects/my-blog/vendor/faapz/pdo/src/AbstractStatement.php(40): FaaPz\PDO\DatabaseException->__construct(NULL, 'HY093') #1 /Users/nixon/PhpstormProjects/my-blog/vendor/faapz/pdo/src/Statement/Update.php(155): FaaPz\PDO\AbstractStatement->execute() #2 /Users/nixon/PhpstormProjects/my-blog/app/Models/User.php(73): FaaPz\PDO\Statement\Update->execute() #3 /Users/nixon/PhpstormProjects/my-blog/app/Services/Auth.php(94): App\Models\User->update('id', 6, Array) #4 /Users/nixon/PhpstormProjects/my-blog/src/dependencies.php(33): App\Services\Auth->user() #5 [internal function]: {closure}(Object(DI\Container)) #6 /Users/nixon/PhpstormProjects/celco in /Users/nixon/PhpstormProjects/my-blog/vendor/faapz/pdo/src/DatabaseException.php on line 23 [Wed Oct 28 22:23:22 2020] 127.0.0.1:60524 Closing [Wed Oct 28 22:25:00 2020] 127.0.0.1:60542 Accepted [Wed Oct 28 22:25:00 2020] 127.0.0.1:60542 [200]: GET /partners [Wed Oct 28 22:25:00 2020] 127.0.0.1:60542 Closing

    Please help with the error above

    Reviewed by nixoncode at 2020-10-28 19:31
  • 2. Help needed on insert statement

    I followed the guide here https://github.com/FaaPz/PDO/blob/2.x/docs/Statement/Insert.md to make an insert page. $user_insert = new Insert($dsn, $user_attributes)->into('users');

    but I get error Parse error: syntax error, unexpected token "->" in app\includes\functions\auth.php on line 57

    I dont know if I have missed anything, but please help me.

    Thank you for your help!

    Reviewed by christogonus at 2021-12-01 02:35
  • 3. V2 Road Map

    V2 Road Map

    • [x] Interfaces for all classes
    • [x] No more private variables and functions, only protected
    • [x] Correct use of getters & setters
    • [x] Support for strings ('one, two'), arrays ([$one, $two]) and ... parameters
    • [x] Nested queries
    • [x] Field aliases AS in SELECT clauses
    • [ ] ON DUPLICATE KEY UPDATE (#32)
    • [ ] Detect database driver (could be useful for e.g. #29)
    • [x] Required PHP version to 7.2
    • [x] Rename on Packagist
    • [x] Unit Tests

    If anyone has any suggestions, let me know! :pray:

    Reviewed by FaaPz at 2016-02-12 21:06
  • 4. v2-dev Refactoring Proposal

    Refactoring version 1.0 to be more Object Oriented in order to expand into other SQL domains. Created a top level interface for all statement types and move a few components around. Removed many convenience methods primarily to simplify the transition. No effort has been made to add support beyond MySql at this point.

    Reviewed by kwhat at 2017-04-23 18:53
  • 5. Support for multiple grouping of 'OR' and 'AND' where clauses

    I need to be able to build queries such as: SELECT * FROM tablename WHERE a = 1 AND (b=2 OR c=3)

    This should be the case when I write: $db->select()->from('tablename')->where('a', '=', 1)->where('b', '=', 2)->orWhere('c', '=', 3)

    But this does not provide any bracket grouping. Whenever a where clause is followed by an orWhere, how can we indicate grouping of or's?

    Cheers.

    Reviewed by shaq147 at 2017-06-02 19:27
  • 6. Update Testing Process

    @FaaPz,

    Hope you are doing well. I am almost done adding unit tests to the master branch. I was wondering what service provides pull-request checks and if it would be possible to run composer test as part of that process? If not, maybe Circle CI?

    $ composer test
    > phpunit --coverage-html build/coverage tests/
    PHPUnit 8.2.5 by Sebastian Bergmann and contributors.
    
    .................................SS......SSS.....SSS.....S        58 / 58 (100%)
    
    Time: 908 ms, Memory: 8.00 MB
    
    OK, but incomplete, skipped, or risky tests!
    Tests: 58, Assertions: 67, Skipped: 9.
    

    image

    Reviewed by kwhat at 2019-07-30 23:33
  • 7. Use MySQL function and column as conditional value

    Hello,

    I tried to use the clause grouping as mentioned in the doc PDO/docs/Clause/CONDITIONAL.md:

     // ... WHERE col_1 = ? AND (col_2 = ? OR col_3 = ?)
     $statement->where(
         new Clause\Grouping("AND", array(
             new Clause\Conditional("col_1", "=", "val_1"),
             new Clause\Grouping("OR", array(
                 new Clause\Conditional("col_2", "=", 'val_2'),
                 new Clause\Conditional("col_3", "=", 'val_2')
             )
         ));
    

    Here's my code:

     $prizesStmt = $db->select(['id', 'value', 'text', 'isLosing', '_maxWinner', '_maxWinnerPerPeriod', '_maxWinnerPeriod', '_minDate', '_maxDate', 'totalWon'])
                 ->from('game_prize_types')
                 ->where(
                     new Grouping("AND",
                     [
                         new Grouping("OR",
                             new Conditional("totalWon", ">", "_maxWinner"),
                             new Conditional("_maxWinner", "IS", "NULL")
                         ),
                         new Grouping("OR",
                             new Conditional("_minDate", "<=", "NOW()"),
                             new Conditional("_minDate", "IS", "NULL")
                         ),
                         new Grouping("OR",
                             new Conditional("_maxDate", ">=", "NOW()"),
                             new Conditional("_maxDate", "IS", "NULL")
                         ),
                     ])
                 );
    

    But I have an error Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '?) AND (_minDate <= ? OR _minDate IS ?) AND (_maxDate >= ? OR _maxDate IS ?)' at line 1. Should I specify with a parameter that the value is a SQL function or a column name ?

    Same for an update, I would like to increment column value but it doesn't work..

    Thanks in advance,

    Reviewed by copostic at 2021-01-12 10:33
  • 8. Can we use multiple insert statement in FaaPz / PDO

    INSERT INTO employee(eid, ename, email, ebirthdate) VALUES (null,'test','[email protected]**.com',1530101800), (null,'test','[email protected]*.com',1530101800)

    Can we create statement in slim pdo for multiple insert in one single query

    Reviewed by ParthWeb at 2018-06-27 12:21
  • 9. Escape column names

    Hi there,

    When it compiles the query it doesn't escape column names in where section.

    Ex,

    $stmt = $pdo
        ->select()
        ->from('region')
        ->where('default', '=', true)
        ->execute()
    

    To fix I have to prefix the column names,

    $stmt = $pdo
        ->select()
        ->from('region')
        ->where('region.default', '=', true)
        ->execute()
    

    Guess, it would be nice if it could output something like

    SELECT * FROM `region` WHERE `default` = 1
    

    Cheers, Evgenii

    Reviewed by nasyrov at 2017-04-04 16:52
  • 10. Should this composer package be named as "slim/pdo"?

    To avoid confusion, I think it's best to rename this to a different name (faapz/slim-pdo?) Devs may think that this is an official package maintained by Slim.

    Any thoughts?

    Reviewed by jordikroon at 2016-01-27 10:33
  • 11. insert update on duplicate key support.

    I've implemented a quick support for inserting on duplicate key update for those interested.

    Use case:

    $db->insert( array('id', 'field1', 'field2', 'field3') )
        ->into('table')
        ->values('1', 'value1', 'value2', 'value3')
        ->updateOnDuplicate( array('field2' => 'value2', 'field3' => 'value3') );
    

    PDO/Statement/InsertStatement.php added 2 functions, 3 private variables, and updated the __toString method

    private $_updateOnDuplicate = false;
    private $_updateValues = array();
    private $_updateFields = array();
    
    public function updateOnDuplicate($fieldsValues)
    {
    	$fieldValues = (array) $fieldValues;
    
    	if(!empty($fieldValues) ) {
    		$this->_updateOnDuplicate = true;
    	        $this->_updateValues = array_values($fieldValues);
    	        $this->_updateFields = array_keys($fieldValues);
            }	
            return $this;
    }
    
    protected function _prepareUpdateFields()
    {
    	$this->setValues($this->_updateValues);
    
    	$str = '';
    	$commaFlag = false;
    	foreach($this->_updateFields as $field) {
    		if($commaFlag) $str .= ', ';
    		$str .= $field . ' = ?';
    		$commaFlag = true;
    	}
    	
    	return $str;
    }
    
    //Updated
    public function __toString()
    {
    	if (empty($this->table)) {
    		trigger_error('No table is set for insertion', E_USER_ERROR);
    	}
    
    	if (empty($this->columns)) {
    		trigger_error('Missing columns for insertion', E_USER_ERROR);
    	}
    
    	if (empty($this->values)) {
    		trigger_error('Missing values for insertion', E_USER_ERROR);
    	}
    
    	$sql = 'INSERT INTO '.$this->table;
    	$sql .= ' '.$this->getColumns();
    	$sql .= ' VALUES '.$this->getPlaceholders();
    	
    	if($this->_updateOnDuplicate) {
    		$sql .= ' ON DUPLICATE KEY UPDATE '.$this->_prepareUpdateFields();
    	}
    
    	return $sql;
    }
    
    
    
    Reviewed by shaq147 at 2017-06-05 20:43
  • 12. add parenthesis in query

    Sametimes we need to write a query with parenthesis like this exemple :

    WHERE ( categorie = 'informatique' AND stock < 20 )
    OR ( categorie = 'fourniture' AND stock < 200 )
    

    So with this merge we will be able to write it like this :

    $subject1 = new Parenthesis(new Conditional('categorie', '=', 'informatique'), 'AND', new Conditional('stock', '<', 20));
    $subject2 = new Parenthesis(new Conditional('categorie', '=', 'fourniture'), 'AND', new Conditional('stock', '<', 200));
    $subject  = new Conditional($subject1, 'OR', $subject2);
    
    Reviewed by kernel64 at 2022-05-05 20:10
  • 13. Limit is treated as a string

    As the statement is executed as $stmt->execute($this->getValues()), any integers will be added to the parameters as strings. This results in an SQL-error for LIMIT, as this needs to be an integer.

    SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''999'' at line 1
    
    Reviewed by jerome2710 at 2022-01-17 16:45
  • 14. orderBy direction is not optional

    Reviewed by jerome2710 at 2022-01-14 15:15
  • 15. README not up to date

    Hello,

    In the README, you mention the possibility to use insert with an associative array:

    $insert = $database->insert([
                           'id' =>1234,
                           'username' => 'user',
                           'password' => 'passwd'
                       ])
                       ->into('users');
    

    But it is not possible anymore as you are using the splat operator as you can see on the error below :

    image

    Is this wanted ? If true, README should be updated and documentation too, otherwise, the functionality should be fixed

    Thanks a lot,

    Reviewed by copostic at 2022-01-05 15:27
  • 16. Update about text "Just another PDO database library"

    Hey @FaaPz,

    I am just thinking of ways to improve SEO for this project and I think updating "Just another PDO database library" to something like "A object-oriented PDO based query builder for modern PHP" could help a lot. I am not 100% on that about phrase yet, still kicking around ideas but we should include query builder, PDO and PHP.

    We should probably also adjust the "topics" keywords as well. Maybe add the following:

    • MySQL
    • SQL
    • Database
    • PDO
    • PHP7 (Remove PHP72)
    Reviewed by kwhat at 2021-12-15 18:16
  • 17. Awesome work

    I've been using this package and it blows Propel and Doctrine out the water in terms of getting started and pure performance.

    Not an issue ;) just wanted to say thanks for the good work.

    Reviewed by plasid at 2016-09-01 12:07
SQL database access through PDO.

Aura.Sql Provides an extension to the native PDO along with a profiler and connection locator. Because ExtendedPdo is an extension of the native PDO,

Jul 30, 2022
Simple MySQL library for PHP 5.4+ includes Query Builder, PDO Native functions, Helper functions for quick use.

Simple MySQL library for PHP 5.4+ includes Query Builder, PDO Native functions, Helper functions for quick use.

Jul 14, 2022
Easy data transfer from one database to another

Migrate DB Easy data transfer from one database to another Installation To get the latest version of Migrate DB, simply require the project using Comp

Jul 22, 2022
Easy-to-use PDO wrapper for PHP projects.

EasyDB - Simple Database Abstraction Layer PDO lacks brevity and simplicity; EasyDB makes separating data from instructions easy (and aesthetically pl

Aug 4, 2022
You can sync any number of PDO supported databases

Features: Can backup any number of databases. No need to introduce column name only table name The Last id based data backup Support 12 different data

Aug 27, 2021
Tiny php mysql lib (PDO-based) with handy fetch/update functionality, supports both SQL and parametric queries

Micro PHP mysql lib (~ 200 lines of code) with ultra powerful CRUD for faster than ever development: parametric fetch/insert/update/delete (based on a

Feb 22, 2022
Pure PHP NoSQL database with no dependency. Flat file, JSON based document database.
Pure PHP NoSQL database with no dependency. Flat file, JSON based document database.

Please give it a Star if you like the project ?? ❤️ SleekDB - A NoSQL Database made using PHP Full documentation: https://sleekdb.github.io/ SleekDB i

Aug 13, 2022
SleekwareDB is a NoSQL database storage service. A database storage service that can be used for various platforms and is easy to integrate.
SleekwareDB is a NoSQL database storage service. A database storage service that can be used for various platforms and is easy to integrate.

SleekwareDB is a NoSQL database storage service. A database storage service that can be used for various platforms and is easy to integrate. NoSQL API

Aug 3, 2022
A drop-in library for certain database functionality in Laravel, that allows for extra features that may never make it into the main project.

Eloquence Eloquence is a package to extend Laravel's base Eloquent models and functionality. It provides a number of utilities and classes to work wit

Jul 13, 2022
A simple library to access and manipulate database records. Built on top of Dibi and hardwired for PostgreSQL.

grifart/tables A simple library to access and manipulate database records. Built on top of Dibi and hardwired for PostgreSQL. This library is develope

Apr 18, 2022
A simple library for managing database connections, results pagination and building queries in PHP

PHP lions-software-database-manager This is a simple library for managing database connections, results pagination and building queries in PHP. Esta é

Feb 7, 2022
The Enobrev\ORM library is a small framework of classes meant to be used for simply mapping a mysql database to PHP classes, and for creating simply SQL statements using those classes.

The Enobrev\ORM library is a small framework of classes meant to be used for simply mapping a mysql database to PHP classes, and for creating simply SQL statements using those classes.

Jan 7, 2022
The lightweight PHP database framework to accelerate development
The lightweight PHP database framework to accelerate development

The lightweight PHP database framework to accelerate development Features Lightweight - Less than 100 KB, portable with only one file Easy - Extremely

Aug 14, 2022
[READ ONLY] Subtree split of the Illuminate Database component (see laravel/framework)

Illuminate Database The Illuminate Database component is a full database toolkit for PHP, providing an expressive query builder, ActiveRecord style OR

Aug 12, 2022
ORM layer that creates models, config and database on the fly

RedBeanPHP 5 RedBeanPHP is an easy to use ORM tool for PHP. Automatically creates tables and columns as you go No configuration, just fire and forget

Aug 13, 2022
Spot v2.x DataMapper built on top of Doctrine's Database Abstraction Layer

Spot DataMapper ORM v2.0 Spot v2.x is built on the Doctrine DBAL, and targets PHP 5.4+. The aim of Spot is to be a lightweight DataMapper alternative

Aug 9, 2022
PHP Database Migrations for Everyone
PHP Database Migrations for Everyone

Phinx: Simple PHP Database Migrations Intro Phinx makes it ridiculously easy to manage the database migrations for your PHP app. In less than 5 minute

Aug 15, 2022
Database management in a single PHP file

Adminer - Database management in a single PHP file Adminer Editor - Data manipulation for end-users https://www.adminer.org/ Supports: MySQL, MariaDB

Aug 13, 2022
Doctrine Database Abstraction Layer

Doctrine DBAL 4.0-dev 3.0 2.13 N/A N/A Powerful database abstraction layer with many features for database schema introspection, schema management and

Aug 11, 2022