0

I've been using the below as the way I connect to a MySQL database and how I query it for awhile now. It seems to be a little bit of a different set up from a lot of the examples I see online. I can't seem to figure out how to get lastInsertId() to work with this setup:

define("DB_HOST", "localhost");
define("DB_NAME", "mydatabase");

$link = new DBLink("username", "password");

class DBLink {

  private $host = DB_HOST;
  private $dbname = DB_NAME;
  private $user;
  private $pass;

  private $dbConnection;
  private $error;

  private $sql;

  public function __construct($user, $pass) {

  $this->user = $user;
  $this->pass = $pass;

  $dsn = 'mysql:host=' . $this->host . ';dbname=' . $this->dbname;
  $options = array(
      PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
      PDO::ATTR_EMULATE_PREPARES => false,
      PDO::ATTR_ERRMODE  => PDO::ERRMODE_EXCEPTION
    );

   try {
      $this->dbConnection = new PDO($dsn, $this->user, $this->pass, $options);
    }

    catch(PDOException $e){
      $this->error = $e->getMessage();
      echo $this->error;
    }
  }

public function query($query) {
  $this->sql = $this->dbConnection->prepare($query);
}

public function bind($param, $value) {
  $this->sql->bindParam($param, $value);
}

public function execute() {
  return $this->sql->execute();
}

Then I'll add a new row to the database like this:

 $link->query("INSERT INTO users (username, phone, email)
 VALUES (:username, :phone, :email)");

 $link->bind(':username', $username);
 $link->bind(':phone', $phone);
 $link->bind(':email', $email);
 $link->execute();

After adding a row, I want to get the id of the row that was inserted into the database. I've tried:

$link->lastInsertId();

$link->$sql->lastInsertId();

Writing a separate function with it then having the function inside the DBLink class:

$link->getLastID();

public function getLastID() {
   return $this->sql->lastInsertId();
}

And other wild guesses, such as 

 $this->dbConnection->lastInsertId();

I also tried all the above variations with insert_id instead of lastInsertId() because I saw that recommended in a few places.

And a few other things, I've wasted about 3 hours on it. Nothing seems to work to return the last inserted id. I've looked at a lot of examples online, but they all do the database query a little differently from how I have it. I believe lastInsertId() is supposed to be on the PDO object, I'm just not sure how to call that the way I have my queries and database link set up. Can anyone help?

Edit: Could it be I don't have the MySQL database set up properly? The primary key for that table is id. Is that how lastInsertId() knows which column to get the "id" from, vs any other column?

John
  • 473
  • 5
  • 20
  • Try `$link = $sql->lastInsertId();` with `=` instead of `->` – Michel Dec 23 '18 at 09:12
  • Doesn't seem to work – John Dec 23 '18 at 09:29
  • actually `$id = $this->dbConnection->lastInsertId();` is the currect way to do this, duplicate of https://stackoverflow.com/questions/10680943/pdo-get-the-last-id-inserted , programming is the art of googling not guessing!!! – Exlord Dec 23 '18 at 09:31
  • I get this error: Uncaught Error: Using $this when not in object context – John Dec 23 '18 at 09:36
  • Thanks, that helped me get it, with this: public function execute() { $ret = $this->sql->execute(); $this->lastInsertID = $this->dbConnection->lastInsertId(); return $ret; } – John Dec 23 '18 at 14:40

1 Answers1

1

I think I used something like this. In the execute() use

public function execute() {
  $this->lastInsertID = null;
  $ret = $this->sql->execute();
  $this->lastInsertID = $this->dbConnection->lastInsertId();
  return $ret;
}

define a variable in the class

private $lastInsertID;

and then

public function getLastID() {
   return $this->lastInsertID;
}

this ensures that the last insert ID is retrieved just after the SQL is executed.

Nigel Ren
  • 56,122
  • 11
  • 43
  • 55
  • I'm getting "Call to undefined method DBLink::lastInsertId()" Stack trace: #0 /public_html/cms/cms/pages/create-page.php(21): DBLink->execute(). I frequently got the same error before. – John Dec 23 '18 at 09:21
  • Maybe I'm calling it wrong. I'd say: $lastid = $link->getLastID(); ? – John Dec 23 '18 at 09:32
  • Got it, thanks! public function execute() { $ret = $this->sql->execute(); $this->lastInsertID = $this->dbConnection->lastInsertId(); return $ret; } – John Dec 23 '18 at 14:42
  • As you point out - the insertid has to be done on the connection, I will update the answer. – Nigel Ren Dec 23 '18 at 14:45
  • Thanks a lot, I never would have thought to put that in the execute function. That was frustrating! – John Dec 23 '18 at 14:50
  • I think I had some problems with it as I was executing more SQL after and of course not getting a value. Always best to fetch it as soon as possible. Down side is that it always calls it - even for select statements. – Nigel Ren Dec 23 '18 at 14:51