0

I am attempting to run MySQL queries sequentially through a for loop. I am using callback functions and asynchronous functions to have this run as close to sequentially as possible.

In the function getUserExerciseId() I am trying to retrieve the last insert Id which comes from the query above it (userExercisesQuery). But when I run this, I get the same value as the last insert id, but in triplicate. So instead of getting 2, 3, 4, I'm getting 4, 4, 4.

How do I go about making the for loop asynchronous, similar to the functions?

My code:

// Asychronous for loop to add userExercise information, capture userExerciseIds, add along with userWorkoutId into userE_userW table
    
    // userWorkoutId is already defined above
    function insertUserExerciseLoop(userWorkoutId) {
        for (var exId = 0; exId < numExercises; exId++) {

            // Insert Exercise information into userExercises, capturing userExerciseId upon completion
            const userExercisesQuery = 'INSERT INTO userExercises(userId, exerciseId, completed, measureType, measure, rating, review, favorite) VALUES (?,?,?,?,?,?,?,?)';
            connection.query(userExercisesQuery, [userId, exerciseId[exId], completed, measureType[exId],
                                           measure[exId], rating[exId], review[exId],
                                           favorite[exId]],
                                           async function (err, rows, fields) {
                if (err) {
                    next(err);
                }
                getUserExerciseId();
            });

            // Capture userExerciseId upon completion of insert into userExercises
            function getUserExerciseId() {
                const queryUserExercisesLastGrab = 'SELECT LAST_INSERT_ID()';
                var context = {};
                var userExerciseId;
                connection.query(queryUserExercisesLastGrab, [],
                    async function (err, rows, fields) {
                        if (err) {
                            next(err);
                        }
                        context.results = JSON.stringify(rows);
                        userExerciseId = rows[0]['LAST_INSERT_ID()']; // <--- Eg. 4,4,4 instead of 2,3,4
                        insertUserW_UserE(userExerciseId);
                });
            };

            // Insert Workout information into userWorkouts
            function insertUserW_UserE(userExerciseId) {
                const userWuserE_Query = 'INSERT INTO userW_userE(userWorkoutId, userExerciseId) VALUES (?,?)';
                connection.query(userWuserE_Query, [userWorkoutId, userExerciseId],
                    async function (err, rows, fields) {
                        if (err) {
                            next(err);
                        }
                });
            };

        } // exId for loop
    }; // insertUserExerciseLoop()
usafutb0l3r
  • 69
  • 9
  • you can do the sql queirs ina one goal and you don't tio cascade it. I personally would wirte a stored procedure for that. – nbk Aug 07 '20 at 22:08
  • I'm not too familiar with that and I apologise for my ignorance... Do you mean execute all the queries in one query call? How would that be done? – usafutb0l3r Aug 07 '20 at 22:34
  • see multi query https://stackoverflow.com/questions/23266854/node-mysql-multiple-statements-in-one-queryyou have to use SELECT LAST_INSERT_ID() INTO _@id and then use that _@id in you next query – nbk Aug 07 '20 at 22:41

0 Answers0