· 10 years ago · Sep 20, 2016, 04:50 PM
1<?php
2 define("DB_HOST", "localhost");
3 define("DB_USER", "UserName");
4 define("DB_PASSWORD", "PWD");
5 define("DB_DATABASE", "db");
6?>
7
8<?php
9
10 class DB_Connect {
11
12 // constructor
13 function __construct() {
14
15 }
16
17 // destructor
18 function __destruct() {
19 // $this->close();
20 }
21
22 // Connecting to database
23 public function connect() {
24 require_once 'config.php';
25 // connecting to mysql
26 $con = @mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);
27 // selecting database
28 @mysql_select_db(DB_DATABASE);
29
30 // return database handler
31 return $con;
32 }
33
34 // Closing database connection
35 public function close() {
36 @mysql_close();
37 }
38
39 }
40?>
41
42<?php
43
44 class DB_Functions {
45
46 private $db;
47
48 //put your code here
49 // constructor
50 function __construct() {
51 include_once './db_connect.php';
52 // connecting to database
53 $this->db = new DB_Connect();
54 $this->db->connect();
55 }
56
57 // destructor
58 function __destruct() {
59
60 }
61
62 /**
63 * Storing new user
64 * returns user details
65 */
66 public function storeUser($Id,$User) {
67 // Insert user into database
68 $result = @mysql_query("INSERT INTO users VALUES($Id,'$User')");
69
70 if ($result) {
71 return true;
72 } else {
73 if( @mysql_errno() == 1062) {
74 // Duplicate key - Primary Key Violation
75 return true;
76 } else {
77 // For other errors
78 return false;
79 }
80 }
81 }
82 /**
83 * Getting all users
84 */
85 public function getAllUsers() {
86 $result = @mysql_query("select * FROM users");
87 return $result;
88 }
89 }
90
91 ?>
92
93<?php
94 include_once './db_functions.php';
95 //Create Object for DB_Functions clas
96 $db = new DB_Functions();
97 //Get JSON posted by Android Application
98 $json = $_POST["usersJSON"];
99 //Remove Slashes
100 if (get_magic_quotes_gpc()){
101 $json = stripslashes($json);
102 }
103 //Decode JSON into an Array
104 $data = json_decode($json);
105 //Util arrays to create response JSON
106 $a=array();
107 $b=array();
108 //Loop through an Array and insert data read from JSON into MySQL DB
109 for($i=0; $i<count($data) ; $i++)
110 {
111 //Store User into MySQL DB
112 $res = $db->storeUser($data[$i]->userId,$data[$i]->userName);
113 //Based on inserttion, create JSON response
114 if($res){
115 $b["id"] = $data[$i]->userId;
116 $b["status"] = 'yes';
117 array_push($a,$b);
118 }else{
119 $b["id"] = $data[$i]->userId;
120 $b["status"] = 'no';
121 array_push($a,$b);
122 }
123 }
124 //Post JSON response back to Android Application
125 echo json_encode($a);
126 ?>
127
128<html>
129 <head><title>View Users</title>
130 <style>
131 body {
132 font: normal medium/1.4 sans-serif;
133 }
134 table {
135 border-collapse: collapse;
136 width: 20%;
137 margin-left: auto;
138 margin-right: auto;
139 }
140 tr > td {
141 padding: 0.25rem;
142 text-align: center;
143 border: 1px solid #ccc;
144 }
145 tr:nth-child(even) {
146 background: #FAE1EE;
147 }
148 tr:nth-child(odd) {
149 background: #edd3ff;
150 }
151 tr#header{
152 background: #c1e2ff;
153 }
154 div.header{
155 padding: 10px;
156 background: #e0ffc1;
157 width:30%;
158 color: #008000;
159 margin:5px;
160 }
161 div.refresh{
162 margin-top:10px;
163 width: 5%;
164 margin-left: auto;
165 margin-right: auto;
166 }
167 div#norecord{
168 margin-top:10px;
169 width: 15%;
170 margin-left: auto;
171 margin-right: auto;
172 }
173 </style>
174 <script>
175 function refreshPage(){
176 location.reload();
177 }
178 </script>
179 </head>
180 <body>
181 <center>
182 <div class="header">
183 Android SQLite and MySQL Sync Results
184 </div>
185 </center>
186 <?php
187 include_once 'db_functions.php';
188 $db = new DB_Functions();
189 $users = $db->getAllUsers();
190 if ($users != false)
191 $no_of_users = mysql_num_rows($users);
192 else
193 $no_of_users = 0;
194 ?>
195 <?php
196 if ($no_of_users > 0) {
197 ?>
198 <table>
199 <tr id="header"><td>Id</td><td>Username</td></tr>
200 <?php
201 while ($row = mysql_fetch_array($users)) {
202 ?>
203 <tr>
204 <td><span><?php echo $row["Id"] ?></span></td>
205 <td><span><?php echo $row["Name"] ?></span></td>
206 </tr>
207 <?php } ?>
208 </table>
209 <?php }else{ ?>
210 <div id="norecord">
211 No records in MySQL DB
212 </div>
213 <?php } ?>
214 <div class="refresh">
215 <button onclick="refreshPage()">Refresh</button>
216 </div>
217 </body>
218 </html>
219
220public class DBController extends SQLiteOpenHelper {
221
222 public DBController(Context applicationcontext) {
223 super(applicationcontext, "androidsqlite.db", null, 1);
224 }
225 //Creates Table
226 @Override
227 public void onCreate(SQLiteDatabase database) {
228 String query;
229 query = "CREATE TABLE users ( userId INTEGER PRIMARY KEY, userName TEXT, udpateStatus TEXT)";
230 database.execSQL(query);
231 }
232 @Override
233 public void onUpgrade(SQLiteDatabase database, int version_old, int current_version) {
234 String query;
235 query = "DROP TABLE IF EXISTS users";
236 database.execSQL(query);
237 onCreate(database);
238 }
239 /**
240 * Inserts User into SQLite DB
241 * @param queryValues
242 */
243 public void insertUser(HashMap<String, String> queryValues) {
244 SQLiteDatabase database = this.getWritableDatabase();
245 ContentValues values = new ContentValues();
246 values.put("userName", queryValues.get("userName"));
247 values.put("udpateStatus", "no");
248 database.insert("users", null, values);
249 database.close();
250 }
251
252 /**
253 * Get list of Users from SQLite DB as Array List
254 * @return
255 */
256 public ArrayList<HashMap<String, String>> getAllUsers() {
257 ArrayList<HashMap<String, String>> wordList;
258 wordList = new ArrayList<HashMap<String, String>>();
259 String selectQuery = "SELECT * FROM users";
260 SQLiteDatabase database = this.getWritableDatabase();
261 Cursor cursor = database.rawQuery(selectQuery, null);
262 if (cursor.moveToFirst()) {
263 do {
264 HashMap<String, String> map = new HashMap<String, String>();
265 map.put("userId", cursor.getString(0));
266 map.put("userName", cursor.getString(1));
267 wordList.add(map);
268 } while (cursor.moveToNext());
269 }
270 database.close();
271 return wordList;
272 }
273
274 /**
275 * Compose JSON out of SQLite records
276 * @return
277 */
278 public String composeJSONfromSQLite(){
279 ArrayList<HashMap<String, String>> wordList;
280 wordList = new ArrayList<HashMap<String, String>>();
281 String selectQuery = "SELECT * FROM users where udpateStatus = '"+"no"+"'";
282 SQLiteDatabase database = this.getWritableDatabase();
283 Cursor cursor = database.rawQuery(selectQuery, null);
284 if (cursor.moveToFirst()) {
285 do {
286 HashMap<String, String> map = new HashMap<String, String>();
287 map.put("userId", cursor.getString(0));
288 map.put("userName", cursor.getString(1));
289 wordList.add(map);
290 } while (cursor.moveToNext());
291 }
292 database.close();
293 Gson gson = new GsonBuilder().create();
294 //Use GSON to serialize Array List to JSON
295 return gson.toJson(wordList);
296 }
297
298 /**
299 * Get Sync status of SQLite
300 * @return
301 */
302 public String getSyncStatus(){
303 String msg = null;
304 if(this.dbSyncCount() == 0){
305 msg = "SQLite and Remote MySQL DBs are in Sync!";
306 }else{
307 msg = "DB Sync neededn";
308 }
309 return msg;
310 }
311
312 /**
313 * Get SQLite records that are yet to be Synced
314 * @return
315 */
316 public int dbSyncCount(){
317 int count = 0;
318 String selectQuery = "SELECT * FROM users where udpateStatus = '"+"no"+"'";
319 SQLiteDatabase database = this.getWritableDatabase();
320 Cursor cursor = database.rawQuery(selectQuery, null);
321 count = cursor.getCount();
322 database.close();
323 return count;
324 }
325
326 /**
327 * Update Sync status against each User ID
328 * @param id
329 * @param status
330 */
331 public void updateSyncStatus(String id, String status){
332 SQLiteDatabase database = this.getWritableDatabase();
333 String updateQuery = "Update users set udpateStatus = '"+ status +"' where userId="+"'"+ id +"'";
334 Log.d("query",updateQuery);
335 database.execSQL(updateQuery);
336 database.close();
337 }
338}
339
340public class NewUser extends Activity {
341 EditText userName;
342 DBController controller = new DBController(this);
343
344 @Override
345 public void onCreate(Bundle savedInstanceState) {
346 super.onCreate(savedInstanceState);
347 setContentView(R.layout.add_new_user);
348 userName = (EditText) findViewById(R.id.userName);
349 }
350
351 /**
352 * Called when Save button is clicked
353 * @param view
354 */
355 public void addNewUser(View view) {
356 HashMap<String, String> queryValues = new HashMap<String, String>();
357 queryValues.put("userName", userName.getText().toString());
358 if (userName.getText().toString() != null
359 && userName.getText().toString().trim().length() != 0) {
360 controller.insertUser(queryValues);
361 this.callHomeActivity(view);
362 } else {
363 Toast.makeText(getApplicationContext(), "Please enter User name",
364 Toast.LENGTH_LONG).show();
365 }
366 }
367
368 /**
369 * Navigate to Home Screen
370 * @param view
371 */
372 public void callHomeActivity(View view) {
373 Intent objIntent = new Intent(getApplicationContext(),
374 MainActivity.class);
375 startActivity(objIntent);
376 }
377
378 /**
379 * Called when Cancel button is clicked
380 * @param view
381 */
382 public void cancelAddUser(View view) {
383 this.callHomeActivity(view);
384 }
385}
386
387public class MainActivity extends AppCompatActivity{
388 //DB Class to perform DB related operations
389 DBController controller = new DBController(this);
390 //Progress Dialog Object
391 ProgressDialog prgDialog;
392
393 @Override
394 protected void onCreate(Bundle savedInstanceState) {
395 super.onCreate(savedInstanceState);
396 setContentView(R.layout.activity_main);
397 //Get User records from SQLite DB
398 ArrayList<HashMap<String, String>> userList = controller.getAllUsers();
399 //
400 if(userList.size()!=0){
401 //Set the User Array list in ListView
402 ListAdapter adapter = new SimpleAdapter( MainActivity.this,userList, R.layout.view_user_entry, new String[] { "userId","userName"}, new int[] {R.id.userId, R.id.userName});
403 ListView myList=(ListView)findViewById(android.R.id.list);
404 myList.setAdapter(adapter);
405 //Display Sync status of SQLite DB
406 Toast.makeText(getApplicationContext(), controller.getSyncStatus(), Toast.LENGTH_LONG).show();
407 }
408 //Initialize Progress Dialog properties
409 prgDialog = new ProgressDialog(this);
410 prgDialog.setMessage("Synching SQLite Data with Remote MySQL DB. Please wait...");
411 prgDialog.setCancelable(false);
412 }
413
414 @Override
415 public boolean onCreateOptionsMenu(Menu menu) {
416 // Inflate the menu; this adds items to the action bar if it is present.
417 getMenuInflater().inflate(R.menu.main, menu);
418 return true;
419 }
420
421 @Override
422 public boolean onOptionsItemSelected(MenuItem item) {
423 // Handle action bar item clicks here. The action bar will
424 // automatically handle clicks on the Home/Up button, so long
425 // as you specify a parent activity in AndroidManifest.xml.
426 int id = item.getItemId();
427 //When Sync action button is clicked
428 if (id == R.id.refresh) {
429 //Sync SQLite DB data to remote MySQL DB
430 syncSQLiteMySQLDB();
431 return true;
432 }
433 return super.onOptionsItemSelected(item);
434 }
435 //Add User method getting called on clicking (+) button
436 public void addUser(View view) {
437 Intent objIntent = new Intent(getApplicationContext(), NewUser.class);
438 startActivity(objIntent);
439 }
440
441 public void syncSQLiteMySQLDB(){
442 //Create AsycHttpClient object
443 AsyncHttpClient client = new AsyncHttpClient();
444 RequestParams params = new RequestParams();
445 ArrayList<HashMap<String, String>> userList = controller.getAllUsers();
446 if(userList.size()!=0){
447 if(controller.dbSyncCount() != 0){
448 prgDialog.show();
449 params.put("usersJSON", controller.composeJSONfromSQLite());
450 client.post("http://192.168.42.68/sqlitemysqlsync/insertuser.php",params ,new AsyncHttpResponseHandler() {
451 @SuppressWarnings("deprecation")
452 public void onSuccess(String response) {
453 System.out.println(response);
454 prgDialog.hide();
455 try {
456 JSONArray arr = new JSONArray(response);
457 System.out.println(arr.length());
458 for(int i=0; i<arr.length();i++){
459 JSONObject obj = (JSONObject)arr.get(i);
460 System.out.println(obj.get("id"));
461 System.out.println(obj.get("status"));
462 controller.updateSyncStatus(obj.get("id").toString(),obj.get("status").toString());
463 }
464 Toast.makeText(getApplicationContext(), "DB Sync completed!", Toast.LENGTH_LONG).show();
465 } catch (JSONException e) {
466 // TODO Auto-generated catch block
467 Toast.makeText(getApplicationContext(), "Error Occured [Server's JSON response might be invalid]!", Toast.LENGTH_LONG).show();
468 e.printStackTrace();
469 }
470 }
471
472 @SuppressWarnings("deprecation")
473 public void onFailure(int statusCode, Throwable error,
474 String content) {
475 // TODO Auto-generated method stub
476 prgDialog.hide();
477 if(statusCode == 404){
478 Toast.makeText(getApplicationContext(), "Requested resource not found", Toast.LENGTH_LONG).show();
479 }else if(statusCode == 500){
480 Toast.makeText(getApplicationContext(), "Something went wrong at server end", Toast.LENGTH_LONG).show();
481 }else{
482 Toast.makeText(getApplicationContext(), "Unexpected Error occcured! [Most common Error: Device might not be connected to Internet]", Toast.LENGTH_LONG).show();
483 }
484 }
485 });
486 }else{
487 Toast.makeText(getApplicationContext(), "SQLite and Remote MySQL DBs are in Sync!", Toast.LENGTH_LONG).show();
488 }
489 }else{
490 Toast.makeText(getApplicationContext(), "No data in SQLite DB, please do enter User name to perform Sync action", Toast.LENGTH_LONG).show();
491 }
492 }
493
494}