ON DUPLICATE KEY UPDATE What am i doing wrong_

Question

When i try to post my data by PHP into SQL, I get an error message on the webpage:

Error: insert INTO mydata ( uke, man1, tir1, colman1, coltir1 ) VALUES ( '7', '','','','') ON DUPLICATE KEY UPDATE man1 = , tir1 = , colman1 = , coltir1 = 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 ' tir1 = ' at line 14

$uke = $_POST[uke];
$man1 = $_POST[man1];
$tir1 = $_POST[tir1];
$colman1 = $_POST[colman1];
$coltir1 = $_POST[coltir1];



  $sql = "insert INTO mydata  ( uke,  
  man1, tir1, 
  colman1, coltir1)
  VALUES 
  ( 
  '$uke',
  '$man1','$tir1',
  '$colman1','$coltir1')
ON DUPLICATE KEY UPDATE 
man1 = $man1,
tir1 = $tir1,
colman1 = $colman1,
coltir1 = $coltir1";

SQL is:

uke int(11) No  Primary NULL    
timer   float(255,2)    Yes None    NULL    
man1    decimal(20,2)   Yes None    NULL    
tir1    decimal(20,2)   Yes None    NULL    
colman1 varchar(255)    Yes None    NULL
coltir1 varchar(255)    Yes None    NULL

Can anyone give me a hint (or tell me) where i have the error?

Is this that you want? `UPDATE man1 = '', tir1 = '', colman1 = '', coltir1 = ''` — John Woo, Jan 19 '18 at 08:26
and this query has sql injection bug you must use parameters. — A1Gard, Jan 19 '18 at 08:28
Please do not include "SOLVED" in a Question title, thank you. — Cœur, Jul 08 '18 at 13:06

score 1 · Answer 1 · answered Jan 19 '18 at 08:30

1

$uke = (isset($_POST[uke])) ? $_POST[uke] : '';
$man1 = (isset($_POST[man1])) ? $_POST[man1] : '';
$tir1 = (isset($_POST[tir1])) ? $_POST[tir1] : '';
$colman1 = (isset($_POST[colman1])) ? $_POST[colman1] : '';
$coltir1 = isset($_POST[coltir1])) ? $_POST[coltir1] : '';

$sql = "INSERT INTO mydata  ( uke, man1, tir1, colman1, coltir1)
    VALUES ( '$uke', '$man1','$tir1', '$colman1','$coltir1')
   ON DUPLICATE KEY UPDATE 
   man1 = '" .  $man1 . "',
   tir1 = '" . $tir1 . "',
   colman1 = '" . $colman1. "',
   coltir1 = '" . $coltir1 . "'";

Try this.

answered Jan 19 '18 at 08:30

Gagan Upadhyay

255
2
11

Thank you for your answer. Is this inject proofing? – Bergum Jan 20 '18 at 09:03
1

@Bergum No, it is not. – Matteo Tassinari Jul 01 '18 at 07:18

Tharindu Thisarasinghe · Accepted Answer · 2018-01-19T08:36:28.310

0

The query should be updated like this.

$sql = "insert INTO mydata  ( uke,  man1, tir1, colman1, coltir1)
                  VALUES ( '$uke','$man1','$tir1', '$colman1','$coltir1')
                ON DUPLICATE KEY UPDATE 
                man1 = '$man1',
                tir1 = '$tir1',
                colman1 = '$colman1',
                coltir1 = '$coltir1'";

edited Jan 19 '18 at 08:36

answered Jan 19 '18 at 08:31

Tharindu Thisarasinghe

3,846
8
39
70

1

This worked. Thank you. I have to read more about sql injecting i see. :-) – Bergum Jan 20 '18 at 09:04
Glad that helpd! :) And mark the answer as the accepted answer. So, others can easily find the correct answer! – Tharindu Thisarasinghe Jan 20 '18 at 09:05
What is the difrence between your and Gagan Upadhyay answer? – Bergum Jan 20 '18 at 09:07
1

That also should work. He's just contacting the variables to the query string. https://stackoverflow.com/questions/5605965/php-concatenate-or-directly-insert-variables-in-string – Tharindu Thisarasinghe Jan 20 '18 at 09:13

ON DUPLICATE KEY UPDATE What am i doing wrong_

2 Answers2