faapz / pdo Goto Github PK
View Code? Open in Web Editor NEWJust another PDO database library
License: MIT License
Just another PDO database library
License: MIT License
Hello everyone,
I'm having some issues to implement a "ON DUPLICATE KEY UPDATE" sentence using Slim-PDO.
Is it supported?
I tried to find in documentation but no luck. Also tried to improvise but no luck either.
Best regards,
DF
Please update README.md and INSERT.md documentation, the argument for INSERT execute() must be TRUE or undefined to return insert id.
This looks wrong:
$insertId = $insertStatement->execute( false );
As it stands in source code, the value must be TRUE or undefined.
/** * @param bool $insertId * * @return string */ public function execute($insertId = true) { if (!$insertId) { return parent::execute(); } parent::execute(); return $this->dbh->lastInsertId(); }
There's an issue when you delete a row that you selected before.
After having selected my user, I want to delete the row.
So first, I select him
$usr = $pdo->select(['id'])
->from('users')
->where('username', '=', $args['username'])
->where('password', '=', $args['userpsw'])
->where('token', '=', $args['usertk'])
->execute()->fetch();
So, $usr['id'] returns me an id (14 in my actual case)
and next, I want to delete him
$pdo->delete()
->from('users')
->where('id', '=', $usr['id'])
->execute();
Slim throws an exception without code error, but when I put another id that was not selected before (like $usr['id']+1
, everything work well.
Is there a way to close select request in order to delete it after?
Thanks
Hi,
$statement->limit($number, $offset);
not working properly.
In MySQL this expression has the format [LIMIT [offset,] rows]
You have mixed up the parameters.
$this->limit = intval($number).' , '.intval($end);
Dose it protect from injections ?
I want to fetch all the records therefore I've tried :
where()
$selectStatement = $pdo->select()
->from('user')
limit(bigNumber)
$selectStatement = $pdo->select()
->from('user')
->limit(1000000);
Followed by :
$stmt = $selectStatement->execute();
$data = $stmt->fetch();
print_r($data);
But i get only the first record in return, would you tell me what i'm missing here ?
Thank you.
Hi bro,
I'd love to know if you can provide the function, which supports the PDO transaction, simply put, the commit, rollback etc.
Hi,
I was wondering if it is already possible to join on multiple columns..? If not, I provided an example function to add this to the library.
Is this possible with the current library?
SELECT *
FROM A
INNER JOIN B ON A.col1 = B.col1
AND A.col2 = B.col2;
If not, could you implement something like this:
/**
* @param string $table
* @param array $columns
* @param string $joinType
* @param string $operator
* @param string $chainType
*/
public function joinOnMultiple($table, array $columns, $operator, $joinType = 'INNER', $chainType = 'AND') {
$string = ' ' . $joinType . ' JOIN ' . $table . ' ON ' . key($columns[0]) . ' ' . $operator . ' ' . $columns[0];
unset($columns[0]);
if (!empty($columns)) {
foreach ($columns as $first => $second) {
$string .= ' ' . $chainType . ' ' . $first . ' ' . $operator . ' ' . $second;
}
}
$this->_container[] = $string;
}
so we can achieve the query by doing:
$statement->joinOnMultiple('B', [
'A.col1' => 'B.col1',
'A.col2' => 'B.col2',
], '=');
Thanks in advance!
Although Slim-PDO has almost every wanted feature, it doesn't cover all the MySQL (or other DB's) features. Is it possible to call the main \PDO class when needed?
Thanks. :)
Current limit() clause use is_int() to check params. I use $request->getParsedBody()['rowCount'] and it does not work. I then must cast it to int.
Please include this in limit() document in case someone has the same problem.
where clause with parameters, how do?
I'd like an ability to modify the command to INSERT IGNORE.
Hi,
$insertStatement = $pdo->insert($fields)->into('accounts')->values($values);
$insertId = $insertStatement->execute();
$insertId is always <= 0 and nothing is inserted.
To fix this I need to do lke this:
$insertId = $insertStatement->execute(false); // now it is OK.
Why you do this?
public function execute($insertId = true){
if (!$insertId) {
return parent::execute();
}
return $this->dbh->lastInsertId();
}
Thanks.
Hello,
I'm looking for a way to write a combined update.
Example:
INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)
ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);
Your sincerly
Stephan
Can we detect driver and change LIMIT 1 to TOP(1) and place after SELECT?
I need where clause like this
$statement->where('DATE(myDate)', '>=', 'DATE(NOW())');
But if we look at what happened with
$statement->__toString()
we will see
TE(myDate) >= ?
The problem in this expression:
return ' WHERE '.ltrim(implode('', $args), ' AND');
ltrim
removes all letters listed in $character_mask
.
string ltrim ( string $str [, string $character_mask ] )
http://php.net/manual/en//function.ltrim.php
Hello,
is there a way to get the real sql from the statement.
For example:
$selectStm = $slimPdo
->select()
->from('users')
->where('id','=',$id);
$stm = $selectStm->execute();
$data = $stm->fetchAll();
$mySql = $selectStm->getSql();
Your sincerly
Stephan
Hello. Just want to know: do we have ability to make CREATE / ALTER TABLE
queries or different queries than standard CRUD.
Hi Fabian,
It looks like the join clause is only available on select statements, but should be available on SELECT, UPDATE, INSERT and DELETE for cross table query constraints. It's a simple fix but should be slated for 2.0.
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?
Proper Postgres syntax is LIMIT 10 OFFSET 0
This is after updating to v1.10.0
Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42601]: Syntax error: 7 ERROR: LIMIT #,# syntax is not supported LINE 10: ) = $1 LIMIT 0 , 10 ^ HINT: Use separate LIMIT and OFFSET clauses.' in /vendor/slim/pdo/src/PDO/Statement/StatementContainer.php:429 Stack trace: #0 /vendor/slim/pdo/src/PDO/Statement/StatementContainer.php(429): PDOStatement->execute(Array) #1 /vendor/slim/pdo/src/PDO/Statement/SelectStatement.php(411): Slim\PDO\Statement\StatementContainer->execute() #2 /user/file.php(382): Slim\PDO\Statement\SelectStatement->execute() #3 /user/file2.php(76): UserFunction() #4 {main} thrown in /vendor/slim/pdo/src/PDO/Statement/StatementContainer.php on line 429
I get this error when using LIMIT with mysql DB
Fatal error: Expects parameters as integers in /home/public_html/api/vendor/slim/pdo/src/PDO/Clause/LimitClause.php on line 29
My code is below:
$selectStatement = $pdo->select()
->from('news')
->limit(10);
When I use ‘select’, I need to use the slave database and how to configure the master slave database.?
Is there a way to group like (this = that AND that = this) AND (foo = bar OR foo = baz)
Query try 1:
$selectStatement = $dbw->select($fields)
->from('projects p')
->join('int_users_projects iup', 'p.id', '=', 'iup."fkProjectId"')
->join('clients c', 'p."fkClientId"', '=', 'c.id')
->where('iup."fkUserId"', '=', $authUser->getId(), 'AND')
->where('p."fkSuperUserId"', '=', $authUser->getId(), 'OR')
->where('p.status', '=', 'active', 'AND')
->where('c.status', '=', 'active', 'AND');
Query try 2:
$selectStatement = $dbw->select($fields)
->from('projects p')
->join('int_users_projects iup', 'p.id', '=', 'iup."fkProjectId"')
->join('clients c', 'p."fkClientId"', '=', 'c.id')
->where('iup."fkUserId"', '=', $authUser->getId())
->orwhere('p."fkSuperUserId"', '=', $authUser->getId())
->where('p.status', '=', 'active')
->where('c.status', '=', 'active');
I get:
SELECT p.id , p.uniq_id , p.name , p.description , p."startDate" , p."endDate" , p.latitude , p.longitude
FROM projects p
INNER JOIN int_users_projects iup ON p.id = iup."fkProjectId"
INNER JOIN clients c ON p."fkClientId" = c.id
WHERE iup."fkUserId" = ? OR p."fkSuperUserId" = ? AND p.status = ? AND c.status = ?
But I want:
SELECT p.id , p.uniq_id , p.name , p.description , p."startDate" , p."endDate" , p.latitude , p.longitude
FROM projects p
INNER JOIN int_users_projects iup ON p.id = iup."fkProjectId"
INNER JOIN clients c ON p."fkClientId" = c.id
WHERE (iup."fkUserId" = ? OR p."fkSuperUserId" = ?) AND p.status = ? AND c.status = ?
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.
Hi,
Can the doc have an example of how to use placeholders or variables in the prepared statements? Should I just use standard PDO placeholders and bindings?
Hello. this query is not working. I believe is trying to bind permissions.account_id
$selectStatement = $this->pdo->select()
->from('accounts, permissions')
->where('accounts.id', '=', 'permissions.account_id')
->where('permissions.company_id', '=', $company_id)
->groupBy('accounts.id');
how are you guys dealing with this?
Creating a new Database instance with SQLSRV throws the following:
The given attribute is only supported on the PDOStatement object.
$pdo = new \Slim\PDO\Database($dsn, $username, $password);
If I comment out the following line, the error doesn't get thrown and I can use the database object as intended.
\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
I would like to know how can i update the existing date and add some days to it.
Example. update( ['date' => "DATE_ADD('date' , INTERVAL 2 DAY)" ] ).
Or how can i add a native sentence in the update function like SET date = "DATE_ADD('date' , INTERVAL 2 DAY)".
Thanks in advance
I don't have a suggestion for this one yet, but if something dawns on me, I'll create a pull request. So the select limit clause needs a check for MS SQL as it use a differen SELECT TOP n * FROM table;
notation.
I'm attempting to insert a row that has NULL values in the data array coming in, like this:
$pdo->insert([ 'id', 'usr', 'pwd', 'name' ])
->into( 'users' )
->values([ 1234, 'your_username', 'your_password', NULL ])
->execute();
setPlaceholders
looks like it passes a sizeof($value)
argument to setPlaceholder
, but if the value is NULL
then this will send in 0
there. This produces a SQL string that throws an error:
INSERT INTO users ( id , usr , pwd , name ) VALUES ( ? , ? , , ? )
I feel like the expected behavior is to just add the ? regardless if the value has a size.
Hi !
Thank you for you great PHP Class. But how can make a select count ?
Here is an sample with simple PDO
$sql = "SELECT count(*) FROM table
WHERE foo = bar";
$result = $con->prepare($sql);
$result->execute();
$number_of_rows = $result->fetchColumn();
Referenced in #11
It's not possible to overwrite any options set in Database::getDefaultOptions() when passing the $options array into the constructor.
Line 31 should be:
$options = $options + $this->getDefaultOptions();
Hi,
How can I perform this query:
UPDATE table SET count = count + 1 WHERE id = ?
Thanks.
Should order by clause be available in UPDATE or DELETE? I noticed that its currently on the StatementContainer but it will not work on MsSQL for UPDATE and DELETE. Not sure what other sql may expect.
I had problem with Order By. It return error "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 'order = ? ORDER BY name_en ASC' at line 1"
This is my code
$query = $this->db->select()->from('provinces');
$query->orderBy('name_en');
$result = $query->execute();
How can I Fix It.
$selectStatement = new SelectStatement($this->pdo, array('*'));
// or
$selectStatement = $this->pdo->select();
$sel = $selectStatement->from('dede_archives')->count();
echo $sel;
result
SELECT * , COUNT( * ) FROM dede_archives
maybe in front of * can be removed
I don't know if this is a problem with PDO or with this library.
I am trying to use this WHERE clause:
->where("date", ">", "CURRENT_DATE - INTERVAL '90 days'")
but I get this exception:
Uncaught exception 'PDOException' with message 'SQLSTATE[0A000]: Feature not supported: 7 ERROR: date/time value "current" is no longer supported' in .../vendor/slim/pdo/src/PDO/Statement/StatementContainer.php:404
Is it possible to use DATE/TIME variables in PostgreSQL like CURRENT_DATE
?
Hello,
How can I make a query like this
SELECT * FROM table WHERE field1 = 1 AND field2 = 2 AND (field3 = 3 OR field4 = 4);
Thanks.
Hi,
My query is
$resultObj = $this->db->select()->from('shuttles')
->leftJoin('routes', 'routes.id', '=', 'shuttles.route_id')
->leftJoin('schedules', 'schedules.id', '=', 'shuttles.schedule_id')
->where('shuttles.id', '=', '4')
->where('shuttles.status','=','active')->execute();
$result = $resultObj->fetchAll();
[Uploading whirlpool_mobility_qa3-29 PMtest.txt…](SQL export of tables)
its not returning me data for shuttle id =4.
Join not working with where conditions.
Can you please let me know the solution for the same
Minor fix select()
method from 95d7f97 is broken. Working fix coming soon.
According to http://php.net/manual/en/function.array-merge.php, the keys that are being set in https://github.com/FaaPz/Slim-PDO/blob/master/src/PDO/Database.php under the __construct function, are being renumbered, so they are being passed to the parent constructor using the incorrect values.
I'd suggest using "$options += array(default options)" statement instead, which preserves the numeric keys, while allowing what I believe is the intended behaviour here.
I have two whereIn
statements, and the second one causes the entire SELECT statement to fail.
My code:
$property_classes = ["2-11"];
$zoning_classes = ["RT-4"];
$select = $pdo->select(["*"])
->from("property_table AS p");
$select->whereIn("property_class_15", $property_classes);
$select->whereIn("zone_class", $zoning_classes);
print_r
on $select
outputs:
[values:protected] => Array
(
[0] => RT-4
[1] => 2-11
[2] => TRUE
)
[table:protected] => propertytaxes_09_15_combined2 AS p
[whereClause:protected] => Slim\PDO\Clause\WhereClause Object
(
[container:protected] => Array
(
[0] => AND zone_class IN ( ? )
[1] => AND property_class_15 IN ( ? , ? )
[2] => AND ST_Intersects(p.geom, ST_Transform(ST_MakeEnvelope(-87.7193069458008,41.931360904903066,-87.7081596851349,41.93654878172632, 4326), 3435)) = ?
)
)
Notice that property_class_15
has two ?
yet it only has 1 corresponding value in the values
array (2-11
). If the property_class_15
has 2 corresponding values, then there will be three ?
.
Why is the second whereIn
statement increasing the number of placeholders by 1?
private
variables and functions, only protected
'one, two'
), arrays ([$one, $two]
) and ...
parametersAS
in SELECT
clausesON DUPLICATE KEY UPDATE
(#32)If anyone has any suggestions, let me know! 🙏
Would it be possible to add the ability to execute raw SQL commands with this package?
About Master slave database?
I want to ask, slimpdo how to open a business
I looked set method of Slim\PDO\Statement\UpdateStatement class and it doesnt provide self column aritmetic operations such as count=count+?
, count=count-?
etc. It could be flexible implement of this method with 2d arrays.
Thnx for repo.
Would be a cool additional feature. :)
See http://www.hackingwithphp.com/9/3/18/advanced-text-searching-using-full-text-indexes
$this->pdo->select()
->form('books')
->match('book_title', '+PHP -SQL')
..
Same as: SELECT * FROM books WHERE MATCH(book_title) AGAINST ('+PHP -SQL' IN BOOLEAN MODE);
IN BOOLEAN MODE could be default, and disabled if needed.
Thanks, and please let me know if you need more info. :)
Hi,
As per documentation, we can use
// ... LIMIT 10
$statement->limit(10);
But if you go to LimitClause.php file, there is check "if (!is_int($number) || !is_int($offset)) {" which throws error if we do not pass 2nd argument. 👎
And btw event I pass both the arguments, there is not result. Please fix this.
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.