Wanjia Wanjia - 1 year ago 35
MySQL Question

Second SQL Statement not excecuting

I'm trying to add a player as well as give him a few stats.

But the problem is 1. Doesn't add the stats, 2. I need the gamer added first so I can get the gamerId returned.

$sqlPlayer = "INSERT INTO players (`gender`, `username`, `email`, `password`) VALUES('$gender','$username','$email','$password')";
echo $sqlPlayer."</br>";

if ($db->query($sqlPlayer))
echo "Player added. </br>";

$GamerId = $db -> lastInsertId();
echo "Gamer id added:".$GamerId." </br>";

$_SESSION['GamerId'] = $GamerId;
echo "Gamer session added:".$_SESSION['GamerId']." </br>";

$sqlStats = "INSERT INTO stats (`GamerId`,`Health`,`Stamina`,`Food`,`Coins`,`SuperCoin`) VALUES(`$GamerId`,`100`,`250`,`4`,`500`,`0`)";
echo $sqlStats."</br>";

if ($db->query($sqlStats))
echo "Stats for player added.</br>";
return true;
return false;

As seen above I've added a few echo's to see what works and what doesn't to have a visual on what is happening.

So far I always get:

INSERT INTO players (`gender`, `username`, `email`, `password`) VALUES('*gender*','*username*','*email*','*password*')

Player added.

Gamer id added:25

Gamer session added:25

INSERT INTO stats (`GamerId`,`Health`,`Stamina`,`Food`,`Coins`,`SuperCoin`) VALUES(`7`,`100`,`250`,`4`,`500`,`0`)

have a standard value in the database and because of this I've also tried:

INSERT INTO stats (`GamerId`,`Health`,`Stamina`,`Food`,`Coins`,`SuperCoin`) VALUES(`25`,` `,` `,` `,` `,` `)

INSERT INTO stats (`GamerId`) VALUES(`25`)

Answer Source

As @Saty pointed out, the VALUES clause contains backticks instead of apostrophes. So, you can do this:

    $sqlStats = "INSERT INTO stats (`GamerId`,`Health`,`Stamina`,`Food`,`Coins`,`SuperCoin`) VALUES('$GamerId','100','250','4','500','0')";
                                                                                                    ^^^      ^^^

Depending on your db schema, using numbers for numeric fields might be useful too. I.e.:

         VALUES(..., 500, ...)
    No apostrophes: ^^^^^^