Connecting to MySQL database
The most spread method to connect to a remote MySQL database from an android device, is to put some kind of service into the middle. Since MySQL is usually used together with PHP, the easiest and most obvious way to write a PHP script to manage the database and run this script using HTTP protocol from the android system. |
![]() |
We can code the data in JSON format, between Android and PHP with the easy to use built in JSON functions in both languages.
I present some sample code, which selects data from a database depending on a given condition and creates a log message on the android side with the received data.
Lets suppose that we have a MySQL database named PeopleData, and a table int created, with the following SQL:
- CREATE TABLE `people` (
- `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
- `name` VARCHAR( 100 ) NOT NULL ,
- `sex` BOOL NOT NULL DEFAULT '1',
- `birthyear` INT NOT NULL
- )
We want to get all the data of the people, who were born after a specified year.
The PHP code will be very simple:
- connect to the database
- run an SQL query, with a WHERE block depending on data from POST/GET values
- output it in JSON format
For example we will have this functionality in the getAllPeopleBornAfter.php file:
- <?php
- mysql_connect("host","username","password");
- mysql_select_db("PeopleData");
- $q=mysql_query("SELECT * FROM people WHERE birthyear>'".$_REQUEST['year']."'");
- while($e=mysql_fetch_assoc($q))
- $output[]=$e;
- print(json_encode($output));
- mysql_close();
- ?>
The Android part is only a bit more complicated:
-use a HttpPost to get the data, sending the year value
-convert response to string
-parse JSON data, and use it as you want
- //the year data to send
- ArrayList<NameValuePair> nameValuePairs = new ArrayList<NameValuePair>();
- nameValuePairs.add(new BasicNameValuePair("year","1980"));
- //http post
- try{
- HttpClient httpclient = new DefaultHttpClient();
- HttpPost httppost = new HttpPost("http://example.com/getAl
lPeopleBornAfter.php"); - httppost.setEntity(new UrlEncodedFormEntity(nameValuePairs));
- HttpResponse response = httpclient.execute(httppost);
- HttpEntity entity = response.getEntity();
- Log.e("log_tag", "Error in http connection "+e.toString());
- }
- //convert response to string
- try{
- StringBuilder sb = new StringBuilder();
- while ((line = reader.readLine()) != null) {
- sb.append(line + "\n");
- }
- is.close();
- result=sb.toString();
- Log.e("log_tag", "Error converting result "+e.toString());
- }
- //parse json data
- try{
- JSONArray jArray = new JSONArray(result);
- for(int i=0;i<jArray.length();i++){
- JSONObject json_data = jArray.getJSONObject(i);
- Log.i("log_tag","id: "+json_data.getInt("id")+
- ", name: "+json_data.getString("name")+
- ", sex: "+json_data.getInt("sex")+
- ", birthyear: "+json_data.getInt("birthyear")
- );
- }
- }
- }catch(JSONException e){
- Log.e("log_tag", "Error parsing data "+e.toString());
- }
Of course it is possible to use HTTPS and send password to access data, or do more complex data processing on either side, or write more general code, which
does not include this much predefined parameters in the database accessing query.
New tutorials from Helloandroid
Recent Apps
Android on Twitter
-
@Idevicegazette (iDevice Gazette)
GSM-to-Skype bridge lets you lose those roaming fees http://bit.ly/lbRJeh #android
11 years 39 weeks ago -
@tommy_banane (tom b.)
RT @AndroidFavorite: #Android New Desktop Android Market Is Live, Adds Several New Features http://zorr0.nl/lFwXNz
11 years 39 weeks ago -
@dwilliams5 (Dennis Williams)
just completed a runtastic run of 3.02 km in 40 min 11 s with #runtastic #Android App: http://tinyurl.com/5tvrpe3
11 years 39 weeks ago -
@S_Pinz (Spinz!)
RT @Androidheadline: Out of box #LG Optimus 3D got Quadrant 2420 score. Thanks @blink_c #io2011 #android http://twitpic.com/4whkdz
11 years 39 weeks ago -
@tayaitapps (Taya IT)
Next Google TV Looks A Lot Like Android http://t.co/dvlTim3 via @alleyinsider #google #apple #android #tv #honeycomb
11 years 39 weeks ago
Poll
Useful resources
Android Development Projects
- iOS/Android Developer to take older Games and bring them Current
- Android apps developer - need to finish urgent.
- Buliding MobileApp For onlie order
- looking for android APP developers
- Create an ecommerce app
- text-to voice for smartphones IOS - GOOGLE - HARMONY - AND ALEXA
- Optimize Images on App
- Create small feature with drag-drop text for Android
- Scouting for advanced website and Mobile apps developers. Potential Long-term contract.
- BLACK SCREEN