sql update on compound key

Discussion in 'Web Design and Development' started by Cabbit, Jun 7, 2009.

  1. Cabbit macrumors 68020

    Cabbit

    Joined:
    Jan 30, 2006
    Location:
    Scotland
    #1
    PHP:
    // Instert a frech product to the order
            
    $result mysql_query("UPDATE `Order Items`  SET `Qty`=`Qty`+1 WHERE `ProductID` = '$productid'");
            if (
    mysql_affected_rows()==0
            {
                
    $result mysql_query("INSERT INTO `Order Items` (`OrderNumber`,`ProductID`,`Qty`) VALUES 
                    ('
    $ordernumber','$productid','1')");
            }
    This has been buging me till daylight, how do i get it to update if a record is already found with a compound key or ProductID and OrderNumber?
     
  2. savar macrumors 68000

    savar

    Joined:
    Jun 6, 2003
    Location:
    District of Columbia
    #2
    Not fully sure I understand, but this might be what you're looking for: http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html
     
  3. Cabbit thread starter macrumors 68020

    Cabbit

    Joined:
    Jan 30, 2006
    Location:
    Scotland
    #3
    No ON DUPLICATE KEY does not function with a compound key.
     
  4. Cabbit thread starter macrumors 68020

    Cabbit

    Joined:
    Jan 30, 2006
    Location:
    Scotland
    #4
    I got it, i forgot to add a AND so that it was checking against both keys.
     

Share This Page