mysql - 它的代码无法更新或插入数据到 mysql 数据库

Question

$retval = mysql_query("
IF EXISTS(SELECT * FROM config WHERE distance1 = '{$distance1}')

    UPDATE config SET distance2 = $distance2, distance3 = $distance3, totaldistance = $totaldistance, passengers = $passengers, chairwell = $chairwell, babychairs = $babychairs, companions = $ppc, luggage = $ppl, pet = $ppp, insurance = $in, stopinway = $siw where distance1 = $distance1

    ELSE

    INSERT into config(distance1, distance2, distance3, totaldistance, passengers, chairwell, babychairs, companions, luggage, pet, insurancestopinway) values('$distance1', '$distance2', '$distance3', '$totaldistance', '$passengers', '$chairwell', '$babychairs', '$ppc', '$ppl', '$ppp', '$in', '$siw')
    ");

Answer 1

我认为最好使用 INSERT INTO ... ON DUPLICATE KEY UPDATE语法。但之前您必须将列距离1 设置为“唯一”。所以这将是您的新查询:

INSERT into config (
distance1, distance2, distance3, 
totaldistance, passengers, chairwell, 
babychairs, companions, luggage, 
pet, insurancestopinway) 
values (
'$distance1', '$distance2', '$distance3', 
'$totaldistance', '$passengers', '$chairwell', 
'$babychairs', '$ppc', '$ppl', 
'$ppp', '$in', 
'$siw' #ATTENTION: you have 8 columns but 9 values, this will be generate an error
)
    ON DUPLICATE KEY UPDATE
    distance2 = '$distance2',
    distance3 = '$distance3'
    #...
    #and so on
");