PDO problem with database connection?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
fando
Forum Newbie
Posts: 3
Joined: Sat Jun 25, 2011 7:09 pm

PDO problem with database connection?

Post 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
User avatar
Zyxist
Forum Contributor
Posts: 104
Joined: Sun Jan 14, 2007 10:44 am
Location: Cracow, Poland

Re: PDO problem with database connection?

Post 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?
fando
Forum Newbie
Posts: 3
Joined: Sat Jun 25, 2011 7:09 pm

Re: PDO problem with database connection?

Post 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
User avatar
Zyxist
Forum Contributor
Posts: 104
Joined: Sun Jan 14, 2007 10:44 am
Location: Cracow, Poland

Re: PDO problem with database connection?

Post 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.
fando
Forum Newbie
Posts: 3
Joined: Sat Jun 25, 2011 7:09 pm

Re: PDO problem with database connection?

Post 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
User avatar
Zyxist
Forum Contributor
Posts: 104
Joined: Sun Jan 14, 2007 10:44 am
Location: Cracow, Poland

Re: PDO problem with database connection?

Post 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.
Post Reply