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

Depending on your server you probably cannot remotely make that connection. You should probably try using a we view and send the data that way - since it would run from the server. — durbnpoisn, Jul 08 '14 at 23:12
Where do you set `$host, $username ,$password,$dbname`? and its `mysql_query('SQL',$con);` your also open to abuse, fix the SQL injection holes before you put it live. [How can I prevent SQL-injection in PHP?](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php), also the `mysql_*` functions are deprecated. — Lawrence Cherone, Jul 08 '14 at 23:14
RTM `mysql_query ( string $query [, resource $link_identifier = NULL ] )` http://www.php.net/manual/en/function.mysql-query.php — Lawrence Cherone, Jul 08 '14 at 23:17

score 1 · Accepted Answer · edited May 25 '19 at 13:34

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));
?>

Thank you for the help I am just getting my head around most of this so this is a great. — Dieseled UP, Jul 08 '14 at 23:44
np, I thought id just port it rather then tell you your doing it wrong, and give an answer that uses the old API.. which you might of not of understood. Plus I prefer writing code more, rather then long arbitrary confusing sentences. Happy coding! — Lawrence Cherone, Jul 08 '14 at 23:56

score 0 · Answer 2 · answered Jul 08 '14 at 23:19

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.

Read my comment about $con – Lawrence Cherone Jul 08 '14 at 23:22 — Lawrence Cherone, Jul 08 '14 at 23:22

Inserting data to a mysql database from android phone

2 Answers2