Connecting Android Studio to 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 ?

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).

For retrieving the values from database, lets insert some values into table. You can do this with mySql queries or phpmyadmin.

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’);          

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');

?>

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:

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