{"id":223,"date":"2018-08-05T07:07:31","date_gmt":"2018-08-05T07:07:31","guid":{"rendered":"https:\/\/ta-laboratories.com\/blog\/?p=223"},"modified":"2018-08-06T07:49:05","modified_gmt":"2018-08-06T07:49:05","slug":"android-app-mysql-connectivity-with-php-and-json","status":"publish","type":"post","link":"https:\/\/ta-laboratories.com\/blog\/2018\/08\/05\/android-app-mysql-connectivity-with-php-and-json\/","title":{"rendered":"Android app MySQL connectivity with PHP and JSON"},"content":{"rendered":"

In this blog post we will discuss adding database connectivity to your Android application. We will look at how to make a HTTP POST request from your android application to a server side PHP script, we will also take a look at how to parse the returned JSON encoded results using Volley. <\/strong><\/p>\n

\nThe following tools will be used for this tutorial:<\/strong><\/p>\n

  • cPanel<\/li>\n
  • Android Studio<\/li>\n
  • MySQL<\/li>\n
  • PHP<\/>\n

    <\/strong>
    \nLet’s get started by configuring a new test database in cPanel, find the Databases tab on the menu.<\/strong><\/p>\n

    \"\"<\/p>\n

    Then use the MySQL Database Wizard to configure a new database called “testdb”.<\/strong><\/p>\n

    \"\"<\/p>\n

    Create a user for your new database.<\/strong><\/p>\n

    \"\"<\/p>\n

    Set user privileges, select “ALL PRIVILEGES” to select all.<\/strong><\/p>\n

    \"\"<\/p>\n

    If all went well you should have a database called “testdb” now setup on your hosted server. <\/strong><\/p>\n

    \"\"<\/p>\n

    Now we will use phpMyAdmin to create a table with some information in our blank database. Let’s get started by making a new table named “test_table” with 2 columns in it, one for an auto increment unique ID and one for some data.<\/strong><\/p>\n

    \"\"<\/p>\n

    We will name our first column “id” and give it an auto increment INT value for an index. Our second column will be named “cust_name” and will be assigned a type of VARCHAR and a length of 45.<\/strong><\/p>\n

    (Note the picture shows INT for the 2nd data type it should show VARCHAR)
    \n \"\"<\/p>\n

    Make sure to set the id field as the primary index key!<\/strong><\/p>\n

    \"\"<\/p>\n

    Once everything is set up you should have a functional MySQL database, now let’s write a simple PHP script to interact with it.<\/strong><\/p>\n

    <\/p>\n

    <?php<\/span>
    \n 
    \n\/\/ Create connection<\/span>
    \n$con<\/span>=<\/span>mysqli_connect<\/span><\/a>(<\/span>"localhost"<\/span>,<\/span>"admin"<\/span>,<\/span>"12345678"<\/span>,<\/span>"testdb"<\/span>)<\/span>;<\/span>
    \n 
    \n\/\/ Check connection<\/span>
    \nif<\/span> (<\/span>    mysqli_connect_errno<\/span><\/a>(<\/span>)<\/span>)<\/span>
    \n{<\/span>
    \n  echo<\/span> "Failed to connect to MySQL: "<\/span> .<\/span>     mysqli_connect_error<\/span><\/a>(<\/span>)<\/span>;<\/span>
    \n}<\/span>
    \n 
    \n\/\/ Define input variable(s)<\/span>
    \n$name<\/span> =<\/span> $_REQUEST<\/span>[<\/span>"custname"<\/span>]<\/span>;<\/span>
    \n\/\/$somename = $_REQUEST["someinput"];<\/span>
    \n
    \n\/\/ This SQL statement selects ALL from the table 'test_table'<\/span>
    \n$sql<\/span> =<\/span> "SELECT * FROM test_table WHERE cust_name = '$name<\/span>'"<\/span>;<\/span>
    \n 
    \n\/\/ check result<\/span>
    \nif<\/span> (<\/span>$result<\/span> =<\/span>     mysqli_query<\/span><\/a>(<\/span>$con<\/span>,<\/span> $sql<\/span>)<\/span>)<\/span>{<\/span>
    \n    $rowcount<\/span> =<\/span>     mysqli_num_rows<\/span><\/a>(<\/span>$result<\/span>)<\/span>;<\/span>
    \n
    \n            \/\/ if no results add entry<\/span>
    \n        if<\/span> (<\/span>$rowcount<\/span> ==<\/span> 0<\/span>)<\/span> {<\/span>
    \n            $sql<\/span> =<\/span> "INSERT INTO test_table (cust_name)
    \n            SELECT * FROM (SELECT '$name<\/span>') AS tmp
    \n            WHERE NOT EXISTS (SELECT cust_name FROM test_table WHERE cust_name = '$name<\/span>') LIMIT 1"<\/span>;<\/span>
    \n        }<\/span>
    \n    }<\/span>  
    \n 
    \n\/\/ Check if there are results<\/span>
    \nif<\/span> (<\/span>$result<\/span> =<\/span>     mysqli_query<\/span><\/a>(<\/span>$con<\/span>,<\/span> $sql<\/span>)<\/span>)<\/span>
    \n{<\/span>    
    \n    while<\/span>(<\/span>$row<\/span> =<\/span> $result<\/span>-><\/span>fetch_object<\/span>(<\/span>)<\/span>)<\/span>
    \n    {<\/span>
    \n    echo<\/span>     json_encode<\/span><\/a>(<\/span>$row<\/span>)<\/span>;<\/span>
    \n    }<\/span>
    \n 
    \n}<\/span><\/div><\/div>\n

    <\/strong><\/p>\n

    This PHP script handles database queries on the server end. It also handles authentication with the database and like any other type of server side script, its source code will remain hidden from the end user. We use the customer name from the HTTP request as a variable, the php script will then check the database to see if any rows match its input. If the row count returns 0 a new database entry will be created, if the row count returns 1 then the entry already exists and the values will be returned. Notice we put a limit of one entry per cust_name, this won’t allow us to add a duplicate record to the table, so for example we could not have 2 of any specific name. <\/p>\n

    By sending a HTTP request in any browser we should see an echo from the script if things are working right. Remember the first request will not produce a response since the database doesn’t contain anything yet, so once you send a new name you will have to refresh the page and send the request again to get the results. <\/strong><\/p>\n

    http:\/\/www.yourdomain.com\/test.php?custname=anyname<\/strong><\/p>\n

    \"\"<\/p>\n

    A quick check in phpMyAdmin shows the database entry was created successfully! <\/strong>
    \n\"\"<\/p>\n

    Now we have to have our app send a database request and parse the results, let’s start a new Android Studio project.<\/strong>
    \n\"\"
    \nSelect empty activity.<\/strong>
    \n\"\"
    \nLeave the default name of MainActivity<\/strong>
    \n\"\"<\/p>\n

    We will need to add Volley to our app dependency list in the app gradle build file, we can do that by adding this line.
    \n<\/strong><\/p>\n

    implementation 'com.android.volley:volley:1.0.0'<\/div><\/div>\n

    \"\"<\/p>\n

    We also have to add permission in the Manifest.xml for internet access.<\/strong><\/p>\n

    <uses-permission<\/span> android:name<\/span>="android.permission.INTERNET"<\/span> \/><\/span><\/div><\/div>\n
    <?xml<\/span> version<\/span>="1.0"<\/span> encoding<\/span>="utf-8"<\/span>?><\/span>
    \n<manifest<\/span> xmlns:android<\/span>="http:\/\/schemas.android.com\/apk\/res\/android"<\/span>
    \n    package<\/span>="com.ta_labsllc.mysql"<\/span>><\/span>
    \n
    \n    <uses-permission<\/span> android:name<\/span>="android.permission.INTERNET"<\/span> \/><\/span>
    \n
    \n    <application<\/span>
    \n        android:allowBackup<\/span>="true"<\/span>
    \n        android:icon<\/span>="@mipmap\/ic_launcher"<\/span>
    \n        android:label<\/span>="@string\/app_name"<\/span>
    \n        android:roundIcon<\/span>="@mipmap\/ic_launcher_round"<\/span>
    \n        android:supportsRtl<\/span>="true"<\/span>
    \n        android:theme<\/span>="@style\/AppTheme"<\/span>><\/span>
    \n        <activity<\/span> android:name<\/span>=".MainActivity"<\/span>><\/span>
    \n            <intent-filter><\/span><\/span>
    \n                <action<\/span> android:name<\/span>="android.intent.action.MAIN"<\/span> \/><\/span>
    \n
    \n                <category<\/span> android:name<\/span>="android.intent.category.LAUNCHER"<\/span> \/><\/span>
    \n            <\/intent-filter><\/span><\/span>
    \n        <\/activity><\/span><\/span>
    \n    <\/application><\/span><\/span>
    \n
    \n<\/manifest><\/span><\/span><\/div><\/div>\n

    Setup an EditText and InputText fields as well as a Button for the send action in the activity_main.xml layout. <\/strong><\/p>\n

    <?xml<\/span> version<\/span>="1.0"<\/span> encoding<\/span>="utf-8"<\/span>?><\/span>
    \n<android.support.constraint.ConstraintLayout<\/span> xmlns:android<\/span>="http:\/\/schemas.android.com\/apk\/res\/android"<\/span>
    \n    xmlns:app<\/span>="http:\/\/schemas.android.com\/apk\/res-auto"<\/span>
    \n    xmlns:tools<\/span>="http:\/\/schemas.android.com\/tools"<\/span>
    \n    android:layout_width<\/span>="match_parent"<\/span>
    \n    android:layout_height<\/span>="match_parent"<\/span>
    \n    tools:context<\/span>=".MainActivity"<\/span>><\/span>
    \n    <EditText<\/span>
    \n        android:id<\/span>="@+id\/editText"<\/span>
    \n        android:layout_width<\/span>="wrap_content"<\/span>
    \n        android:layout_height<\/span>="wrap_content"<\/span>
    \n        android:layout_marginEnd<\/span>="84dp"<\/span>
    \n        android:layout_marginLeft<\/span>="85dp"<\/span>
    \n        android:layout_marginRight<\/span>="84dp"<\/span>
    \n        android:layout_marginStart<\/span>="85dp"<\/span>
    \n        android:layout_marginTop<\/span>="16dp"<\/span>
    \n        android:ems<\/span>="10"<\/span>
    \n        android:gravity<\/span>="center_horizontal"<\/span>
    \n        android:inputType<\/span>="textPersonName"<\/span>
    \n        android:text<\/span>="@string\/enter_name"<\/span>
    \n        app:layout_constraintEnd_toEndOf<\/span>="parent"<\/span>
    \n        app:layout_constraintStart_toStartOf<\/span>="parent"<\/span>
    \n        app:layout_constraintTop_toTopOf<\/span>="parent"<\/span> \/><\/span>
    \n
    \n    <Button<\/span>
    \n        android:id<\/span>="@+id\/sendButton"<\/span>
    \n        android:layout_width<\/span>="wrap_content"<\/span>
    \n        android:layout_height<\/span>="wrap_content"<\/span>
    \n        android:layout_marginEnd<\/span>="148dp"<\/span>
    \n        android:layout_marginLeft<\/span>="148dp"<\/span>
    \n        android:layout_marginRight<\/span>="148dp"<\/span>
    \n        android:layout_marginStart<\/span>="148dp"<\/span>
    \n        android:layout_marginTop<\/span>="16dp"<\/span>
    \n        android:text<\/span>="@string\/send"<\/span>
    \n        app:layout_constraintEnd_toEndOf<\/span>="parent"<\/span>
    \n        app:layout_constraintHorizontal_bias<\/span>="0.0"<\/span>
    \n        app:layout_constraintStart_toStartOf<\/span>="parent"<\/span>
    \n        app:layout_constraintTop_toBottomOf<\/span>="@+id\/resultText"<\/span> \/><\/span>
    \n
    \n    <TextView<\/span>
    \n        android:id<\/span>="@+id\/resultText"<\/span>
    \n        android:layout_width<\/span>="185dp"<\/span>
    \n        android:layout_height<\/span>="42dp"<\/span>
    \n        android:layout_marginEnd<\/span>="99dp"<\/span>
    \n        android:layout_marginLeft<\/span>="100dp"<\/span>
    \n        android:layout_marginRight<\/span>="99dp"<\/span>
    \n        android:layout_marginStart<\/span>="100dp"<\/span>
    \n        android:layout_marginTop<\/span>="16dp"<\/span>
    \n        android:gravity<\/span>="center_vertical|center_horizontal"<\/span>
    \n        android:text<\/span>="@string\/result"<\/span>
    \n        app:layout_constraintEnd_toEndOf<\/span>="parent"<\/span>
    \n        app:layout_constraintHorizontal_bias<\/span>="0.0"<\/span>
    \n        app:layout_constraintStart_toStartOf<\/span>="parent"<\/span>
    \n        app:layout_constraintTop_toBottomOf<\/span>="@+id\/editText"<\/span> \/><\/span>
    \n
    \n<\/android.support.constraint.ConstraintLayout><\/span><\/span><\/div><\/div>\n

    In the main activity we will setup a method called checkDatabase(), we will set up an onClickListener for the send button to call this method anytime the button is pushed. The checkDatabase() method will form a HTTP POST request using params.put() and the string from the inputText field. The request will be sent to the server where the server side php script will process it and make the actual database query. Once finished the php script encodes the results in a JSON format and sends back a reply. When the response listener receives that reply it is parsed using jObject.getString() and the individual row values are accessible.
    \nMainActivity.java<\/strong><\/p>\n

    package<\/span> com.ta_labsllc.mysql<\/span>;<\/span>
    \n
    \nimport<\/span> android.support.v7.app.AppCompatActivity<\/span>;<\/span>
    \nimport<\/span> android.os.Bundle<\/span>;<\/span>
    \nimport<\/span> android.util.Log<\/span>;<\/span>
    \nimport<\/span> android.view.View<\/span>;<\/span>
    \nimport<\/span> android.widget.Button<\/span>;<\/span>
    \nimport<\/span> android.widget.EditText<\/span>;<\/span>
    \nimport<\/span> android.widget.TextView<\/span>;<\/span>
    \nimport<\/span> com.android.volley.Request<\/span>;<\/span>
    \nimport<\/span> com.android.volley.RequestQueue<\/span>;<\/span>
    \nimport<\/span> com.android.volley.Response<\/span>;<\/span>
    \nimport<\/span> com.android.volley.VolleyError<\/span>;<\/span>
    \nimport<\/span> com.android.volley.toolbox.StringRequest<\/span>;<\/span>
    \nimport<\/span> com.android.volley.toolbox.Volley<\/span>;<\/span>
    \nimport<\/span> org.json.JSONException<\/span>;<\/span>
    \nimport<\/span> org.json.JSONObject<\/span>;<\/span>
    \nimport<\/span> java.util.HashMap<\/span>;<\/span>
    \nimport<\/span> java.util.Map<\/span>;<\/span>
    \n
    \n
    \npublic<\/span> class<\/span> MainActivity extends<\/span> AppCompatActivity {<\/span>
    \n
    \n    EditText inputText;<\/span>
    \n    TextView outputText;<\/span>
    \n    Button<\/span><\/a> sendBtn;<\/span>
    \n
    \n    @Override
    \n    protected<\/span> void<\/span> onCreate(<\/span>Bundle savedInstanceState)<\/span> {<\/span>
    \n        super<\/span>.onCreate<\/span>(<\/span>savedInstanceState)<\/span>;<\/span>
    \n        setContentView(<\/span>R.layout<\/span>.activity_main<\/span>)<\/span>;<\/span>
    \n        inputText =<\/span> findViewById(<\/span>R.id<\/span>.editText<\/span>)<\/span>;<\/span>
    \n        outputText =<\/span> findViewById(<\/span>R.id<\/span>.resultText<\/span>)<\/span>;<\/span>
    \n        sendBtn =<\/span> findViewById(<\/span>R.id<\/span>.sendButton<\/span>)<\/span>;<\/span>
    \n        sendBtn.setOnClickListener<\/span>(<\/span>new<\/span>     View<\/span><\/a>.OnClickListener<\/span>(<\/span>)<\/span> {<\/span>
    \n            @Override
    \n            public<\/span> void<\/span> onClick(<\/span>    View<\/span><\/a> v)<\/span> {<\/span>
    \n                checkDatabase(<\/span>)<\/span>;<\/span>
    \n            }<\/span>
    \n        }<\/span>)<\/span>;<\/span>
    \n    }<\/span>
    \n
    \n    public<\/span> void<\/span> checkDatabase(<\/span>)<\/span> {<\/span>
    \n
    \n        RequestQueue queue =<\/span> Volley.newRequestQueue<\/span>(<\/span>this<\/span>)<\/span>;<\/span>
    \n
    \n        final<\/span>     String<\/span><\/a> url =<\/span> "http:\/\/yourIPhere.com\/test.php"<\/span>;<\/span> \/\/ location of php script<\/span>
    \n
    \n        \/\/ send HTTP POST request<\/span>
    \n        StringRequest postRequest =<\/span> new<\/span> StringRequest(<\/span>    Request<\/span><\/a>.Method<\/span><\/a>.POST<\/span>, url,
    \n                new<\/span> Response.Listener<\/span><<\/span>String><\/span>(<\/span>)<\/span> {<\/span>
    \n                    @Override
    \n                    public<\/span> void<\/span> onResponse(<\/span>    String<\/span><\/a> response)<\/span> {<\/span>
    \n                        \/\/ On response<\/span>
    \n                        Log.d<\/span>(<\/span>"PHP Response"<\/span>, response)<\/span>;<\/span>
    \n                        try<\/span> {<\/span>
    \n                            JSONObject jObject =<\/span> new<\/span> JSONObject(<\/span>response)<\/span>;<\/span>
    \n                            \/\/ Pulling items from the array<\/span>
    \n                                String<\/span><\/a> id =<\/span> jObject.getString<\/span>(<\/span>"id"<\/span>)<\/span>;<\/span> \/\/ parse id <\/span>
    \n                                String<\/span><\/a> custName =<\/span> jObject.getString<\/span>(<\/span>"cust_name"<\/span>)<\/span>;<\/span> \/\/ parse name<\/span>
    \n                            Log.d<\/span>(<\/span>"JSON ID"<\/span>, id)<\/span>;<\/span>
    \n                            Log.d<\/span>(<\/span>"JSON CUST_NAME"<\/span>, custName)<\/span>;<\/span>
    \n                                String<\/span><\/a> output =<\/span> (<\/span>"Results: "<\/span> +<\/span> id +<\/span> "|"<\/span> +<\/span> custName)<\/span>;<\/span>
    \n                            outputText.setText<\/span>(<\/span>output)<\/span>;<\/span>
    \n
    \n                        }<\/span> catch<\/span> (<\/span>JSONException e)<\/span> {<\/span>
    \n                            \/\/ error<\/span>
    \n                            Log.d<\/span>(<\/span>"JSON PARSE"<\/span>, "ERROR"<\/span>)<\/span>;<\/span>
    \n                        }<\/span>
    \n                    }<\/span>
    \n                }<\/span>,
    \n                new<\/span> Response.ErrorListener<\/span>(<\/span>)<\/span>
    \n                {<\/span>
    \n                    @Override
    \n                    public<\/span> void<\/span> onErrorResponse(<\/span>VolleyError error)<\/span> {<\/span>
    \n                        \/\/ error<\/span>
    \n                        Log.d<\/span>(<\/span>"PHP Error.Response"<\/span>, "error"<\/span>)<\/span>;<\/span>
    \n                    }<\/span>
    \n                }<\/span>
    \n        )<\/span> {<\/span>
    \n            @Override
    \n            protected<\/span> Map<<\/span>    String<\/span><\/a>, String><\/span> getParams(<\/span>)<\/span>
    \n            {<\/span>
    \n                \/\/ encode inputText <\/span>
    \n                    String<\/span><\/a> input =<\/span> inputText.getText<\/span>(<\/span>)<\/span>.toString<\/span>(<\/span>)<\/span>;<\/span>
    \n                Log.d<\/span>(<\/span>"User Input"<\/span>, input)<\/span>;<\/span>
    \n                Map<<\/span>    String<\/span><\/a>, String><\/span>  params =<\/span> new<\/span> HashMap<<\/span>String<\/span><\/a>, String><\/span>(<\/span>)<\/span>;<\/span>
    \n                params.put<\/span>(<\/span>"custname"<\/span>, input)<\/span>;<\/span>
    \n              \/\/params.put("somerow", "input");<\/span>
    \n                return<\/span> params;<\/span>
    \n            }<\/span>
    \n        }<\/span>;<\/span>
    \n        queue.add<\/span>(<\/span>postRequest)<\/span>;<\/span>
    \n        }<\/span>
    \n
    \n    }<\/span><\/div><\/div>\n

    \"\"<\/p>\n

    When everything is up and working you should be able to send a name to the database, if the entry exists already it will be returned with its unique id, if it is not it will be added and returned on the next request.<\/strong><\/p>\n

    Thanks for checking out this tutorial on using MySQL with Android!<\/strong><\/p>\n

    https:\/\/ta-laboratories.com\/blog\/wp-content\/uploads\/2018\/08\/device-2018-08-05-045724.mp4<\/a><\/video><\/div>\n","protected":false},"excerpt":{"rendered":"

    In this blog post we will discuss adding database connectivity to your Android application. We will look at how to make a HTTP POST request from your android application to a server side PHP script, we will also take a… Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":257,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[11,12,13,5],"tags":[],"class_list":["post-223","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-android","category-mysql","category-php","category-programming"],"_links":{"self":[{"href":"https:\/\/ta-laboratories.com\/blog\/wp-json\/wp\/v2\/posts\/223","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ta-laboratories.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ta-laboratories.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ta-laboratories.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ta-laboratories.com\/blog\/wp-json\/wp\/v2\/comments?post=223"}],"version-history":[{"count":57,"href":"https:\/\/ta-laboratories.com\/blog\/wp-json\/wp\/v2\/posts\/223\/revisions"}],"predecessor-version":[{"id":308,"href":"https:\/\/ta-laboratories.com\/blog\/wp-json\/wp\/v2\/posts\/223\/revisions\/308"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/ta-laboratories.com\/blog\/wp-json\/wp\/v2\/media\/257"}],"wp:attachment":[{"href":"https:\/\/ta-laboratories.com\/blog\/wp-json\/wp\/v2\/media?parent=223"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ta-laboratories.com\/blog\/wp-json\/wp\/v2\/categories?post=223"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ta-laboratories.com\/blog\/wp-json\/wp\/v2\/tags?post=223"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}