Get all inserted IDs when inserting multiple rows

Question

I've already looked at other answers and I still feel that my question is relevant and deserves a separate entry.

I have a table named settings(which stores user settings) and I have to insert multiple settings for each user. Initially, I had executed a separate insert statement for each setting, but having felt this wasn't a particularly good way to do it, I thought of inserting multiple rows by the same insert statement. My only problem is that I want the auto_incremented IDs of each of the newly inserted rows.

I've read answers that say this isn't possible/scalable etc, but I feel that I have hit upon the solution. I want feedback whether my way is correct or not and hence this question.

What I've done is simple. After inserting the multiple rows, I call last_insert_id() to get the ID of the first row of the simultaneously inserted rows. I already have the count of the number of rows inserted, so I simply create a new array and populate it with IDs starting at last_insert_id() and ending at last_insert_id()+n-1 (where n is the number of rows inserted).

I feel this will work because of the following reasons:

1.) MYSQL documentation states that last_insert_id() is connection dependent and if another client/connection inserts new records, then that won't affect other client's last_insert_id().

2.) I feel that as the insert is being done by a single SQL statement, the whole insertion should be treated as a single transaction. If that is true, then ACID rules should apply and the auto_incremented values should be sequential. I'm not sure about this one.

Those are my reasons why I feel the logic should work. So my question is, will the above logic work for ALL conditions? Can I rely on it to work correctly in all situations? I know it is working for me currently.

Answer 1

Something has been bugging me about this question...why are you needing the insert_id of each row? It just seems to me if you are inserting into the settings table then to correlate the settings to the user it would be better to add some sort of user key to the table. I'm probably just not seeing the whole picture, but this is the idea that I'm getting:

+---------------------------------------+
|  `settings`                           |
+------+--------+-----------+-----------+
|  id  |  user  |  setting  |  value    |
+------+--------+-----------+-----------+
|  `id` INT(11) PRIMARY AUTO_INCREMENT  |
+---------------------------------------+
|  `user` INT(11)                       |
+---------------------------------------+
|  `setting` VARCHAR(15)                |
+---------------------------------------+
|  `value` VARCHAR(25)                  |
+---------------------------------------+

+---------------------------------------+
|  `users`                              |
+------+--------+--------+--------------+
|  id  |  name  |  email |  etc         |
+------+--------+--------+--------------+
|  `id` INT(11) PRIMARY AUTO_INCREMENT  |
+---------------------------------------+
|  `name` VARCHAR(32)                   |
+---------------------------------------+
|  `email` VARCHAR(64)                  |
+---------------------------------------+
|  `etc` VARCHAR(64)                    |
+---------------------------------------+

My basic thinking here is that what you are doing with the insert_id(s) is then some how trying to create a reference between the users and the settings so you know who set what and you are give them back their settings later.

If I've totally lost the point, please, guide me back on topic and I'll try to come up with another solution, but if I'm hitting anywhere close to where you are trying to go:

If you are indeed trying to correlate the two tables using the insert_id(s) then wouldn't something like the below code make more sense (assuming you have a table structure similar to the above):

I'll assume that $user is a reference to the particular user (users.id).

I'll also assume you have an associative array called $settings which you are trying to put into the database.

$mysqli = new mysqli('host', 'username', 'password', 'database') or trigger_error('[MYSQLI]: Could not connect.');

if ($mysqli)
{
    foreach ($settings AS $setting_name=>$setting_value)
    {
        // I'll do individual queries here so error checking between is easy, but you could join them into a single query string and use a $mysqli->multi_query();
        // I'll give two examples of how to make the multi_query string below.
        $sql = "INSERT INTO `settings` (`id`, `user`, `setting`, `value`) VALUES ('', $user, '$setting_name', '$setting_value')";
        $mysqli->query($sql) or trigger_error('[MYSQLI]: ' . $mysqli->error . '['.$sql.']';
    }
    $mysqli->close() // Since we know in this scope that $mysqli was created we need to close it when we are finished with it.
}

Now when you need the user's settings you could do a SELECT with a JOIN to put all the settings and the user info together and forgive me if I louse this bit up because I am by far not a mysql(i) expert, so I'm not sure if you'll need to do anything special being there are multiple entries in the settings table and a single entry in the users table, but I'm sure someone can set us both straight if I screw this up:

$mysqli = new mysqli('host', 'username', 'password', 'database') or trigger_error('[MYSQLI]: Unable to connect.');

if ($mysqli)
{

    $sql = "SELECT `users`.`name`, `users`.`email`, `users`.`id`, `users`.`etc`, `settings`.`setting`, `settings`.`value` FROM `settings` JOIN (`users`) ON (`users`.`id`=`settings`.`user`) WHERE `settings`.`user`=$user GROUP BY `settings`.`user` ORDER BY `settings`.`user` ASC";

    if ($result=$mysqli->query($sql))
    {
        if ($result->num_rows == 0)
            echo "Uh oh! $user has no settings or doesn't exist!";
        else
        {
            // Not sure if my sql would get multiple results or if it would get it all in one row like I want so I'll assume multiple which will work either way.
            while ($row=$result->fetch_array())
                print_r($row);  // Just print the array of settings to see that it worked.
        }
        $result->free(); // We are done with our results so we release it back into the wild.
    } else trigger_error('[MYSQLI]: '.$mysqli->error . '['.$sql.']');
    $mysqli->close(); // Our if ($mysqli) tells us that in this scope $mysqli exists, so we need to close it since we are finished.
}

As I previously stated, I am by far not a mysql(i) expert and there are probably ways to streamline things and I may have made some mistakes with the syntax on my JOIN statement or just used superfluous claus(es) like the GROUP BY. I made the SELECT a pull from settings and joined users to it because I wasn't sure if joining settings to users would make a single result containing users and all the possible values from settings that matched our WHERE clause. I've only ever joined A with B where there was 1 result in each, but I feel confident that a JOIN is in the right general area.

Alternatively to the multiple queries, I said I would give examples of building a single query string for a multi_query, so:

$arr = array();
foreach($settings AS $setting_name=>$setting_value)
{
    $arr[] = "INSERT INTO `settings` (`id`, `user`, `setting`, `value`) VALUES ('', $user, '$setting_name', '$setting_value')";
}
$sql = join('; ', $arr);

or

foreach($settings AS $setting_name=>$setting_value)
{
    $sql = ( isset($sql) ) ? $sql.'; '."INSERT INTO `settings` (`id`, `user`, `setting`, `value`) VALUES ('', $user, '$setting_name', '$setting_value')" : "INSERT INTO `settings` (`id`, `user`, `setting`, `value`) VALUES ('', $user, '$setting_name', '$setting_value')";
}

One last thing I want to note: Unless you are specifically needing multiple entries of the same setting for the user, Then you could do an UPDATE before your INSERT query and only do the INSERT if the resulting $mysqli->insert_id == 0. If you are attempting to keep a history of the settings changes for users and that is why you need multiple entries then I would suggest creating a separate table with a log containing a table structure like:

+--------------------------------------------------+
|  `logs`                                          |
+------+--------+--------+-----------+-------------+
|  id  |  time  |  user  |  setting  |  new_value  |
+------+--------+--------+-----------+-------------+
|  `id` INT(11) PRIMARY AUTO_INCREMENT             |
+--------------------------------------------------+
|  `time` TIMESTAMP                                |
+--------------------------------------------------+
|  `user` INT(11)                                  |
+--------------------------------------------------+
|  `setting` INT(11)                               |
+--------------------------------------------------+
|  `new_value` VARCHAR(25)                         |
+--------------------------------------------------+

If you make the default of time the CURRENT_TIMESTAMP or just insert date('Y-m-d G:i:s') into that field then you can keep track of changes to settings by inserting into the log when new settings are created or changed.

To do the INSERTS if the UPDATE didn't change anything you could use two separate statements. It is also possible to do it with "INSERT VALUES() ON DUPLICATE KEY UPDATE ... ", but this method is apparently not recommended on tables with multiple UNIQUE fields. To use the latter method would mean a single query, but you would have to make the combination of user and setting UNIQUE (or maybe DISTINCT?). If you made setting UNIQUE then you would only be able to have one setting='foo' in the table unless I am mistaken. To do it with two statements you would do something like:

$sql = "UPDATE `settings` SET `value`='bar' WHERE `user`=$user AND `setting`='foo' LIMIT 1";
$mysqli->query() or trigger_error('[MYSQLI]: ' . $mysqli->error . '['.$sql.']');
if ( $mysqli->affected_rows == 0 )
{
    $sql = "INSERT INTO `settings` (`id`, `user`, `setting`, `value`) VALUES ('', $user, 'foo', 'bar')";
    $mysqli->query($sql) or trigger_error('[MYSQLI]: ' . $mysqli->error . '['.$sql.']');
}

In the above code you could substitute $mysqli->insert_id for $mysqli->affected_rows if you prefer, but the end result is the same. The INSERT statement is only called if the UPDATE didn't change anything in the table (which would indicate that there were no records for that setting and that user).

I apologize that this is a very lengthy reply and it strayed from your original question, but I hope it is relavent to your true purpose/goal. I look forward to your response and to the comments about ways to improve my SQL statements from the true SQL masters.