Connect to MYSQL remote server database using PHP and display ListView

Print Friendly

This article describes how to create/access a MYSQL database in a remote server from Android using PHP and display it on a ListView. Because most online tutorials did not show the full process or all source code, I decided how to create a fully functional prototype. Currently you can see my JSON objects by going to this link: http://www.ece301.com/food.php which accesses my database and extracts the information. This article uses Cpanel, php and Android API 2.1.

This can easily run in XAMPP, but because most online tutorials focus on running it this way, I will show how to run it in a real live server.

Download the APK file first and install in your phone (adb install). Look at the php JSON request here: http://www.ece301.com/food.php:

DOWNLOAD APK FILE HERE

Create MYSQL database

My hosting provider offers Cpanel, if yours is different please send me an e-mail and I will do my best to help you out.

So in Cpanel, I go under Databases and click on the “MYSQL Databases” button:

This takes me to another screen where I can create a new database; which I do. My database name is “users”. Once the database is created you will see it. The full database name is your Cpanel username and databasename, the name will show:

demousername_databasename

After the database is created, you will create a user for this database. On the same page you can add a new user:

You can give it a name such as “testuser” with some password, “testpass”. Again, just like the database, the name is:

demousername_testuser

Last, we need to add the user to the database. On the same page, you can add the user to the database:

Populate MYSQL database

Go back to the Cpanel home and click on the third button from the left under Databases, phpMyAdmin. This will bring up another page with your databases on the left. Click on the “users” database. Now click the icon SQL:

This will allow us to type the commands to create the table “food” and insert three rows: beef,chicken and pork.

create table `food` (food_id INT auto_increment primary key, food_name varchar(20));
insert into `food` values (null,"beef");
insert into `food` values (null,"chicken");
insert into `food` values (null,"pork");

I did one at a time by this method:

Finally, our database has been created and populated.

Creating PHP script and loading it on the server

The PHP script is simple, but make sure that you fill in the right fields with the right values:

username: demousername_testuser
password: testpass
database: demousername_users
table: food

Since your php script is in the same server as your database, you can use “localhost”

<?php   mysql_connect("localhost","demousername_testuser","testpass");   mysql_select_db("demousername_users");   $sql=mysql_query("select * from food");   while($row=mysql_fetch_assoc($sql)){   	$output[]=$row;   }   print(json_encode($output));   mysql_close(); ?>

This simply connects to your mysql database and runs a query “select * from food” to take all rows and puts them into a JSON object.

Once you have created your script, go to your Cpanel and into your File manager:

You will go to the root of your wordpress or joomla installation for a website you have. In this article, I am using the website to run my php script.

This will take you to a different page. Here, press the upload button and upload the php script. Mine is called food.php.

Now test it out by going to your web browser and typing: www.yourdomain.com/food.php

You can see mine here that returns the contents of my MYSQL database:
http://www.ece301.com/food.php

Android code

This code is very similar to the article Parse JSON data from web server. So create a new project and create an Activity JSONParser:

public class JSONParser extends ListActivity {

	/** Called when the activity is first created. */
	@SuppressWarnings("unchecked")
	@Override
	public void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);
		setContentView(R.layout.main);

		setListAdapter(new ArrayAdapter(
				this,android.R.layout.simple_list_item_1,
				this.populate()));
	}

	private ArrayList populate() {
		ArrayList items = new ArrayList();

		try {
			URL url = new URL
                        // TODO - change line below to your own domain
			("http://www.ece301.com/food.php");
			HttpURLConnection urlConnection =
				(HttpURLConnection) url.openConnection();
			urlConnection.setRequestMethod("POST");
			urlConnection.connect();

			BufferedReader bufferedReader =
				new BufferedReader(new InputStreamReader(
						urlConnection.getInputStream()));

			String next;
			while ((next = bufferedReader.readLine()) != null){
				JSONArray ja = new JSONArray(next);

				for (int i = 0; i < ja.length(); i++) {
					JSONObject jo = (JSONObject) ja.get(i);
					items.add(jo.getString("food_name"));
				}
			}
		} catch (MalformedURLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (JSONException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return items;
	}

The main points in this code are:
1. Change the line below to your domain name.

URL url = new URL ("http://www.ece301.com/food.php");

2. When we created the database’s table “food”, the field was “food_name”. If you change it to something else, then the line below has to change.

items.add(jo.getString("food_name"));

Do not forget to add internet manifest permissions

uses-permission android:name=android.permission.INTERNET

Finally we end up with this:

Comments are closed.