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

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!

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:

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.

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.

@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.

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,

INSERT INTO employee(eid, ename, email, ebirthdate) VALUES (null,'test','test1@**.com',1530101800), (null,'test','test@*.com',1530101800)

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

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

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?

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

Hi,

When I write something like that :

        $db
            ->select()
            ->columns(['col1' => 'C1', 'col2' => 'C2'])
            ->from('table')
            ->where(new Grouping('AND', ...[
                new Conditional('C2', 'BETWEEN', ['2022-01-01', '2022-12-31']),
                new Conditional('C1', '=', 'OK'),
            ]));

the "between" clause is writing like that :

C2 BETWEEN ('2022-01-01' AND '2022-12-31') AND C1 = 'OK'

It's not working like expected because : https://dba.stackexchange.com/a/251542

(And I cannot passing array like second argument in Grouping, php raise an Exception. The doc seem to say "it's okay with an array")

For informations, i use php 7.2 and mariadb 10.3.

Thanks !

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

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

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)