0

I am trying to get the last inserted id in the database and use that to insert another record(s), in this case, associated images with a particular record. Note: I am using MVC and this is in the model. These 2 operations should happen straight after each other i.e.: the user does't see that there are 2 different inserts happening at the same time.

This is the initial insert:

public function addAccom($data)
    {
        $this->db->query("INSERT INTO `accommodation` (`name`, `description`, `category`, `slug`) VALUES (:name, :description, :category, :slug)");
        $this->db->bind(":name", $data['name']);
        $this->db->bind(":description", $data['description']);
        $this->db->bind(":category", $data['category']);
        $this->db->bind(":slug", $data['slug']);
        if($this->db->execute()){
        $id = $this->db->lastInsertId();
            return true;
        } else {
            return false;
        }
    }

I don't know if this line is correct in the above:

$id = $this->db->lastInsertId();

After this insert, I need to insert more records into another table using that id.

Should I try run another db query in the same query as this or create another query?

This is the second query I had:

public function addAccomPic($data)
{
    $this->db->query("INSERT INTO `accom_pics` (`accom_id`, `pic_name`) VALUES (:accom_id, :pic_name)");
    $this->db->bind(":accom_id", $data['accom_id']);
    $this->db->bind(":pic_name", $data['pic_name']);
    if($this->db->execute()) {
        return true;
    } else {
        return false;
    }
}

Database:

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

    private $dbh; // Is this the same as $link ?
    private $stmt;
    private $error;

    public function __construct() {
        $dsn = "mysql:host=$this->host;dbname=$this->dbname";
        $options = array(
            PDO::ATTR_PERSISTENT => true,
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
        );

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

        } catch(PDOexception $e) {
            $this->error = $e->getMessage();
            echo $this->error;
        }
    }
Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
user8463989
  • 2,275
  • 4
  • 20
  • 48
  • $this->db->insert_id is what you looking for and pass that id to your other method – Roljhon Dec 06 '17 at 17:03
  • is this mysqli? what sql api you're using? var_dump your $this->db – Roljhon Dec 06 '17 at 17:06
  • I am using PDO. – user8463989 Dec 06 '17 at 17:06
  • ops thats for mysqli, sorry didn't bother to ask, then you're correct, and just pass it to your next method – Roljhon Dec 06 '17 at 17:07
  • Getting error: `Uncaught Error: Call to undefined method Database::lastInsertId()` – user8463989 Dec 06 '17 at 17:09
  • not familiar with PDO yet but query and bind? or should it be prepare and bind? – Roljhon Dec 06 '17 at 17:09
  • Possible duplicate of [PDO get the last ID inserted](https://stackoverflow.com/questions/10680943/pdo-get-the-last-id-inserted) – Rotimi Dec 06 '17 at 17:10
  • I don't think I need to bind it because it is only created once the database record is inserted? – user8463989 Dec 06 '17 at 17:10
  • @Akintunde, not sure this is a duplicate because I am using MVC? – user8463989 Dec 06 '17 at 17:11
  • this is a dup. check the answer there. You are obviously using a custom class in the `$this->db`. show where `$this->db` comes from. the class i mean – Rotimi Dec 06 '17 at 17:12
  • @user8463989 no dude, you do it like this http://php.net/manual/en/pdostatement.bindparam.php what I mean is binding is you're binding your value into a param inside your statement/query – Roljhon Dec 06 '17 at 17:12
  • @Akintunde, `private $db; public function __construct() { $this->db = new Database; }` – user8463989 Dec 06 '17 at 17:23
  • 1
    Please, stop making "database classes". You already have one: PDO. – tereško Dec 06 '17 at 19:58
  • this code is not PDO, so removing the tag. and yes, I have no idea why people are so eager to create a database class if they have no idea how to use it afterwards – Your Common Sense Dec 06 '17 at 20:24
  • I have resolved the issue after spending lots of time on google. I am wrapping PDO in a database class it would seem which apparently isn't the best thing to do. That is what I learned through a tutorial so perhaps I learned a bad practice there? But anyway, I shouldn't be using stmt->, I should be using return $this->dbh->lastInsertId(); It now works. – user8463989 Dec 07 '17 at 08:57

1 Answers1

0

The first thing i see, it's $id is a local variable. Try to set it as global:

public $id = null;

public function addAccom($data)
{
    $this->id = $this->db->lastInsertId();
}
Stefano Spkg Gagliardi
  • 157
  • 1
  • 4