php - 将数据从android手机插入mysql数据库

Question

想知道任何人都可以帮助我是 android 的新手，我在尝试创建的应用程序中遇到了问题。该应用程序从附近的手机获取 rssi 值，然后我尝试将这些读取内容保存到 Web 服务器，根据 Logcat，我看起来连接正常，但我的 PHP 脚本中的 SQL 查询似乎导致了问题我可能错了任何帮助将不胜感激。

我正在发布我的 android 代码、php 脚本和 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脚本:

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

和 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

强制性建议, Don't use mysql_* functions in new code .它们不再维护 and are officially deprecated .查看red box ？了解 prepared statements相反，并使用 PDO , 或 MySQLi - this article将帮助您决定选择哪个。如果选择 PDO，here is a good tutorial .

---

我已经将您的代码移植到 PDO，希望对您有所帮助。

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