SitePoint Sponsor

User Tag List

Results 1 to 18 of 18
  1. #1
    SitePoint Guru
    Join Date
    Apr 2007
    Posts
    685
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    What is wrong with this mysql statement?

    What is wrong with this mysql statement?
    PHP Code:
    UPDATE timesheet 
    SET
    monlaborcode0 
    '$_POST['monlaborcode0']',
    monexchange0 '$_POST['monexchange0']',
    monworkjob0 '$_POST['monworkjob0']',
    monreghours0 '$_POST['monreghours0']',
    WHERE username '$_SESSION[user_name]'") 

  2. #2
    He's No Good To Me Dead silver trophybronze trophy stymiee's Avatar
    Join Date
    Feb 2003
    Location
    Slave I
    Posts
    23,449
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)
    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.

  3. #3
    SitePoint Guru
    Join Date
    Apr 2007
    Posts
    685
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The server I use has error reporting off. So I'm not sure. It may be the quotes idk.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru
    Join Date
    Apr 2007
    Posts
    685
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You suggest:

    PHP Code:
    UPDATE timesheet 
    SET monlaborcode0 
    '$_POST['monlaborcode0']'
    monexchange0 '$_POST['monexchange0']'
    monworkjob0 '$_POST['monworkjob0']'
    monreghours0 '$_POST['monreghours0']'
    WHERE username '$_SESSION[user_name]'") 

  6. #6
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,706
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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 []

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by wickedneat View Post
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Guru
    Join Date
    Apr 2007
    Posts
    685
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Those quotes have been removed, still facing the error.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    sounds like it's not a mysql problem, moving thread to php forum
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    play of mind Ernie1's Avatar
    Join Date
    Sep 2005
    Posts
    1,252
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    you have to enclose the string with double quotes:
    PHP Code:
    UPDATE timesheet
    SET
    monlaborcode0 
    '".$_POST['monlaborcode0']."',
    monexchange0 '".$_POST['monexchange0']."',
    monworkjob0 '".$_POST['monworkjob0']."',
    monreghours0 '".$_POST['monreghours0']."',
    WHERE username '".$_SESSION[user_name]."'") 
    my mobile portal
    ghiris.ro

  11. #11
    SitePoint Guru
    Join Date
    Apr 2007
    Posts
    685
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    okay it's telling me this now:

    PHP Code:
    INSERT ErrorYou have an error in your SQL syntaxcheck the manual that corresponds to your MySQL server version for the right syntax to use near 'SET monlaborcode0 = 'asdf', monexchange0 = 'asdf', monworkjob0 = 

  12. #12
    play of mind Ernie1's Avatar
    Join Date
    Sep 2005
    Posts
    1,252
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    sorry I didn't notice the comma
    PHP Code:
    $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()); 
    my mobile portal
    ghiris.ro

  13. #13
    . shoooo... silver trophy logic_earth's Avatar
    Join Date
    Oct 2005
    Location
    CA
    Posts
    9,013
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)
    Things are easier to debug when you have a clean separation between different layers.

    PHP Code:
    $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']
    ); 
    Logic without the fatal effects.
    All code snippets are licensed under WTFPL.


  14. #14
    SitePoint Guru
    Join Date
    Apr 2007
    Posts
    685
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here is the error code I'm getting:

    PHP Code:
    INSERT ErrorYou have an error in your SQL syntaxcheck 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:

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

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    that's not how the ON DUPLICATE syntax works

    you want something like
    Code:
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    SitePoint Enthusiast
    Join Date
    Jul 2006
    Posts
    83
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    kill the single quote within the array
    PHP Code:
    UPDATE timesheet 

    SET

    monlaborcode0 
    '$_POST[monlaborcode0]',

    monexchange0 '$_POST[monexchange0]',

    monworkjob0 '$_POST[monworkjob0]',

    monreghours0 '$_POST[monreghours0]',

    WHERE username '$_SESSION[user_name]'") 
    This should work fine.
    My Network
    Deluxe Web Directory
    FontCubes Free Fonts

  17. #17
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    grumps, i think he's doing an INSERT, not an UPDATE

    wickedneat, could you give us some more info please?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  18. #18
    SitePoint Guru
    Join Date
    Apr 2007
    Posts
    685
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    that's not how the ON DUPLICATE syntax works

    you want something like
    Code:
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •