dongyu1918
dongyu1918
2016-01-29 15:34

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 dtdvbf37193 5年前

    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);
    
    点赞 评论 复制链接分享
  • dou91808 dou91808 5年前

    Update your query to:

    ON DUPLICATE KEY UPDATE 
      uv = VALUES(?),
      weather_status = VALUES(?),
      weather_type = VALUES(?),
      temperature = VALUES(?),
      temperature_feels = VALUES(?),
      humidity = VALUES(?),
      precipitation = VALUES(?),
      UID = VALUES(?)
    

    Also the issue is that param go's with each ?, since after the insert you continue to use ? you need to add those arguments as well. You could avoid this with PDO using named params.

    $stmt->bind_param('dsidddddidsidddddi', /* first set dsidddddi */ 
      $uv, 
      $weather_status,
      $weather_type,
      $temperature,
      $temperature_feels,
      $humidity,
      $precipitation,
      $UID,
      $uv, // because ? counts further.
      $weather_status,
      $weather_type,
      $temperature,
      $temperature_feels,
      $humidity,
      $precipitation,
      $UID,
    );
    
    $stmt->execute();
    
    点赞 评论 复制链接分享