Connecting to MySQL database

Mon, 05/10/2010 - 01:58 | by gabor

SDK Version:

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

String result = "";
//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/getAllPeopleBornAfter.php");
        httppost.setEntity(new UrlEncodedFormEntity(nameValuePairs));
        HttpResponse response = httpclient.execute(httppost); 
        HttpEntity entity = response.getEntity();
        InputStream is = entity.getContent();
}catch(Exception e){
        Log.e("log_tag", "Error in http connection "+e.toString());
}
//convert response to string
try{
        BufferedReader reader = new BufferedReader(new InputStreamReader(is,"iso-8859-1"),8);
        StringBuilder sb = new StringBuilder();
        String line = null;
        while ((line = reader.readLine()) != null) {
                sb.append(line + "\n");
        }
        is.close();
 
        result=sb.toString();
}catch(Exception e){
        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.

Tags:

Bookmark/Search this post with

Comments

دردشة سورية دردشة

ameer | Tue, 01/03/2012 - 06:11.

دردشة سورية
دردشة لبنانية
دردشة عراقية
شات سوري
شات لبناني
دردشة سوريا
دردشة لبنان
شات سوريا
شات لبنان
دردشة السويدي
منتديات السويدي
اغاني عراقية
صور فنانين
الرياضة العراقية
شعراء العراق
نغمات عراقية
اغاني عربية
اغاني كردية
دردشة عراقية
دردشة بنات العراق
دردشة صبايا بغداد
دردشة البصره
دردشة بغداد
دردشة بغدادية
دردشة صبايا بغداد
دردشة شباب العراق
دردشة بنات العراق
دردشة الكرادة
دردشة دمشق
دردشة بيروت
دردشة حلب
دردشة حلب
دردشة عراقية
دردشة العراق
شات عراقي
جات عراقي
دردشه عراقيه
دردشة صبايا لبنان
دردشة بنات لبنان

شات صوتي | دردشة صوتية | كلام

nn7575 | Mon, 12/12/2011 - 16:05.

شات صوتي

Scrambled Result

kaxil | Sat, 08/06/2011 - 23:21.

The result i got is not the desired.. can u help
Download Video Song

Rugby World Cup 2011 Live Streaming

Kreditkarten im

grippon | Thu, 08/11/2011 - 11:21.

Kreditkarten im Vergleich.
Eine kostenlose Kreditkarte testen.
Wie Kreditkarten funktionieren.

Grate site.

Rahul | Sat, 08/06/2011 - 06:13.

I really loved reading your blog. It was very well authored and easy to undertand. Unlike additional blogs I have read which are really not tht good. I also found your posts very interesting. In fact after reading, I had to go show it to my friend and he ejoyed it as well.Park Hotel Brno

Grate site.

Rahul | Sat, 08/06/2011 - 06:12.

I ran into this page

nitesh002 | Sat, 08/06/2011 - 01:36.

I ran into this page mistakenly, surprisingly, this is a great website.The site owner has carried out a superb job of putting it together, the info here is really insightful. Now i am going to bookmark this internet site so that I can revisit in the future Jobs.
CCNA Salary

Ein Smartphone testen. Wie

grippon | Thu, 08/11/2011 - 11:22.

Ein Smartphone testen.
Wie der Kreditrechner funktioniert.

Good post. Very impressive.

wenger backpack | Fri, 08/05/2011 - 22:24.

Good post. Very impressive. Thanks for sharing.
wenger backpack

buy percocet online

jenifer_4499 | Fri, 08/05/2011 - 15:51.

Online pharmacy gives a good service against local pharmacy because last few years people have tendency towards online. There are many reasons behind this factor like:
• Online pharmacy provides cheap and low rates medicines.
• Quality wise and standard medicines due to competition
• Online pharmacy cut traveling cost
• Save time
• Free prescriptions
• Privacy policy
http://www.mycarepharmacy.com

buy vicodin online

kimsteiner | Fri, 08/05/2011 - 14:08.

such a wonderful post, i got useful info from this post and i like the readers comments. Thanks for sharing this post to us. Thanks

buy Vicodin online

Tubal Reversals

annagrace32 | Thu, 08/04/2011 - 14:06.

There are two common reasons women develop blocked fallopian tubes and there are several advanced surgical procedures which can correct or reverse tubal blockage and allow a woman to become pregnant again...
For more information you can visit: mybabydoc.com

Tubal Reversals | Tubal Reversal Surgery

Excellent info on 'Connecting

sunitha | Sat, 07/30/2011 - 02:54.

Excellent info on 'Connecting to MySQL database'. Even I was in searching for the same resource. I found excellent info on this specific topic. Hope you guys update the posts frequently.

Joomla Developer | Joomla Web Developer

Re:Connecting to MySQL database

Webworld | Thu, 07/28/2011 - 05:51.

Such a cool format while making an Android apps. The JSON format can increase the security and that's why developers are establishing connection between SQL database and Android core management. I really appreciated this concepts and programming scripts above because now after the salvation of the old process, I can build up new Charlotte accountant apps for the Android OS.

fine block.

Rahul | Wed, 07/27/2011 - 05:35.

I really enjoy reding your posts as I learn a lot from them I also broaden my thinking as far as what I can useand do with things.Luxury Accommodation

fantistic blog

Rahul | Tue, 07/26/2011 - 06:20.

I just bought an android, your article will going to be really helpful for my gadget. Thank you very much.Bay Area Events

nice blog.

Rahul | Tue, 07/26/2011 - 04:31.

Nice movie list! Pinoy din ako! Sana mabisita mo din ang lenses ko. Salamat!.Vermögensverwaltung

good.......

Prem | Mon, 07/25/2011 - 05:17.

Hi. Useful information, many thanks to the author. It is puzzling to me now, but in general, the usefulness and importance is overwhelming.series 56 exam

ansari

Rashmi | Fri, 07/22/2011 - 04:13.

Great post!! Keep up the good work - I'm sure someday soon, someone will be doing an Ada Lovelace post about you.Tarif Vergleich

very good

alam | Thu, 07/21/2011 - 06:11.

Thank you for such a fantastic blog. Where else could anyone get that kind of info written in such a perfect way? I have a presentation that I am presently working on, and I have been on the look out for such information, and I many Thank to you.Nanoversiegelung

organic multivitamin for women-Healthy Green

kathrinefernandoz | Fri, 07/15/2011 - 11:14.

Sometimes we are very concerned about our health, do not understand what to do. It is very easy to make better health. we use natural vitamin supplements. Vitamins function in many metabolic reactions that occur in foods used in the body, organic multivitamin for women , vitamins control and energy metabolism of our body.

Connecting to MySQL database

prateekagrawal08 | Mon, 07/11/2011 - 06:20.

Error in http connection java.net...
tel me what is the problem....

Connecting to MySQL database

prateekagrawal08 | Mon, 07/11/2011 - 06:18.

I am getting this log Error;

Error in http connection java.net...
tel me what is the problem...

Thanks!

Jane1208 | Wed, 07/06/2011 - 08:34.

mysql has always been a great help for me when configuring my website. This guide has also given me some valuable tips to widen my skills. Thanks!

Chicago Home Builder

Thanks for this,,,

perruci | Wed, 07/06/2011 - 03:35.

Thanks for this great tip....

Nikmaya John

freindzone

دردشة الرياض الصوتية | شات

BuenokSA | Sat, 07/02/2011 - 13:35.

دردشة الرياض الصوتية

|

شات الرياض الصوتي

|

الرياض كام

|

منتدى سعودي كام منتديات سعودي

966cam | Sat, 07/02/2011 - 08:10.

Thank you - the site very

marya2011 | Sat, 07/02/2011 - 02:44.

Thank you - the site very very nice

المجتمع المصري هو مجتمع يحب الترفية حيث ان سكان مصر تصل الى تسعون مليون فرد ولذلك نحن نقدم خدمة دردشة مصرية لكل اهل مصر ام الدنيا , وتعتبر مصر متقدمة فى هذا العالم من حيث النمو فى مجالات التكنولوجيا و الكمبيوتر بنسبة كبيرة جدا لذلك نفضل دخول شات مصري اكبر تجمع بنات و شباب فى شات الحب و اجمل بنات فى دردشة دردشة الحب , اضافة الى ذلك حيث يوجد دردشة قوية تضم جميع محافظات مصر هي شات مصرية الكتابية نتمني لكم قضاء وقت ممكن فى شات الاصدقاء و احلي دردشة التعارف الذي يوجد به بنات رومانسية فى دردشة بنات مصر المصرية

شات الحب - دردشة مصرية - شات - شات مصري - شات حب - دردشة - دردشة كتابية - دردشة الحب - شات حبنا - شات حبي - شات مصرية - دردشة حب - شات بنات - دردشة مصر - دردشة بنات مصر - شات بنات مصر - شات صوتي - chat love - دردشة ياحبي - شات بنات لبنان - الحب - شات كتابي - منتدي ياحبي - شات مصر - دردشة حبنا - العاب تلبيس البنات - دردشة بنات - شات ياحبي - Chat - شات لبناني - شات كلامنجي - دردشة كلامنجي - شبكة - تصميم مواقع - تصميم - شات الاسكندرية - دردشة لبنانية - دردشة بنات لبنان - بنات لبنان - موقع شات - سعودي كول - شات سعودي كول - شات بنت مصر - شات سكس

My hubby and I discovered

stacie28marcette | Fri, 07/01/2011 - 08:03.

My hubby and I discovered this excellent content article whilst doing research via the internet. The blog posts helps as a helpful tutorial as well as it is educational. Nice. download movies

This is literally helpful and

maria51harlow | Mon, 06/27/2011 - 14:26.

This is literally helpful and noticeably informing. This short post has a lot of splendid crucial specifics. Nice. movie downloads

thank you for

makkah | Sat, 06/25/2011 - 02:11.

thank you for informations

فنادق مكة

حجز فنادق مكة

hi

karen1leee | Thu, 06/23/2011 - 17:00.

I just came into this site during search of cleaning products and got interest to read it

very useful

ciidoctor | Tue, 06/21/2011 - 19:11.

very useful tutorial
galaxystab
iphone 5 apps
عربى داونلود

Connect my sql database to android phone

raj | Tue, 06/21/2011 - 00:26.

I am new to android programming ,

Can please anyone tell me how to run the code i am having following doubts
1) i have written the php code in notepad but dont know where to save it ?
2) should i create tables in the mysql software i have ?

3) wat changes should be made to main.xml ,AndroidManifest.xml , strings.xml etc so that the code runs

can u mail me the entire source code

email: pakky10@yahoo.com

It’s useful for me.. Keep up

wakthing | Fri, 06/17/2011 - 03:25.

It’s useful for me.. Keep up the good work. I am going to save this article to share it with some of my friends. You gave abounding believability here. Mercedes-Benz Mobil Mewah Terbaik Indonesia | Mercedes-Benz Mobil Mewah Terbaik Indonesia | Tablet Android Honeycomb Terbaik Murah | Meriahkan pesta ulang tahun bersama GarudaFood | Mari Berkomunitas Di Faceblog | Harga Jual Blackberry iPhone Laptop Murah | Video Music | Mp3 | Beauty and Health

Thanks

cemal | Thu, 06/16/2011 - 08:35.

We can give them a good education that they can use in their life to get a better chance in this life. Im sure with a good education all of us will see the changing that all that kids can have
BenTen Oyunları - Zeka Oyunları - Komik Oyunlar - Oyunlar- Araba Yarışı Oyunları

Why are vitamin supplements

karen1leee | Mon, 06/13/2011 - 12:29.

Why are vitamin supplements for diabetics necessary? Eating healthy food sometimes does not guarantee that your body gets the right amount of vitamins that you need to make your body function properly

vitamin supplements

Really Really Thanks For This

mihir23192 | Sat, 06/11/2011 - 10:15.

Really Really Thanks For This Wonderful article. Its glad that nowadays also, there is some original content on the internet. Otherwise, everywhere you go you will find duplicate content. Thanks Again. Like to Read You article and blog.

movie download | mediafire links | all buzzy news | ready movie download

Took me time to read all the

anoglancer | Mon, 06/20/2011 - 02:07.

Took me time to read all the comments, but I really enjoyed the article. It proved to be Very helpful to me and I am sure to all the commenters here! It’s always nice when you can not only be informed, but also entertained! I’m sure you had fun writing this article.....

clubmz e-spy

My SQL Error

anjarsulistiyo | Thu, 06/09/2011 - 22:37.

Since MySQL is usually used together with PHP, the easiest and most obvious way to write a PHP script to manage the Bikram Yoga database and run this script using HTTP protocol from the android system.

Web Development Qatar

roopa | Tue, 06/07/2011 - 23:41.

I am so glad that I found an fascinating and useful blog like yours. Most of the blogs that I've seen over these past few days didn't please me due to a lack of substance. Nonetheless, reading this one of a kind article truly made my day. I'm so proud of you.

Web Development Qatar | Website Development Qatar

thanks

komik | Sat, 06/04/2011 - 10:02.

I visited this page first time and found it Very Good Job of acknowledgment and a marvelous source of info.........Thanks Admin!
Komik Oyunlar

mysql and java Script

parmod | Sat, 06/04/2011 - 05:54.

How Connect To mysql using java script or jquery

can any help me

Thnak's Parmod

conncetivity Using Java script

bestseosoftware | Sat, 06/04/2011 - 05:49.

Can we connect to myswl using javascript

Help me

Thnak's John

Best Seo software

My SQL

anjarsulistiyo | Mon, 05/30/2011 - 20:34.

I present some sample code, Bali Property Investment | Baju Muslim which selects data from a database depending on a given condition and creates a log message on the android side with the received data.

I always find some thing

karen1leee | Fri, 07/08/2011 - 11:06.

Before attempting to talk about Organic Vitamin C, allow me to mention where we are supposed to find this wonderful source of nutrients: Our food! and pose a caveat that contrary to popular belief, not all foods are created equal, with Organic Food being better than the rest!

Natural vitamins supplements
and
Organic vitamins

Great!

kaycee | Mon, 05/16/2011 - 08:33.

I think this one's a winner. I am just a newbie with an Android phone. Thanks!
Best Stretch Mark Cream

Today everybody knows that

karen1leee | Fri, 07/08/2011 - 11:07.

Today everybody knows that organic food is healthier than the non organic food. Non organic food is the food which has been produced with the help of chemicals and pesticides. There are many side effects of eating non organic food as it cannot give you all the nutrients which our body required.

Natural vitamins supplements
and
Organic vitamins

Great resource! Thanks so

jacob2012 | Sun, 05/08/2011 - 01:44.

Great resource! Thanks so much for sharing your wealth of information. I just started in this and I'm getting to know it

better! Cheers, keep up the good work!
Mobile Spy Software

hi everyone

IceRafa | Sat, 05/07/2011 - 22:35.

I dont understand where to put the HttpPost and everithing off please anyone cant let me the code if not much I really need to understand this :S pd mi mail is icecoolsv@hotmail.com

Connecting to MySQL database

Comments

|

|

|

Main menu

New tutorials from Helloandroid

Recent Apps

Android on Twitter

Poll

Useful resources

Android Development Projects