Get last inserted record id

[montyonthebonty]

Hi. I'm building a project using OOP and PHP to write to a database. I have a dbHandler class:

class dbHandler {

  protected function connect() {
    try {
      $usrName = 'username';
      $password = 'password';
      $dbh = new PDO('mysql:host=localhost;dbname=dbname', $usrName, $password);
      return $dbh;
    }
    catch (PDOException $e) {
      print 'Error: ' . $e->getMessage() . '<br />';
      die();
    }

  }

}

This is called from an update class:

class update extends dbHandler {

  protected function addUpdate($success) {

    $statement = $this->connect()->prepare('
     INSERT INTO tblScheduleUpdates (updSuccess)
     VALUES (?)');

     if (!$statement->execute(array($success))) {

       header('location: ../sqlerror.php');
       exit();
     }

     $last_id = $dbh->lastInsertId();
    
     echo 'Record ID: ' . $last_id . '<br />';

	$statement = null;

  }

}

But I need to find the ID (Autonumber) of the last record inserted. How do I get this?

Thanks
Chris

[ginerjm]

To answer your question I went to the Go To resource for everything PHP & PDO  - the manual.

BTW - might be a good idea to add a parm to your connection logic to allow you to pass the dbname into it

[montyonthebonty]

Good afternoon

I did look there, but I can't work out what I'm doing wrong - to be fair, I got the basic code from a tutorial on building a login system, so I'm a bit sketchy on what the different objects are.

I'm pretty sure lastInsertId() is the right one, but most examples use something like $conn->lastInsertId() and I can't see a connection object in my code, if that makes sense? I'm not very new to PHP, but quite new to using PHP for OOP.

Good point about passing the database name in though - I will modify that.

Thanks

Chris

[ginerjm]

To clarify  here is an example from the PHP manual:

$stmt = $dbh->prepare($sql);
$stmt->bindParam(':val', $val, PDO::PARAM_INT);
$stmt->execute();
$lastId = $dbh->lastInsertId();

I found this with the following:

https://www.php.net/manual/en/pdo.lastinsertid.php

[kicken]

4 hours ago, montyonthebonty said:

I can't see a connection object in my code, if that makes sense?

Your connection object is the $dbh variable you create in your connect method and then return.  That variable is only accessible in your connect method though, not your addUpdate method. As a result, where you have your lastInsertId call $dbh is null and you'll get an error.

Since you're returning $dbh from your connect method, you can save it to the same variable name in your addUpdate method by splitting your connect call from your prepare call like so.

$dbh = $this->connect();
$statement = $dbh->prepare('
     INSERT INTO tblScheduleUpdates (updSuccess)
     VALUES (?)');

Now your $dbh variable is defined and you can use it to call the lastInsertId method.

 

[montyonthebonty]

Thanks, that's perfect - that's the bit I was missing!

Really appreciate your help.

Chris.