PHP将JSON数据插入到Android的MySQL数据库中

I have an Android app that sends data via HTTP POST method to a PHP script and am trying to parse the data to store into a MySQL. The Android app was not written by me but I have access to the source code which I have included below; it sends the data packed as a JSON array. My PHP script right now attempts to put the data both into a text file and into a MySQL DB:

我有一个Android应用程序，它通过HTTP POST方法向PHP脚本发送数据，并试图解析数据以存储到MySQL中。Android应用不是我写的，但是我可以访问我下面包含的源代码;它以JSON数组的形式发送数据。我的PHP脚本现在尝试将数据放到文本文件中，并输入到MySQL DB中:

<?php

   $con=mysqli_connect("localhost","dbname","dbpassword","table");
   if (mysqli_connect_errno())
   {
     echo "Failed to connect to MySQL DB: " . mysqli_connect_error();
   }

   $filename = __DIR__.DIRECTORY_SEPARATOR."jsontest.txt";

   $postdata = file_get_contents("php://input"); 
   $data = json_decode($postdata, true);


   //if (array_key_exists('records', $data) && is_array($data['records'])) {
   //above if loop threw warning "array_key_exists() expects parameter 2 to be array,  null given"

   if (is_array($data['records'])) {
      foreach ($data['records'] as $record) {
        $name = $record['name'];
        $value = $record['value'];
    $event = $record['event'];
        $timestamp = $record['timestamp'];

        file_put_contents($filename, "$name -> $value with event: $event at $timestamp\n", FILE_APPEND);
        mysqli_query($con,"INSERT INTO `Driving Data`(`Name`, `Value`, `Event`, `Timestamp`) VALUES ($name, $value, $event, $timestamp)");
      }
   }

   mysqli_close($con);
?>

The text file prints out all of the data like I tell it to. The database, however, is never updated at all. The database is set up as follows:

文本文件打印出所有我告诉它的数据。然而，数据库从来没有更新过。数据库设置如下:

CREATE TABLE `Driving Data` (
 `Name` varchar(75) NOT NULL,
 `Value` varchar(40) NOT NULL,
 `Event` varchar(20) NOT NULL,
 `Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Here is how an example of the JSON data looks:

以下是JSON数据的示例:

{"records":[{"name":"accelerator_pedal_position","value":15.400001,"timestamp":1367598908.278000},{"name":"engine_speed","value":1716.0,"timestamp":1367598908.285000},{"name":"vehicle_speed","value":32.040001,"timestamp":1367598908.290000},{"name":"brake_pedal_status","value":false,"timestamp":1367598908.293000},{"name":"fuel_consumed_since_restart","value":0.147325,"timestamp":1367598908.301000},{"name":"transmission_gear_position","value":"third","timestamp":1367598908.304000},{"name":"steering_wheel_angle","value":-2.3733,"timestamp":1367598908.307000},{"name":"fuel_consumed_since_restart","value":0.14745,"timestamp":1367598908.314000},{"name":"transmission_gear_position","value":"third","timestamp":1367598908.317000},{"name":"door_status","value":"driver","event":false,"timestamp":1367598908.320000},{"name":"door_status","value":"passenger","event":false,"timestamp":1367598908.326000},{"name":"door_status","value":"rear_left","event":false,"timestamp":1367598908.329000},{"name":"door_status","value":"rear_right","event":false,"timestamp":1367598908.331000},{"name":"odometer","value":0.0,"timestamp":1367598908.338000},{"name":"high_beam_status","value":false,"timestamp":1367598908.341000},{"name":"steering_wheel_angle","value":-2.3733,"timestamp":1367598908.343000},{"name":"engine_speed","value":1716.0,"timestamp":1367598908.351000},{"name":"powertrain_torque","value":74.0,"timestamp":1367598908.358000},{"name":"accelerator_pedal_position","value":12.1,"timestamp":1367598908.364000},{"name":"latitude","value":42.293911,"timestamp":1367598908.367000},{"name":"longitude","value":-83.238762,"timestamp":1367598908.376000},{"name":"engine_speed","value":1718.0,"timestamp":1367598908.380000},{"name":"vehicle_speed","value":32.200001,"timestamp":1367598908.382000},{"name":"brake_pedal_status","value":false,"timestamp":1367598908.391000},{"name":"transmission_gear_position","value":"third","timestamp":1367598908.393000}]} 

So I am relatively new to PHP, and I cannot seem to figure out what the problem is (i.e. why the database is not being updated with these values but it can still write them out to a text file. For reference, below is the Android code used to send the data to the web server (I cannot change this):

因此，我对PHP比较陌生，而且我似乎无法弄清楚问题是什么(例如，为什么数据库没有使用这些值进行更新，但它仍然可以将这些值写到文本文件中。以下是用于将数据发送到web服务器的Android代码(我无法更改):

public class UploaderSink extends ContextualVehicleDataSink {
private final static String TAG = "UploaderSink";
private final static int UPLOAD_BATCH_SIZE = 25;
private final static int MAXIMUM_QUEUED_RECORDS = 5000;
private final static int HTTP_TIMEOUT = 5000;

private URI mUri;
private BlockingQueue<String> mRecordQueue =
        new LinkedBlockingQueue<String>(MAXIMUM_QUEUED_RECORDS);
private Lock mQueueLock = new ReentrantLock();
private Condition mRecordsQueued = mQueueLock.newCondition();
private UploaderThread mUploader = new UploaderThread();

/**
 * Initialize and start a new UploaderSink immediately.
 *
 * @param uri the URI to send HTTP POST requests to with the JSON data.
 */
public UploaderSink(Context context, URI uri) {
    super(context);
    mUri = uri;
}

public UploaderSink(Context context, String path) throws DataSinkException {
    this(context, uriFromString(path));
}

@Override
public void stop() {
    super.stop();
    mUploader.done();
}

public boolean receive(RawMeasurement measurement) {
    String data = measurement.serialize(true);
    mRecordQueue.offer(data);
    if(mRecordQueue.size() >= UPLOAD_BATCH_SIZE) {
        mQueueLock.lock();
        mRecordsQueued.signal();
        mQueueLock.unlock();
    }
    return true;
}

/**
 * Returns true if the path is not null and if it is a valid URI.
 *
 * @param path a URI to validate
 * @return true if path is a valid URI.
 *
 */
public static boolean validatePath(String path) {
    if(path == null) {
        Log.w(TAG, "Uploading path not set (it's " + path + ")");
        return false;
    }

    try {
        uriFromString(path);
        return true;
    } catch(DataSinkException e) {
        return false;
    }
}

@Override
public String toString() {
    return Objects.toStringHelper(this)
        .add("uri", mUri)
        .add("queuedRecords", mRecordQueue.size())
        .toString();
}

private static URI uriFromString(String path) throws DataSinkException {
    try {
        return new URI(path);
    } catch(java.net.URISyntaxException e) {
        throw new UploaderException(
            "Uploading path in wrong format -- expected: ip:port");
    }
}

private static class UploaderException extends DataSinkException {
    private static final long serialVersionUID = 7436279598279767619L;

    public UploaderException() { }

    public UploaderException(String message) {
        super(message);
    }
}

private class UploaderThread extends Thread {
    private boolean mRunning = true;

    public UploaderThread() {
        start();
    }

    public void run() {
        while(mRunning) {
            try {
                ArrayList<String> records = getRecords();
                String data = constructRequestData(records);
                HttpPost request = constructRequest(data);
                makeRequest(request);
            } catch(UploaderException e) {
                Log.w(TAG, "Problem uploading the record", e);
            } catch(InterruptedException e) {
                Log.w(TAG, "Uploader was interrupted", e);
                break;
            }
        }
    }

    public void done() {
        mRunning = false;
    }

    private String constructRequestData(ArrayList<String> records)
            throws UploaderException {
        StringWriter buffer = new StringWriter(512);
        JsonFactory jsonFactory = new JsonFactory();
        try {
            JsonGenerator gen = jsonFactory.createJsonGenerator(buffer);

            gen.writeStartObject();
            gen.writeArrayFieldStart("records");
            Iterator<String> recordIterator = records.iterator();
            while(recordIterator.hasNext()) {
                gen.writeRaw(recordIterator.next());
                if(recordIterator.hasNext()) {
                    gen.writeRaw(",");
                }
            }
            gen.writeEndArray();
            gen.writeEndObject();

            gen.close();
        } catch(IOException e) {
            Log.w(TAG, "Unable to encode all data to JSON -- " +
                    "message may be incomplete", e);
            throw new UploaderException();
        }
        return buffer.toString();
    }

    private HttpPost constructRequest(String data)
            throws UploaderException {
        HttpPost request = new HttpPost(mUri);
        try {
            ByteArrayEntity entity = new ByteArrayEntity(
                    data.getBytes("UTF8"));
            entity.setContentEncoding(
                    new BasicHeader("Content-Type", "application/json"));
            request.setEntity(entity);
        } catch(UnsupportedEncodingException e) {
            Log.w(TAG, "Couldn't encode records for uploading", e);
            throw new UploaderException();
        }
        return request;
    }

    private void makeRequest(HttpPost request) throws InterruptedException {
        HttpParams parameters = new BasicHttpParams();
        HttpConnectionParams.setConnectionTimeout(parameters, HTTP_TIMEOUT);
        HttpConnectionParams.setSoTimeout(parameters, HTTP_TIMEOUT);
        final HttpClient client = new DefaultHttpClient(parameters);
        try {
            HttpResponse response = client.execute(request);
            final int statusCode = response.getStatusLine().getStatusCode();
            if(statusCode != HttpStatus.SC_CREATED) {
                Log.w(TAG, "Got unxpected status code: " + statusCode);
            }
        } catch(IOException e) {
            Log.w(TAG, "Problem uploading the record", e);
            try {
                Thread.sleep(5000);
            } catch(InterruptedException e2) {
                Log.w(TAG, "Uploader interrupted after an error", e2);
                throw e2;
            }
        }
    }

    private ArrayList<String> getRecords() throws InterruptedException {
        mQueueLock.lock();
        if(mRecordQueue.isEmpty()) {
            // the queue is already thread safe, but we use this lock to get
            // a condition variable we can use to signal when a batch has
            // been queued.
            mRecordsQueued.await();
        }

        ArrayList<String> records = new ArrayList<String>();
        mRecordQueue.drainTo(records, UPLOAD_BATCH_SIZE);

        mQueueLock.unlock();
        return records;
    }
}

3 个解决方案

#1

try Converting unix timestamp to mysql timestamp

尝试将unix时间戳转换为mysql时间戳

if (is_array($data['records'])) {
  foreach ($data['records'] as $record) {
    $name = $record['name'];
    $value = $record['value'];
$event = $record['event'];
    $timestamp = date('Y-m-d H:i:s',$record['timestamp']);

    file_put_contents($filename, "$name -> $value with event: $event at $timestamp\n", FILE_APPEND);
    mysqli_query($con,"INSERT INTO `Driving Data`(`Name`, `Value`, `Event`, `Timestamp`) VALUES ($n

ame, $value, $event, $timestamp)");
      }
   }

#2

I think your SQL syntax is wrong. Look at this line:

我认为您的SQL语法是错误的。看看这条线:

mysqli_query($con,"INSERT INTO `Driving Data`(`Name`, `Value`, `Event`, `Timestamp`) VALUES ($name, $value, $event, $timestamp)");

If any variable ($name for example) has spaces, your SQL will be like this:

如果任何变量(例如$name)都有空格，那么SQL将如下所示:

INSERT INTO `Driving Data`(`Name`, `Value`, `Event`, `Timestamp`) VALUES (Name with spaces, ...)

but it should be between single quotes:

但它应该在单引号之间:

INSERT INTO `Driving Data`(`Name`, `Value`, `Event`, `Timestamp`) VALUES ('Name with spaces', ...)

I haven't tested this, but I think it's the source of your problem. I assume that's the error line because, as you say, the text file is created (so the file_put_contents before is running ok).

我还没有测试过这个，但我认为这是你的问题的根源。我假设这是错误行，因为如您所言，文本文件已经创建(因此之前的file_put_contents运行正常)。

Also, why aren't you using some kind of "ID" for your inserted rows? I would add an auto-incrementable "id" column as the primary key.

另外，为什么不为插入的行使用某种“ID”呢?我将添加一个自动递增的“id”列作为主键。

#3

Right now, just looking at your code you have VALUES written twice in the INSERT command.

现在，只需查看代码，就可以在INSERT命令中写入两次值。

It currently appears as: ...Timestamp) VALUES VALUES ($name,...

它当前显示为:…时间戳)值($name，…

Remove the second occurance of VALUES and see if it works. It never threw an error message?

删除第二次出现的值，并查看它是否有效。它从来没有抛出错误信息?