What is wrong with this mysql statement?

[wickedneat]

What is wrong with this mysql statement?

UPDATE timesheet 
SET
monlaborcode0 = '$_POST['monlaborcode0']',
monexchange0 = '$_POST['monexchange0']',
monworkjob0 = '$_POST['monworkjob0']',
monreghours0 = '$_POST['monreghours0']',
WHERE username = '$_SESSION[user_name]'") 

[stymiee]

What does Mysql say? It will give you an error message letting you know the error is.

Also, you may have syntax errors there with your quotes. You should clearly separate your PHP from your SQL.

[wickedneat]

The server I use has error reporting off. So I'm not sure. It may be the quotes idk.

[r937]

dangling comma

see also these previous threads for examples of how using the leading comma convention would've avoided it

http://www.sitepoint.com/forums/showthread.php?t=332060
http://www.sitepoint.com/forums/showthread.php?t=311102
http://www.sitepoint.com/forums/showthread.php?t=307641
http://www.sitepoint.com/forums/showthread.php?t=237924
http://www.sitepoint.com/forums/showthread.php?t=184470

[wickedneat]

You suggest:

UPDATE timesheet 
SET monlaborcode0 = '$_POST['monlaborcode0']'
, monexchange0 = '$_POST['monexchange0']'
, monworkjob0 = '$_POST['monworkjob0']'
, monreghours0 = '$_POST['monreghours0']'
WHERE username = '$_SESSION[user_name]'") 

[guelphdad]

your error might be the use of your quotes. you cant use single quotes around '$_POST[]' as well as single quotes around the value inside of []

[r937]

You suggest: ...

as far as the commas go, yes, that's it exactly

don't know about the php quotes, as i don't do php

[wickedneat]

Those quotes have been removed, still facing the error.

[r937]

sounds like it's not a mysql problem, moving thread to php forum

[Ernie1]

you have to enclose the string with double quotes:

UPDATE timesheet
SET
monlaborcode0 = '".$_POST['monlaborcode0']."',
monexchange0 = '".$_POST['monexchange0']."',
monworkjob0 = '".$_POST['monworkjob0']."',
monreghours0 = '".$_POST['monreghours0']."',
WHERE username = '".$_SESSION[user_name]."'") 

[wickedneat]

okay it's telling me this now:

INSERT Error: 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 'SET monlaborcode0 = 'asdf', monexchange0 = 'asdf', monworkjob0 = 

[Ernie1]

sorry I didn't notice the comma

$x = mysql_query("UPDATE timesheet 
SET 
monlaborcode0 = '".$_POST['monlaborcode0']."', 
monexchange0 = '".$_POST['monexchange0']."', 
monworkjob0 = '".$_POST['monworkjob0']."', 
monreghours0 = '".$_POST['monreghours0']."' 
WHERE username = '".$_SESSION['user_name']."'") or die(mysql_error()); 

[logic_earth]

Things are easier to debug when you have a clean separation between different layers.

$sql = <<<SQL
UPDATE timesheet
   SET
        monlaborcode0 = '%s'
      , monexchange0  = '%s'
      , monworkjob0   = '%s'
      , monreghours0  = '%s'
    WHERE
        username = '%s'
SQL;

$sql = sprintf($sql,
    $_POST['monlaborcode0'],
    $_POST['monexchange0'],
    $_POST['monworkjob0'],
    $_POST['monreghours0'],
    $_SESSION['user_name']
); 

[wickedneat]

Here is the error code I'm getting:

INSERT Error: 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 'SET monlaborcode0 = '740' WHERE username = 'test')' at line 4 

Here is the code:

ON DUPLICATE KEY
UPDATE timesheet 
SET
monlaborcode0 = '$monlaborcode0'
WHERE 
username = '".$_SESSION['user_name']."')") 

[r937]

that's not how the ON DUPLICATE syntax works

you want something like

ON DUPLICATE KEY UPDATE monlaborcode0 = '$monlaborcode0'

note there's no WHERE clause because it's the attempted insert of a duplicate key that triggers the update

[Grumps]

kill the single quote within the array

UPDATE timesheet 

SET

monlaborcode0 = '$_POST[monlaborcode0]',

monexchange0 = '$_POST[monexchange0]',

monworkjob0 = '$_POST[monworkjob0]',

monreghours0 = '$_POST[monreghours0]',

WHERE username = '$_SESSION[user_name]'") 

This should work fine.

[r937]

grumps, i think he's doing an INSERT, not an UPDATE

wickedneat, could you give us some more info please?

[wickedneat]

that's not how the ON DUPLICATE syntax works

you want something like

ON DUPLICATE KEY UPDATE monlaborcode0 = '$monlaborcode0'

note there's no WHERE clause because it's the attempted insert of a duplicate key that triggers the update

That's the fix :-D