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

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

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Libraries & Helpers (https://forum.codeigniter.com/forumdisplay.php?fid=22)
+--- Thread: How to get the last value from a sequence in MySQL? (/showthread.php?tid=42987)

How to get the last value from a sequence in MySQL? - El Forum - 06-25-2011

[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 Wink

Thanks in advance!!
Carlos

How to get the last value from a sequence in MySQL? - El Forum - 06-25-2011

[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

How to get the last value from a sequence in MySQL? - El Forum - 06-26-2011

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

How to get the last value from a sequence in MySQL? - El Forum - 06-28-2011

[eluser]marjune[/eluser]
or use max

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

How to get the last value from a sequence in MySQL? - El Forum - 06-30-2011

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

Code:
$this->db->insert_id();

The other alternatives are complete nonsense.