Getting auto_increment value

[eluser]bikuta[/eluser]
Hi guys,

I have a table which has an 'id' field which is auto_increment. Is it possible for me to use Active Record and have it return the 'id' value (after doing an INSERT) or is there another way to get that value without having to do a SELECT query.

The reason I need this is so that I can insert the id into the related tables.

E.g.,
Composer (id, name)
Song (id, title, composerid)

Once I insert a new composer I need the 'id' to be inserted as the 'composerid' in the corresponding Song record.

Just trying to find the best way to do this, because I know MySQL does have a method to retrieve the unique value, i.e., mysql_insert_id(). It would be good if I can do it through Active Record.

[eluser]johnmchilton[/eluser]
From:
ellislab.com/codeigniter/user-guide/database/helpers.html


The first function is:

Code:

$this->db->insert_id()

Use it just after you use $this->db->insert()

[eluser]bikuta[/eluser]
ah oops sorry I didn't look in the helpers page.

Thanks for that!

[eluser]imamiscool[/eluser]
yeah, or if you didn't use active record,
simply use: "SELECT max(id) FROM myTable"

cheezz

[eluser]johnmchilton[/eluser]
If you didn't use active record, you always want to use mysql_insert_id() in case somehow someone else besides the person invoking your script (using another script, or perhaps the same one) snuck in a database insert. imamiscool's method will get you the last record inserted no matter what, whereas mysql_insert_id() will get you the id resulting from the last insertion in the script and instance you're running.

http://us2.php.net/mysql_insert_id

[eluser]penta997[/eluser]
How can I add max value to another foreign key. this method doesn't work.

Code:

function set_order_books($id=1,$ilosc=1)
        {
            $ORDER_ID = $this->db->query("select MAX(ORDER_ID) from orders_ID")->row()->ORDER_ID;
            $BOOK_Price = $this->db->query("select BOOK_Price from books where BOOK_ID=$id")->row()->BOOK_Price;
             $order_books = array(

               'ORDERS_ORDER_ID' => $ORDER_ID,
               'BOOKS_BOOK_ID' => $id,
               'ORDERB_Price' => $BOOK_Price*$ilosc,
               'ORDERB_Quantity' => $ilosc
           );

                 $sob = $this->db->insert('order_books',$order_books);
                 return $sob;
        }

I have a php error

A PHP Error was encountered

Severity: 4096

Message: Object of class stdClass could not be converted to string

Filename: mysql/mysql_driver.php

Line Number: 535

[eluser]bikuta[/eluser]
That's because $ORDER_ID is not a single value but an array object.

So to access the value you need to do something like this:
<code>
$r = $ORDER_ID->row_array();
$max_id = $r['MAX(ORDER_ID)']; // where MAX(ORDER_ID) is the column name
</code>

[eluser]penta997[/eluser]
thanks, I've founded it 2 hours ago