Getting the UUID() after INSERT with PHP

Question

I am trying to get the UUID that had just been inserted.

This works in phpMyAdmin. But throws an error in PHP.

$insert = $conn->query(" 

    SET @usr_uuid = uuidToBin(UUID());

    INSERT INTO `users` (`users`.`usr_uuid`) VALUES ( @usr_uuid );

    SELECT HEX(@usr_uuid) AS usr_uuid;
");

However I get this error:

[errno] => 1064
[sqlstate] => 42000
[error] => You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INSERT INTO `users` (`users`.`usr_uuid`) VALUES ( @u' at line 3

How do I go about fixing this?

divide your query to 3 and execute consistently or use multi-query - http://php.net/manual/en/mysqli.multi-query.php — splash58, Dec 09 '18 at 14:06
Maybe just do `INSERT INTO \`users\` (\`users\`.\`usr_uuid\`) VALUES (uuidToBin(UUID()))` and select the `usr_uuid ` via http://php.net/manual/en/mysqli.insert-id.php with another query — user3783243, Dec 09 '18 at 14:39
@splash58 What if there is a new insert by the time I do another query? — Nikk, Dec 09 '18 at 16:24
https://stackoverflow.com/questions/12091971/how-to-start-and-end-transaction-in-mysqli — splash58, Dec 09 '18 at 17:26
Why store it as bin? Why use UUID and not autoincrement ids or a counter? — symcbean, Dec 12 '18 at 12:32

score -1 · Answer 1 · answered Dec 12 '18 at 12:16

You have two options to do it.

The first of all, are using transactions. For example:

$conn->begin_transaction();
$insert = $conn->query("SET @usr_uuid = uuidToBin(UUID());";
$insert = $conn->query("INSERT INTO `users` (`users`.`usr_uuid`) VALUES ( @usr_uuid );";
$insert = $conn->query("SELECT HEX(@usr_uuid) AS usr_uuid;";
$conn->commit();

This option only works if you're using mysqli and innodb storage engine.

Second option, doing two queries:

$insert = $conn->query("INSERT INTO `users` (`users`.`usr_uuid`) VALUES ( uuidToBin(UUID() );";
$insert = $conn->query("SELECT HEX(usr_uuid) AS usr_uuid FROM `users`;";

This option can fall in the problem of having a new insert while you're doing it. But, if the table users have an ID, you can use mysql-insert-id() as suggested by @user3783243

You don't need transactions; variables persist for the session. Your second method will return *all* the records - not just the inserted one. — symcbean, Dec 12 '18 at 12:30
I don't know the table. It's because this, on second option I've suggested the user of mysql-insert-id(), but without an EXPLAIN TABLE I cannot know if there's an AUTO_INCREMENT column to use it. — Sakura Kinomoto, Dec 12 '18 at 12:47

Getting the UUID() after INSERT with PHP

1 Answers1