1

I've never used Transactions before but I want to ensure that when I the data into the database via my HTML Form, that should there be an issue, there is a rollback.

Sounds awesome, but I'm struggling to fully understand the place and use of them in a MySQL Query.

$sql = "BEGIN

        /* Insert the user to the WordPress Database */
        INSERT INTO wp_users (user_login, user_pass, user_email)
        VALUES ('John', 'Doe', 'john@example.com');

        /* Insert the user into our Custom Database */
        INSERT INTO users (ID, name)
        VALUES (LAST_INSERT_ID(), 'John')

        COMMIT";

I've edited some of the code to be easier to read, and I know for example the password isn't secure, but am I doing something wrong with the BEGIN & COMMIT functions for the Transaction?

I'm also trying to use Transactions so that I can make full use of the LAST_INSERT_ID() function. This should then allow me to ensure that between both Databases, the user will share the same ID so I can easily call upon their unique data for various website application reasons.

I've found a few things online, but none really provide an easily understood example. What exactly am I doing wrong? Is my implementation terrible, or am I just missing something? Is the LAST_INSERT_ID() going to work like that?

I'd greatly appreciate any help you can offer. Thank you.

Alec Weekes
  • 33
  • 5
  • 1) Do you use MySQLi to connect to the DB by any chance? 2) What error message do you get? – BeetleJuice Jul 19 '16 at 09:35
  • have a look at http://stackoverflow.com/questions/9974325/mysql-transaction-within-a-stored-procedure - it shows some good usage of rollbacks – Professor Abronsius Jul 19 '16 at 09:40
  • Yes, I'm using MySQLi - The system works fine when inserting just one, into a single table, but when implementing the Transaction it fails. Thanks RamRaider - I'll take a look. I'm mostly concerned about if I've simply got the commands in the right place and such. – Alec Weekes Jul 19 '16 at 09:45
  • 1) There is not much point of using transactions if you do not check for errors and roll it back in case you detect one. 2) last_insert_id() works without transactions as well. 3) Begin and end are not functions, but statements. 4) You have missing semi-colons (syntax error). Probably should check for errors after trying to execute the above command. 5) You have not shown how you try to execute the above command, but simple mysqli_query() can only execute a single command. You need mysqli_multi_query() to execute more than one - but that's not that safe, and makes error handling difficult. – Shadow Jul 19 '16 at 10:01

2 Answers2

0

You might use PDO for handling transaction in php. Refer here

For last inserted row id. Refer here

Tamil
  • 1,193
  • 9
  • 24
  • Already using MySQLi - I don't really want to change it too much as the rest of the system is built on MySQLi. Thanks for the Last_Inserted_Row() link. That's more helpful than the one I found. – Alec Weekes Jul 19 '16 at 09:46
0

Since you are using Mysqli and you want to rollback if any part of the query failed or commit when queries succeed you can do something like shown in the code below:

( also I assume this part is wrong " INSERT INTO users (ID, name) VALUES (LAST_INSERT_ID(), 'John') " as you probably want to use "UPDATE" )

//just a temporary variable to store eventual error
$query_ok=true;

//start transaction here
$mysqli->begin_transaction(MYSQLI_TRANS_START_READ_WRITE);

$mysqli->query("INSERT INTO wp_users (user_login, user_pass, user_email)
        VALUES ('John', 'Doe', 'john@example.com');") ? null : $query_ok=false;

$mysqli->query("UPDATE users SET name ='John' WHERE ID=".$mysqli->insert_id ) ? null : $query_ok=false;

//if $query_ok is still set to true then we commit changes to database otherwise we do rollback
$query_ok ? $mysqli->commit() : $mysqli->rollback();

You can also use procedural style ( mysqli_query, mysqli_insert_id etc )

Pawel Dubiel
  • 18,665
  • 3
  • 40
  • 58