Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 2 down vote favorite

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

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}]}

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;
    }
}
share|improve this question
 
The sending code shouldn't be relevant, only the receiving code and input data. Please post your query string in the form it's run with all input variables inserted. –  dutt May 22 at 5:52
 
You should add some error checking for mysqli_query, it will help you debug the issue. php.net/manual/en/mysqli.query.php –  user1615903 May 22 at 5:53
 
What is the error you are getting? Also mention your data type of your table columns –  Kiren Siva May 22 at 5:54
 
Also, there's no quotation in your insert statement for the values. And it's vulnerable to SQL injection. –  user1615903 May 22 at 5:55
 
I am not getting any errors. Just added the data types for the MySQL columns into the post –  mbecker73 May 22 at 6:00

3 Answers

up vote 1 down vote accepted

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)");
      }
   }
share|improve this answer
 
This was causing me the problem, the timestamp was not in the correct format. Thanks for the help! –  mbecker73 May 23 at 1:58
up vote 1 down vote

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

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:

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).

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.

share|improve this answer
 
You could use if (!mysqli_query($con, ...)) die("SQL error: " . mysqli_error($con)); to check for SQL errors –  Alejandro Iván May 22 at 6:27
up vote 0 down vote

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

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

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

share|improve this answer
 
Oh, I think I accidentally somehow copied another "VALUES" when I was putting the code in the post, that is not actually in the code I am testing (so is not causing a problem). I removed it from the post –  mbecker73 May 22 at 5:49
2  
Ahh, okay. You might need to add single quotes before and after the variables, unless it is a number. Like so: VALUES ('$name', '$value', '$event', '$timestamp') I don't believe floats need to be quoted, but test it out and remove the quotes around them if they don't. –  Stuart Hannig May 22 at 5:56
 
Yeah I have tried that (and just retried it) but to no avail –  mbecker73 May 22 at 6:02
 
If the data is getting written to file, then it's definitely not an Android problem. Did you try just inserting the value of 1 instead of the variables to see if the query is even talking to the database for debugging purposes? Perhaps add die() to the tail end directly after the mysqli_query function. Like so: mysqli_query() or die('Query did not work.'); And see if the did not work statement comes back. Perhaps this link will help as well? stackoverflow.com/questions/2304894/… –  Stuart Hannig May 22 at 6:10

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.