0

I am having a bit of trouble with some of my code. This is the code:

$STMT3 = $PDO->prepare('
   INSERT INTO `lotteries_entries` (steamID, lottery_id) VALUES (:STEAMID, :ID);
   UPDATE `lotteries_entries` SET `winner` = 1 WHERE `lottery_id` = :ID ORDER BY RAND() LIMIT 1;
   UPDATE `lotteries` SET `status` = 1 WHERE `id` = :ID;
   INSERT INTO `lotteries` (status, type) VALUES (0, :TYPE);'
);

$STMT3->bindParam(':STEAMID', $_SESSION['STEAM_steamid'], \PDO::PARAM_INT);

$STMT3->bindParam(':ID', $result['id'], \PDO::PARAM_INT);

$STMT3->bindParam(':TYPE', $result['type'], \PDO::PARAM_INT);

$STMT3->execute();

Although the $STMT3 returnes true (by using var_dump to check it), it unfortunatly never Inserts or Updates any of the tables it was told to. I've tried to run the script on the MySQL "SQL" tab (Directly through SQL) and it work fine. No errors are thrown through the script, and the SQL code isn't problematic.

Please not i use transactions, and yes i have started an commited the transaction. Both start and commit is reached by the script without any difficulty.

To see the whole code please visit: https://gist.github.com/vasil7112/8913f002940aaeb2f682

Kind regards and wish you have had a wonderful year.

5
  • What happens if you remove the transaction around the execute? Commented Dec 29, 2014 at 8:59
  • Removing the transaction makes it actually work. Although the transaction is required to make sure that every sql command will be executed
    – vasil7112
    Commented Dec 29, 2014 at 12:44
  • OK, so if you put the transaction into the MySQL query runner directly, can you reproduce the issue there? Commented Dec 29, 2014 at 13:12
  • Reproduced the issue with the code START TRANSACTION; INSERT INTO lotteries_entries` (steamID, lottery_id) VALUES (76561198029050621, 12); UPDATE lotteries_entries SET winner = 1 WHERE lottery_id = 12 ORDER BY RAND() LIMIT 1; UPDATE lotteries SET status = 1 WHERE id = 12; INSERT INTO lotteries (status, type) VALUES (0, 1); COMMIT;`, and the issue didn't show up*. So there is no problem with the transaction there i guess
    – vasil7112
    Commented Dec 29, 2014 at 13:27
  • The next logical step is to enable the MySQL query log and trace what is happening. Commented Dec 29, 2014 at 18:30

2 Answers 2

1

SOLUTION

The problem was fixed by adding this line after the execute. $STMT3->closeCursor();

Thanks everyone that tried to help :)

0

Alright, so i tried to start the transaction both on the phpmyadmin website and my PHP script.

Results:

PHPMYADMIN: Works both with transactions and without.

PHP SCRIPT: That specific part of the script works ONLY without transactions without any specific reason.

The binds are correct if you are wondering.

Still no solution found, but i keep searching the web.

Kind regards, Vasilis Dimitriadis

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.