PHP从Android将JSON数据插入MySQL数据库 [英] PHP Inserting JSON Data into MySQL database from Android
本文介绍了PHP从Android将JSON数据插入MySQL数据库的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有一个Android应用程序，该程序通过HTTP POST方法将数据发送到PHP脚本，并试图解析数据以存储到MySQL中. Android应用不是我写的，但是我可以访问下面包含的源代码；它发送打包为JSON数组的数据.我的PHP脚本现在正尝试将数据放入文本文件和MySQL DB中:
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:
<?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
这是JSON数据示例的外观:
Here is how an example of the JSON data looks:
{"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}]}
所以我对PHP还是比较陌生，我似乎无法弄清楚问题出在哪里(即为什么不使用这些值来更新数据库，但仍可以将它们写到文本文件中.下面作为参考)是用于将数据发送到Web服务器的Android代码(我无法更改):
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):
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;
}
}
推荐答案
尝试将unix时间戳转换为mysql时间戳
try Converting unix timestamp to mysql timestamp
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)");
}
}
这篇关于PHP从Android将JSON数据插入MySQL数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
