Inserting data to a mysql database from android phone

Question

Wondering can anyone help I am pretty new to android and I've run into a problem in an app I am trying to create. The app takes in rssi values from nearby phones what I am attempting to do then is save these reads to a web server, I look to be making the connection ok according the Logcat but my SQL query in my PHP script seems to be causing the issue I maybe wrong any help would be much appreciated.

I am posting my android code, php script and logcat.

class ConnectToDB extends AsyncTask<String, Void, String>
{

    @Override
    protected String doInBackground(String... params)
    {
        try
        {
            Looper.prepare();

            String ssid1 = params[0];
            String rssi1 = params[1];
            String ssid2 = params[2];
            String rssi2 = params[3];

            // Add items to be added to the database
            List<NameValuePair> prams = new ArrayList<NameValuePair>();
            prams.add(new BasicNameValuePair("Ssid1", ssid1));
            prams.add(new BasicNameValuePair("Rssi1", rssi1));
            prams.add(new BasicNameValuePair("Ssid2", ssid2));
            prams.add(new BasicNameValuePair("Rssi2", rssi2));

            Log.e("Values in prams", String.valueOf(prams.size()));
            Log.e("Elements in prams", prams.toString());

            HttpClient httpClient = new DefaultHttpClient();
            HttpPost httpPost = new HttpPost(URL);
            httpPost.setEntity(new UrlEncodedFormEntity(prams));
            HttpResponse response = httpClient.execute(httpPost);
            HttpEntity entity = response.getEntity();
            is = entity.getContent();

            Log.e("pass 1", "connection success ");
            Toast.makeText(getApplicationContext(), "Connection Made", Toast.LENGTH_LONG).show();

        }
        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 secondB = new StringBuilder();

            String line;
            while ((line = reader.readLine()) != null)
            {
                secondB.append(line).append("\n");
            }
            is.close();
            result = secondB.toString();

            Log.e("pass 2", "reader success");
        }
        catch (Exception e)
        {
            Log.e("Fail 2", e.toString());
        }
        try
        {
            Log.i("JsonObject", result);
            JSONObject json_data = new JSONObject(result);

            int 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());
        }

        return "complete";
    }

    protected void onPostExecute(String string)
    {
        Log.d("Response: ", string);
    }
}

php script:

$con = mysql_connect($host, $username ,$password) or die("connection failed");
mysql_select_db($dbname, $con) or die("db selection failed");

$ap1 = $_POST['Ssid1'];
    $rssi1 = $_POST['Rssi1'];
    $ap2 = $_POST['Ssid2'];
    $rssi2 = $_POST['Rssi2'];

    $flag['code']=0;

if($r = mysql_query($con, "INSERT INTO phone_table(ap, rssi) values($ap1, $rssi1)"));
    {
    $flag['code']=1;
}
    if($r = mysql_query($con, "INSERT INTO phone_table(ap, rssi) values($ap2, $rssi2)"));
    {
    $flag['code']=1;
}

print(json_encode($flag));

mysql_close($con);

and Logcat:

07-08 23:35:42.991    5465-5465/com.lyit_android_society.com.rssireader E/﹕ mali:      REVISION=Linux-r3p2-01rel3 BUILD_DATE=Fri Nov 29 14:18:37 KST 2013
07-08 23:35:43.056    5465-5465/com.lyit_android_society.com.rssireader D/OpenGLRenderer﹕ Enabling debug mode 0
07-08 23:35:43.061    5465-5465/com.lyit_android_society.com.rssireader D/AbsListView﹕ unregisterIRListener() is called
07-08 23:35:43.086    5465-5465/com.lyit_android_society.com.rssireader D/AbsListView﹕ unregisterIRListener() is called
07-08 23:35:43.096    5465-5465/com.lyit_android_society.com.rssireader D/AbsListView﹕ unregisterIRListener() is called
07-08 23:35:51.731    5465-5500/com.lyit_android_society.com.rssireader E/Values in prams﹕ 4
07-08 23:35:51.731    5465-5500/com.lyit_android_society.com.rssireader E/Elements in prams﹕ [Ssid1=TestPhone3, Rssi1=-38, Ssid2=Testphone1, Rssi2=-28]
07-08 23:35:51.766    5465-5465/com.lyit_android_society.com.rssireader D/AbsListView﹕ unregisterIRListener() is called
07-08 23:35:52.366    5465-5500/com.lyit_android_society.com.rssireader E/pass 1﹕ connection success
07-08 23:35:52.406    5465-5500/com.lyit_android_society.com.rssireader E/pass 2﹕ reader success
07-08 23:35:52.406    5465-5500/com.lyit_android_society.com.rssireader I/JsonObject﹕ <br />
<b>Warning</b>:  mysql_query(): supplied argument is not a valid MySQL-Link resource in <b>/home/denis/public_html/webservice/insert.php</b> on line <b>18</b><br />
<br />
<b>Warning</b>:  mysql_query(): supplied argument is not a valid MySQL-Link resource in <b>/home/denis/public_html/webservice/insert.php</b> on line <b>19</b><br />
null
07-08 23:35:52.406    5465-5500/com.lyit_android_society.com.rssireader E/Fail 3﹕ org.json.JSONException: Value <br of type java.lang.String cannot be converted to JSONObject
07-08 23:35:52.406    5465-5465/com.lyit_android_society.com.rssireader D/Response:﹕ complete

Answer 1

Obligatory suggestion, Don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO, or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial.

---

I've made a port of your code to PDO, hope it helps.

<?php 
//connect using PDO - change CAPPED strings to suit
try{
    $db = new PDO("mysql:host=127.0.0.1;dbname=DBNAME", 'USERNANE', 'PASSWORD',
        array(
            PDO::ATTR_EMULATE_PREPARES => false,
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        )
    );
}catch(PDOException $e){
    exit($e->getMessage());
}

//set default
$flag['code'] = 0;

//is it POST
if($_SERVER['REQUEST_METHOD'] == 'POST'){

    //are expected values not empty
    if( !empty($_POST['Ssid1']) && !empty($_POST['Rssi1']) &&
        !empty($_POST['Ssid2']) && !empty($_POST['Rssi2']) )
    {
        //build query
        $sql = "INSERT INTO phone_table (ap, rssi) VALUES (?, ?), (?, ?)";

        //prepare it
        $stmt = $db->prepare($sql);

        //bind POST values
        $stmt->bindParam(1, $_POST['Ssid1']);
        $stmt->bindParam(2, $_POST['Rssi1']);
        $stmt->bindParam(3, $_POST['Ssid2']);
        $stmt->bindParam(4, $_POST['Rssi2']);

        $stmt->execute();

        $flag['code'] = 1;
    }

}

header('Content-Type: application/json');
exit(json_encode($flag));
?>

Answer 2

You need to fix your sql strings.They are missing single quotes around the values. I.e. should be:

mysql_query($con, "INSERT INTO phone_table(ap, rssi) values('$ap1', '$rssi1')")

But I must also add that you should fix the code to prevent sql-injections. This is not the best way to interact with the database. Look at MySQL APIs like the standard PDO (http://www.php.net/manual/en/mysqlinfo.api.choosing.php). Generally speaking your sql should be parameterized and all values passed to the database should be escaped. Most APIs provide some handy ways to help with that.