将空字段从PHP引入MYSQL

Question

我在尝试将NULL引入我的数据库字段时遇到了问题。我的行由24列(双精度值)组成，无论我尝试什么，它总是显示为0。

有什么想法吗？我已经阅读了插入不带引号的null应该有效(mysql的保留字)，但是它插入了0。

这是我的代码的简短版本：

Android：

for(int i = lastID; i < data2.get(SensorType.ACCELEROMETER_X).size(); i++){

    List<NameValuePair> nameValuePairs = new ArrayList<NameValuePair>(data2.keySet().size()+1);
    Set<SensorType> signals = data2.keySet();
    nameValuePairs.add(new BasicNameValuePair("TableName", tableNameSignals));
    for(SensorType s: signals){

        if(data2.get(s).get(i).isNaN()){
            //HERE IS WHERE I WANT THE FIELD TO APPEAR NULL
            nameValuePairs.add(new BasicNameValuePair(s.getAbbreviature(), "null"));
        }
        else{
            nameValuePairs.add(new BasicNameValuePair(s.getAbbreviature(), data2.get(s).get(i).toString()));

        }
    }

    httpPost2.setEntity(new UrlEncodedFormEntity(nameValuePairs));
    HttpResponse httpResponse2 = httpClient.execute(httpPost2);

PHP：

<?php

//Array for Json response
$response = array();

//Include DB class
require_once __DIR__ . '/db_connect_v2.php';

require_once __DIR__ . '/db_config_v2.php';

$mysqli = new mysqli(DB_SERVER, DB_USER, DB_PASSWORD, DB_DATABASE);

if ($mysqli->connect_errno)

    die('Connection Error (' . $mysqli->connect_errno . ') ' . $mysqli->connect_error);

$tableName = $_POST['TableName'];

$sql1 = "CREATE TABLE IF NOT EXISTS " . "$tableName" . "(ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT, AccX double, AccY double, AccZ double, MagX double, MagY double, MagZ double, GyrX double, GyrY double,
    GyrZ double, Light double, Pressure double, Proximity double, GravX double, GravY double, GravZ double, LinAccX double, LinAccY double, LinAccZ double, 
    RotVecX double, RotVecY double, RotVecZ double, Humidity double, Temperature double, TimeStamp double);";

$result1 = $mysqli->query($sql1);

if($result1){

    $accX = $_POST['AccX']; $accY = $_POST['AccY']; $accZ = $_POST['AccZ']; $magX = $_POST['MagX'];
    $magY = $_POST['MagY']; $magZ = $_POST['MagZ']; $gyrX = $_POST['GyrX']; $gyrY = $_POST['GyrY'];
    $gyrZ = $_POST['GyrZ']; $light = $_POST['Light']; $pressure = $_POST['Pressure']; $proximity = $_POST['Proximity'];
    $gravX = $_POST['GravX']; $gravY = $_POST['GravY']; $gravZ = $_POST['GravZ']; $linAccX = $_POST['LinAccX'];
    $linAccY = $_POST['LinAccY']; $linAccZ = $_POST['LinAccZ']; $rotVecX = $_POST['RotVecX'];
    $rotVecY = $_POST['RotVecY'];  $rotVecZ = $_POST['RotVecZ'];  $humidity = $_POST['Humidity'];  $temperature = $_POST['Temperature'];
    $timeStamp = $_POST['TimeStamp'];

    $sql2 = "INSERT INTO " . "$tableName" . "(AccX, AccY, AccZ, MagX, MagY, MagZ, GyrX, GyrY, GyrZ, Light, Pressure, Proximity, GravX, GravY, GravZ, 
    LinAccX, LinAccY, LinAccZ, RotVecX, RotVecY, RotVecZ, Humidity, Temperature, TimeStamp) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);";

    $stmt = $mysqli->prepare($sql2);
    $stmt->bind_param('dddddddddddddddddddddddd', $accX, $accY, $accZ, $magX, $magY, $magZ, $gyrX, $gyrY, $gyrZ, $light, $pressure,
    $gravX, $gravY, $gravZ, $proximity, $linAccX, $linAccY, $linAccZ, $rotVecX, $rotVecY, $rotVecZ, $humidity, $temperature, $timeStamp);

    $result2 = $stmt->execute();

    $stmt->close();
    $mysqli->close();

    if($result2){

        $response['Success'] = 1;
        $response['Message'] = "Row was uploaded succesfully!";
        echo json_encode($response);
    }

    else{

        $response['Success'] = 0;
        $response['Message'] = "Oops! Couldnt upload data! (Mobile Signals)";
        $response["PhpError"] = mysql_error();
        echo json_encode($response);
    }
}

else{

    $response['Success']=0;
    $response['Message']="Oops! Couldnt create table! (Mobile Signals)";
    $response["PhpError"] = mysql_error();
    echo json_encode($response);
}

?>

解决方案：

问题是在BasicNameValuePair中插入null时，字段将作为空字符串发送到php脚本。如果我在php脚本中这样做：

if($_POST['AccX'] != "") $accX = $_POST['AccX']; else $accX = NULL;

一切都很好。

Answer 1

尝尝这个。

for(SensorType s: signals){

    nameValuePairs.add(new BasicNameValuePair(s.getAbbreviature(), 
        data2.get(s).get(i).isNaN()?null:data2.get(s).get(i).toString();
    ));
}

不是以字符串形式发送"null"，而是以null形式发送。BaseNameValuePair也接受null值。

• BaseNameValuePair | Android Developers