Connecting Android Application to a Local or Remote MySql Server Database
This article will help you to retrieve your data from database and display the same into your android project. Before you buying an hosting account you can simply test your android project locally. This article will he useful even if you are using remote database or local database.
There are few things you should know before you begin, if you are using local database.
Just go through the link below and learn how to setup a web server locally on your windows computer. Setup local web server on your laptop will be really useful to test your project before you uploading to your online server.
- How To Install and Configure Web server on your local Machine ?
Connecting Android Application to a Local or Remote MySql Server Database
You can follow the steps regardless of your database on hosted locally or remotely.Remote Database: go to the phpmyadmin or mySql from cPanel of your hosting account and create database and table as below
Local Database : if you are using local database, open browser and type http://localhost/phpmyadmin, and create databse and table as below
1. Create a database and Table
You can create a database and table for storing the data for the project with required number of fields depending on what project you creating. Create an database using "SQL Query" or 'phpmyadmin' interface as showing below.Lets create a 'quiz database'.Open http://localhost/phpmyadmin/ in your browser. This will prompt you for inputting username and password. By default username is root and there is no password (leave the password field empty). Once you login you will see the screen as shown below:
For retrieving the values from database, lets insert some values into table. You can do this with mySql queries or phpmyadmin.
Insert values using phpmyadmin UI:
Insert values using MySql Queries:
INSERT INTO `quiz` (`id`, `question`, `optionA`, `optionB`, `optionC`, `optionD`, `answer`) VALUES ('1', 'sample Question', 'choice one', 'choice two', 'choice three', 'choice four', 'choice one');
2. Connecting to MySQL database from PHP
Navigate to the htdocs folder of your XAMP installation directory(Usually located in C:\xamp\httdocs\, if you haven’t changed during the installation). Create a folder named testing for this project. This is the folder where you PHP projects reside.
Create a file with named dbConnect.php inside xamp server directory and add the following code. This file contains database connection details as shown below and helps in connecting to MySQL database. This file has to be included in all files where we need to perform a database operation.
<?php
define('HOST','localhost');
define('USER','root');
define('PASS','');
define('DB','quizDb');
$con = mysqli_connect(HOST,USER,PASS,DB) or die('Unable to Connect');
?>
3. Fetching a single movie detail (Retrieving a single row from database)
We can fetch the details of a single movie by passing the movie_id in the request. That is by using the primary key of the table, we can fetch a unique row from the database.
Create a file test.php inside the movies folder and add the following code. The code expects the mandatory parameter id to be passed in an HTTP GET method.
<?php
if($_SERVER['REQUEST_METHOD']=='GET'){
$id = $_GET['id'];
require_once('dbConnect.php');
$sql = "SELECT question,optionA,optionB,optionC,optionD,answer FROM `quizDb` WHERE id=".$id;
$r = mysqli_query($con,$sql);
$res = mysqli_fetch_array($r);
$result = array();
array_push($result,array(
"question"=>$res['question'],
"optionA"=>$res['optionA'],
"optionB"=>$res['optionB'],
"optionC"=>$res['optionC'],
"optionD"=>$res['optionD'],
"answer"=>$res['answer']
)
);
echo " \n";
echo json_encode(array("result"=>$result));
mysqli_close($con);
}
Now if you hit http://localhost/testing/test.php?id=1 in your browser you should be able to see the following response:
 |
| Result in json format |
4. Creating Android Project
Now we will see how to use the created APIs in the Android application.Lets Create a Sample Project in android studio for testing and do the following.
i. Add Internet permissions
ii. Design Main Activity
iii. Create Config File
iv. Main Activity class code
i. Add Internet Permissions
Providing internet permission to your android app in very important if you are using remote database. copy the below code and paste it in androidmanifest.xml file.
<uses-permission android:name="android.permission.INTERNET" />
ii. Design Main Activity
Lets design our main_activity.xml file for be ready to receive the database filed values.Create Textviews and buttons accordingly. You can use the sample xml design code copy the below code and paste in main_activity.xml
<?xml version="1.0" encoding="utf-8"?> <RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android" android:layout_width="match_parent" android:layout_height="match_parent" android:orientation="vertical" android:background="@drawable/bgapp"tools:context=".MainActivity">
<TextView android:id="@+id/textQuestion" android:layout_height="wrap_content" android:layout_width="match_parent" android:layout_marginTop="31dp" android:layout_marginStart="15sp" android:layout_marginEnd="15sp" android:textAlignment="center" android:layout_centerHorizontal="true" android:textColor="#332FA2" android:background="@drawable/bgpurple" android:textSize="17sp" android:lineSpacingExtra="6dp" android:text="@string/question" />
<LinearLayout android:id="@+id/ly01" android:orientation="vertical" android:layout_width="match_parent" android:layout_height="wrap_content">
<Button android:id="@+id/txtOptA" android:layout_width="wrap_content" android:layout_height="wrap_content" android:layout_marginTop="13dp" android:padding="10dp" android:clickable="false" android:focusable="false" android:textColor="#332FA2" android:textSize="15sp" android:background="@drawable/bgpurple" />
<Button android:id="@+id/txtOptB" android:padding="10dp" android:clickable="false" android:focusable="false" android:textColor="#332FA2" android:textSize="15sp" android:layout_marginTop="13dp" android:background="@drawable/bgpurple" android:layout_width="wrap_content" android:layout_height="wrap_content" />
<Button android:id="@+id/txtOptC" android:layout_width="wrap_content" android:layout_height="wrap_content" android:layout_marginTop="13dp" android:padding="10dp" android:clickable="false" android:focusable="false" android:textColor="#332FA2" android:textSize="15sp" android:background="@drawable/bgpurple" />
<Button android:id="@+id/txtOptD" android:layout_width="wrap_content" android:layout_height="wrap_content" android:layout_marginTop="13dp" android:padding="10dp" android:clickable="false" android:focusable="false" android:textColor="#332FA2" android:textSize="15sp" android:background="@drawable/bgpurple" />
</LinearLayout>
</RelativeLayout>
Our activity_main will look like as below,
iii. Create Config File
Create a config file which hold our database connectivity information.Create a new java Class named "Config.java". Copy and paste below code and change IP address and php file path according to your project.In this project we are using local database to to test
class Config {
//Created one table there in lyricsMalayalam database for this with specific user privilege for this app static final String DATA_URL = "http://192.168.2.65/testing/test.php?id=";
static final String ques ="question";
static final String optA ="optionA";
static final String optB ="optionB";
static final String optC ="optionC";
static final String optD ="optionD";
static final String ans ="answer";
// static final String explanation ="explanation"; static final String JSON_ARRAY ="result";
}
iv. Main Activity class code
We should code to display the json values which we saw in the previous step to android project or main_activity. We are doing this with the help of volley library. So before we start coding lets update our build.gradle file. Copy and paste below code build.gradle module level file and sync the projects as showing the picture below.implementation 'com.mcxiaoke.volley:library-aar:1.0.0'
 |
| Add Volley Library |
Go to MainActivity.java
import android.app.ProgressDialog;
import android.content.Intent;
import android.graphics.Typeface;
import android.os.Bundle;
import android.support.v7.app.AppCompatActivity;
import android.view.View;
import android.widget.Button;
import android.widget.TextView;
import android.widget.Toast;
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.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;public class MainActivity extends AppCompatActivity {String tempAns; TextView txtQuestion; Button btnOptionA, btnOptionB, btnOptionC, btnOptionD; @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_main); txtQuestion = findViewById(R.id.textQuestion); btnOptionA = findViewById(R.id.txtOptA); btnOptionB = findViewById(R.id.txtOptB); btnOptionC = findViewById(R.id.txtOptC); btnOptionD = findViewById(R.id.txtOptD); callQuizSet(1); } //To get one quiz set private void showJSON(String response){ String ques =" "; String optA =" "; String optB =" "; String optC =" "; String optD =" "; String explanation =" "; String ans =" "; try { JSONObject jsonObject = new JSONObject(response); JSONArray result = jsonObject.getJSONArray(Config.JSON_ARRAY); JSONObject collegeData = result.getJSONObject(0); ques = collegeData.getString(Config.question); optA = collegeData.getString(Config.optionA); optB = collegeData.getString(Config.optionB); optC = collegeData.getString(Config.optionC); optD = collegeData.getString(Config.optionD); ans = collegeData.getString(Config.answer); } catch (JSONException e) { e.printStackTrace(); } String tempOptionA="A. "+optA; String tempOptionB="B. "+optB; String tempOptionC="C. "+optC; String tempOptionD="D. "+optD; txtQuestion.setText(ques); btnOptionA.setText(tempOptionA); btnOptionB.setText(tempOptionB); btnOptionC.setText(tempOptionC); btnOptionD.setText(tempOptionD); tempAns=ans; } public void callQuizSet(int id){ //Loading from database final ProgressDialog dialog= ProgressDialog.show(QuizActivity.this, "","Loading. Please wait...", true); final String url = Config.DATA_URL+ id +""; StringRequest stringRequest = new StringRequest(url, new Response.Listener<String>() { @Override public void onResponse(String response) { dialog.dismiss(); showJSON(response); } }, new Response.ErrorListener() { @Override public void onErrorResponse(VolleyError error) { Toast.makeText(QuizActivity.this,error.getMessage(),Toast.LENGTH_LONG).show(); } }); RequestQueue requestQueue = Volley.newRequestQueue(getApplicationContext()); requestQueue.add(stringRequest); } }
5. Run the Project
We are done.! Simple run the project.
0 Comments