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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

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
Black SulfurAuthor 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.