My NodeJS MySQL Query function always returns wrong results

Question

I'm working on a Game, which uses MySQL for Multiplayer. Now, everytime I request a query (for example

dat("SELECT * FROM server1;"); 

it won't give me the data of server1, instead it'll return the result of

dat("SELECT * FROM highscore;");

Even when I try other commands such as

dat("SELECT * FROM playernames;");

). So my function seems to save a query from an initialization I did at the beginning automatically, and executes it everytime I use the function.

I've tried searching the web for new functions, I've tried a Promise, but I'm a newcomer and I CAN'T HANDLE PROMISES I don't know how to deal with them, so I use async and await

async function dat(Befehl) {
  console.error(Befehl);
  con.connect(function(err) {
    if (err) throw err;
    console.log("MySQL: Connected to localhost!");
    con.query("USE abschlussprojekt;", function (err, result) {
      if (err) throw err;
      console.log(result);
    });
  });
  con.query(Befehl, function (err, result) {
    if (err) throw err;
    //await Sleep(10);
    while(result == undefined)
    {}
    temp = result;
    delete result;
  });
  return temp;
  con.close();
}

So with

let x = await dat("SELECT * FROM server1;"); 

I would expect:

Player1Cards = "Cards"
Player1CardsColour = "Colour";
Player2Cards = "Cards";
Player2CardsColour = "Colour";
Player1Name = "Name"
Player2Name = "Name"

But instead it would return the highscore table, I don't know, why.

BUT!!! When I call the same function repeatedly, I then may get the real result. Like after I called it 3-6 times.

Here the functions which also use the query.

async function generateTable()
{
  let highscoredata = [];
  //await Sleep(3000);
   highscoredata = await loadhighscore();
   while(highscoredata === undefined)
   {
     highscoredata = await loadhighscore();
     await Sleep(10);
   }
   //else {

    console.log(highscoredata);

    if (gen>0) {
      await Sleep(500);
      gen--;
      generateTable();
    }
    else {
      var Spielername = [];
      var Spiel = [];
      var Highscore = [];
      await Sleep(100);
      for (var i = 0; i < highscoredata.length; i++) {
        Spielername[i] = highscoredata[i].Name;
        Spiel[i] = highscoredata[i].Spiel;
        Highscore[i] = highscoredata[i].Highscore;
        if (CONLOG) {
          console.log("SpielerName: "+Spielername);
        }
      }

      console.log("Daten implementiert");
      let table = document.getElementById("table");
      document.getElementById("table").innerHTML = "";
      var row, SpielerID1 = [], SpielerName1 = [], SpielArt1 = [], Highscore1 = [];
      for( var i = Spielername.length-1; i>-1; i--)
      {
        //console.log(Spielername);
        //console.log(i);
        row = await table.insertRow(0); //TR Element Neue Spalte

        SpielerID1[i] = await row.insertCell(0); //SpielerID
        SpielerName1[i] = await row.insertCell(1); //SpielerName
        SpielArt1[i] = await row.insertCell(2); //Spiel
        Highscore1[i] = await row.insertCell(3); //Score

        SpielerID1[i].innerHTML = i;
        SpielerName1[i].innerHTML = Spielername[i];
        SpielArt1[i].innerHTML = Spiel[i];
        Highscore1[i].innerHTML = Highscore[i];

      }
      //console.log(SpielerName);
      row = await table.insertRow(0);
      var stdID = await row.insertCell(0); //SpielerID
      var stdSN = await row.insertCell(1); //SpielerName
      var stdSP = await row.insertCell(2); //Spiel
      var stdSC = await row.insertCell(3); //Score

      stdID.innerHTML = "ID";
      stdSN.innerHTML = "Spieler";
      stdSP.innerHTML = "Spiel";
      stdSC.innerHTML = "Score";
    }

A function which clears all servers for debug reason

function AlleFreigeben()
{
  dat("UPDATE INUSE SET Server1 = FALSE;");
  dat("UPDATE INUSE SET Server2 = FALSE;");
  dat("UPDATE INUSE SET Server3 = FALSE;");
  dat("UPDATE INUSE SET Server4 = FALSE;");
  dat("UPDATE INUSE SET Server5 = FALSE;");
  console.warn("Alle Server wurden wieder freigegeben!");
}

Also here a login-function.

async function prelogin()
{

  preuser = await dat("SELECT * FROM users;"); //Everytime, this function is executed, it gets the results from the wrong table, so just ONE execution
  login(preuser);
}

async function login(user)
{
  preuser = await dat("SELECT * FROM users WHERE username = '"+document.getElementById("usernameinput").value+"';");
  console.warn(preuser);
if (preuser[0].password == await md5(document.getElementById("passwordinput").value)) {
  console.warn("Passwort korrekt");
  alert("Login erfolgreich");
  isUserLoggedIn = true;
  document.getElementById("Spieler1NameFeldHM").value = preuser[0].username;
  reverse(true);
  CPUTable();
}
else {
  alert("Falsches Passwort");
  console.error("Passwort falsch");
}
}

And of course the register-function

async function register()
{
  if (document.getElementById("usernameinput").value == "") {
    alert("Geben Sie einen Benutzernamen ein");
  }
  else if (document.getElementById("usernameinput").value.length <3) {
    alert("Der Nutzername darf nicht weniger als 3 Zeichen haben")
  }
  else if (document.getElementById("usernameinput").value == "root") {
    alert("Ey, ich bin schon Admin");
  }
  else {
    let dbuser = await dat("SELECT * FROM users WHERE username = '"+document.getElementById("usernameinput").value+"';");
    console.log(dbuser);
    if (dbuser.length != 0) {
      if (document.getElementById("usernameinput").value == dbuser[0].username) {
        console.error(document.getElementById("usernameinput").value);
        console.error(await dat("SELECT * FROM users WHERE username = '"+document.getElementById("usernameinput").value+"';"));
        alert("Benutzername ist bereits vergeben");
      }
    }

    else {
      await dat("INSERT INTO users VALUES('"+document.getElementById("usernameinput").value+"', '"+md5(document.getElementById("passwordinput").value)+"');");
      alert("Registrierung erfolgreich!");
      prelogin();
    }
  }
}

And here a function to push the Names in the Table and start the game.

async function UnoStartHM()
{
  await dat("UPDATE spielernamen SET Spieler1 = '"+document.getElementById("Spieler1NameFeldHM").value+"';");
  await dat("UPDATE spielernamen SET Spieler2 = '"+document.getElementById("Spieler2NameFeldHM").value+"';");
  location.href = "./Uno.html";
}

However, this is the only function which doesn't work aside the manual mysql query execution (via console)

async function Abfrage()
{
  if (confirm("Möchtest du im lokalen Multiplayer spielen?")) { //If local multiplayer is confimed
    Multiplayer = true;
    myEmitter.setMaxListeners(100);
    console.log(myEmitter);
    let SpielerNamen = await dat("SELECT * FROM spielernamen");
    //Spieler1Name = SpielerNamen[0]
    Server1 = await dat("SELECT * FROM INUSE;");
    // Server1 = await dat2("SELECT * FROM INUSE;");
    // Server1 = await dat2("SELECT * FROM INUSE;");
    //Erzeuge Tabelle, falls sie noch nicht existiert!
    await dat("CREATE TABLE IF NOT EXISTS INUSE (Server1 boolean NOT NULL, Server2 boolean NOT NULL, Server3 boolean NOT NULL, Server4 boolean NOT NULL, Server5 boolean NOT NULL);");
    await Sleep(100);
    // await dat("INSERT INTO INUSE VALUES (FALSE, FALSE, FALSE, FALSE, FALSE);");
    await dat_alt();
    //await dat(" UPDATE INUSE SET Server2 = FALSE WHERE INUSE.Server2 IS NULL; UPDATE INUSE SET Server3 = FALSE WHERE INUSE.Server3 IS NULL; UPDATE INUSE SET Server4 = FALSE WHERE INUSE.Server4 IS NULL; UPDATE INUSE SET Server5 = FALSE WHERE INUSE.Server5 IS NULL;");
    //await dat("UPDATE INUSE SET Server2 = FALSE WHERE INUSE.Server2 IS NULL;");
    //await dat2("UPDATE INUSE SET Server3 = FALSE WHERE INUSE.Server3 IS NULL;");
    //await dat("UPDATE INUSE SET Server4 = FALSE WHERE INUSE.Server4 IS NULL;");
    //await dat2("UPDATE INUSE SET Server5 = FALSE WHERE INUSE.Server5 IS NULL;");
    console.warn("TABLE INITIALISIERT");
    Server1 = await dat("SELECT * FROM INUSE;");
    console.log("Verbinde mit Datenbank...");
    await Sleep(1000);
    console.log("Verbunden");
    console.log(typeof Server1);
    console.log(Server1);
    let errcount = 0;
    while (Server1 === undefined && errcount < 6) {
      errcount++;
      await Sleep(100);
      if (errcount == 5) {
        alert("Datenbank konnte nicht geladen werden. Bitte versichere, dass die Datenbank existiert");
        if (confirm("Möchtest du zum Hauptmenü zurückkehren?")) {
          location.href = "./index.html";
        }
        else {
          break;
        }
      }
      console.log(errcount);
      console.log("Erneut");
      // Server1 = await dat("SELECT * FROM INUSE;");
      // console.log(Server1);
      // await Sleep(10);
    }
    console.log(Server1);
    console.log(myEmitter);
    if (Server1[0].Server1 == 0) {
      await dat("UPDATE INUSE SET Server1 = TRUE;"); //Server 1 belegen || Use Server 1
       belegt = 1;
       belegt2 = belegt;
       //return belegt;
    }
    else if (Server1[0].Server2 == 0) {
      await dat("UPDATE INUSE SET Server2 = TRUE;"); //Server 2 belegen
      const belegt = 2;
      belegt2 = belegt;
      //return belegt;
    }
    else if (Server1[0].Server3 == 0) {
      await dat("UPDATE INUSE SET Server3 = TRUE;"); //Server 3 belegen
      const belegt = 3;
      belegt2 = belegt;
      //return belegt;
    }
    else if (Server1[0].Server4 == 0) {
      await dat("UPDATE INUSE SET Server4 = TRUE;"); //Server 4 belegen
      const belegt = 4;
      belegt2 = belegt;
      //return belegt;
    }
    else if (Server1[0].Server5 == 0) {
      await dat("UPDATE INUSE SET Server5 = TRUE;"); //Server 5 belegen
      const belegt = 5;
      belegt2 = belegt;
      //return belegt;
    }
    else {
      alert("Zurzeit sind alle Server belegt... bitte versuche es später noch einmal!"); //Alle Server belegt
      location.reload();
    }
    alert("Es wurde Server "+belegt2+" ausgewählt!");

    await dat("UPDATE SERVER"+belegt2+" SET Spieler1IP = '"+IPAdresse+"'; UPDATE SERVER"+belegt2+" SET Spieler1Name = '"+Spieler1Name+"';");
    WaitForPlayer();
    return belegt;
  }
}

Answer 1

The issue is that the variable temp gets set by a query you have requested before and returns that even before the query has been answered by the database. I would strongly advise looking into Promises.

The following code is doing what the one in the question should do. Take note of where the methods resolve() and reject() are called.

async function connect() {
  return new Promise((resolve, reject) => {
    // Assuming con is defined somewhere above.
    con.connect(function(err) {
      if (err) return reject(err);
      console.log("MySQL: Connected to localhost!");
      con.query("USE abschlussprojekt;", function (err, result) {
        if (err) return reject(err);
        console.log(result);
        return resolve();
      });
    });
  })
}

async function query(Befehl) {
  return new Promise((resolve, reject) => {
    con.query(Befehl, function (err, result) {
      if (err) return reject(err);

      return resolve(result);
    });
  })
}

async function dat(Befehl) {
  await connect();
  const result = await query(Befehl);
  con.close();
  return result;
}

Help with Promises: https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Promise

Help with Async: https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Statements/async_function