java如何过滤数据库输出？（安卓 PHP）

Question

我正在构建一个移动应用程序，用户在其中登录并输出我的数据库表（名为“公告”）的内容

我试图做的是根据存储用户的“accounts”表中的“department”列过滤掉这些输出

“公告”表中有一列名为“receiver”

仅当登录用户的“部门”列与“公告”列的“接收者列”的值相同，或接收者的值为“全部”时，内容才会显示

我该怎么做

我的PHP脚本

<?php
$host="localhost"; //replace with database hostname 
$username="root"; //replace with database username 
$password=""; //replace with database password 
$db_name="sunshinedb"; //replace with database name

$con=mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");
$sql = "select * from announcement"; 
$result = mysql_query($sql);
$json = array();

if(mysql_num_rows($result)){
    while($row=mysql_fetch_assoc($result)){
        $json['services'][]=$row;
    }
}
mysql_close($con);
echo json_encode($json); 
?>

Java类

JSONObject jsonobject;
JSONArray jsonarray;
ListView listview;

ArrayList<HashMap<String, String>> arraylist;
ProgressDialog mProgressDialog;

JSONParser jsonParser = new JSONParser();
String email;

String[] services;

private String url = "http://10.0.3.2/sunshine-ems/services.php";

String user_id;

// ALL JSON node names
private static final String TAG_TRANS_ID = "announcement_id";
private static final String TAG_DATE = "date";
private static final String TAG_SERVICES = "title";

public void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.videos_layout);

    // get listview
    ListView lv = getListView();

    lv.setOnItemClickListener(new 安卓.widget.AdapterView.OnItemClickListener() {
        @Override
        public void onItemClick(AdapterView<?> arg0, View view, int arg2,
                long arg3) {

            Intent i = new Intent(getApplicationContext(),
                    Single_List.class);

            String transaction_id = ((TextView) view
                    .findViewById(R.id.transac_id)).getText().toString();

            i.putExtra("announcement_id", transaction_id);

            startActivity(i);
        }
    });

    new DownloadJSON().execute();
}

// DownloadJSON AsyncTask
private class DownloadJSON extends AsyncTask<String, String, String> {

    @Override
    protected void onPreExecute() {
        super.onPreExecute();
        // Create a progressdialog
        mProgressDialog = new ProgressDialog(VideosActivity.this);
        // Set progressdialog title
        mProgressDialog.setTitle("Loading Services");
        // Set progressdialog message
        mProgressDialog.setMessage("Loading...");
        mProgressDialog.setIndeterminate(false);
        // Show progressdialog
        mProgressDialog.show();
    }

    @Override
    protected String doInBackground(String... params) {

        JSONObject json = JSONfunctions.getJSONfromURL(url);

        // Check your log cat for JSON reponse
        Log.d("Service history ", json.toString());

        // Create the array
        arraylist = new ArrayList<HashMap<String, String>>();

        try {
            // Locate the array name
            jsonarray = json.getJSONArray("services");

            for (int i = 0; i < jsonarray.length(); i++) {
                HashMap<String, String> map = new HashMap<String, String>();
                json = jsonarray.getJSONObject(i);

                String transac_id = json.getString(TAG_TRANS_ID);
                String date = json.getString(TAG_DATE);
                String service = json.getString(TAG_SERVICES);

                // Retrive JSON Objects
                map.put(TAG_SERVICES, service);
                map.put(TAG_DATE, date);
                map.put(TAG_TRANS_ID, transac_id);

                // Set the JSON Objects into the array
                arraylist.add(map);
            }
        } catch (JSONException e) {
            Log.e("Error", e.getMessage());
            e.printStackTrace();
        }
        return null;
    }

    @Override
    protected void onPostExecute(String file_url) {
        mProgressDialog.dismiss();
        // updating UI from Background Thread
        runOnUiThread(new Runnable() {
            public void run() {

                /**
                 * Updating parsed JSON data into ListView
                 * */
                ListAdapter adapter = new SimpleAdapter(
                        VideosActivity.this, arraylist,
                        R.layout.listview_services, new String[] {
                                TAG_TRANS_ID, TAG_SERVICES, TAG_DATE },
                        new int[] { R.id.transac_id, R.id.txt_service,
                                R.id.txt_date });
                // updating listview
                setListAdapter(adapter);

            }
        });

    }
}

Answer 1

你没有在你的代码中做任何过滤

要做到这一点，步骤如下（按以下顺序）：

1. Android端：使用用户的部门调用Web服务（PHP代码）（在GET或POST参数中）
2. WS-side：使用类似于SELECT * FROM announcement WHERE receiver = '<department'> OR receiver = 'ALL'的内容请求数据库，其中department是用户的部门
3. WS-side：构造JSON响应
4. Android端：处理JSON响应以显示结果

这样做的好处是：

1. 限制传输的数据数量（限制Android设备上的网络消耗，并限制PHP服务器上的负载）
2. 限制Android端处理的数据数量（限制Android应用程序的负载：它不是桌面应用程序！永远不要原谅它！）

PS：阅读你的帖子和评论，我真的认为在开始制作应用程序之前，你应该仔细考虑以下几点：SQL请求、PHP MySQL访问（正如@Jay Blanchard所指出的）、Web服务和HTTP协议、Android AsyncTask