Page 1 of 1

PDO problem with database connection?

Posted: Sat Jun 25, 2011 7:45 pm
by fando
Hi there,

I've looked everywhere for an answer to this so hopefully one of you wise people can help me out.

Since changing web servers in the last couple of weeks none of my database driven code seems to be working. I've identified a work around but I'm not sure why the problem is occurring.

Here's a simple example to explain the problem:

the db.inc file:

Code: Select all

try {
    $db = new PDO('mysql:host=localhost;dbname=wedding', $user, $pass);
} catch (PDOException $e) {
    print "Error!: " . $e->getMessage() . "<br/>";
    die();
}
My login script below:

Code: Select all

include 'db.inc';

// first time accessing the database
$sql = 'SELECT COUNT(*) FROM users WHERE guest_email = ?;';
$pds = $db->prepare($sql);
$pds->execute(array($email));
		
if($pds->fetchColumn() == 1) {
  
  // second time accessing the database
  $sql = 'SELECT * FROM users WHERE guest_email = ?;';
  $pds = $db->prepare($sql);
  $pds->execute(array($email));
}
The above worked on the old web server and currently works on my testing environment. All I had to do is include the database connection once at the top of the page and I could reuse the $db object multiple times.
The only way to get this to work on the new web server, however, is to re include the db.inc file every time i access the database.

See below:

Code: Select all

include 'db.inc';

// first time accessing the database
$sql = 'SELECT COUNT(*) FROM users WHERE guest_email = ?;';
$pds = $db->prepare($sql);
$pds->execute(array($email));
		
if($pds->fetchColumn() == 1) {

  // now i need to re-include the db connection before i can access again
  include 'db.inc';

  // second time accessing the database
  $sql = 'SELECT * FROM users WHERE guest_email = ?;';
  $pds = $db->prepare($sql);
  $pds->execute(array($email));
}
Any ideas?

Web Server is running php v5.1.6
Test environment is running php v5.3.0

Cheers,
fando

Re: PDO problem with database connection?

Posted: Sun Jun 26, 2011 2:05 am
by Zyxist
If you need some help, you should explain what you mean by "not working". Does it throw any exceptions? Any errors? Maybe you should point some places?

Re: PDO problem with database connection?

Posted: Sun Jun 26, 2011 3:39 am
by fando
This is the error i get:

"Fatal error: Call to a member function execute() on a non-object in ...login.php on line 28"

which relates to:

Code: Select all

include 'db.inc';

// first time accessing the database
$sql = 'SELECT COUNT(*) FROM users WHERE guest_email = ?;';
$pds = $db->prepare($sql);
$pds->execute(array($email));
               
if($pds->fetchColumn() == 1) {
 
  // second time accessing the database
  $sql = 'SELECT * FROM users WHERE guest_email = ?;';
  $pds = $db->prepare($sql);

// THE ERROR IS RELATING TO THIS LINE
  $pds->execute(array($email));
}
Thanks again,
fando

Re: PDO problem with database connection?

Posted: Sun Jun 26, 2011 4:12 am
by Zyxist
PDO::prepare() returns false, if there is a problem with a query, and you have exceptions turned off. Set the PDO error handling to exceptions, and you will get a nice, and quite exact message, what is wrong with the query and where.

I suppose that the problem lies in the semicolon at the end of the query. Semicolons are commonly used in SQL scripts to separate the queries, but they are not a part of the query itself. PDO accepts a query and nothing more.

Re: PDO problem with database connection?

Posted: Mon Jun 27, 2011 9:29 pm
by fando
The web master upgraded the PHP version to 5.3 and it fixed all my problems.

For the record, I believe the problem had to do with the use of PDO::execute() and the database looking to query further results.
I believe the correct way to fix this problem was to use PDOStatement::closeCursor() before using another PDO::prepare() or PDO::execute().

However, for some reason, with the update to php v5.3 all my code seems to work without needing to call PDOStatement::closeCursor().

Now i'm not sure if this is a bug in 5.1.6 that got fixed or if i'm not releasing the database resource correctly before making another call to the PDOStatement object?

Any insight appreciated.

cheers,
fando

Re: PDO problem with database connection?

Posted: Tue Jun 28, 2011 1:06 am
by Zyxist
I think that this was a database issue, not PHP. Generally speaking, closeCursor() should ALWAYS be called. I've been using it since I remember and migration to PHP 5.3 caused absolutely no problems.