Connect mySQL with Android

Question

I want to be able to, using my android device, connect to a mySQL database, send in a parameter that will be used in an SQL-statement, and I want to get the result back and be able to present it. It sounds easy, but all tutorials and examples I can find suffer from:

• extremely overbuilt (10 classes minimum to make that perfect button)
• incredibly confusing (no comments, explanations and retarded variable names)
• dependent on classes that don't exist

If I strip something unnecessary down everything crashes, so I can't extract what's actually important to make it remotely readable/understandable.

So, in the simplest way possible: what is needed in my android app to connect to my database? How do I send a parameter to the php-script? How can I generate a result from it that the android app can read?

UPDATE, STRIPPING ESSENTIALS TAKE 1 So as I mentioned in one of the comments on SoftCoder's answer, I'll try and take his complete app and strip out the fancy stuff to just get what's needed to connect to mySQL.

First off, I have added <uses-permission android:name="android.permission.INTERNET" /> in the manifest. The .php looks like this (host, user, password etc is something else in reality):

<?php
   $con = mysql_connect("HOST","USER","PASSWORD");
   if (!$con)
   {
      die('Could not connect: ' . mysql_error());
   }

   mysql_select_db("database_name", $con);

   $result = mysql_query("SELECT * FROM Table;");

   while($row = mysql_fetch_array($result))
   {
      echo $row['col1'];
      echo $row['col2'];
   }

   mysql_close($con);
?>

This script prints out all entries from the table.

Now to the complete activity!

package com.example.project;

import java.io.*;

import org.apache.http.HttpResponse;
import org.apache.http.client.*;
import org.apache.http.client.methods.*;
import org.apache.http.impl.client.*;
import org.json.*;

import android.app.*;
import android.os.*;
import android.util.*;
import android.view.*;
import android.widget.*;

public class MainActivity extends Activity 
{
    private String jsonResult;
    private String url = "url_to_php";
    InputStream is=null;
    String result=null;
    String line=null;

    protected void onCreate(Bundle savedInstanceState)
    {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        //supposedly the app wont crash with "NetworkOnMainThreadException". It crashes anyway
        StrictMode.ThreadPolicy policy = new StrictMode.ThreadPolicy.Builder().permitAll().build();
        StrictMode.setThreadPolicy(policy);

        //create our Async class, because we can't work magic in the mainthread
        JsonReadTask task = new JsonReadTask();
        task.execute(new String[] { url });
    }
    private class JsonReadTask extends AsyncTask<String, Void, String> 
    {
        // doInBackground Method will not interact with UI 
        protected String doInBackground(String... params)
        {
            // the below code will be done in background
            HttpClient httpclient = new DefaultHttpClient();
            HttpPost httppost = new HttpPost(params[0]);
            try 
            {
                //not sure what this does but it sounds important
                HttpResponse response = httpclient.execute(httppost);

                //took the "stringbuilder" apart and shoved it here instead
                String rLine = "";
                StringBuilder answer = new StringBuilder();
                BufferedReader rd = new BufferedReader(new InputStreamReader(response.getEntity().getContent()));

                while ((rLine = rd.readLine()) != null)
                    answer.append(rLine);

                //put the string into a json, don't know why really
                jsonResult = answer.toString();
            }

            catch (ClientProtocolException e)
            {
                e.printStackTrace();
                Log.e("Fail 12", e.toString());
            } 
            catch (IOException e) 
            {
                Log.e("Fail 22", e.toString());
                e.printStackTrace();
            }
            return null;
        }
    }
    // after the doInBackground Method is done the onPostExecute method will be called
    protected void onPostExecute(String result) throws JSONException 
    {
        // I skipped the method "drwer"-something and put it here instead, since all this method did was to call that method
        // getting data from server 
        JSONObject jsonResponse = new JSONObject(jsonResult);
        if(jsonResponse != null)
        {
            //I think the argument here is what table we'll look at... which is weird since we use php for that
            JSONArray jsonMainNode = jsonResponse.optJSONArray("Tablename");

            // get total number of data in table
            for (int i = 0; i < jsonMainNode.length(); i++)
            {
                JSONObject jsonChildNode = jsonMainNode.getJSONObject(i);

                String name = jsonChildNode.optString("col1"); // here name is the table field
                String number = jsonChildNode.optString("col2"); // here id is the table field
                String outPut = name + number ; // add two string to show in listview 
                //output to log instead of some weird UI on the device, just to see if it connects
                Log.d("Log", outPut.toString());
            }
        }
    }
    public boolean onCreateOptionsMenu(Menu menu) 
    {
        // Inflate the menu; this adds items to the action bar if it is present.
        getMenuInflater().inflate(R.menu.main, menu);
        return true;
    }
    public boolean onOptionsItemSelected(MenuItem item)
    {
        // Handle action bar item clicks here. The action bar will
        // automatically handle clicks on the Home/Up button, so long
        // as you specify a parent activity in AndroidManifest.xml.
        int id = item.getItemId();
        if (id == R.id.action_settings) 
            return true;
        return super.onOptionsItemSelected(item);
    }
}

So this is what I came up with so far that would count as "simple as possible", no fancy UI or jumping between methods (utilizing good code conventions is not important here). Since everything crashes with a "NetworkOnMainThreadException" like someone else already said it would, it's impossible to test it. Why is it crashing with this exception even though I'm using both an AsyncTask and call the Strict-thingy?

Answer 1

Here is the example

EDIT: First Create A database Name suppose dbname in MySql in wamp or in your server and create a table named emp_info in which two fields are added id and name

Here the Scenario is to insert ID and NAME of employee from the EDITTEXT to MYSQL server database

The Global Variables are

  String name;
    String id;
    InputStream is=null;
    String result=null;
    String line=null;
    int code;

For Activity Code

import java.io.BufferedReader;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.util.ArrayList;

import org.apache.http.HttpEntity;
import org.apache.http.HttpResponse;
import org.apache.http.NameValuePair;
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.DefaultHttpClient;
import org.apache.http.message.BasicNameValuePair;
import org.json.JSONObject;

import android.app.Activity;
import android.os.Bundle;
import android.os.StrictMode;
import android.util.Log;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Toast;

public class add extends Activity {

    String name;
    String id;
    InputStream is=null;
    String result=null;
    String line=null;
    int code;
    String tobed = null;



    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.add);
        StrictMode.ThreadPolicy policy = new StrictMode.ThreadPolicy.Builder().permitAll().build();
        StrictMode.setThreadPolicy(policy);
        final EditText e_id=(EditText) findViewById(R.id.editText1);
        final EditText e_name=(EditText) findViewById(R.id.editText2);
        Button insert=(Button) findViewById(R.id.button1);

        insert.setOnClickListener(new View.OnClickListener() {

        @Override
        public void onClick(View v) {
            // TODO Auto-generated method stub

            id = e_id.getText().toString();
            name = e_name.getText().toString();

            insert();
        }
    });
    }
}

Method of inserting data

public void insert()
    {
        ArrayList<NameValuePair> nameValuePairs = new ArrayList<NameValuePair>();

      // put the values of id and name in that variable
    nameValuePairs.add(new BasicNameValuePair("id",id));
    nameValuePairs.add(new BasicNameValuePair("name",name));

        try
        {
        HttpClient httpclient = new DefaultHttpClient();

          // here is the php file
         // for local use for example if you are using wamp just put the file in www/project folder
        HttpPost httppost = new HttpPost("http://10.0.2.2/project/insert2.php");
        // if the file is on server
        HttpPost httppost = new HttpPost("http://example.com/insert2.php");
            httppost.setEntity(new UrlEncodedFormEntity(nameValuePairs));
            HttpResponse response = httpclient.execute(httppost); 
            HttpEntity entity = response.getEntity();
            is = entity.getContent();
            Log.e("pass 1", "connection success ");
    }
        catch(Exception e)
    {
            Log.e("Fail 1", e.toString());
            Toast.makeText(getApplicationContext(), "Invalid IP Address",
            Toast.LENGTH_LONG).show();
    }     

        try
        {
            BufferedReader reader = new BufferedReader
            (new InputStreamReader(is,"iso-8859-1"),8);
            StringBuilder sb = new StringBuilder();
            while ((line = reader.readLine()) != null)
        {
                sb.append(line + "\n");
            }
            is.close();
            result = sb.toString();
        Log.e("pass 2", "connection success ");
    }
        catch(Exception e)
    {
            Log.e("Fail 2", e.toString());
    }     

    try
    {

            // get the result from php file
            JSONObject json_data = new JSONObject(result);
            code=(json_data.getInt("code"));

            if(code==1)
            {
        Toast.makeText(getBaseContext(), "Inserted Successfully",
            Toast.LENGTH_SHORT).show();
            }
            else
            {
         Toast.makeText(getBaseContext(), "Sorry, Try Again",
            Toast.LENGTH_LONG).show();
            }
    }
    catch(Exception e)
    {
            Log.e("Fail 3", e.toString());
            Log.i("tagconvertstr", "["+result+"]");
    }
    }

here is insert2.php file

<?php
    // this variables is used for connecting to database and server
    $host="yourhost";
    $uname="username";
    $pwd='pass';
    $db="dbname";

     // this is for connecting
    $con = mysql_connect($host,$uname,$pwd) or die("connection failed");
    mysql_select_db($db,$con) or die("db selection failed");

    // getting id and name from the client
     if(isset($_REQUEST)){
    $id=$_REQUEST['id'];
    $name=$_REQUEST['name'];}

    $flag['code']=0;

    // query for insertion
    // table name emp_info and its fields are id and name
    if($r=mysql_query("insert into emp_info values('$name','$id') ",$con))
    {
        // if query runs succesfully then set the flag to 1 that will be send to client app
        $flag['code']=1;
        echo"hi";
    }
      // send result to client that will be 1 or 0
    print(json_encode($flag));
    //close
    mysql_close($con);


 ?>

Second Edit:-

For reading data i have done asyntask

Here the data is displayed in a listview.

public class read extends Activity {
     private String jsonResult;//
      // use this if your file is on server
     private String url = "http://exmaple.com/read.php";
     // use this if you are locally using
     // private String url = "http://10.0.2.2/project/read.php";
     private ListView listView;
     Context context;
     String name;
        String id;
        InputStream is=null;
        String result=null;
        String line=null;
        int code;
     @Override
     protected void onCreate(Bundle savedInstanceState) {
      super.onCreate(savedInstanceState);
      setContentView(R.layout.read);
      StrictMode.ThreadPolicy policy = new StrictMode.ThreadPolicy.Builder().permitAll().build();
      StrictMode.setThreadPolicy(policy);
      context = this;
      listView = (ListView) findViewById(R.id.listView1);
      accessWebService();
     }

accessWebService method

       public void accessWebService() {
       JsonReadTask task = new JsonReadTask();
       task.execute(new String[] { url });
         }

for JsonReadTask class

private class JsonReadTask extends AsyncTask<String, Void, String> {
      // doInBackground Method will not interact with UI 
      @Override

      protected String doInBackground(String... params) {
       // the below code will be done in background
       HttpClient httpclient = new DefaultHttpClient();
       HttpPost httppost = new HttpPost(params[0]);
       try {
        HttpResponse response = httpclient.execute(httppost);
        jsonResult = inputStreamToString(
          response.getEntity().getContent()).toString();
       }

       catch (ClientProtocolException e) {
        e.printStackTrace();
        Log.e("Fail 12", e.toString());
       } catch (IOException e) {
           Log.e("Fail 22", e.toString());
        e.printStackTrace();
       }
       return null;
      }

      private StringBuilder inputStreamToString(InputStream is) {
       String rLine = "";
       StringBuilder answer = new StringBuilder();
       BufferedReader rd = new BufferedReader(new InputStreamReader(is));

       try {
        while ((rLine = rd.readLine()) != null) {
         answer.append(rLine);
        }
       }

       catch (IOException e) {
        // e.printStackTrace();
        Toast.makeText(getApplicationContext(),
          "Error..." + e.toString(), Toast.LENGTH_LONG).show();
       }
       return answer;
      }

      // after the doInBackground Method is done the onPostExecute method will be called
      @Override
      protected void onPostExecute(String result) {
      // here you can interact with UI
       ListDrwaer();
      }
     }// end async task

ListDrawaer Method

 // build hash set for list view
     public void ListDrwaer() {
      List<Map<String, String>> employeeList = new ArrayList<Map<String, String>>();

      try {
        // getting data from server 
       JSONObject jsonResponse = new JSONObject(jsonResult);
       if(jsonResponse != null)
       {
       JSONArray jsonMainNode = jsonResponse.optJSONArray("emp_info");

       // get total number of data in table
       for (int i = 0; i < jsonMainNode.length(); i++) {
        JSONObject jsonChildNode = jsonMainNode.getJSONObject(i);

        String name = jsonChildNode.optString("name"); // here name is the table field
        String number = jsonChildNode.optString("id"); // here id is the table field
        String outPut = name + number ; // add two string to show in listview 
        employeeList.add(createEmployee("employees", outPut));
       }
       }
      } catch (JSONException e) {
       Toast.makeText(getApplicationContext(), "Error" + e.toString(),
         Toast.LENGTH_SHORT).show();
      }

      SimpleAdapter simpleAdapter = new SimpleAdapter(this, employeeList,
        android.R.layout.simple_list_item_1,
        new String[] { "employees" }, new int[] { android.R.id.text1 });
      listView.setAdapter(simpleAdapter);
     }

     private HashMap<String, String> createEmployee(String name, String number) {
      HashMap<String, String> employeeNameNo = new HashMap<String, String>();
      employeeNameNo.put(name, number);
      return employeeNameNo;
     }
    }

and your read.php file code

<?php
$host="localhost"; //replace with database hostname
$username="root"; //replace with database username
$password=""; //replace with database password
$db_name="dbname"; //replace with database name

$con=mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");
$sql = "select * from emp_info";
$result = mysql_query($sql);
$json = array();

if(mysql_num_rows($result)){
while($row=mysql_fetch_assoc($result)){
$json['emp_info'][]=$row;
}
}
mysql_close($con);
echo json_encode($json);
?> 

and if you want to check you internet connection before using this insertion and reading use this method .. i.e put this method in if else statement

 public boolean isOnline() {
                ConnectivityManager cm =
                    (ConnectivityManager) getSystemService(Context.CONNECTIVITY_SERVICE);
                NetworkInfo netInfo = cm.getActiveNetworkInfo();
                if (netInfo != null && netInfo.isConnectedOrConnecting()) {
                    return true;
                }
                return false;
            }

For Updation and deletion use the insert method to pass the values to the server and just change the query of insert2.php to update ones like this

if($r=mysql_query("UPDATE emp_info SET employee_name = '$name' WHERE employee_name = '$id'",$con))
    {
        $flag['code']=1;
    } 

for deletion

if($r=mysql_query("DELETE FROM emp_info WHERE employee_name = '$name'",$con))
    {
        $flag['code']=1;
        echo"hi";
    }

Also when you have learn this, the next task you should learn threading and Asyntask to make it more improved as working on main thread is not good in android. just put this insert method in Asyntask as i have mentioned in Reading method so that UI dont get disturbed and the internet thing is done in background.

NOTE:

for new version of php add this line after <?php snippet

error_reporting(E_ALL ^ E_DEPRECATED);