Get a random record from table where a field is null

I am trying to retrieve a random apartment from the database table where the ownerID is null. This script is used for a GTA 5 Server on AltV the problem is the letrandomHouse = Math.floor(Math.random()*res.length); it wont update the Apartment’s owner where id is

player.data.ownerID = res[0]["id"];
            con.query('SELECT * FROM apartements WHERE owner = null', function(err, res, row){
                if(res){
                    let randomHouse = Math.floor(Math.random()*res.length);
                    console.log(randomHouse);
                    con.query('UPDATE apartements SET owner = ? WHERE id = ?', [player.data.ownerID, randomHouse], function(res, err, row){
                        if (err){
                            console.log(err);
                            console.log("test");
                        }
                        console.log(res);
                    });
                    console.log(res);
                } else {
                    console.log(err);
                }
});

Full Export Function

export function registerHouse(player){
    con.query('SELECT `id` FROM characters WHERE accountID = ?', [player.data.accountID], function(err, res, row){
        if(!err){
            player.data.ownerID = res[0]["id"];
            con.query('SELECT * FROM apartements WHERE owner = null', function(err, res, row){
                if(res){
                    let randomHouse = Math.floor(Math.random()*res.length);
                    console.log(randomHouse);
                    con.query('UPDATE apartements SET owner = ? WHERE id = ?', [player.data.ownerID, randomHouse], function(res, err, row){
                        if (err){
                            console.log(err);
                            console.log("test");
                        }
                        console.log(res);
                    });
                    console.log(res);
                } else {
                    console.log(err);
                }
            });
        } else {
            console.log(err);
        }
    });
}

Answer

A problem with your code is the WHERE clause in the SELECT statement:

WHERE owner = null

This will never match. To check for nullness, you need the IS NULL construct.

But as far as concerns, you can do what you want with a single statement:

update apartements 
set owner = ? 
where owner is null
order by rand()
limit 1

Advertisement

Answer