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
– durbnpoisn, Commented Jul 8, 2014 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?, also the mysql_* functions are deprecated. — Lawrence Cherone
– Lawrence Cherone, Commented Jul 8, 2014 at 23:14
RTM mysql_query ( string $query [, resource $link_identifier = NULL ] ) php.net/manual/en/function.mysql-query.php — Lawrence Cherone
– Lawrence Cherone, Commented Jul 8, 2014 at 23:17

Zoe - Save the data dump · Accepted Answer · 2019-05-25 13:34:17Z

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

edited May 25, 2019 at 13:34

Zoe - Save the data dump

28.4k22 gold badges130 silver badges163 bronze badges

answered Jul 8, 2014 at 23:38

Lawrence Cherone

46.7k7 gold badges66 silver badges107 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dieseled UP Over a year ago

Thank you for the help I am just getting my head around most of this so this is a great.

Lawrence Cherone Over a year ago

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!

skarist · Accepted Answer · 2014-07-08 23:19:47Z

0

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.

answered Jul 8, 2014 at 23:19

skarist

1,0307 silver badges9 bronze badges

1 Comment

Lawrence Cherone Over a year ago

Read my comment about $con

Collectives™ on Stack Overflow

Inserting data to a mysql database from android phone

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related