Use last inserted id to insert query

Question

I have a query like this:

<?php   
$this->db->query("INSERT INTO customer SET email = '" . $this->db->escape($data['email']) . "'");
?>

And after this i need to make an INSERT to another table like this:

$this->db->query("INSERT INTO order SET customer_id = '" . //here goes the id i need from previous query ."'";

In my customer table i have only one auto-increment column called customer_id and after first insertion to this table i need to get this customer_id value and use it to the next insertion (to order table).

How can i do this? I read about

$mysqli->insert_id

but all the examples connect to a database whereas i am already connected to database and run queries with this->db. Any help about syntax or other solution is really appreciated!

Answer 1

With the below code you can get the last inserted id, so you need use it after your 1st insert and before your 2nd insert.

$insert_id = $this->db->insert_id();

An example would be:

<?php   
$this->db->query("INSERT INTO customer (email) values ('".$this->db->escape($data['email']."')";
$insert_id = $this->db->insert_id();
$this->db->query("INSERT INTO `order` (customer_id) values ({$insert_id})";
?>

$insert_id is an integer, so you dont need to escape it again. order is a reserved word though, so you might want to change your table name.

Answer 2

you will get the last insert id as follows..

<?php
$this->db->query("INSERT INTO customer SET email = '" . $this->db->escape($data['email']) . "'");    
$id=$this->db->insert_id();
?>

Normally the insert_id() function returns the last inserted id in an auto-incrementing Mysql column. It will work most of the time but for that one instance where it is not working, you will be lucky to find out in time(like I did)

$this->db->insert(table_name, $data);
return $this->db->insert_id();

In the event that it malfunctions, the quick-fix is to go raw, like below:

$this->db->insert(table_name, $data);
$query = $this->db->query('SELECT LAST_INSERT_ID()');
$row = $query->row_array();
return $row['LAST_INSERT_ID()'];

Answer 3

Actually your insert query is wrong, so used below code

 <?php   
    $query = "INSERT INTO customer ( email ) VALUES ('" .$this->db->escape($data['email']) . "')";
    $this->db->query($query );
    $id=$this->db->insert_id();
    ?>