CODEWITHSUNDEEP
phpmysqlsqlpdorequest
dev691
dev691

Reputation: 1114

PHP-PDO-Mysql: Can't execute two requests in the same page

I'm moving a php application to a new web server. In the origin server, the following code is working, but it is not in the new server.

$request=$connexion->prepare("SELECT * FROM access_control ");
$request->execute(); 

$request2=$connexion->prepare("SELECT * FROM tplmail ");
$request2->execute();`

The first request always works. I tried to reverse the two requests, but the script always stop at the second execute. In the others scripts, there is the same problem, I can't do two executes in the same page.

Upvotes: 1

Views: 1636

Answers (1)

Your Common Sense
Your Common Sense

Reputation: 157870

Your problem #1 is lack of error reporting. Without seeing an error message you will be unable to do anything

To be able to see database errors, one have to set PDO errmode to exceptions. Exceptions are better than regular errors in many ways: they always contains a stack trace, they can be caught using try..catch or handled using dedicated error handler. And even unhandled, they act as regular PHP errors providing all the important information, following site-wide error reporting settings.

Note that setting this mode as a connection option will let PDO throw exceptions on connection errors too, which is very important.
So, here is an example for creating a PDO connection right way:

$dsn = "mysql:host=$host;dbname=$db;charset=utf8";
$opt = array(
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    // other options 
);
$pdo = new PDO($dsn, $user, $pass, $opt);

Connecting this way, you will be always notified of all database errors, occurred during query execution. Note that you have to be able to see PHP errors in general. On a live site you have to peek into error logs, so, settings have to be

error_reporting(E_ALL);
ini_set('display_errors',0);
ini_set('log_errors',1);

while on a local development server it's ok to make errors on screen:

error_reporting(E_ALL);
ini_set('display_errors',1);

and of course you should never ever use error suppression operator (@) in front of your PDO statements.

Also, due to many bad examples telling you to wrap every PDO statement into try..catch block, I have to make a distinct note:

DO NOT use try..catch operator just to echo an error message. Uncaught exception is already excellent for this purpose, as it will act just the same way as other PHP errors - so, you can define the behavior using site-wide settings - so, you will have your error message without this useless code. While unconditionally echoed error message may reveal some sensitive information to a potential attacker, yet confuse a honest visitor.

  • A custom exception handler could be added later, but not required. Especially for new users, it is recommended to use unhandled exceptions, as they are extremely informative, helpful and secure.
  • Use try..catch only if you are going to handle the error itself - say, to rollback a transaction.

Your problem #2 is a buffering state which prevent second query from execution.

Adding 

PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => TRUE,

to the init array should solve the problem

Or you can just fetch all the data from the first query before firing the second

Upvotes: 5

Related Questions