mysql transaction catch mistakes in a loop

Question

I did a function to record some informations in my database, some elements of the form posted are provided by a select list (multi select).

So I have an array to store in my database.

I have to use transaction in order to make sure that everything is fine, but it is the first time I use transaction, and I do not know how to proceed for the loop.

Below is my function:

function recordSports() {
    if (isset($_POST['edit_sport'])) {
        //echappement des variables
        $liste_sport = mysql_real_escape_string($_POST['liste_sport']);
        $id_user     = $_SESSION['login'];
        //démarrage des transactions
        mysql_query("START TRANSACTION");

        //requettage 
        $query1 = "DELETE FROM `fit__sport_practice`
                                    WHERE 
                                    `sport_practice__id_user` = {$id_user}
                                    ";
        mysql_query($query1);

        foreach ($liste_sport as $sport) {
            $query = "INSERT INTO `fit__sport_practice`
                                                SET                                
                                                `sport_practice__id_user` = {$id_user},
                                                `sport_practice__id_sport` = {$sport},
                                            ";
            mysql_query($query);
        }
        if ($query1) {
            mysql_query("COMMIT");
            $retour['message'] = $_SESSION['maj_sport_ok'];
            $retour['error']   = 0;
        } else {
            //sinon on renvoit une erreur
            mysql_query("ROLLBACK");
            $retour['message'] = $_SESSION['maj_sport_error'];
            $retour['error']   = 1;
        }
    } else {
        $retour['message'] = null;
        $retour['error']   = null;
    }
    return $retour;
}

score 1 · Accepted Answer · edited May 23 '17 at 11:57

First, you should not use mysql_* function, read more here: Why shouldn't I use mysql_* functions in PHP?. They don't support transactions as far as I know either way, so I think you'll have to switch to either PDO or mysqli.

Good to know is that the standard database storage engine in MySQL (if that's what you're using) MyISAM doesn't support transactions either. I use InnoDB as storage engine and PDO.

Note: If you have big table with a lot of rows and/or a lot of constraints InnoDB have pour performance compared to MyISAM.

The basic flow of a transaction is as follows (using PDO at least):

Initiate the database connection.
Initiate/begin the transaction.
Run all your queries, preferably using prepared statements and execute (Gives you some defense against sql injection). At any point, if anything goes wrong, make a roll back.
If everything went as planned, commit the transaction.

Hope this will help you getting started with transactions!

Sorry, forgot to answer you're actual question...

The foreach loop. You want the roll back check to be inside the loop, so it can verify each query and roll back if needed. Something like this in your code above:

    foreach ($liste_sport as $sport) {
        $query = "INSERT INTO `fit__sport_practice`
                                            SET                                
                                            `sport_practice__id_user` = {$id_user},
                                            `sport_practice__id_sport` = {$sport},
                                        ";
        if(!mysql_query($query)) {
            //sinon on renvoit une erreur
            mysql_query("ROLLBACK");
            $retour['message'] = $_SESSION['maj_sport_error'];
            $retour['error']   = 1;
            break;
        }
    }
    if (!isset($retour['error'])) {
        mysql_query("COMMIT");
        $retour['message'] = $_SESSION['maj_sport_ok'];
        $retour['error']   = 0;
    }

thanks for the information, but for the loop, I do not know how to check if there is some errors because it is not a stitic request, but multi reqest — Stanislas Piotrowski, Jul 25 '14 at 20:34
ok, I will try to do it the way you show me, thanks you verry much for the answer, I will try this — Stanislas Piotrowski, Jul 25 '14 at 20:41

mysql transaction catch mistakes in a loop

1 Answers1