0

I have a basic PDO Update Statement which is not working. I can't find why...Can anybody help me ?

$conn = new PDO("mysql:host=$host;dbname=$db", $login, $mdp);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$requete=$conn->prepare("UPDATE eleves
                            SET id_ecole = ?,
                            id_classe = ?,
                            id_eleve = ?,
                            nom = ?,
                            prenom = ?,
                            date_naiss = ?,
                            tel = ?,
                            mail = ?,
                            sortie = ?,
                            photo = ?,
                            cantine = ?,
                            religion = ?
                            WHERE (id_ecole = $id_ecole 
                                AND id_classe = $id_classe 
                                AND id_eleve = $id_eleve);");
$requete->execute(array($id_ecole, $id_classe, $id_eleve, 
                        $nom, $prenom, $date_naiss, $tel, 
                        $mail, $sortie, $photo, $cantine, 
                        $religion));

The table "eleves" contains an additional column which acts as an auto-increment key

Note that the following query works well :

$conn = new PDO("mysql:host=$host;dbname=$db", $login, $mdp);

$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$requete=$conn->prepare("INSERT INTO eleves (id_ecole, id_classe, 
                            id_eleve, nom, prenom, date_naiss, 
                            tel, mail, sortie, photo, cantine, religion)    
                    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);");
$requete->execute(array($id_ecole, $id_classe, $id_eleve, $nom, 
                        $prenom, $date_naiss, $tel, $mail, 
                        $sortie, $photo, $cantine, $religion));

Thank you !

RiggsFolly
  • 93,638
  • 21
  • 103
  • 149
Evosub
  • 47
  • 8
  • Your script is open to [SQL Injection Attack](http://stackoverflow.com/questions/60174). Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187) You should alway use [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) in either the `MYSQLI_` or `PDO` API's instead of concatenating user provided values into the query. Never trust ANY user input! – RiggsFolly Jul 27 '21 at 16:44
  • 1
    You clearly know how to use parametrized queries? Why aren't you using parameters in the `WHERE` clause? – Barmar Jul 27 '21 at 16:44
  • 2
    WHY did you not use parameters for ALL the query including the WHERE clause – RiggsFolly Jul 27 '21 at 16:44
  • Why are you setting the `id_ecole`, `id_classe`, and `id_eleve` columns to the same values that they already have? – Barmar Jul 27 '21 at 16:45
  • @RiggsFolly : You're right but i am the only user here... I will change that anyway thanks for the advice ! – Evosub Jul 27 '21 at 16:48
  • @Barmar humm you're right that's pretty dumb ! But the original problem stay the same. – Evosub Jul 27 '21 at 16:49
  • I can't see anything obviously wrong with the query. Are you sure the row with those IDs already exists? – Barmar Jul 27 '21 at 16:50
  • @Barmar : yeah i am sur that the row with those IDs exist. – Evosub Jul 27 '21 at 16:52
  • Then it should work. Note that `id_eleve` is enough to uniquely specify the row, since it's the primary key. – Barmar Jul 27 '21 at 16:55
  • @Barmar id_eleve is not the primary key. I can have 2 identical id_eleve in different "classe". And i can have 2 identical id_classe in different "ecole". That's why the primary key is a auto increment value. – Evosub Jul 27 '21 at 16:58
  • You say it's "not working" but that's not clear. Is there an exception? If so, what is the message returned in the exception? Does the exception occur on `prepare()` or `execute()`? Or is there no exception but the update doesn't affect any rows? Or it affects the wrong rows, or sets the wrong values? Please describe what "not working" means in this case. – Bill Karwin Jul 27 '21 at 17:01
  • Sorry, I misunderstood and thought that `id_eleve` was the auto increment column for the `eleves` table. Usually a column named `id_XXX` or `XXX_id` is the PK of the table `XXX`. – Barmar Jul 27 '21 at 17:02
  • What are the values of `$id_ecole`, `$id_classe`, and `$id_eleve` when you format the UPDATE statement? Are you sure they contain valid values and are not blank or null? – Bill Karwin Jul 27 '21 at 17:04
  • @BillKarwin "Not working" in this case = the treatment does nothing and i have 0 exception... I will ads echo on $id_ecole, $id_classe and $id_eleve – Evosub Jul 27 '21 at 17:05
  • The problem is solved... i had a space in the php name... My bad ! Thank you for your help ! – Evosub Jul 27 '21 at 17:51

0 Answers0