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