Android Delete data in Web Server Database (Web Server) |
Android Delete data in Web Server Database (Web Server) การเขียน Android เพื่อติดต่อกับ Server Database และทำการลบ Delete ข้อมูลที่อยู่บน Server โดยใช้ PHP กับ MySQL เป็นตัวกลางสำหรับการแสดงรายการข้อมูล และการรับคำสั่งจาก Android Client ที่จะลบข้อมูล (Delete) ในแต่ล่ะรายการ
รูปอธิบายขั้นตอนการลบข้อมูลในฝั่งของ Web Server ที่ถูก Request จาก Android Client
Basic Android Server and Client
AndroidManifest.xml
<uses-permission android:name="android.permission.INTERNET" />
ในการเขียน Android เพื่อติดต่อกับ Internet จะต้องกำหนด Permission ในส่วนนี้ด้วยทุกครั้ง
member
CREATE TABLE `member` (
`MemberID` int(2) NOT NULL auto_increment,
`Username` varchar(50) NOT NULL,
`Password` varchar(50) NOT NULL,
`Name` varchar(50) NOT NULL,
`Tel` varchar(50) NOT NULL,
`Email` varchar(150) NOT NULL,
PRIMARY KEY (`MemberID`),
UNIQUE KEY `Username` (`Username`),
UNIQUE KEY `Email` (`Email`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;
--
-- Dumping data for table `member`
--
INSERT INTO `member` VALUES (1, 'weerachai', 'weerachai@1', 'Weerachai Nukitram', '0819876107', '[email protected]');
INSERT INTO `member` VALUES (2, 'adisorn', 'adisorn@2', 'Adisorn Bunsong', '021978032', '[email protected]');
INSERT INTO `member` VALUES (3, 'surachai', 'surachai@3', 'Surachai Sirisart', '0876543210', '[email protected]');
INSERT INTO `member` VALUES (4, 'surapong', 'surapong@4', 'Surapong Siriphun', '0812345678', '[email protected]');
โครงสร้างของ MySQL Database
Web Server
showAllData.php เป็นไฟล์สำหรับแสดงรายการข้อมูลทั้งหมด
<?php
$objConnect = mysql_connect("localhost","root","root");
$objDB = mysql_select_db("mydatabase");
// $_POST["txtKeyword"] = "a"; // for Sample
$strKeyword = $_POST["txtKeyword"];
$strSQL = "SELECT * FROM member WHERE 1 AND Name LIKE '%".$strKeyword."%' ORDER BY MemberID ASC ";
$objQuery = mysql_query($strSQL);
$intNumField = mysql_num_fields($objQuery);
$resultArray = array();
while($obResult = mysql_fetch_array($objQuery))
{
$arrCol = array();
for($i=0;$i<$intNumField;$i++)
{
$arrCol[mysql_field_name($objQuery,$i)] = $obResult[$i];
}
array_push($resultArray,$arrCol);
}
mysql_close($objConnect);
echo json_encode($resultArray);
?>
deleteData.php ไฟล์สำหรับลบข้อมูลแต่ล่ะรายการ
<?php
$objConnect = mysql_connect("localhost","root","root");
$objDB = mysql_select_db("mydatabase");
// $_POST["sMemberID"] = "1"; // for Sample
$strMemberID = $_POST["sMemberID"];
$strSQL = "DELETE FROM member WHERE 1 AND MemberID = '".$strMemberID."' ";
$objQuery = mysql_query($strSQL);
if(!$objQuery)
{
$arr["StatusID"] = "0";
$arr["Error"] = "Cannot delete data!";
}
else
{
$arr["StatusID"] = "1";
$arr["Error"] = "";
}
/**
$arr['StatusID'] // (0=Failed , 1=Complete)
$arr['Error' // Error Message
*/
mysql_close($objConnect);
echo json_encode($arr);
?>
Android Project
โครงสร้างของไฟล์ประกอบด้วย 3 ไฟล์คือ MainActivity.java, activity_main.xml และ activity_column.xml
activity_main.xml
<TableLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:id="@+id/tableLayout1"
android:layout_width="fill_parent"
android:layout_height="fill_parent" >
<TableRow
android:id="@+id/tableRow1"
android:layout_width="wrap_content"
android:layout_height="wrap_content" >
<TextView
android:id="@+id/textView1"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:gravity="center"
android:text="Delete Member : "
android:layout_span="1"
android:textAppearance="?android:attr/textAppearanceMedium" />
<EditText
android:id="@+id/txtKeySearch"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:ems="4" >
</EditText>
<Button
android:id="@+id/btnSearch"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="Search" />
</TableRow>
<View
android:layout_height="1dip"
android:background="#CCCCCC" />
<LinearLayout
android:orientation="horizontal"
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:layout_weight="0.1">
<ListView
android:id="@+id/listView1"
android:layout_width="match_parent"
android:layout_height="wrap_content">
</ListView>
</LinearLayout>
<View
android:layout_height="1dip"
android:background="#CCCCCC" />
<LinearLayout
android:id="@+id/LinearLayout1"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:padding="5dip" >
<TextView
android:id="@+id/textView2"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="By.. ThaiCreate.Com" />
</LinearLayout>
</TableLayout>
activity_column.xml
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:id="@+id/linearLayout1"
android:layout_width="fill_parent"
android:layout_height="fill_parent" >
<ImageButton
android:id="@+id/btnDelete"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:src="@android:drawable/ic_notification_clear_all" />
<TextView
android:id="@+id/ColMemberID"
android:layout_width="0dp"
android:layout_height="wrap_content"
android:layout_weight="0.2"
android:text="CustomerID"
android:textAppearance="?android:attr/textAppearanceSmall" />
<TextView
android:id="@+id/ColName"
android:layout_width="0dp"
android:layout_height="wrap_content"
android:layout_weight="1.0"
android:text="Name"
android:textAppearance="?android:attr/textAppearanceSmall" />
<TextView
android:id="@+id/ColTel"
android:layout_width="0dp"
android:layout_height="wrap_content"
android:layout_weight="1"
android:text="Tel"
android:textAppearance="?android:attr/textAppearanceSmall" />
</LinearLayout>
MainActivity.java
package com.myapp;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import org.apache.http.HttpEntity;
import org.apache.http.HttpResponse;
import org.apache.http.NameValuePair;
import org.apache.http.StatusLine;
import org.apache.http.client.ClientProtocolException;
import org.apache.http.client.HttpClient;
import org.apache.http.client.entity.UrlEncodedFormEntity;
import org.apache.http.client.methods.HttpPost;
import org.apache.http.impl.client.DefaultHttpClient;
import org.apache.http.message.BasicNameValuePair;
import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;
import android.os.Bundle;
import android.os.StrictMode;
import android.annotation.SuppressLint;
import android.app.Activity;
import android.app.AlertDialog;
import android.content.Context;
import android.content.DialogInterface;
import android.graphics.Color;
import android.util.Log;
import android.view.LayoutInflater;
import android.view.View;
import android.view.Menu;
import android.view.ViewGroup;
import android.view.inputmethod.InputMethodManager;
import android.widget.BaseAdapter;
import android.widget.Button;
import android.widget.EditText;
import android.widget.ImageButton;
import android.widget.ListView;
import android.widget.TextView;
import android.widget.Toast;
public class MainActivity extends Activity {
ArrayList<HashMap<String, String>> MyArrList;
@SuppressLint("NewApi")
@Override
public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
// Permission StrictMode
if (android.os.Build.VERSION.SDK_INT > 9) {
StrictMode.ThreadPolicy policy = new StrictMode.ThreadPolicy.Builder().permitAll().build();
StrictMode.setThreadPolicy(policy);
}
ShowData();
// btnSearch
final Button btnSearch = (Button) findViewById(R.id.btnSearch);
//btnSearch.setBackgroundColor(Color.TRANSPARENT);
// Perform action on click
btnSearch.setOnClickListener(new View.OnClickListener() {
public void onClick(View v) {
ShowData();
}
});
}
public void ShowData()
{
// listView1
final ListView lisView1 = (ListView)findViewById(R.id.listView1);
// keySearch
EditText strKeySearch = (EditText)findViewById(R.id.txtKeySearch);
// Disbled Keyboard auto focus
InputMethodManager imm = (InputMethodManager)getSystemService(
Context.INPUT_METHOD_SERVICE);
imm.hideSoftInputFromWindow(strKeySearch.getWindowToken(), 0);
/**
* [{"MemberID":"1","Username":"weerachai","Password":"weerachai@1","Name":"Weerachai Nukitram","Tel":"0819876107","Email":"[email protected]"},
* {"MemberID":"2","Username":"adisorn","Password":"adisorn@2","Name":"Adisorn Bunsong","Tel":"021978032","Email":"[email protected]"},
* {"MemberID":"3","Username":"surachai","Password":"surachai@3","Name":"Surachai Sirisart","Tel":"0876543210","Email":"[email protected]"}]
*/
String url = "https://www.thaicreate.com/android/showAllData.php";
// Paste Parameters
List<NameValuePair> params = new ArrayList<NameValuePair>();
params.add(new BasicNameValuePair("txtKeyword", strKeySearch.getText().toString()));
try {
JSONArray data = new JSONArray(getJSONUrl(url,params));
MyArrList = new ArrayList<HashMap<String, String>>();
HashMap<String, String> map;
for(int i = 0; i < data.length(); i++){
JSONObject c = data.getJSONObject(i);
map = new HashMap<String, String>();
map.put("MemberID", c.getString("MemberID"));
map.put("Username", c.getString("Username"));
map.put("Password", c.getString("Password"));
map.put("Name", c.getString("Name"));
map.put("Email", c.getString("Email"));
map.put("Tel", c.getString("Tel"));
MyArrList.add(map);
}
lisView1.setAdapter(new ImageAdapter(this));
} catch (JSONException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public class ImageAdapter extends BaseAdapter
{
private Context context;
public ImageAdapter(Context c)
{
// TODO Auto-generated method stub
context = c;
}
public int getCount() {
// TODO Auto-generated method stub
return MyArrList.size();
}
public Object getItem(int position) {
// TODO Auto-generated method stub
return position;
}
public long getItemId(int position) {
// TODO Auto-generated method stub
return position;
}
public View getView(final int position, View convertView, ViewGroup parent) {
// TODO Auto-generated method stub
LayoutInflater inflater = (LayoutInflater) context
.getSystemService(Context.LAYOUT_INFLATER_SERVICE);
if (convertView == null) {
convertView = inflater.inflate(R.layout.activity_column, null);
}
// imgCmdDelete
ImageButton cmdDelete = (ImageButton) convertView.findViewById(R.id.btnDelete);
cmdDelete.setBackgroundColor(Color.TRANSPARENT);
final AlertDialog.Builder adb1 = new AlertDialog.Builder(MainActivity.this);
final AlertDialog.Builder adb2 = new AlertDialog.Builder(MainActivity.this);
cmdDelete.setOnClickListener(new View.OnClickListener() {
public void onClick(View v) {
adb1.setTitle("Delete?");
adb1.setMessage("Are you sure delete [" + MyArrList.get(position).get("Name") +"]");
adb1.setNegativeButton("Cancel", null);
adb1.setPositiveButton("Ok", new AlertDialog.OnClickListener() {
public void onClick(DialogInterface dialog, int which) {
// Request to Delete data.
String url = "https://www.thaicreate.com/android/deleteData.php";
List<NameValuePair> params = new ArrayList<NameValuePair>();
params.add(new BasicNameValuePair("sMemberID", MyArrList.get(position).get("MemberID")));
String resultServer = getJSONUrl(url,params);
/** Get result delete data from Server (Return the JSON Code)
* StatusID = ? [0=Failed,1=Complete]
* Error = ? [On case error return custom error message]
*
* Eg Login Failed = {"StatusID":"0","Error":"Cannot delete data!"}
* Eg Login Complete = {"StatusID":"1","Error":""}
*/
String strStatusID = "0";
String strError = "Unknow Status";
try {
JSONObject c = new JSONObject(resultServer);
strStatusID = c.getString("StatusID");
strError = c.getString("Error");
} catch (JSONException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
// Prepare Delete
if(strStatusID.equals("0"))
{
// Dialog
adb2.setTitle("Error! ");
adb2.setIcon(android.R.drawable.btn_star_big_on);
adb2.setPositiveButton("Close", null);
adb2.setMessage(strError);
adb2.show();
}
else
{
Toast.makeText(MainActivity.this, "Delete data successfully.", Toast.LENGTH_SHORT).show();
ShowData(); // reload data again
}
}});
adb1.show();
}
});
// ColMemberID
TextView txtMemberID = (TextView) convertView.findViewById(R.id.ColMemberID);
txtMemberID.setPadding(10, 0, 0, 0);
txtMemberID.setText(MyArrList.get(position).get("MemberID") +".");
// R.id.ColName
TextView txtName = (TextView) convertView.findViewById(R.id.ColName);
txtName.setPadding(5, 0, 0, 0);
txtName.setText(MyArrList.get(position).get("Name"));
// R.id.ColTel
TextView txtTel = (TextView) convertView.findViewById(R.id.ColTel);
txtTel.setPadding(5, 0, 0, 0);
txtTel.setText(MyArrList.get(position).get("Tel"));
return convertView;
}
}
public String getJSONUrl(String url,List<NameValuePair> params) {
StringBuilder str = new StringBuilder();
HttpClient client = new DefaultHttpClient();
HttpPost httpPost = new HttpPost(url);
try {
httpPost.setEntity(new UrlEncodedFormEntity(params));
HttpResponse response = client.execute(httpPost);
StatusLine statusLine = response.getStatusLine();
int statusCode = statusLine.getStatusCode();
if (statusCode == 200) { // Download OK
HttpEntity entity = response.getEntity();
InputStream content = entity.getContent();
BufferedReader reader = new BufferedReader(new InputStreamReader(content));
String line;
while ((line = reader.readLine()) != null) {
str.append(line);
}
} else {
Log.e("Log", "Failed to download result..");
}
} catch (ClientProtocolException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return str.toString();
}
@Override
public boolean onCreateOptionsMenu(Menu menu) {
getMenuInflater().inflate(R.menu.activity_main, menu);
return true;
}
}
Screenshot
แสดงรายการข้อมูลทั้งหมด
คลิกที่ปุ่ม Delete ซึ่งจะมีการ Confirm ยืนยัน
ข้อมูลถูกลบเรียบร้อย
ListView จะ Update เพื่อ Refresh ข้อมูล
เมื่อกลับไปดูฐานข้อมูล MySQL ที่ฝั่งของ Web Server ข้อมูลก็จะถูกลบออกไป
.
|