Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 3 123 LastLast
Results 1 to 15 of 41
  1. #1
    New Coder
    Join Date
    Oct 2007
    Location
    Washington
    Posts
    86
    Thanks
    3
    Thanked 2 Times in 2 Posts

    Update mysql php code not working

    Any ideas why the code below is not updating?

    PHP Code:
    $fieldstoinsert=array('purchaser''orderno''contact''shipto''street''city''state''zip''orderdate''reqshipdate''shipviaUPS''shipviaTC''shiptotel''dealertel''mobiletel''specinstr''blumotion''casetape''caseconst''drwbox''state''RosBox''t2''t3''t4''t5''t6''t7''t8');


    $query "UPDATE formdata SET `userid` = '$userid', `fieldid` = '$fieldid', `value` = '$value', `saveas` = '$saveas' VALUES";
    foreach(
    $_POST as $fieldid => $value){
        if(
    in_array($fieldid,$fieldstoinsert)){
            if(
    trim($value)!=""){
              
    $query.= "('$userid', '$fieldid', '$value', '$saveas'),";
            }
        }
    }  
    $query=substr($query,0,-1); 
    "The one closer to the truth is the one still learning"

  • #2
    Senior Coder djm0219's Avatar
    Join Date
    Aug 2003
    Location
    Wake Forest, North Carolina
    Posts
    1,293
    Thanks
    4
    Thanked 202 Times in 199 Posts
    The code you posted never does an update. You build up an UPDATE clause but never do anything with it. At least not in what you posted.
    Dave .... HostMonster for all of your hosting needs

  • #3
    New Coder
    Join Date
    Oct 2007
    Location
    Washington
    Posts
    86
    Thanks
    3
    Thanked 2 Times in 2 Posts
    Originally I had

    PHP Code:
    $fieldstoinsert=array('purchaser''orderno''contact''shipto''street''city''state''zip''orderdate''reqshipdate''shipviaUPS''shipviaTC''shiptotel''dealertel''mobiletel''specinstr''blumotion''casetape''caseconst''drwbox''state''RosBox''t2''t3''t4''t5''t6''t7''t8');


    $query "INSERT INTO formdata (userid, fieldid, value, saveas) VALUES";
    foreach(
    $_POST as $fieldid => $value){
        if(
    in_array($fieldid,$fieldstoinsert)){
            if(
    trim($value)!=""){
              
    $query.= "('$userid', '$fieldid', '$value', '$saveas'),";
            }
        }
    }  
    $query=substr($query,0,-1); 
    How would I make it so it updates and saves any new fields to the db?

    The code above works perfect for saving to the db and works well retrieving but I cant seem to replace any duplicate values pertaining to saveas value which is the saved form name.
    "The one closer to the truth is the one still learning"

  • #4
    Senior Coder tomws's Avatar
    Join Date
    Nov 2007
    Location
    Arkansas
    Posts
    2,644
    Thanks
    29
    Thanked 330 Times in 326 Posts
    Are you a Help Vampire?

  • #5
    New Coder
    Join Date
    Oct 2007
    Location
    Washington
    Posts
    86
    Thanks
    3
    Thanked 2 Times in 2 Posts
    Thanks for the link, but im getting ahead of my self now.

    This works perfect
    PHP Code:
    foreach($_POST as $fieldid => $value){
        if(
    in_array($fieldid,$fieldstoinsert)){
            if(
    trim($value)!=""){
                        
    mysql_query("UPDATE formdata SET userid = '$userid', fieldid = '$fieldid', value = '$value', saveas = '$saveas' WHERE saveas = '$saveas'");
            }
        }

    now how can I make these to work together so that it can insert new values as well as update existing values?

    PHP Code:
    $fieldstoinsert=array('purchaser''orderno''contact''shipto''street''city''state''zip''orderdate''reqshipdate''shipviaUPS''shipviaTC''shiptotel''dealertel''mobiletel''specinstr''blumotion''casetape''caseconst''drwbox''state''RosBox''t2''t3''t4''t5''t6''t7''t8');


    $query "INSERT INTO formdata (userid, fieldid, value, saveas) VALUES";
    foreach(
    $_POST as $fieldid => $value){
        if(
    in_array($fieldid,$fieldstoinsert)){
            if(
    trim($value)!=""){
              
    $query.= "('$userid', '$fieldid', '$value', '$saveas'),";
            }
        }
    }  
    $query=substr($query,0,-1); 
    table: formdata
    The db structure as shown
    userid | fieldid | value | saveas
    "The one closer to the truth is the one still learning"

  • #6
    Senior Coder tomws's Avatar
    Join Date
    Nov 2007
    Location
    Arkansas
    Posts
    2,644
    Thanks
    29
    Thanked 330 Times in 326 Posts
    Can you clarify your question? Sounds like you're asking how to perform two queries simultaneously, which can't be done.
    Are you a Help Vampire?

  • #7
    New Coder
    Join Date
    Oct 2007
    Location
    Washington
    Posts
    86
    Thanks
    3
    Thanked 2 Times in 2 Posts
    According to ON DUPLICATE KEY UPDATE I can but I still cant get it to work. Any ideas?

    I've tried using this before and I've tried every combination I could think of and will not save and update, any Ideas what I'm doing wrong?
    PHP Code:
    $query "INSERT INTO formdata (userid, fieldid, value, saveas) VALUES (saveas) ON DUPLICATE KEY UPDATE WHERE saveas = '$saveas'";
    foreach(
    $_POST as $fieldid => $value){
        if(
    in_array($fieldid,$fieldstoinsert)){
            if(
    trim($value)!=""){
              
    $query.= "('$userid', '$fieldid', '$value', '$saveas'),";
            }
        }
    }  
    $query=substr($query,0,-1); 
    "The one closer to the truth is the one still learning"

  • #8
    Senior Coder tomws's Avatar
    Join Date
    Nov 2007
    Location
    Arkansas
    Posts
    2,644
    Thanks
    29
    Thanked 330 Times in 326 Posts
    Well, that's not 2 queries executing simultaneously, but at least I understand what you're trying to do.

    It looks like your query is all screwed up. Aren't you seeing any errors? For one thing, the parameter count is mismatched between the field list and the values list.

    Another problem is that you're using the UPDATE portion incorrectly. Look at the man page from your link. While I've never used it before, it certainly looks like anything past the keyword phrase is the update query itself, so your current WHERE makes no sense there.

    Furthermore, that loop doesn't make sense either. Unless I'm seeing it wrong, it looks like you're just attaching junk at the end of the query that also won't work. Echo the query string to confirm this.
    Are you a Help Vampire?

  • #9
    Senior Coder tomws's Avatar
    Join Date
    Nov 2007
    Location
    Arkansas
    Posts
    2,644
    Thanks
    29
    Thanked 330 Times in 326 Posts
    They don't have anchors on the comments, but look for this one in the top half of the page: Posted by Shane McEwan on October 27 2006 4:09am
    Are you a Help Vampire?

  • #10
    New Coder
    Join Date
    Oct 2007
    Location
    Washington
    Posts
    86
    Thanks
    3
    Thanked 2 Times in 2 Posts
    What about doing something like REPLACE

    PHP Code:
    $fieldstoinsert=array('purchaser''orderno''contact''shipto''street''city''state''zip''orderdate''reqshipdate''shipviaUPS''shipviaTC''shiptotel''dealertel''mobiletel''specinstr''blumotion''casetape''caseconst''drwbox''state''RosBox''t2''t3''t4''t5''t6''t7''t8');

    $query "REPLACE INTO formdata (userid, fieldid, value, saveas) VALUES WHERE saveas='$saveas'";
    foreach(
    $_POST as $fieldid => $value){ 
        if(
    in_array($fieldid,$fieldstoinsert)){ 
            if(
    trim($value)!=""){ 
              
    $query.= "('$userid', '$fieldid', '$value', '$saveas'),"
            } 
        } 


    $query=substr($query,0,-1); 
    The way its set up when REPLACE was INSERT before saved perfectly.
    now with REPLACE it still saves but REPLACE is ignored and is inserted instead. How would I make it if WHERE saveas='$saveas' is to replace any duplicates matching the rows with the same saveas value, and insert if there is no existing value?
    "The one closer to the truth is the one still learning"

  • #11
    Senior Coder tomws's Avatar
    Join Date
    Nov 2007
    Location
    Arkansas
    Posts
    2,644
    Thanks
    29
    Thanked 330 Times in 326 Posts
    I've used REPLACE INTO, and that will work as long as you don't need to update column data based upon previous values.

    Your query there doesn't provide any values to insert/replace into the table, so you're telling it to insert NULL where saveas='$saveas'.

    Moreover, I think that "value" is a reserved word in the MySQL syntax, so using that as a fieldname and using it in the query string without backticking it may cause problems. And there's still that loop that's tacking on the extra stuff at the end. Have you echoed the string to see it yet? You still haven't mentioned anything about specific errors you're receiving, either.
    Are you a Help Vampire?

  • #12
    New Coder
    Join Date
    Oct 2007
    Location
    Washington
    Posts
    86
    Thanks
    3
    Thanked 2 Times in 2 Posts
    table structure as follows,

    Table name: formdata
    userid | fieldid | value | saveas

    I've tried INSERT ON DUPLICATE and cant seem to get it to work.
    Original code which successfully works great for inserting:
    PHP Code:
    $fieldstoinsert=array('purchaser''orderno''contact''shipto''street''city''state''zip''orderdate''reqshipdate''shipviaUPS''shipviaTC''shiptotel''dealertel''mobiletel''specinstr''blumotion''casetape''caseconst''drwbox''state''RosBox''t2''t3''t4''t5''t6''t7''t8');


    $query "INSERT INTO formdata (userid, fieldid, value, saveas) VALUES";
    foreach(
    $_POST as $fieldid => $value){
        if(
    in_array($fieldid,$fieldstoinsert)){
            if(
    trim($value)!=""){
              
    $query.= "('$userid', '$fieldid', '$value', '$saveas'),";
            }
        }
    }  
    $query=substr($query,0,-1); 
    I've tried UPDATE which works great for updating
    PHP Code:
    foreach($_POST as $fieldid => $value){
        if(
    in_array($fieldid,$fieldstoinsert)){
            if(
    trim($value)!=""){
                        
    mysql_query("UPDATE formdata SET userid = '$userid', fieldid = '$fieldid', value = '$value', saveas = '$saveas' WHERE saveas = '$saveas'");
            }
        }

    I just need to insert if no row exists and/or update if row exists.
    I've tried INSERT ON DUPLICATE but not sure how to get it working. everybody keeps telling me different.
    The data is put into the $fieldstoinsert=array and according to the table at top, It INSERTS and UPDATES just fine, just not at the same time
    "The one closer to the truth is the one still learning"

  • #13
    Senior Coder tomws's Avatar
    Join Date
    Nov 2007
    Location
    Arkansas
    Posts
    2,644
    Thanks
    29
    Thanked 330 Times in 326 Posts
    Ok, if it's inserting, then that clarifies that "value" as a field name is at least working in this case. One potential problem out of the way.

    I see now where your mistake is - you're trying to just change part of the query above that loop and not changing anything else, so your query ends up being garbage. If you would examine the error messages (as previously requested) and if you would echo the query string (also as previously requested) for either of the ON DUPLICATE KEY UPDATE or REPLACE INTO, you would likely see the problem.

    In the case of the error message, it might say something about a problem with your query near blah-blah-blah. That blah-blah-blah is pointing to the location of the error. Echoing the query string would be even better. It might show something like this:

    REPLACE INTO formdata (userid, fieldid, value, saveas) VALUES WHERE saveas='someSaveAs'('someUID', 'someFID', 'someValue', 'someSaveAs'),('someUID', 'someFID', 'someValue', 'someSaveAs'),('someUID', 'someFID', 'someValue', 'someSaveAs'),('someUID', 'someFID', 'someValue', 'someSaveAs'),('someUID', 'someFID', 'someValue', 'someSaveAs'),('someUID', 'someFID', 'someValue', 'someSaveAs'),('someUID', 'someFID', 'someValue', 'someSaveAs'),('someUID', 'someFID', 'someValue', 'someSaveAs')

    That's not a properly structured query, so it's going to barf on you with the error message(s) that you refuse to post.

    For the ON DUPLICATE KEY UPDATE query, follow the man page and build the new query as an extension on the base INSERT query. Try something like this:
    PHP Code:
    $query /*same base query*/
    foreach($_POST as $fieldid => $value){
    /*same loop*/
    }  
    $query=substr($query,0,-1); // leave this, too
    /*Now, do what the man page clearly shows*/
    // assuming the unique key is userid
    $query .= " ON DUPLICATE KEY UPDATE fieldid=VALUES(fieldid), value=VALUES(value), saveas=VALUES(saveas)"
    Will that work? I don't know. But it looks like it follows the man page and the comment that I mentioned above.
    Are you a Help Vampire?

  • #14
    New Coder
    Join Date
    Oct 2007
    Location
    Washington
    Posts
    86
    Thanks
    3
    Thanked 2 Times in 2 Posts
    EDIT: I'm not sure if I'm doing the error checking right, I'm doing some research as I'm still new at this.

    Errors as follows from phpmyadmin
    The INSERT & UPDATE seem to work but an still an error when I insert it into phpmyadmin

    Error:INSERT
    Code:
    Error
    
    SQL query:
    
    $query = "INSERT INTO formdata (userid, fieldid, value, saveas) VALUES";
    
    MySQL said: Documentation
    #1064 - 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 '$query = "INSERT INTO formdata (userid, fieldid, value, saveas) VALUES"' at line 1
    Error:UPDATE
    Code:
    Error
    
    There seems to be an error in your SQL query. The MySQL server error output below, if there is any, may also help you in diagnosing the problem
    
    ERROR: Unknown Punctuation String @ 28
    STR: =>
    SQL: foreach($_POST as $fieldid => $value){
        if(in_array($fieldid,$fieldstoinsert)){
            if(trim($value)!=""){
                        mysql_query("UPDATE formdata SET value = '$value' WHERE fieldid = '$fieldid'");foreach($_POST as $fieldid => $value){
        if(in_array($fieldid,$fieldstoinsert)){
            if(trim($value)!=""){
                        mysql_query("UPDATE formdata SET value = '$value' WHERE fieldid = '$fieldid'");foreach($_POST as $fieldid => $value){
        if(in_array($fieldid,$fieldstoinsert)){
            if(trim($value)!=""){
                        mysql_query("UPDATE formdata SET value = '$value' WHERE fieldid = '$fieldid'");
    
    
    SQL query:
    
    foreach($_POST as $fieldid => $value){ if(in_array($fieldid,$fieldstoinsert)){ if(trim($value)!=""){ mysql_query("UPDATE formdata SET value = '$value' WHERE fieldid = '$fieldid'");
    
    MySQL said: Documentation
    #1064 - 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 'foreach($_POST as $fieldid => $value){
        if(in_array($fieldid,$fieldstoinsert' at line 1
    Error:REPLACE
    Code:
    Error
    
    SQL query:
    
    $query = "REPLACE INTO formdata (userid, fieldid, value, saveas) VALUES WHERE saveas='$saveas'";
    
    MySQL said: Documentation
    #1064 - 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 '$query = "REPLACE INTO formdata (userid, fieldid, value, saveas) VALUES WHERE sa' at line 1
    Error:ON DUPLICATE KEY UPDATE
    Code:
    Error
    
    SQL query:
    
    $query = "INSERT INTO formdata (userid, fieldid, value, saveas) VALUES";
    
    MySQL said: Documentation
    #1064 - 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 '$query = "INSERT INTO formdata (userid, fieldid, value, saveas) VALUES"' at line 1
    Last edited by Jodarecode; 03-18-2009 at 05:56 PM.
    "The one closer to the truth is the one still learning"

  • #15
    Senior Coder tomws's Avatar
    Join Date
    Nov 2007
    Location
    Arkansas
    Posts
    2,644
    Thanks
    29
    Thanked 330 Times in 326 Posts
    You can't paste php code into the query window of phpMyAdmin. It needs real SQL. That's the cause of all 4 of those errors.
    Are you a Help Vampire?


  •  
    Page 1 of 3 123 LastLast

    Posting Permissions

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