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 = [email protected], 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
– FirstOne, Commented Oct 6, 2015 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
– kakajan, Commented Oct 6, 2015 at 18:06
Does this answer your question? How to include a PHP variable inside a MySQL statement — Dharman
– Dharman ♦, Commented Dec 10, 2020 at 23:14

mergenchik · Accepted Answer · 2015-10-07 20:03:54Z

2

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.

edited Oct 7, 2015 at 20:03

mergenchik

1,13917 silver badges28 bronze badges

answered Oct 6, 2015 at 18:01

itoctopus

4,2915 gold badges36 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Bernd Buffen · Accepted Answer · 2015-10-06 17:59:48Z

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 6, 2015 at 17:59

Bernd Buffen

15.1k2 gold badges27 silver badges41 bronze badges

Comments

Quanlong · Accepted Answer · 2015-10-06 18:01:50Z

2

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."'";

answered Oct 6, 2015 at 18:01

Quanlong

25.7k17 gold badges73 silver badges80 bronze badges

Comments

kakajan · Accepted Answer · 2015-10-06 18:02:50Z

2

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."'";

answered Oct 6, 2015 at 18:02

kakajan

2,7172 gold badges24 silver badges41 bronze badges

Collectives™ on Stack Overflow

SQL Error while updating

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related