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.
The following tools will be used for this tutorial:
Let’s get started by configuring a new test database in cPanel, find the Databases tab on the menu.
Then use the MySQL Database Wizard to configure a new database called “testdb”.
Create a user for your new database.
Set user privileges, select “ALL PRIVILEGES” to select all.
If all went well you should have a database called “testdb” now setup on your hosted server.
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.
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.
(Note the picture shows INT for the 2nd data type it should show VARCHAR)
Make sure to set the id field as the primary index key!
Once everything is set up you should have a functional MySQL database, now let’s write a simple PHP script to interact with it.
// Create connection
$con=mysqli_connect("localhost","admin","12345678","testdb");
// Check connection
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
// Define input variable(s)
$name = $_REQUEST["custname"];
//$somename = $_REQUEST["someinput"];
// This SQL statement selects ALL from the table 'test_table'
$sql = "SELECT * FROM test_table WHERE cust_name = '$name'";
// check result
if ($result = mysqli_query($con, $sql)){
$rowcount = mysqli_num_rows($result);
// if no results add entry
if ($rowcount == 0) {
$sql = "INSERT INTO test_table (cust_name)
SELECT * FROM (SELECT '$name') AS tmp
WHERE NOT EXISTS (SELECT cust_name FROM test_table WHERE cust_name = '$name') LIMIT 1";
}
}
// Check if there are results
if ($result = mysqli_query($con, $sql))
{
while($row = $result->fetch_object())
{
echo json_encode($row);
}
}
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.
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.
http://www.yourdomain.com/test.php?custname=anyname
A quick check in phpMyAdmin shows the database entry was created successfully!
Now we have to have our app send a database request and parse the results, let’s start a new Android Studio project.
Select empty activity.
Leave the default name of MainActivity
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.
We also have to add permission in the Manifest.xml for internet access.
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
package="com.ta_labsllc.mysql">
<uses-permission android:name="android.permission.INTERNET" />
<application
android:allowBackup="true"
android:icon="@mipmap/ic_launcher"
android:label="@string/app_name"
android:roundIcon="@mipmap/ic_launcher_round"
android:supportsRtl="true"
android:theme="@style/AppTheme">
<activity android:name=".MainActivity">
<intent-filter>
<action android:name="android.intent.action.MAIN" />
<category android:name="android.intent.category.LAUNCHER" />
</intent-filter>
</activity>
</application>
</manifest>
Setup an EditText and InputText fields as well as a Button for the send action in the activity_main.xml layout.
<android.support.constraint.ConstraintLayout xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:app="http://schemas.android.com/apk/res-auto"
xmlns:tools="http://schemas.android.com/tools"
android:layout_width="match_parent"
android:layout_height="match_parent"
tools:context=".MainActivity">
<EditText
android:id="@+id/editText"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_marginEnd="84dp"
android:layout_marginLeft="85dp"
android:layout_marginRight="84dp"
android:layout_marginStart="85dp"
android:layout_marginTop="16dp"
android:ems="10"
android:gravity="center_horizontal"
android:inputType="textPersonName"
android:text="@string/enter_name"
app:layout_constraintEnd_toEndOf="parent"
app:layout_constraintStart_toStartOf="parent"
app:layout_constraintTop_toTopOf="parent" />
<Button
android:id="@+id/sendButton"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_marginEnd="148dp"
android:layout_marginLeft="148dp"
android:layout_marginRight="148dp"
android:layout_marginStart="148dp"
android:layout_marginTop="16dp"
android:text="@string/send"
app:layout_constraintEnd_toEndOf="parent"
app:layout_constraintHorizontal_bias="0.0"
app:layout_constraintStart_toStartOf="parent"
app:layout_constraintTop_toBottomOf="@+id/resultText" />
<TextView
android:id="@+id/resultText"
android:layout_width="185dp"
android:layout_height="42dp"
android:layout_marginEnd="99dp"
android:layout_marginLeft="100dp"
android:layout_marginRight="99dp"
android:layout_marginStart="100dp"
android:layout_marginTop="16dp"
android:gravity="center_vertical|center_horizontal"
android:text="@string/result"
app:layout_constraintEnd_toEndOf="parent"
app:layout_constraintHorizontal_bias="0.0"
app:layout_constraintStart_toStartOf="parent"
app:layout_constraintTop_toBottomOf="@+id/editText" />
</android.support.constraint.ConstraintLayout>
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.
MainActivity.java
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.util.Log;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.TextView;
import com.android.volley.Request;
import com.android.volley.RequestQueue;
import com.android.volley.Response;
import com.android.volley.VolleyError;
import com.android.volley.toolbox.StringRequest;
import com.android.volley.toolbox.Volley;
import org.json.JSONException;
import org.json.JSONObject;
import java.util.HashMap;
import java.util.Map;
public class MainActivity extends AppCompatActivity {
EditText inputText;
TextView outputText;
Button sendBtn;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
inputText = findViewById(R.id.editText);
outputText = findViewById(R.id.resultText);
sendBtn = findViewById(R.id.sendButton);
sendBtn.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
checkDatabase();
}
});
}
public void checkDatabase() {
RequestQueue queue = Volley.newRequestQueue(this);
final String url = "http://yourIPhere.com/test.php"; // location of php script
// send HTTP POST request
StringRequest postRequest = new StringRequest(Request.Method.POST, url,
new Response.Listener<String>() {
@Override
public void onResponse(String response) {
// On response
Log.d("PHP Response", response);
try {
JSONObject jObject = new JSONObject(response);
// Pulling items from the array
String id = jObject.getString("id"); // parse id
String custName = jObject.getString("cust_name"); // parse name
Log.d("JSON ID", id);
Log.d("JSON CUST_NAME", custName);
String output = ("Results: " + id + "|" + custName);
outputText.setText(output);
} catch (JSONException e) {
// error
Log.d("JSON PARSE", "ERROR");
}
}
},
new Response.ErrorListener()
{
@Override
public void onErrorResponse(VolleyError error) {
// error
Log.d("PHP Error.Response", "error");
}
}
) {
@Override
protected Map<String, String> getParams()
{
// encode inputText
String input = inputText.getText().toString();
Log.d("User Input", input);
Map<String, String> params = new HashMap<String, String>();
params.put("custname", input);
//params.put("somerow", "input");
return params;
}
};
queue.add(postRequest);
}
}
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.
Thanks for checking out this tutorial on using MySQL with Android!