PDO::exec() or PDO::query()?
Solution 1
When using PDO::EXEC
the result returned is not of an PDOStatement
but an integer of the rows affected.
When using PDO::QUERY
the result returned is a PDOStatement
.
So the answer is it depends on what you need to do with the data, if you need to run query and not do anything with the results, then you should use exec
to execute the query, otherwise if you need the number of rows, the data returned you should use pdo::query
and then use the results returned by the call.
in regards to the bug there are several work around that you can take
- Install
PDO_MYSQL
- Replace
MYSQL_ATTR_INIT_COMMAND
with1002
- Update your PHP To the latest stable release where it has been passed and patched.
the second issue may have some issues on 64bit's OS's and Some windows configurations.
Bug Information: http://bugs.php.net/bug.php?id=47224
Solution 2
PDO::exec() should be used for queries that do not return a resultset, such as a delete statement or 'set'. PDO::query() should be used when you expect a resultset to be returned. It returns a PDOStatement object to you that you can iterate over to get the individual rows. Note though that if you're using data from an untrusted source in your queries prepared statements would be the best way to go for either kind of query (but you probably knew that).
So, in your case PDO::exec() would be right. Are you sure though that passing the set names command to PDO::__construct() as the last value doesn't work? It works for me and I have PHP 5.3 on Windows. Could you post some more sample code of what you're doing?
Dmitri
Developer of Bind-DI - Open source Dependency Injection Framework in TypeScript Looking for new developers to join Source available on github
Updated on February 13, 2020Comments
-
Dmitri about 4 years
I used to have this as one of the options (4th param) passed to PDO constructor:
$aOptions[PDO::MYSQL_ATTR_INIT_COMMAND] = "SET NAMES utf8";
But just found that it does not work on certain php versions on Windows (does not work in 5.3) due to some bug.
Now I need to run
SET NAMES utf8
using either$pdo->exec("SET NAMES utf8");
or
$pdo->query("SET NAMES utf8");
right after the instantiating the pdo object. So, which one should I use - exec() or query()?
-
Dmitri about 13 yearsYes, I'm sure. I am getting this: PHP Fatal error: Undefined class constant 'MYSQL_ATTR_INIT_COMMAND'
-
RobertPitt about 13 yearsDmitri, There is a bug you should see: bugs.php.net/bug.php?id=47224 Temporary work around would be to use
1002
instad of the constant -
Dmitri about 13 yearsI am on 64 bit Windows, so maybe this is related. Anyway, I will just use $pdo->exec("SET NAMES utf8"); in constructor instead of passing it as argument.
-
RobertPitt about 13 yearsWhat PHP Version do you have ?
-
Jeremy about 13 yearsIt looks like the bug doesn't exist if you use the new mysqlnd driver on Windows. This is what I'm using which is why the bug doesn't exist for me. Mysqlnd is also supposed to offer better performance than the old libmysql driver...