dongyu1918 2016-01-29 15:34
浏览 68
已采纳

INSERT INTO表打开DUPLICATE KEY UPDATE - 插入一个空行?

What is wrong with this MySQLi statment?

I want to do an INSERT INTO ON DUPLICATE KEY UPDATE

schema

'UID', 'char(17)', 'NO', 'PRI', NULL, ''
'stationID', 'int(11)', 'YES', '', NULL, ''
'temperature', 'float', 'YES', '', NULL, ''
'UV', 'float', 'YES', '', NULL, ''
'temperature_feels', 'float', 'YES', '', NULL, ''
'humidity', 'float', 'YES', '', NULL, ''
'weather_type', 'int(11)', 'YES', '', '-1', ''
'precipitation', 'float', 'YES', '', NULL, ''
'update_station_id', 'tinyint(4)', 'YES', '', '1', ''
'update_due', 'timestamp', 'YES', '', NULL, ''
'weather_status', 'varchar(128)', 'YES', '', NULL, ''

// code

    $sql = "INSERT INTO weather_data (uv, weather_status, weather_type, temperature, temperature_feels, humidity, precipitation, UID)
            VALUES (uv, weather_status, weather_type, temperature, temperature_feels, humidity, precipitation, UID)
            ON DUPLICATE KEY UPDATE uv = ?, weather_status = ?, weather_type = ?, temperature = ?, temperature_feels = ?, humidity = ?, precipitation = ?, UID = ?";
    $stmt = $dbh->prepare($sql);

    if (!$stmt) {
        throw new \Exception($dbh->error);
    }

    $stmt->bind_param('ssssssss', $uv, $weather_status, $weather_type, $temperature, $temperature_feels, $humidity, $precipitation, $UID);
    $stmt->execute();
    $stmt->close();

It seems to insert an empty row every time?

  • 写回答

2条回答 默认 最新

  • dtdvbf37193 2016-01-29 15:45
    关注

    looks kind of crazy but the initial values for all the fields are strings ( of sorts ) and not placeholders. You would need to bind for each

    $sql = "INSERT INTO weather_data (uv, weather_status, weather_type, temperature, temperature_feels, humidity, precipitation, UID)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?)
            ON DUPLICATE KEY UPDATE uv = ?, weather_status = ?, weather_type = ?, temperature = ?, temperature_feels = ?, humidity = ?, precipitation = ?, UID = ?";
    
        $stmt->bind_param('ssssssssssssssss', $uv, $weather_status, $weather_type, $temperature,
                               $temperature_feels, $humidity, $precipitation, $UID, 
                               $uv, $weather_status, $weather_type, $temperature, 
                               $temperature_feels, $humidity, $precipitation, $UID);
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?