Joomla DB Insert, how to get last inserted id?

Question

I have to insert my data into 2 different tables, in the first table I have the following query which works perfectly:

$insertProjectUser = 'INSERT INTO #__pmanager_user (user_cms_id, user_email, user_created_ip) VALUES (' . $user->id . ', "' . $user->email . '", "' . $_SERVER['REMOTE_ADDR'] . '")';
$db->setQuery($insertProjectUser );
$db->query();

This table has an auto-incremented column called project_user_id and I need this value immediately.

How can I correctly get the "last inserted id"? I tried more solutions like:

• mysqli_insert_id($db)
• $selectedID = insertid()
• mysqli_insert_id($db)

All these solutions provide only zero 0 value.

---

Updated:

My query is:

$db = JFactory::getDbo();
$insertProjectUser = 'INSERT INTO #__pmanager_user (user_cms_id, user_email, user_created_ip) VALUES (' . $user->id . ', "' . $user->email . '", "' . $_SERVER['REMOTE_ADDR'] . '")';
$db->setQuery($insertProjectUser );
$db->query();
$addressID = $db->insertid();

If I do: echo $addressID, I get nothing (empty string).

Answer 1

It seems that the problem is not related to Joomla. Just Google around for your problem. The fist answer I found is this:

The problem seemed to be in MySQL's phpmyadmin config file PersistentConnections set to FALSE which resulted in a new CONNECTION_ID every time a query was issued - therefore rendering SELECT LAST_INSERT_ID() ineffective.

Answer 2

To get the ID of the last inserted row, you can use insertid() like so:

$db->query();
$lastRowId = $db->insertid();

You can then use $lastRowId where ever you wish in your next query

Update with latest DB coding standards (J2.5)

$db = JFactory::getDbo();
$query = $db->getQuery(true);

$columns = array('user_cms_id', 'user_email', 'user_created_ip');   
$values = array($db->quote($user->id), $db->quote($user->email), $db->quote($_SERVER['REMOTE_ADDR']));

$query->insert($db->quoteName('#__pmanager_user'))
      ->columns($db->quoteName($columns))
      ->values(implode(',', $values));

$db->setQuery($query);
$db->query();

$addressID = $db->insertid();

echo $addressID;

Answer 3

The insertid function, in Joomla 3.7.4 (and in many previous Joomla versions), relies on the extremely unreliable mysqli_insert_id PHP function. On extremely active sites, this function will mostly return 0 or an inaccurate number. Let me explain a bit:

• If the last activity on the database is an insert or an update activity and the table structure has an autoincrement column, then the value of that column is returned.

• If the last activity on the database is a select activity, for example, then the function will return 0.

• If there was another insert/update subsequent activity after your insert, then the function will return the ID of the autoincrement column of the last insert/update.

The problem with this whole thing is not really Joomla, but MySQL, which doesn't have a reliable method to get the last ID inserted. The most reliable (but still far from ideal) method to get the last inserted ID is to query the table for a unique value. For example, if you are inserting a contact with a unique email, then you will need to query the table for his email.

Hope this helps, and if someone has better information on the subject, then feel free to comment or edit this answer.