I am working on an Android project and I am completely new to Android. I'm following the below tutorial link. My project is about police registry system. I am connecting to MySQL database with the help of PHP and JSON.
I have created a form where user's can register and they can login using their user name and password. Also I have made a form where user can register their complaint and complaints are stored in a single database known as db_complaints in the database. I am able to retrieve all complaints using following PHP code.
<?php
/*
Our "config.inc.php" file connects to database every time we include or require
it within a php script. Since we want this script to add a new user to our db,
we will be talking with our database, and therefore,
let's require the connection to happen:
*/
require("config.inc.php");
//initial query
if (!empty($_POST)) {
//initial query
$query = "INSERT INTO complaints ( username, title, message ) VALUES ( :user, :title, :message ) ";
//Update query
$query_params = array(
':user' => $_POST['username'],
':title' => $_POST['title'],
':message' => $_POST['message']
);
//execute query
try {
$stmt = $db->prepare($query);
$result = $stmt->execute($query_params);
}
catch (PDOException $ex) {
$response["success"] = 0;
$response["message"] = "Database Error!";
die(json_encode($response));
}
// Finally, we can retrieve all of the found rows into an array using fetchAll
$rows = $stmt->fetchAll();
if ($rows) {
$response["success"] = 1;
$response["message"] = "Post Available!";
$response["posts"] = array();
foreach ($rows as $row) {
$post = array();
$post["post_id"] = $row["post_id"];
$post["username"] = $row["username"];
$post["title"] = $row["title"];
$post["message"] = $row["message"];
//update our repsonse JSON data
array_push($response["posts"], $post);
}
// echoing JSON response
echo json_encode($response);
} else {
$response["success"] = 0;
$response["message"] = "No Post Available!";
die(json_encode($response));
}}
?>i want to retrieve a particular user complaints separately so they can view there complaints in my_complaints form but i am not able to do this and i have been stuck.
</div>
Your code looks pretty neat using prepared statements and error handling, so where is your problem actually?
$query = "SELECT * FROM complaints WHERE username = :user";
$query_params = array(':user' => $_POST['username']);
That query should do the trick to retrieve complaints from a specific user.
and this is the code i am using in android to retrieve all the user complaint's
package com.example.policadda;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Locale;
import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;
import android.app.ListActivity;
import android.app.ProgressDialog;
import android.content.Intent;
import android.content.SharedPreferences;
import android.os.AsyncTask;
import android.os.Bundle;
import android.preference.PreferenceManager;
import android.util.Log;
import android.view.View;
import android.widget.AdapterView;
import android.widget.AdapterView.OnItemClickListener;
import android.widget.Button;
import android.widget.ImageView;
import android.widget.LinearLayout;
import android.widget.ListAdapter;
import android.widget.ListView;
import android.widget.SimpleAdapter;
import android.widget.TextView;
public class Mycomplaints extends ListActivity {
// Progress Dialog
private ProgressDialog pDialog;
//php read comments script
//localhost :
//testing on your device
//put your local ip instead, on windows, run CMD > ipconfig
//or in mac's terminal type ifconfig and look for the ip under en0 or en1
// private static final String READ_COMMENTS_URL = "http://xxx.xxx.x.x:1234/webservice/comments.php";
//testing on Emulator:
private static final String READ_COMMENTS_URL = "http://www.iamnotcrazy.hol.es/webservice/mycomplaints.php";
//testing from a real server:
//private static final String READ_COMMENTS_URL = "http://www.mybringback.com/webservice/comments.php";
//JSON IDS:
private static final String TAG_SUCCESS = "success";
private static final String TAG_TITLE = "title";
private static final String TAG_POSTS = "posts";
private static final String TAG_COMPLAINT_ID = "complaint_id";
private static final String TAG_USERNAME = "username";
private static final String TAG_SUBJECT = "subject";
//it's important to note that the message is both in the parent branch of
//our JSON tree that displays a "Post Available" or a "No Post Available" message,
//and there is also a message for each individual post, listed under the "posts"
//category, that displays what the user typed as their message.
//An array of all of our comments
private JSONArray mComments = null;
//manages all of our comments in a list.
private ArrayList<HashMap<String, String>> mCommentList;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
//note that use read_comments.xml instead of our single_post.xml
setContentView(R.layout.my_complaints);
}
@Override
protected void onResume() {
// TODO Auto-generated method stub
super.onResume();
//loading the comments via AsyncTask
new LoadComments().execute();
}
public void addComment(View v)
{
Intent i = new Intent(Mycomplaints.this, Complaints.class);
startActivity(i);
}
/**
* Retrieves json data of comments
*/
public void updateJSONdata() {
// Instantiate the arraylist to contain all the JSON data.
// we are going to use a bunch of key-value pairs, referring
// to the json element name, and the content, for example,
// message it the tag, and "I'm awesome" as the content..
mCommentList = new ArrayList<HashMap<String, String>>();
// Bro, it's time to power up the J parser
JSONParser jParser = new JSONParser();
// Feed the beast our comments url, and it spits us
//back a JSON object. Boo-yeah Jerome.
JSONObject json = jParser.getJSONFromUrl(READ_COMMENTS_URL);
//when parsing JSON stuff, we should probably
//try to catch any exceptions:
try {
//I know I said we would check if "Posts were Avail." (success==1)
//before we tried to read the individual posts, but I lied...
//mComments will tell us how many "posts" or comments are
//available
mComments = json.getJSONArray(TAG_POSTS);
// looping through all posts according to the json object returned
for (int i = 0; i < mComments.length(); i++) {
JSONObject c = mComments.getJSONObject(i);
//gets the content of each tag
String title = c.getString(TAG_TITLE);
String content = c.getString(TAG_SUBJECT);
String username = c.getString(TAG_USERNAME);
// creating new HashMap
HashMap<String, String> map = new HashMap<String, String>();
map.put(TAG_TITLE, title);
map.put(TAG_SUBJECT, content);
map.put(TAG_USERNAME, username);
// adding HashList to ArrayList
mCommentList.add(map);
//annndddd, our JSON data is up to date same with our array list
}
} catch (JSONException e) {
e.printStackTrace();
}
}
/**
* Inserts the parsed data into our listview
*/
private void updateList() {
// For a ListActivity we need to set the List Adapter, and in order to do
//that, we need to create a ListAdapter. This SimpleAdapter,
//will utilize our updated Hashmapped ArrayList,
//use our single_post xml template for each item in our list,
//and place the appropriate info from the list to the
//correct GUI id. Order is important here.
ListAdapter adapter = new SimpleAdapter(this, mCommentList,
R.layout.single_post, new String[] { TAG_TITLE, TAG_SUBJECT,
TAG_USERNAME }, new int[] { R.id.title, R.id.message,
R.id.username });
// I shouldn't have to comment on this one:
setListAdapter(adapter);
// Optional: when the user clicks a list item we
//could do something. However, we will choose
//to do nothing...
ListView lv = getListView();
lv.setOnItemClickListener(new OnItemClickListener() {
@Override
public void onItemClick(AdapterView<?> parent, View view,
int position, long id) {
// This method is triggered if an item is click within our
// list. For our example we won't be using this, but
// it is useful to know in real life applications.
}
});
}
public class LoadComments extends AsyncTask<Void, Void, Boolean> {
@Override
protected void onPreExecute() {
super.onPreExecute();
pDialog = new ProgressDialog(Mycomplaints.this);
pDialog.setMessage("Loading complaints...");
pDialog.setIndeterminate(false);
pDialog.setCancelable(true);
pDialog.show();
}
@Override
protected Boolean doInBackground(Void... arg0) {
//we will develop this method in version 2
updateJSONdata();
return null;
}
@Override
protected void onPostExecute(Boolean result) {
super.onPostExecute(result);
pDialog.dismiss();
//we will develop this method in version 2
updateList();
}
}
}</div>
this is the code i altered as your suggestion in php
<?php
/*
Our "config.inc.php" file connects to database every time we include or require
it within a php script. Since we want this script to add a new user to our db,
we will be talking with our database, and therefore,
let's require the connection to happen:
*/
require("config.inc.php");
//initial query
$query = "Select * FROM complaint WHERE username= :user";
$query_params = array(':user' => $_POST['username']);
//execute query
try {
$stmt = $db->prepare($query);
$result = $stmt->execute($query_params);
}
catch (PDOException $ex) {
$response["success"] = 0;
$response["message"] = "Database Error!";
die(json_encode($response));
}
// Finally, we can retrieve all of the found rows into an array using fetchAll
$rows = $stmt->fetchAll();
if ($rows) {
$response["success"] = 1;
$response["message"] = "Post Available!";
$response["posts"] = array();
foreach ($rows as $row) {
$post = array();
$post["complaint_id"] = $row["complaint_id"];
$post["username"] = $row["username"];
$post["title"] = $row["title"];
$post["subject"] = $row["subject"];
//update our repsonse JSON data
array_push($response["posts"], $post);
}
// echoing JSON response
echo json_encode($response);
} else {
$response["success"] = 0;
$response["message"] = "No Post Available!";
die(json_encode($response));
}
?>and i altered my android in such a way that where i used shared preference to store the username and send it to php and php can receive the username and execute the code :) . below is my android code.
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Locale;
import org.apache.http.NameValuePair;
import org.apache.http.message.BasicNameValuePair;
import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;
import android.app.ListActivity;
import android.app.ProgressDialog;
import android.content.Intent;
import android.content.SharedPreferences;
import android.os.AsyncTask;
import android.os.Bundle;
import android.preference.PreferenceManager;
import android.util.Log;
import android.view.View;
import android.widget.AdapterView;
import android.widget.AdapterView.OnItemClickListener;
import android.widget.Button;
import android.widget.ImageView;
import android.widget.LinearLayout;
import android.widget.ListAdapter;
import android.widget.ListView;
import android.widget.SimpleAdapter;
import android.widget.TextView;
public class Mycomplaints extends ListActivity {
// Progress Dialog
private ProgressDialog pDialog;
JSONParser jsonParser = new JSONParser();
//php read comments script
//localhost :
//testing on your device
//put your local ip instead, on windows, run CMD > ipconfig
//or in mac's terminal type ifconfig and look for the ip under en0 or en1
// private static final String READ_COMMENTS_URL = "http://xxx.xxx.x.x:1234/webservice/comments.php";
//testing on Emulator:
private static final String READ_COMPLAINTS_URL = "http://www.iamnotcrazy.hol.es/webservice/mycomplaints.php";
//testing from a real server:
//private static final String READ_COMMENTS_URL = "http://www.mybringback.com/webservice/comments.php";
//JSON IDS:
private static final String TAG_SUCCESS = "success";
private static final String TAG_TITLE = "title";
private static final String TAG_POSTS = "posts";
private static final String TAG_COMPLAINT_ID = "complaint_id";
private static final String TAG_USERNAME = "username";
private static final String TAG_SUBJECT = "subject";
//it's important to note that the message is both in the parent branch of
//our JSON tree that displays a "Post Available" or a "No Post Available" message,
//and there is also a message for each individual post, listed under the "posts"
//category, that displays what the user typed as their message.
//An array of all of our comments
private JSONArray mComments = null;
//manages all of our comments in a list.
private ArrayList<HashMap<String, String>> mCommentList;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
//note that use read_comments.xml instead of our single_post.xml
setContentView(R.layout.my_complaints);
}
@Override
protected void onResume() {
// TODO Auto-generated method stub
super.onResume();
//loading the comments via AsyncTask
new LoadComments().execute();
}
public void addComment(View v)
{
Intent i = new Intent(Mycomplaints.this, Complaints.class);
startActivity(i);
}
/**
* Retrieves json data of comments
*/
public void updateJSONdata() {
// Instantiate the arraylist to contain all the JSON data.
// we are going to use a bunch of key-value pairs, referring
// to the json element name, and the content, for example,
// message it the tag, and "I'm awesome" as the content..
mCommentList = new ArrayList<HashMap<String, String>>();
// Bro, it's time to power up the J parser
JSONParser jParser = new JSONParser();
// Feed the beast our comments url, and it spits us
//back a JSON object. Boo-yeah Jerome.
JSONObject json = jParser.getJSONFromUrl(READ_COMPLAINTS_URL);
SharedPreferences sp = PreferenceManager.getDefaultSharedPreferences(Mycomplaints.this);
String post_username = sp.getString("username", "diva");
//when parsing JSON stuff, we should probably
//try to catch any exceptions:
try {
List<NameValuePair> params = new ArrayList<NameValuePair>();
params.add(new BasicNameValuePair("username", post_username));
Log.d("request!", "starting");
JSONObject json1 = jsonParser.makeHttpRequest(
READ_COMPLAINTS_URL, "POST", params);
//I know I said we would check if "Posts were Avail." (success==1)
//before we tried to read the individual posts, but I lied...
//mComments will tell us how many "posts" or comments are
//available
mComments = json1.getJSONArray(TAG_POSTS);
// looping through all posts according to the json object returned
for (int i = 0; i < mComments.length(); i++) {
JSONObject c = mComments.getJSONObject(i);
//gets the content of each tag
String title = c.getString(TAG_TITLE);
String content = c.getString(TAG_SUBJECT);
String username = c.getString(TAG_USERNAME);
// creating new HashMap
HashMap<String, String> map = new HashMap<String, String>();
map.put(TAG_TITLE, title);
map.put(TAG_SUBJECT, content);
map.put(TAG_USERNAME, username);
// adding HashList to ArrayList
mCommentList.add(map);
//annndddd, our JSON data is up to date same with our array list
}
} catch (JSONException e) {
e.printStackTrace();
}
}
/**
* Inserts the parsed data into our listview
*/
private void updateList() {
// For a ListActivity we need to set the List Adapter, and in order to do
//that, we need to create a ListAdapter. This SimpleAdapter,
//will utilize our updated Hashmapped ArrayList,
//use our single_post xml template for each item in our list,
//and place the appropriate info from the list to the
//correct GUI id. Order is important here.
ListAdapter adapter = new SimpleAdapter(this, mCommentList,
R.layout.single_post, new String[] { TAG_TITLE, TAG_SUBJECT,
TAG_USERNAME }, new int[] { R.id.title, R.id.message,
R.id.username });
// I shouldn't have to comment on this one:
setListAdapter(adapter);
// Optional: when the user clicks a list item we
//could do something. However, we will choose
//to do nothing...
ListView lv = getListView();
lv.setOnItemClickListener(new OnItemClickListener() {
@Override
public void onItemClick(AdapterView<?> parent, View view,
int position, long id) {
// This method is triggered if an item is click within our
// list. For our example we won't be using this, but
// it is useful to know in real life applications.
}
});
}
public class LoadComments extends AsyncTask<Void, Void, Boolean> {
@Override
protected void onPreExecute() {
super.onPreExecute();
pDialog = new ProgressDialog(Mycomplaints.this);
pDialog.setMessage("Loading complaints...");
pDialog.setIndeterminate(false);
pDialog.setCancelable(true);
pDialog.show();
}
@Override
protected Boolean doInBackground(Void... arg0) {
//we will develop this method in version 2
updateJSONdata();
return null;
}
@Override
protected void onPostExecute(Boolean result) {
super.onPostExecute(result);
pDialog.dismiss();
//we will develop this method in version 2
updateList();
}
}
}</div>