SQL - How to Insert same value into two tables simultaneously

Question

EDIT This answer has been solved, the correct code is at the bottom of the question. I hope it's useful to whoever arrives at this page with the same problem :) ~~

I’m trying to have some data insert into two tables in my database at the same time. I’ve tried using the method from here: SQL Server: Is it possible to insert into two tables at the same time? but I don’t think I’m implementing it correctly.

The tables are named “player” and “balance”. “player” contains these rows: facebook_id, first_name, last_name. “balance” contains these rows: facebook_id, gold_balance.

The same value for “facebook_id” should inserted into both tables.
I thought maybe this Insert statement would work but it’s not:

$sql="INSERT INTO player, balance 
(facebook_id, first_name, last_name, gold_balance) 
VALUES 
('$userid', '$userfirst_name', '$userlast_name', “100”)";

I’ve never used the “Transaction” method, and was wondering if that would be a suitable method to use? And if so, what would be the correct syntax to implement it? Thanks in advance for any help with this!

Update After reading @Barada's comment this is what I'm currently trying:

$sql=START TRANSACTION;
"INSERT INTO player
(facebook_id, first_name, last_name) VALUES 
('$userid', '$userfirst_name', '$userlast_name')"

"INSERT INTO balance
(facebook_id, gold_balance) 
VALUES 
('$userid', "100")"

commit;

The values for $userid, $userfirst_name, $userlast_name are correct (they enter correctly if I just use a single Insert statement, so the problem is probably with the syntax of the Transaction statement.

This Insert statement is contained in a PHP file that's called using AJAX during an onClick event (I'm not sure if that affects the Insert syntax required, but I thought I should mention that), also, I have the following check at the end of the PHP file to send an echo back to the AJAX function that called the PHP file (I then initiate a popup depending on whether it's a "successful" or "failed" response). I was wondering if this could negatively affect the Insert operation?

//If the SQL insert statement completed correctly:
$result = mysqli_query($conn, $sql);
if ($result) {
   $verify=1;
}
else{
  $verify=0;
}
echo $verify;

mysqli_close($conn);

Update 2 I thought I should mention what I'm using to test this: I’m working with WAMP on localhost, my PHP version is 5.6.5 and mySQL version is 5.6.12

The following SQL works when I enter it directly into phpMyAdmin’s SQL console:

START TRANSACTION;
INSERT INTO player (facebook_id, first_name, last_name) VALUES ('test_id', 'test_firstname', 'test_lastname');
INSERT INTO balance (facebook_id, gold_balance) VALUES ('test_id', '100');
COMMIT;

It updates the tables perfectly. However when I try to use the following in my .php script it’s failing to enter anything:

//After database connection has taken place…
$sql=START TRANSACTION;
INSERT INTO player (facebook_id, first_name, last_name) VALUES ('test_id', 'test_firstname', 'test_lastname');
INSERT INTO balance (facebook_id, gold_balance) VALUES ('test_id', '100');
COMMIT;

//If the SQL insert statement completed correctly...
$result = mysqli_query($conn, $sql);
if ($result) {
   $verify=1;
}
else{
  $verify=0;
}
echo $verify;

mysqli_close($conn);

Since the SQL works perfectly when entered directly into phpMyAdmin’s SQL console, does that mean that the problem is an error in my PHP code? I’m putting $sql= before the Transaction statement because I need to check that $sql completed correctly before closing the database connection. But could this be why it’s not working? I’ve tried omitting it, and have tried adding and removing () “” ; marks in the hope that there’s just one little thing missing but so far nothing has worked. Could using mysqli_query be the reason it's not working? Any help would be much appreciated!

The following code works! :)

$sql = "INSERT INTO player (facebook_id, first_name, last_name) VALUES 
('$userid', '$userfirst_name', '$userlast_name');";
$sql .= "INSERT INTO balance (facebook_id, gold_balance)
VALUES ('$userid', '100');";

$result = mysqli_multi_query($conn, $sql);
if ($result) {
   $verify=1;
}
else{
  $verify=0;
}
echo $verify;

mysqli_close($conn);

Answer 1

You are right. For this case transactions were invented. Try this:

START TRANSACTION;
INSERT INTO player
(facebook_id, first_name, last_name, gold_balance) 
VALUES 
('$userid', '$userfirst_name', '$userlast_name', “100”)

INSERT INTO BALANCE
(facebook_id, first_name, last_name, gold_balance) 
VALUES 
('$userid', '$userfirst_name', '$userlast_name', “100”)"

commit;       

Fix insert statements to fit tables.

But i see no problems to use two separate queries like you were already suggested.