#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    26
    Rep Power
    0

    Insert Into...On Duplicate update


    I have done this before and its making me crazy. Also I have spent considerable time trying to fix this latest iteration and its killing me. I have searched and attempted to solve this on my own to no avail.

    I am trying to do a simple INSERT INTO /ON DUPLICATE UPDATE

    This works fine
    PHP Code:

    mysqli_query
    ($link,"INSERT INTO ss_cats 

    (cat_num, cat_des, attr1,attr2) 


    VALUES 
    (
    16,'endometrium','burrito','extravaganza'
    )"
    ); 

    This

    Does Absolutely nothing


    PHP Code:

    mysqli_query
    ($link,"INSERT INTO ss_cats 

    (cat_num, cat_des, attr1,attr2) 


    VALUES 
    (
    16,'endometrium','burrito','extravaganza'
    )

    ON DUPLIATE KEY UPDATE cat_des=VALUES(cat_des)"
    ); 

    Ultimately I want to end up Here: (which used to work, prior to my last PC format - has some slightly different table nakes , etc)

    PHP Code:

    $url_value
    ="http://www.ssicat.com/smart/updates.asmx/CategoryUpdate?CustomerNumber=15753&UserName=15753&Password=75035&Source=15753";
    $url_to_work_on=file_get_contents($url_value);//get the data
    $wrapping_xml=simplexml_load_string($url_to_work_on);//clean it up
    $final_data_to_parse=simplexml_load_string(html_entity_decode($wrapping_xml));

    $link mysqli_connect('localhost''root''password','test');
    if (
    mysqli_connect_errno())
      {
     
      die(
    "Failed to connect to MySQL: " mysqli_connect_error());
      }


    for(
    $i=0;$i<count($final_data_to_parse->Table);$i++)

     
    mysqli_query($link,"INSERT INTO cat_attributes 

    (cat_number, cat_des, attr1, attr2, attr3 , attr4 ,attr5 ,attr6 ,attr7 ,attr8 , attr9, attr10,attr11,attr12,attr13,attr14,attr15,attr16,attr17,attr18,attr19,attr20) 

    VALUES 
    (
    '"
    .$final_data_to_parse->Table[$i]->CATID."',
    '"
    .$final_data_to_parse->Table[$i]->CATDES."',
    '"
    .$final_data_to_parse->Table[$i]->ATTR1."',
    '"
    .$final_data_to_parse->Table[$i]->ATTR2."',
    '"
    .$final_data_to_parse->Table[$i]->ATTR3."',
    '"
    .$final_data_to_parse->Table[$i]->ATTR4."',
    '"
    .$final_data_to_parse->Table[$i]->ATTR5."',
    '"
    .$final_data_to_parse->Table[$i]->ATTR6."',
    '"
    .$final_data_to_parse->Table[$i]->ATTR7."',
    '"
    .$final_data_to_parse->Table[$i]->ATTR8."',
    '"
    .$final_data_to_parse->Table[$i]->ATTR9."',
    '"
    .$final_data_to_parse->Table[$i]->ATTR0."',
    '"
    .$final_data_to_parse->Table[$i]->ATTR11."',
    '"
    .$final_data_to_parse->Table[$i]->ATTR12."',
    '"
    .$final_data_to_parse->Table[$i]->ATTR13."',
    '"
    .$final_data_to_parse->Table[$i]->ATTR14."',
    '"
    .$final_data_to_parse->Table[$i]->ATTR15."',
    '"
    .$final_data_to_parse->Table[$i]->ATTR16."',
    '"
    .$final_data_to_parse->Table[$i]->ATTR17."',
    '"
    .$final_data_to_parse->Table[$i]->ATTR18."',
    '"
    .$final_data_to_parse->Table[$i]->ATTR19."',
    '"
    .$final_data_to_parse->Table[$i]->ATTR20."'

    )

     ON DUPLICATE KEY UPDATE 

    cat_number = values(cat_number),
     cat_des = values(cat_des),
     attr1 = values(attr1),
     attr2 = values(attr2),
     attr3 = values(attr3),
     attr4 = values(attr4),
     attr5 = values(attr5),
     attr6 = values(attr6),
     attr7 = values(attr7),
     attr8 = values(attr8),
     attr9 = values(attr9),
     attr10 = values(attr10),
     attr11= values(attr11),
     attr12= values(attr12),
     attr13= values(attr13),
     attr14= values(attr14),
     attr15= values(attr15),
     attr16= values(attr16),
     attr17 = values(attr17),
     attr18 = values(attr18),
     attr19 = values(attr19),
     attr20 = values(attr20)
      "
    );


    What the hell is going on with my code?


    Thanks in advance
  2. #2
  3. Did you steal it?
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,007
    Rep Power
    9398
    The ON DUPLICATE only works if the INSERT would violate a uniqueness constraint, either on the primary key or a unique key. If your INSERT is working without it then there's no constraint being violated.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    26
    Rep Power
    0
    Originally Posted by requinix
    The ON DUPLICATE only works if the INSERT would violate a uniqueness constraint, either on the primary key or a unique key. If your INSERT is working without it then there's no constraint being violated.
    I meant it works if I make it unique. But the whole statement when adding the DUPLICATE doesn't work, unique or not. I think it was a typo in my first couple statements i.e DUPLIATE instead of DUPLICATE, but that doesn't explain why the old code stopped working. Ill have to dig into that more. At least now the updating appears to work, so i can move on in the troubleshooting.

IMN logo majestic logo threadwatch logo seochat tools logo