This is an archived copy of the forum for reference purposes

grab insert_id after raw SQL via query()

posted by codehooligans 9 years ago

Greetings all. I've only been using Flourish about a week. Most of the backend database use is for a database management tool. So chose not to use the Flourish ORM parts. I'm mostly using the direct query() calls with my own SQL statements.

So my question that I've spent most of the morning trying to figure out is. How or where is the insert_id returned on an INSERT? The query does execute successfully I can see the new row in my table. I can print_r on the return $result. I see the insert_id field is set but cannot figure out how to access it.

Help.

(Apologies if this is a simple answer. I did search the site for 'insert_id' and found very little information.

Check out fResult::getAutoIncrementedValue().

posted by wbond 9 years ago

Thanks. Yeah forgot to mention I tried the call to getAutoIncrementedValue(). It returns 0. But I know the table has at least 50 row. So the insert_id should be 51.

I did figure out a work around on accessing the insert_id.

$connection = $db->getConnection(); if ($connection) { return $connection->insert_id; }

posted by codehooligans 9 years ago

Can you tell me what database and extension you are running and what the SQL query you called was?

posted by wbond 9 years ago

Sure. The extension is mysql.

$sql_str = "INSERT companies (active, name, contact_user_id, user_type_id) VALUES('1', 'Paul
's Sound Place', '0', '2');"; $result = $hts_settings['db']->query($sql_str); if ($result) { $connection = $hts_settings['db']->getConnection(); if ($connection) { return $connection->insert_id; } }

posted by codehooligans 9 years ago

This ended up being a bug in fDatabase where it would only look for INSERT INTO, whereas MySQL allows the INTO keyword to be omitted. r925 includes an updated version of fDatabase that should fix this.

posted by wbond 9 years ago

Ah. Well I guess I did leave out the INTO. Thanks for the note up the issue tracking. I'll make sure to upgrade. Or better correct my SQL statement.

posted by codehooligans 9 years ago