connecting android apps to mysql database

Question

I have been trying out the tutorial shown on various websites on connecting a MySQL database using php to android. I dont know whats wrong with my code below. Can anyone tell me what i need to do.

This is my php code

 <?php
 mysql_connect("localhost","root","sugi");
 mysql_select_db("android");
 $q=mysql_query("SELECT * FROM people
 WHERE
 birthyear>'".$_REQUEST['year']."'");
 while($e=mysql_fetch_assoc($q))
             $output[]=$e;
       print(json_encode($output));
       mysql_close(); ?>

This is my sql query

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 
)

This is my java code in android

public class main extends Activity {
    InputStream is;
    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.main);
        String result = "";
        //the year data to send
        ArrayList<NameValuePair> nameValuePairs = new ArrayList<NameValuePair>();
        nameValuePairs.add(new BasicNameValuePair("year","1990"));

        //http post
        try{
                HttpClient httpclient = new DefaultHttpClient();
                HttpPost httppost = new HttpPost("http://localhost/index.php");
                httppost.setEntity(new UrlEncodedFormEntity(nameValuePairs));
                HttpResponse response = httpclient.execute(httppost); 
                HttpEntity entity = response.getEntity();
                is = entity.getContent();
                Log.e("log_tag", "connection success ");
                Toast.makeText(getApplicationContext(), "pass", Toast.LENGTH_SHORT).show();
        }catch(Exception e){
                Log.e("log_tag", "Error in http connection "+e.toString());
                Toast.makeText(getApplicationContext(), "fail", Toast.LENGTH_SHORT).show();

        }
        //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");
                        Toast.makeText(getApplicationContext(), "pass", Toast.LENGTH_SHORT).show();
                }
                is.close();

                result=sb.toString();
        }catch(Exception e){
               Log.e("log_tag", "Error converting result "+e.toString());
            Toast.makeText(getApplicationContext(), "fail", Toast.LENGTH_SHORT).show();

        }

        //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")
                        );
                        Toast.makeText(getApplicationContext(), "pass", Toast.LENGTH_SHORT).show();
               }

        }catch(JSONException e){
                Log.e("log_tag", "Error parsing data "+e.toString());
                Toast.makeText(getApplicationContext(), "fail", Toast.LENGTH_SHORT).show();
        }
    }
}

The program work fine. but i cant connect to http://localhost/index.php. The program display fail 3 times. Can help me see where i goes wrong?

Thank everyone for the help. Now i am able to connect to mysql. But i cant get the value of json data. The prog toast a msg 2 pass and 1 fail. Can anyone help me? The image below is when i type http://localhost/index.php in my IE. And line 6 is all this

$q=mysql_query("SELECT * FROM people WHERE birthyear>'".$_REQUEST['year']."'");

I dont know where i goes wrong.

Solution

If your php script is deployed at localhost and you are deploying your android app on emulator then you should use this constructor: HttpPost httppost = new HttpPost("http://10.0.2.2/index.php");

See: http://developer.android.com/guide/developing/devices/emulator.html#emulatornetworking

Comments

[Loïc Faure-Lacroix]

127.0.0.1 is a synonym for localhost. Either way, it's pointing to itself (the phone) it will never reach and other computer if it points to itself. That would work though if the mysql server and the webserver were hosted directly on the phone.

[James]

Looks to me as hes working with the emulator, and i had problems with using localhost and when i changed to 127.0.0.1 it worked.

[Loïc Faure-Lacroix]

Not my point, the webserver/mysql isn't on localhost. And even in the emulator localhost is still the phone and not the host computer.

[sugianto]

Hi, Thank alot man. Finally It can connect to my sql :D. But I cant get the value of my json_data. The emulator display 2 passes and 1 fail. I suspect there is something wrong with my php. Am I right? Can you help me solve this problem?

[Andrey Marchenko]

Try to debug your script with different client, see if Http answer is right.

[sugianto]

Can you tell me the script with different client? Sorry I am quite new to all this.