SQL Error while updating

Question

I have encountered an error and I do not know what is wrong with my code. Can someone help me?

Error: UPDATE users SET voornaam = Test, achternaam = Test2, mail = test@test.com, tel = ,adres = , geslacht = man, bestuur = 0, tc = 0, ic = 0, jec = 0, rvr = , instructeur = 0, webmaster = 0 WHERE id = 1 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'adres = , geslacht = man, bestuur = 0, tc = 0, ic = 0, jec = 0, rvr = , instruct' at line 1

$id = $_GET['u'];

$sql = "UPDATE users SET voornaam = $voornaam, achternaam = $achternaam,
        mail = $mailadres, tel = $tel, adres = $adres, geslacht = $geslacht,
        bestuur = $bestuur, tc = $tc, ic = $ic, jec = $jec, rvr = $rvr, 
        instructeur = $instructeur, webmaster = $webmaster WHERE id = ".$id."";

if ($conn->query($sql) === TRUE) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

Looks like you didn't set the value for `$tel` and `$adres`, are they empty? Consider using quotes on the strings (like this `voornaam = '$voornaam'`) just for sake of testing. But in reality, a preparedStatement should be used. — FirstOne, Oct 06 '15 at 17:56
Your $adres is empty, your query `adres = `, gives error, if they are empty strings, it should be in qoutes like this: `adres = '$adres'` — kakajan, Oct 06 '15 at 18:06
Does this answer your question? [How to include a PHP variable inside a MySQL statement](https://stackoverflow.com/questions/7537377/how-to-include-a-php-variable-inside-a-mysql-statement) — Dharman, Dec 10 '20 at 23:14

score 2 · Answer 1 · answered Oct 06 '15 at 17:59

2

You must quote ANY string like 'xxxx' else empty strings are missing and string with spaces are see as 2 strings achternaam= van mueller is wrong but achternaam = 'van mueller' is ok and also achternaam=''

answered Oct 06 '15 at 17:59

Bernd Buffen

14,525
2
24
39

score 2 · Accepted Answer · edited Oct 07 '15 at 20:03

You should have something like:

$sql = "UPDATE `users` 
           SET `voornaam` = '$voornaam', 
             `achternaam` = '$achternaam', 
                   `mail` = '$mailadres', 
                    `tel` = '$tel', 
                  `adres` = '$adres', 
               `geslacht` = '$geslacht', 
                `bestuur` = '$bestuur', 
                     `tc` = '$tc', 
                     `ic` = '$ic', 
                    `jec` = '$jec', 
                    `rvr` = '$rvr', 
            `instructeur` = '$instructeur', 
              `webmaster` = '$webmaster' 
           WHERE id = '".$id."'";

This way you are protected if there are empty fields anywhere. You should also make sure you sanitize all the entries you wish to insert in the database.

score 2 · Answer 3 · answered Oct 06 '15 at 18:01

You need add quote ' around the updated value, like

$sql = "UPDATE users SET voornaam = '$voornaam', achternaam = '$achternaam', mail = '$mailadres', tel = '$tel', adres = '$adres', geslacht = '$geslacht', bestuur = '$bestuur', tc = '$tc', ic = '$ic', jec = '$jec', rvr = '$rvr', instructeur = '$instructeur', webmaster = '$webmaster' WHERE id = '".$id."'";

score 2 · Answer 4 · answered Oct 06 '15 at 18:02

If you are not sure that your variables are set before your query, then check them before you create sql query with isset:

if (!isset($a)) {
    $a = ''
}

and put your variables in quote:

$sql = "UPDATE users SET voornaam='$voornaam', achternaam='$achternaam',   
        mail='$mailadres', tel='$tel', adres='$adres', geslacht='$geslacht', 
        bestuur='$bestuur', tc='$tc', ic='$ic', jec='$jec', rvr='$rvr', 
        instructeur='$instructeur', webmaster='$webmaster' WHERE id='".$id."'";

SQL Error while updating

4 Answers4