Update table error in PHP

Question

I'm working on an website and made an form where you can add vehicles. Now I created a form where you should be able to edit them, but when I press on edit vehicle it gives out my default error message

Beim Speichern ist leider ein Fehler aufgetreten

Now I need your help. I have no clue where the problem could be, but I think it has to do something with the update query

 $showFormular = true; //Variable ob das Registrierungsformular anezeigt werden soll
    $pdo = new PDO('mysql:host=localhost;dbname=', '', '');

    if(isset($_GET['bearbeiten'])) {
        $error = false;
        $EKNR = $_POST['EKNR'];
        $EKDatum = $_POST['EK-Datum'];
        $RGNummer = $_POST['RG-Nummer'];
        $Marke = $_POST['Marke'];
        $Modell = $_POST['Modell'];
        $EZ = $_POST['EZ'];
        $MotorNummer = $_POST['Motornummer'];
        $Fin = $_POST['FIN'];
        $KM = $_POST['KM'];
        $VK = $_POST['VK'];
        $EKBrutto = $_POST['EK-Brutto'];
        $EKNetto = $_POST['EK-Netto'];
        $VKDatum = $_POST['Verkaufsdatum'];
        $Lack = $_POST['Farbe'];

        //Keine Fehler, wir können den Nutzer registrieren
        if(!$error) {   
            $statement = $pdo->prepare("UPDATE `Fahrzeugverkauf` SET `EKNR`=[$EKNR],`EKDatum`=[$EKDatum],`RGNummer`=[$RGNummer],`Marke`=[$Marke],`Modell`=[$Modell],`EZ`=[$EZ],`MotorNr`=[$MotorNummer],`FIN`=[$Fin],`KM`=[$KM],`VK`=[$VK],`EKBrutto`=[$EKBrutto],`EKNetto`=[$EKNetto],`Farbe`=[$Lack],`VKDatum`=[$VKDatum] WHERE EKNR = $id");
                $result = $statement->execute(array('EKNR' => '$EKNR', 'EKDatum' => '$EKDatum', 'RGNummer' => '$RGNummer', 'Marke' => '$Marke', 'Modell' => '$Modell', 'EZ' => '$EZ', 'MotorNr' => '$MotorNummer', 'FIN' => '$Fin', 'KM' => '$KM', 'VK' => '$VK', 'EKBrutto' => '$EKBrutto', 'EKNetto' => '$EKNetto', 'Farbe' => '$Lack', 'VKDatum' => '$VKDatum'));

                if($result) {       
                echo 'Das Fahrzeug wurde erfolgreich hinzugefügt.';

                $showFormular = false;
            } else {
                echo 'Beim Abspeichern ist leider ein Fehler aufgetreten<br>'
;

Bitte beantworte deine Frage in einer Antwort, und markiere diese Antwort dann als akzeptiert. Bitte editiere die Antwort nicht in die Frage hinein! — baao, Sep 04 '16 at 12:34
Pro-tips for getting the best results out of Stack Overflow: (a) avoid obviously offensive messages in your avatars; (b) if someone puts a lot of effort into assisting you, do not make a code change (e.g. PDO to mysqli) that invalidates all their effort; (c) if someone assists you, be more detailed than "still wont work", otherwise it is hard to know how to help you further. — halfer, Sep 21 '16 at 14:42

score 1 · Answer 1 · edited May 23 '17 at 11:51

1

Prepared statements have different SQL syntax for parameters. Instead of [$EKNR] use :EKNR.

And then when you are executing it pass the parameters in an array: array(':EKNR' => $EKNR,....

The SQL statement can contain zero or more named (:name) or question mark (?) parameter markers for which real values will be substituted when the statement is executed.

http://php.net/manual/en/pdo.prepare.php

Update

You have several errors in your code:

Remove quotes around the values you pass. This: '$Modell' will save the literal value "$Model1" and not the value, stored in $Modell variable.
Parameter names must match the names in SQL. For example, in SQL you have:
```
`MotorNr` = :MotorNummer,
```
But you pass a parameter:
```
':MotorNr' => $MotorNummer,
```
:MotorNr must be replaced with :MotorNummer. There are several mistakes like this. Fix them all.
Your code is vulnerable to SQL injection. At the end you pass a variable directly into SQL without using parameters:
```
EKNR = $id
```
Replace this with:
```
EKNR = :id
```
And pass an extra parameter into execute:
```
':id' => $id
```
No need to save the id value:
```
SET `EKNR`=:EKNR
```
As far as I understand EKNR is the id of the record. No need to save it, it will stay the same.

edited May 23 '17 at 11:51

Community

1
1

answered Sep 03 '16 at 10:15

user4035

22,508
11
59
94

I tried it like you wrote , but it's outputting another error now : Warning: PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: parameter was not defined in /volume1/web/34/fahrzeuge_bearbeiten.php on line 134 Beim Abspeichern ist leider ein Fehler aufgetreten – Max Bogatec Sep 03 '16 at 10:33
@MaxBogatec Please, update your question and show us the code that you've written. No need for the form code. – user4035 Sep 03 '16 at 10:39
Updated , still no clue what's wrong .... – Max Bogatec Sep 03 '16 at 12:24
@MaxBogatec Answer updated. – user4035 Sep 03 '16 at 13:58
Updated, MotorNr is the db value and $MotorNummer selects it from the form... – Max Bogatec Sep 04 '16 at 06:49
@MaxBogatec You need to run $statement = $pdo->prepare("SQL here"). – user4035 Sep 04 '16 at 10:49
Solved it , i used mysqli instead – Max Bogatec Sep 04 '16 at 12:32
@MaxBogatec You had to make just 1 step to make it work in PDO. Put your mysqli code as an answer and accept it please. So the question could be closed. – user4035 Sep 04 '16 at 18:10
1

@halfer Thank you. – user4035 Sep 21 '16 at 20:33

score 0 · Answer 2 · answered Sep 03 '16 at 13:47

0

In the array you are passing to execute remove the single quotes around the values in the array.

$result = $statement->execute(array(
    ':EKNR'     => $EKNR        ,
    ':EKDatum'  => $EKDatum     ,
    ':RGNummer' => $RGNummer    ,
    ':Marke'    => $Marke       ,
    ':Modell'   => $Modell      ,
    ':EZ'       => $EZ          ,
    ':MotorNr'  => $MotorNummer ,
    ':FIN'      => $Fin         ,
    ':KM'       => $KM          ,
    ':VK'       => $VK          ,
    ':EKBrutto' => $EKBrutto    ,
    ':EKNetto'  => $EKNetto     ,
    ':Farbe'    => $Lack        ,
    ':VKDatum'  => $VKDatum     ,
));

answered Sep 03 '16 at 13:47

Dave

5,108
16
30
40

updated, still wont work! – Max Bogatec Sep 04 '16 at 06:48
You have 2 variable name mismatches between your retrieval of POST values and what you are using in the query. You use ':FIN' in the execute but :Fin in the query so those do not match. You also use :Farbe in the execute but :Lack in the query and those do not match either. Correct both of those names and see if things work. – Dave Sep 04 '16 at 10:13

score 0 · Accepted Answer · answered Sep 05 '16 at 10:36

Solved code:

<?php
function updater($value,$value2,$value3,$value4,$value5,$value6,$value7,$value8,$value9,$value10,$value11,$value12,$value13,$value14,$value15,$value16,$value17,$value18){
    // Create connection
    $conn = new mysqli( 'localhost' , 'user' , 'pass' ,'db_name' );
    $value =mysqli_real_escape_string($conn,$value);
    $value2 =mysqli_real_escape_string($conn,$value2);
    $value3 =mysqli_real_escape_string($conn,$value3);
    $value4 =mysqli_real_escape_string($conn,$value4);
    $value5 =mysqli_real_escape_string($conn,$value5);
    $value6 =mysqli_real_escape_string($conn,$value6);
    $value7 =mysqli_real_escape_string($conn,$value7);
    $value8 =mysqli_real_escape_string($conn,$value8);
    $value9 =mysqli_real_escape_string($conn,$value9);
    $value10 =mysqli_real_escape_string($conn,$value10);
    $value11 =mysqli_real_escape_string($conn,$value11);
    $value12 =mysqli_real_escape_string($conn,$value12);
    $value13 =mysqli_real_escape_string($conn,$value13);
    $value14 =mysqli_real_escape_string($conn,$value14);
    $value15 =mysqli_real_escape_string($conn,$value15);
    $value16 =mysqli_real_escape_string($conn,$value16);
    $value17 =mysqli_real_escape_string($conn,$value17);
    $value18 =mysqli_real_escape_string($conn,$value18);
    // Check connection

    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    }   
    $sql = "UPDATE Fahrzeugverkauf SET EKNR='{$value}', EKDatum='{$value3}' , RGNummer='{$value4}' , Marke='{$value5}' , Modell='{$value6}' , Farbe='{$value7}', VKNummer='{$value8}', EZ='{$value9}', FIN='{$value10}', KM='{$value11}', VK='{$value12}', EKBrutto='{$value13}', EKNetto='{$value14}', VKDatum='{$value15}', MotorNr='{$value16}', Status='{$value17}', Anmerkung='{$value18}' WHERE EKNR='{$value2}'";
    if ($conn->query($sql) === TRUE) {
        echo "Record updated successfully";
    } else {
        echo "Error updating record: " . $conn->error;
    }
    $conn->close();
}   

if($_SERVER['REQUEST_METHOD'] === 'POST') {
    $value = $_POST['EKNR'];
    $value2 = $_POST['EKNR2'];
    $value3 = $_POST['EK-Datum'];
    $value4 = $_POST['RG-Nummer'];
    $value5 = $_POST['Marke'];
    $value6 = $_POST['Modell'];
    $value7 = $_POST['Farbe'];
    $value8 = $_POST['VKnummer'];
    $value9 = $_POST['EZ'];
    $value10 = $_POST['FIN'];
    $value11 = $_POST['KM'];
    $value12 = $_POST['VK'];
    $value13 = $_POST['EK-Brutto'];
    $value14 = $_POST['EK-Netto'];
    $value15 = $_POST['Verkaufsdatum'];
    $value16 = $_POST['Motornummer'];
    $value17 = $_POST['selektion'];
    $value18 = $_POST['anmerkung'];
    updater($value,$value2,$value3,$value4,$value5,$value6,$value7,$value8,$value9,$value10,$value11,$value12,$value13,$value14,$value15,$value16,$value17,$value18);
}
?>

It is generally considered safer to use parameter binding instead of escaping these days, since there are some edge-case injection attacks involving extended character sets. The good news is that MySQLi supports parameter binding directly. — halfer, Sep 22 '16 at 09:45

Update table error in PHP

3 Answers3

Update