Express MySql query response undefined

Question

I am new to Node.js and Express. I have a PHP background on server side, just for you to understand how I understand the backend side.

I will explain in words what I want to achieve and after that I will paste a minimal example of code from my project, the part which does not work as expected.

Description:

• I have a table called room which has an id, room_name and player_id. This table is already populated with 1 row using: INSERT INTO room (id, room_name, player_id) VALUES (1, "room1", ""). In other words I have one room which has some missing information regarding the player which joined that room.

• I have a function called addPlayerToRoom(id_player, room_name) which is executed whenever I want to add a player to a room. This function can return 3 type of responses :

  1. Room does not exist (if the room_name is not found in the table)
  2. Room is full (if the player_id is already populated in the db on that specific room row)
  3. Player added to the room (when that room exists and no players were previously added to that room)

• I want to return one of these 3 statuses and console.log them but everything I do, I get undefined even though the code works for each case. If I console log the response before the return I get the good response in the console. Also everything works in the db also, meaning that the player is added if the room is empty.

Code:

const response = addPlayerToRoom(7, "room1"); // 7 is the player_id which is any random number
console.log(response) // => undefined (why I get undefined ???)

function addPlayerToRoom(id_player, room_name) {
    let sql1 = "SELECT * FROM room WHERE room_name = ?";
    let pre1 = [room_name];
        sql1 = mysql.format(sql1, pre1);

    // fetch the room in the db
    connection.query(sql1, function (err1, rows1) { 

        // check if room exists
        if (rows1.length > 0) {
            
            // check if the room is full
            if (row1[0].player_id !== '') {
                return "Room is full !"
            }                  
            else {
                let sql2 = 'UPDATE room SET player_id = ? WHERE name = ?'; 
                let pre2 = [id_player, room_name];
                    sql2 = mysql.format(sql2, pre2);

                connection.query(sql2, function (err2, rows2) { 
                    return "Player added to room !"                  
                }); 
            }
        }
        else {
            return "Room does not exist !"
        }
    }
}

What I believe at this point is that the console.log triggers before the function is executed because if I add any random console.log in the function it gets pasted in the console after I get the undefined pasted...which means to me that it does not wait for the function execution.

This is somehow strange for me when I think at PHP where I did not experienced this type of issues. Does this means that everytime when I do a mysql query I should somehow use a promise or something? I tried something but without success.

Any ideea how to solve this ? Thanks