Store php value to java

Question

I wanted to insert data from an Android into a MySQL database. After inserting a record, I want to get the last insert id and retrieve the lastID value. Is it possible? How can I achieve this ? Any help would be appreciated.

 addInformation(status, timeIn);

 lastID =? // what should I write in order to get the last ID ? 

  public void addInformation(final String name, final String weather, final String date2, final String status, final String timeIn, final String timeOut) {
        class AddInfo extends AsyncTask<String, Void, String> {
            ProgressDialog loading;

            @Override
            protected void onPreExecute() {
                super.onPreExecute();
                loading = ProgressDialog.show(WorkDetailsTable.this, "Please Wait", null, true, true);
            }

            @Override
            protected void onPostExecute(String s) {
                super.onPostExecute(s);
                loading.dismiss();
                Toast.makeText(getApplicationContext(),s, Toast.LENGTH_LONG).show();
                //addWorkForce(Sub, NoP, NoH, Long.parseLong(s));
               // addWorkDetails(results, Long.parseLong(s));
            }

            @Override
            protected String doInBackground(String... params) {

                HashMap<String, String> data = new HashMap<String, String>();
                data.put(Config.KEY_USER_NAME, name);
                data.put(Config.KEY_WEATHER, weather);
                data.put(Config.KEY_DATE, date2);
                data.put(Config.KEY_STATUS, status);
                data.put(Config.KEY_TIMEIN, timeIn);
                data.put(Config.KEY_TIMEOUT, timeOut);
                RequestHandler rh = new RequestHandler();
                String result = rh.sendPostRequest(Config.ADD_INFORMATION, data);
                return result;
            }
        }

        AddInfo ru = new AddInfo();
        ru.execute(name, weather, date2, status, timeIn, timeOut);
    }

ADD_INFORMATION.php

<?php 

    if($_SERVER['REQUEST_METHOD']=='POST'){

        //Getting values

        $status = $_POST['status'];
        $timeIn = $_POST['timeIn'];


        //Creating an sql query
        $sql = "INSERT INTO information(status, time_in) VALUES ('$status', '$timeIn')";

        //Importing our db connection script
        require_once('dbConnect.php');

        //Executing query to database
        if(mysqli_query($con,$sql)){
            echo 'Information Added Successfully';
            $insertId=mysql_insert_id();
            echo json_encode($insertId);
        }else{
            echo 'Could Not Add Information';
        }

        //Closing the database 
        mysqli_close($con);
    }
?>

I want to get the $insertId and put into lastID

Hi, please complete your question: what is your code related to? What do you want to do with that last ID? — Joël, Jan 04 '16 at 13:08
@Joël Edited. I want to get the `$insertId` and put into `lastID` — John, Jan 04 '16 at 13:11

Michele La Ferla · Answer 1 · 2016-01-06T07:54:22.513

1

In the ADD_INFORMATION script, all you need to do is get the data after the insert statement. Therefore I would use something similar to the below:

<?php 

    if($_SERVER['REQUEST_METHOD']=='POST'){

        //Getting values

        $status = $_POST['status'];
        $timeIn = $_POST['timeIn'];


        //Creating an sql query
        $sql = "INSERT INTO information(status, time_in) VALUES ('$status', '$timeIn')";

        //Importing our db connection script
        require_once('dbConnect.php');

        //Executing query to database
        if(mysqli_query($con,$sql)){
            $sql = "SELECT * FROM INFORMATION ORDER BY time_in DESC LIMIT 1";
            echo $sql;
            $insertId=mysql_insert_id();
            echo json_encode($insertId);
        }else{
            echo 'Could Not Add Information';
        }

        //Closing the database 
        mysqli_close($con);
    }
?>

In reality what I did was create a SELECT statement on the data I have just inserted in the INSERT statement.

Hope this helps.

edited Jan 06 '16 at 07:54

answered Jan 04 '16 at 13:38

Michele La Ferla

6,775
11
53
79

"SELECT * FROM INFORMATION LIMIT 1" is for ? – John Jan 04 '16 at 13:41
that is used to get the last id of the record you just inserted. That is what you needed. – Michele La Ferla Jan 04 '16 at 14:08
Do you mean I create another table used to store the last inserted id ? – John Jan 04 '16 at 14:10
No, all you need to do is get it from the insert. If you have a column called Last Insert ID in the table, all you need to do is `SELECT LastInsertId FROM information LIMIT 1;` – Michele La Ferla Jan 04 '16 at 14:12
so I have to create a column named LastInsertId? will the id will automatic insert into LastInsertId ? – John Jan 04 '16 at 14:18
You need to set it to auto-increment and set the data type as an integer and get that id when returning the select statement. – Michele La Ferla Jan 04 '16 at 15:10
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/99706/discussion-between-john-and-codedbymi). – John Jan 04 '16 at 15:13
I only can have id auto-increment...lastId cannot – John Jan 04 '16 at 17:22
I would then return the id in the select statement. :) – Michele La Ferla Jan 05 '16 at 08:57
Query "SELECT * FROM INFORMATION LIMIT 1" will not return the latest row added. In best case it gonna return first row and even that is not true, if any of your primary keys are missing in sequence before insert. Downvoting. – Reloecc Jan 06 '16 at 06:57
@Reloecc if your order the query by last created, then it will return the last inserted row. Please upvote – Michele La Ferla Jan 06 '16 at 07:53
it always worked on my projects when I used prepared statements to return what I have just inserted into a php database. Take a look at this tutorial: http://www.androidhive.info/2012/01/android-login-and-registration-with-php-mysql-and-sqlite/ – Michele La Ferla Jan 06 '16 at 08:31
@codedByMi I can't, you are echoing the query-string, not querying it to database for no reason. You are mixing mysqli and mysql extenstions. You still pick the last_insert_id from the db, and you echoing the json right after the "select query", making it in unreadable both as json and query-string. You are trying to select the * and not escaping the input. That's all wrong = not worth as viable answer and actually I am downvoting it again, cause I realized all the mistakes, sorry. – Reloecc Jan 06 '16 at 08:35
Sir can you [help](https://stackoverflow.com/questions/34988037/only-the-list-which-has-max-id-can-be-deleted) – John Jan 25 '16 at 09:40

score 1 · Answer 2 · edited May 23 '17 at 12:04

1

As first, the line

 echo 'Information Added Successfully';

will break your json format, I suggest you to format the desired json like

{"status": "ok", "message": "Information Added Successfully", "data": {"lastid": 1234}}

after that, read what to do with

String result = rh.sendPostRequest(Config.ADD_INFORMATION, data);

at How to convert String to JSONObject in Java. Most satisfying approach is to prepare java class for the json response, eg. like

class AddInformationResponse
{

   public String status;
   public String messsage;
   public ObjectNode data;
}

you can read how to map json to java object in one of many tutorials.

edited May 23 '17 at 12:04

Community

1
1

answered Jan 04 '16 at 13:44

Reloecc

256
2
13

Not really get it. I thought it should always display int since it is id ? – John Jan 04 '16 at 13:46
I suppose, you are commenting first line. For $insertId 1234, you will echo the message "Information Added Successfully1234". So it's not an int. Still, you can echo only $insertId, and forget about json. But you will lost the message "Inf. Added.." – Reloecc Jan 04 '16 at 13:54
Sorry,new to php. I want it display 1234 only, and then store 1234 to variable lastID in android – John Jan 04 '16 at 13:57

score 1 · Accepted Answer · answered Jan 04 '16 at 16:41

Based on the comments, I am creating new answer, as both my answers are correct in the basics. I made example, working with android (apache commons 4.5.1) and php 5.6. Both version (4.5.1, 5.6) are not requirements, just what I am using right now.

Example assume you have a mysql table called information with fields status, time_in and with another field marked as AUTO_INCREMENT.

Java Part

in original ::doInBackground(String ...params) function you could have

HttpClient client = HttpClients.createDefault();
HttpPost httpPost = new HttpPost("http://localhost/so/sendrequest/addInformation.php");

try{
   List<NameValuePair> data = new ArrayList<NameValuePair>(2);
   data.add(new BasicNameValuePair("status", "ok"));
   data.add(new BasicNameValuePair("timein", "12:55"));
   httpPost.setEntity(new UrlEncodedFormEntity(data));
   String response = EntityUtils.toString(client.execute(httpPost).getEntity());
   System.out.println(response); //here you have your insertid
}catch(ClientProtocolException e){
   // TODO Auto-generated catch block
}catch(IOException e){
   // TODO Auto-generated catch block
}

based on native Java

import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

and apache commons, which should be included with android library (download link if not)

import org.apache.http.NameValuePair;
import org.apache.http.client.ClientProtocolException;
import org.apache.http.client.HttpClient;
import org.apache.http.client.entity.UrlEncodedFormEntity;
import org.apache.http.client.methods.HttpPost;
import org.apache.http.impl.client.HttpClients;
import org.apache.http.message.BasicNameValuePair;
import org.apache.http.util.EntityUtils;

PHP Part

addInformation.php

<?php

class AddInformation
{

   function response(){
      /** @var mysqli $con */
      require_once('dbConnect.php'); //$con = new mysqli('127.0.0.1', 'root', '', 'so');
      $status = $con->real_escape_string($_POST['status']);
      $timein = $con->real_escape_string($_POST['timein']);

      $con->query("INSERT INTO information (status, time_in) VALUES ('$status', '$timein')");
      echo $con->insert_id;
   }
}

$ai = new AddInformation();
$ai->response();

Undefined index? Yes, the php script is not runnable by itself, you need to post data to it (what does the Java part), it's just an example of solution for your problem, you have to do some job in both Java and PHP side to get it to work in your application. In your example, "if($_SERVER['REQUEST_METHOD']=='POST')" prevents this error. Or you have some typo in names of posted values. Who knows, you are giving no informations. — Reloecc, Jan 04 '16 at 22:21

Store php value to java

3 Answers3

Java Part

PHP Part

Linked