How to get the last value from a sequence in MySQL?

[eluser]carlos[/eluser]
Hi,

I need some help here with a quite typical problem, but as I'm more used to java and oracle, I don't know if I'm doing it correctly:

1. I have a couple of tables - call them parent and child.
2. Parent has an ID column, which auto increments.
3. I need to insert rows in the child after inserting on the parent, using the last auto-genetated id value in the parent.

I'm doing this:

Code:

$stmt = "SELECT LAST_INSERT_ID() AS LAST_VALUE";
         $last_record = $this->db->query($stmt);
        
         if ($last_record->num_rows() > 0)
        {
           $row = $last_record->row();
        
           log_message('debug', 'Last generated ID: '.$row->LAST_VALUE);
        }

Is this a good way or there is a better one?

You know, once you get user to Oracle's sequence.curr_val and next_vat is difficult to switch

Thanks in advance!!
Carlos

[eluser]John_Betong_002[/eluser]
Try this:

Code:

// http://ellislab.com/codeigniter/user-guide/database/helpers.html
  $this->db->insert_id();


  // or

  // http://php.net/manual/en/function.mysql-insert-id.php
  mysql_insert_id();

  // or

  $sql='SELECT id FROM table ORDER BY id DESC LIMIT 1';

 
 
 
edit: added alternatives

[eluser]carlos[/eluser]
That certainly looks cleaner, thanks!

[eluser]marjune[/eluser]
or use max

Code:

$sql='SELECT MAX(id) As id FROM table';

[eluser]JonoB[/eluser]
The only correct method is to use

Code:

$this->db->insert_id();

The other alternatives are complete nonsense.