Converting to PDO

PHP
1

Hi there. I’m trying to convert one of my scripts to PDO. I have the database connection and I seem to have got my SELECT statement working but I’m having trouble with the UPDATE. The code takes forever to run and eventually times out. I’ve looked at several tutorials but I still can’t fathom what I’m doing wrong.

$query = 'UPDATE bookings SET date=:date, description=:description WHERE id=:id;';
$stmt = $db->prepare($query);
$stmt->bindParam(':id', $id);
$stmt->bindParam(':date', $date);
$stmt->bindParam(':description', $description);
$stmt->execute();

In case it helps here is my SELECT

$query = 'SELECT * FROM bookings WHERE id=:id;';
$stmt = $db->prepare($query);
$stmt->bindParam(':id', $id);
$stmt->execute();
$row = $stmt->fetch();
$date = $row['date'];
$description = $row['description'];

and here is my connect

try {
  $db = new PDO('sqlite:bookings.sqlite3');
  $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  $db->exec("CREATE TABLE IF NOT EXISTS bookings (id INTEGER PRIMARY KEY, date TEXT, description TEXT);");
}
catch(PDOException $e) {
  echo $e->getMessage();
}

Thanks

2

When working with PDO you should make use of try-catch blocks:

try {
    $sql="
          SELECT
              name
          FROM
              users
          WHERE
              email = :email
      ";
      $stmt = $this->db->prepare($sql);
      $stmt->bindParam(':email', $email);
      $stmt->execute();
    }              
catch (PDOException $e) {
    echo "
    Database Error!
    There was a problem with your database query:
    
    $sql";
    echo 'Reason: ';
    echo $e->getMessage();
}

That’s a quick example.

If you add this line right after the line where you have PDO open the database connection

    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);

Then for every database error a warning error will be displayed.

3

Ah - thanks. So it seems my database is locked which is presumably causing the timeout. Er, how to unlock…

4

Can you please post the error that’s being given?

5

Sure

[B]Warning[/B]:  PDOStatement::execute() [[pdostatement.execute](http://localhost/www/b-and-b/admin/pdostatement.execute)]: SQLSTATE[HY000]: General error: 5 database is locked in [B]C:\\xampp\\htdocs\\www\\b-and-b\\admin\\bookings-new.php[/B] on line [B]57[/B]

[B]Fatal error[/B]:  Maximum execution time of 60 seconds exceeded in [B]C:\\xampp\\htdocs\\www\\b-and-b\\admin\\bookings-new.php[/B] on line [B]59[/B]
6

Are there any points in your script where you’re trying to create a second connection to the database?

Check your database error logs, are there any entries that indicate a server crash at some point?

7

Ah, well. I have deleted the database and let my script recreate it. I have wrapped all my queries in try / catch - and all seems to be well. Thanks for all your help. :slight_smile:

8

Just for info, exceptions will not be raised unless you change:


$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);  
TO
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

Also, don’t go overboard on try/catch blocks. One main block surrounding most of your code usually works well and makes the rest of your code easier to read and maintain.

9

Ah - thanks. I have taken the try/catch statements rather seriously! I’ll have to look again at my code and see if I can wrap the whole lot up with just one!