Getting last insert id and inserting it into db php MVC

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;
        }
    }

Open in new window


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

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

Open in new window


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", $id);
    $this->db->bind(":pic_name", $data['pic_name']);
    if($this->db->execute()) {
        return true;
    } else {
        return false;
    }
}

Open in new window


The error I get is:

Uncaught Error: Call to undefined method Database::lastInsertId()
LVL 1
Black SulfurAsked:
Who is Participating?
 
Black SulfurConnect With a Mentor Author Commented:
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.
0
 
Pawan KumarDatabase ExpertCommented:
Please use like this -

$id = mysql_insert_id();
0
 
Black SulfurAuthor Commented:
Pawn, I am using PDO and OOP, will that still work?
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
zephyr_hex (Megan)DeveloperCommented:
Side note:  you should consider logic that handles the instance where the first insert succeeds, but one of the subsequent inserts fails.  Should the first insert get rolled back, or ???
1
 
Pawan KumarDatabase ExpertCommented:
sorry..you need to use like below for PDO.

$id = $db->lastInsertId();
0
 
Black SulfurAuthor Commented:
Still getting the same error. I probably should have put the whole class in my question... Here it is:

class Adminaccom {
	
	private $db;
	
	public function __construct()
	{
		$this->db = new Database;
	}
	
	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;
		}
	}
}

Open in new window

0
 
Black SulfurAuthor Commented:
@zephyr_hex (Megan), that is a great point. If you have any code suggestions for that please let me know!
0
 
Pawan KumarDatabase ExpertCommented:
Query your DB again to fetch the latest ID..

"SELECT MAX(Id) From yourtableName " ...
0
 
Black SulfurAuthor Commented:
@Pawan Kumar, I hear you. But that seems like a compromise because I can't get $id = $this->db->lastInsertId(); to work.
0
 
zephyr_hex (Megan)DeveloperCommented:
NOOOO!  Do not rely on MAX(id).  That's a horrible approach and prone to giving you the wrong Id if someone else does an insert.
1
 
Pawan KumarDatabase ExpertCommented:
Just try this - ( do not add/substract anything..)

$db->lastInsertId()
0
 
zephyr_hex (Megan)DeveloperCommented:
The way I would handle this would be to use a Stored Procedure.  That way, you can have SQL give you the inserted id, AND, you can wrap all of your insert commands in a TRY / CATCH, that, when it fails, does a transaction rollback.

The general structure:
DECLARE @id INT
TRY
     BEGIN TRANSACTION
        //...do first insert
        SET @id = SCOPE_IDENTITY();
        //do other inserts using @id
     COMMIT TRANSACTION
END TRY
CATCH
     IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION
END CATCH

Open in new window

0
 
Pawan KumarDatabase ExpertCommented:
@zephyr_hex (Megan)

This is not SQL Server. It is mySQL Server
0
 
Black SulfurAuthor Commented:
@Pawan, that gives me error:

Undefined variable: db
0
 
Pawan KumarDatabase ExpertCommented:
I think we are doing something incorrect. Please try this simple statements..

$s = $db->prepare("INSERT INTO `accommodation` (`name`, `description`, `category`, `slug`) VALUES (:name, :description, :category, :slug)");
$s->execute();
$lastid = $db->lastInsertId();
0
 
zephyr_hex (Megan)DeveloperCommented:
Ah, ok.  Well, I'd still use a stored procedure.

BEGIN
     DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING
     BEGIN
         ROLLBACK;
     END;
     
     START TRANSACTION;
          //do first insert
       SET @id = SELECT LAST_INSERT_ID();
         //do other inserts
     COMMIT;
END

Open in new window

0
 
Black SulfurAuthor Commented:
@ Pawan, I can't just use $lastid = $db->lastInsertId(); because I must use $this->db
0
 
Pawan KumarDatabase ExpertCommented:
Great , thats what ..the missing link.. :)
0
 
zephyr_hex (Megan)DeveloperCommented:
The OP should select comment  42395192 as the solution.
0
 
Black SulfurAuthor Commented:
I solved the issue
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.