Updating a table drives me crazy

Hi folks,
it’s been some time since I coded in PHP so please be patient with me. I’m currently working on a roster for our model railroad club and came to the point where I need to update a table. The code:

                  $id             = $_POST['id'];
                  $power          = $_POST['power'];
                  $engtype        = $_POST['engtype'];
                  $wheels         = $_POST['wheels'];
                  $owner          = htmlspecialchars($_POST['owner']);
                  $marks          = htmlspecialchars($_POST['marks']);
                  $last           = $_POST['last'];
                  $next           = $_POST['next'];
                  $make           = $_POST['make'];
                  $decoder        = $_POST['decoder'];
                  $pic            = $_POST['pic'];
                  $sichern        = 'UPDATE engines SET traction = '.$power.',entype =       '.$engtype.',wheels = '.$wheels.',
                                     owner = '.$owner.',marks = '.$marks.',last_service = '.$last.',next_service = '.$next.',
                                     en_photo = '.$pic.',manufacturer = '.$make.',decoder = '.$decoder.' WHERE en_id = "'.$id.'"';
                  $res            = mysqli_query($con,$sichern);
                          echo '<h2>These data heve been saved:</h2>';
                         // Output test
                        echo mysqli_error($con);
                        echo '<br>Feld traction - Wert: '.$power;
                        echo '<br> entype: '.$engtype;
                       echo '<br> wheels: '.$wheels;
                        echo '<br> owner: '.$owner.'     marks: '.$marks.'     last_service: '.$last;
                        echo '<br> next_service: '.$next.'     en_phote: '.$pic.'     manufacturer: '.$make;
                        echo '<br> decoder: '.$decoder;
                    echo '
                           <form action="engines.php" method="POST">
                                        <tr><td>Record # : </td><td>'.$id.'</td></tr>
                                        <tr><td>Power type : </td><td>'.$power.'</td></tr>
                                        <tr><td>Engine Type : </td><td>'.$engtype.'</td></tr>
                                        <tr><td>Wheel Arrangement : </td><td>'.$wheels.'</td></tr>
                                        <tr><td>Owner : </td><td>'.$owner.'</td></tr>
                                        <tr><td>Reporting Marks : </td><td>'.$marks.'</td></tr>
                                        <tr><td>Last serviced : </td><td>'.$last.'</td></tr>
                                        <tr><td>Next Service due : </td><td>'.$next.'</td></tr>
                                        <tr><td>Manufacturer : </td><td>'.$make.'</td></tr>
                                        <tr><td>Decoder : </td><td>'.$decoder.'</td></tr>
                                        <tr><td>Photo : </td><td>'.$pic.'</td></tr>
                                        <tr>&nbsp;</td><td><input type="submit" value="OK"></td></tr>
                          </form> ';

When I execute the query ($sichern), I get this error message:
“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 'O,marks = B&O 1401,last_service = 2021-05-02,next_service = 2021-08-25, ’ at line 2”

All values coming from the input form are strings. Same goes for the table fields - they are CHAR-fields with various lengths. Could it be that PHP 7.4 doesn’t like an ampersand in a string field? Not even after I converted to an HTML-entity? I am stumped and running out of ‘wisdom’. Actually feel like I code for the first time.
Can anybody help me out here? If needed, I can provide a URL to the test site.
Thanks in advance,

Hello Erhard,

I believe the problem lies in the fact that the values you’re trying to update in your table with a wrong query. First, please try to output the whole query that gets executed with something like var_dump($sichern) or similar.

However, my bet is on the fact that the values of the updates values are not within quotation marks ('). You will want to write your query with double quotation marks so it will look like this:

$sichern        = "UPDATE engines SET traction = '{$power}', entype = '{$engtype}', wheels = '{$wheels}', owner = '{$owner}',

... etc


Try this first and also escape your query with: https://www.php.net/manual/en/mysqli.real-escape-string.php for security purposes.

Let me know how it went, I will try to help you.

Thanks a bunch! That did the trick. Problem solved…


If the values are string-type and contain strings (not numbers), the values passed to the query should be double quoted:
$sichern = ‘UPDATE engines SET traction = "’.$power.’",entype = “’.$engtype.’”,wheels = “’.$wheels.’”, owner = “’.$owner.’”,marks = “’.$marks.’”,last_service = “’.$last.’”,next_service = “’.$next.’”, en_photo = “’.$pic.’”,manufacturer = “’.$make.’”,decoder = “’.$decoder.’” WHERE en_id = “’.$id.’”’;

It is a very common error with the use of double quotes and single quotes. Glad it’s already resolved.