I'm tearing my hair out over this problem I am having. I am trying to allow a user to upload some data from their android application to a website service which I have developed.

The data is to be uploaded using JSON and Android to a PHP web service which will then 'INSERT' the data into my PostgreSQL database.

I am unsure where the logic error is in my whole application as the app produces no errors at runtime but when I check the database records of my PostgreSQL server space there has been no data committed.

Please see below the code I am using and please try to help identify where I am going wrong. I have looked for tutorials on Google but they all are based on reading data FROM a PHP web service to an android app but I am looking to send the original data from the android app.

DataPost Activity

public void postData() throws JSONException{
       Toast.makeText(DataSummary.this, "Done! Check your profile online to see your record.", Toast.LENGTH_LONG).show();

       Thread trd = new Thread(new Runnable(){
           public void run(){
             //Create a new HttpClient and Post Header
               HttpClient httpclient = new DefaultHttpClient();
               HttpPost httppost = new HttpPost("http://users.aber.ac.uk/dwd/mfb/php/jsonscript.php");
               JSONObject json = new JSONObject();

               Bitmap bitmapOrg = BitmapFactory.decodeResource(getResources(), i);
                ByteArrayOutputStream bao = new ByteArrayOutputStream();
                bitmapOrg.compress(Bitmap.CompressFormat.JPEG, 90, bao);
                byte[] ba = bao.toByteArray();
                String ba1=Base64.encodeToString(ba, i);

               try {
                   //JSON data:
                   json.put("photo", ba1.toString());
                   json.put("name", name);
                   json.put("description", description);
                   json.put("latitude", latitude);
                   json.put("longitude", longitude);
                   json.put("project", project);
                   json.put("owner", username);

                   JSONArray postjson = new JSONArray();
                   postjson.put(json);

                   //Post the data
                   httppost.setHeader("json", json.toString());
                   httppost.getParams().setParameter("jsonpost", postjson);

                   //Execute HTTP Post Request
                   System.out.println(json);
                   HttpResponse response = httpclient.execute(httppost);

                   //for JSON
                   if(response != null)
                   {
                       InputStream is = response.getEntity().getContent();

                       BufferedReader reader = new BufferedReader(new InputStreamReader(is));
                       StringBuilder sb = new StringBuilder();

                       String line = null;
                       try{
                           while((line = reader.readLine()) != null){
                               sb.append(line + "\n");
                           }
                       } catch (IOException e){
                           e.printStackTrace();
                       } finally {
                           try {
                               is.close();
                           } catch(IOException e){
                               e.printStackTrace();
                           }
                       }
                   }

               } catch(ClientProtocolException e){
                   e.printStackTrace();
               } catch (IOException e){
                   e.printStackTrace();
               } catch (JSONException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
           }
       });
       trd.start();
   }

PHP Webservice

<?php
session_start();
$conn = pg_connect("database_string");

//VARIABLES TO BE WRITTEN TO THE DATABASE
$photo = $_REQUEST["photo"];

echo $photo;
$binary=base64_decode($photo);
header('Content-Type: bitmap; charset=utf-8');

$name = json_decode(stripslashes($_POST["name"]));
$safe_name = pg_escape_string($name);
$desc = json_decode(stripslashes($_POST["description"]));
$safe_desc = pg_escape_string($desc);
$latitude = json_decode(stripslashes($_POST["latitude"]));
$longitude = json_decode(stripslashes($_POST["longitude"]));
$project = json_decode(stripslashes($_POST["project"]));
$owner = json_decode(stripslashes($_POST["owner"]));

$id = pg_query("SELECT * FROM users WHERE email = $owner");
$id_assoc = pg_fetch_assoc($id);
$id_res = $id_assoc['u_id'];

//SQL STATEMENT HERE FOR INSERT

$res = pg_query("INSERT INTO records (photo, name, description, latitude, longitude, project, owner) VALUES ('$photo', '$safe_name', '$safe_desc', '$latitude', '$longitude', '$project', '$id_res'");

pg_close($conn);

?>

Anyone who can provide some advice/tutorials/code solutions would be a hero in my book!

share|improve this question
Maybe a stupid suggestion... but transactions? Is the INSERT statement committed or rolled back at pg_close? – mthmulders yesterday
would you suggest getting rid of pg_close and testing the submit again? – DanielD yesterday
@mthmulders no success when getting rid of pg_close(); – DanielD yesterday
1  
Don't make everyone else guess. Log your variables and log your results. That way you'll know what the problem is. Also, use proper query parameters rather than passing values blindly into an SQL statement. – Richard Huxton yesterday
My suggestion would be to add pg_query("COMMIT");, in order to explicitly commit your transaction. – mthmulders 15 hours ago
show 3 more comments

1 Answer

Does the SELECT query return anything? I'm not a PHP expert but to me it looks like you're sending the variables wrong so there shouldn't be:

$id = pg_query("SELECT * FROM users WHERE email = $owner");

But

$id = pg_query("SELECT * FROM users WHERE email ='".$owner."'");

Similar for the INSERT query. Other thoughts:

  • don't do a SELECT * when you just want one column it will be slower. For example with index-only-scans in 9.2 you could return the id straight from the index(email,id)
  • if you want to use just the id of the user it's better to put it in the subquery of the insert query INSERT INTO records ( ... ,owner) VALUES (... ,(SELECT id FROM users WHERE email='".$owner."')") You could even add RETURNING owner at the end to get the owner id out from the insert query if you need it somewhere else.
share|improve this answer
modified my SQL queries to your suggestions but still no success :( – DanielD yesterday
@DanielD you haven't answered if the SELECT returns the right thing or not. – Jakub Kania yesterday

Your Answer

 
or
required, but never shown
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.