4

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).

  • Is it possible that your table lacks an "id" field with autoincrement? – Riccardo Zorn Dec 23 '14 at 13:28
2

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.

7

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;
  • This return also 0, i tried again but the same problem... – xxx Dec 22 '14 at 14:12
  • What version of Joomla are you using? Cause I've just tested this and it works perfectly fine for me – Lodder Dec 22 '14 at 14:17
  • Joomla 2.5.16, php: 5.5.9, everytime i get 0 as value. – xxx Dec 22 '14 at 14:22
  • 1
    I'm not going to try replicating this on Joomla 2.5.16. You need to update to Joomla 2.5.28 as you're 12 versions behind in the 2.5 series – Lodder Dec 22 '14 at 14:25
  • I updated it but there is still the same problem, i controlled also db version is the same like joomla 2.5.28, is this statement correct: $db = JFactory::getDbo(); ? – xxx Dec 22 '14 at 14:46
1

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.

  • 1
    you voiced my suspicions well – RozzA Aug 6 '17 at 21:42
  • Umm...Shouldn't the last insert ids and things like that, be isolated to their respective connection in mysql out of the box? Thats what the "I" in ACID Compliance stands for. Or am I missing something? – Mohd Abdul Mujib Oct 11 '18 at 21:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.