How to filter database output? (android PHP)

Question

I am building an mobile app in where a user logs in and it outputs the contents of my database table which is named "announcements".

What I'm trying to do is to filter out these output based on the "department" column from the "accounts" table in which the users are stored.

The "announcements" table has the column named "receiver".

The contents will only show if the "department" column of the user logged in has the same value as the "receiver column" of the "announcements" column or if the value of the receiver is "all".

How do I do this?

My PHP script

<?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 class

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 android.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

You are not making any filtering anywhere in your code...

The steps to do it should be these ones (in this order) :

1. Android side : Calling your webservice (PHP code) with the user's department (in GET or POST parameter)
2. WS side : Requesting your database with something like SELECT * FROM announcement WHERE receiver = '<department'> OR receiver = 'ALL' where department is the user's department
3. WS side : Construct the JSON response
4. Android side : Process the JSON response to display results

The advantages of making it like this :

1. Limit the number of data transfered (limit network consumption on the Android device and you limit the load on your PHP server)
2. Limit the number of data processed Android side (limit the load of the Android app : it's not a desktop app ! Never forgive it !)

PS : reading your post and your comment, I really think you should look into these points before starting to make your app : SQL request, PHP MySQL access (as pointed out by @Jay Blanchard), Web services and HTTP protocol, Android AsyncTask