CRUD Android dengan database Mysql

Aplikasi yang menggunakan database pada umumnya memiliki fungsi sebagai berikut :

  • Menampilkan data
  • Menambah data
  • Menghapus data
  • Merubah data

Database SQLite pada android mempunyai keterbatasan pada salah satunya pada kapasitas penyimpanan data. Akan bermasalah apabila kita memaksakan menyimpan data yang besar (lebih dari 10000 data) pada SQLite. Selain itu jika kita ingin perubahan data aplikasi dirasakan oleh semua user tanpa harus install ulang aplikasi, maka data harus disimpan di Server. Cara yang paling sering digunakan adalah dengan perantara Webserver, PHP dan MySQL. Dimana android untuk melakukan perubahan data atau mendapatkan data dari database server cukup dengan memanggil url php yang telah ditentukan.

Agar lebih dimengerti mari kita buat aplikasi tentang crud sederhana yang database servernya menggunakan MySQL dengan Web Server Apache dan Server Side Scriptnya menggunakan PHP untuk itu hal yang penting untuk dilakukan sebelum memulai pembuatan aplikasinya adalah menyiaplkan environment servernya dengan menginstall XAMPP (Windows) atau LAMP (Linux). Setelah selesai Instalasi lalu kita lakukan langkah-langkah yang ada pada document dibawah ini.

ok

# Pertama  Buat database dengan  nama “detailproducts” dengan nama tabel Products, dan field seperti dibawah ini :

–>  pid                                                : integer (auto increment)

–>  name                                            : varchar (50)

–>  price                                             : numeric (length 10 decimal 2)

–>  description                                  : text

–>  created_at timestamp               : <Insert-TimeStamp>

# Langkah kedua kita akan membuat middle ware berupa file berekstensi .php

Langkah satu buat file dan beri nama db_config.php

<?php

define(‘DB_USER’, “root”); // user database

define(‘DB_PASSWORD’, “”); // password database

define(‘DB_DATABASE’, “deatailproducts”); // nama database

define(‘DB_SERVER’, “localhost”); // server

?>

Langkah dua buat file dengan nama db_connect.php

<?php

/**
* A class file to connect to database
*/
class DB_CONNECT {

// constructor
function __construct() {
// connecting to database
$this->connect();
}

// destructor
function __destruct() {
// closing db connection
$this->close();
}

/**
* Function to connect with database
*/
function connect() {
// import database connection variables
require_once __DIR__ . ‘/db_config.php’;

// Connecting to mysql database
$con = mysql_connect(DB_SERVER, DB_USER, DB_PASSWORD) or die(mysql_error());

// Selecing database
$db = mysql_select_db(DB_DATABASE) or die(mysql_error()) or die(mysql_error());

// returing connection cursor
return $con;
}

/**
* Function to close db connection
*/
function close() {
// closing db connection
mysql_close();
}

}

?>

Langkah ketiga buat file dan beri nama delete_product.php

<?php

/*
* Following code will delete a product from table
* A product is identified by product id (pid)
*/

// array for JSON response
$response = array();

// check for required fields
if (isset($_POST[‘pid’])) {
$pid = $_POST[‘pid’];

// include db connect class
require_once __DIR__ . ‘/db_connect.php’;

// connecting to db
$db = new DB_CONNECT();

// mysql update row with matched pid
$result = mysql_query(“DELETE FROM products WHERE pid = $pid”);

// check if row deleted or not
if (mysql_affected_rows() > 0) {
// successfully updated
$response[“success”] = 1;
$response[“message”] = “Product successfully deleted”;

// echoing JSON response
echo json_encode($response);
} else {
// no product found
$response[“success”] = 0;
$response[“message”] = “No product found”;

// echo no users JSON
echo json_encode($response);
}
} else {
// required field is missing
$response[“success”] = 0;
$response[“message”] = “Required field(s) is missing”;

// echoing JSON response
echo json_encode($response);
}
?>

Langkah ke-empat buat file dengan nama create_product.php

<?php

/*
* Following code will create a new product row
* All product details are read from HTTP Post Request
*/

// array for JSON response
$response = array();

// check for required fields
if (isset($_POST[‘name’]) && isset($_POST[‘price’]) && isset($_POST[‘description’])) {

$name = $_POST[‘name’];
$price = $_POST[‘price’];
$description = $_POST[‘description’];

// include db connect class
require_once __DIR__ . ‘/db_connect.php’;

// connecting to db
$db = new DB_CONNECT();

// mysql inserting a new row
$result = mysql_query(“INSERT INTO products(name, price, description) VALUES(‘$name’, ‘$price’, ‘$description’)”);

// check if row inserted or not
if ($result) {
// successfully inserted into database
$response[“success”] = 1;
$response[“message”] = “Product successfully created.”;

// echoing JSON response
echo json_encode($response);
} else {
// failed to insert row
$response[“success”] = 0;
$response[“message”] = “Oops! An error occurred.”;

// echoing JSON response
echo json_encode($response);
}
} else {
// required field is missing
$response[“success”] = 0;
$response[“message”] = “Required field(s) is missing”;

// echoing JSON response
echo json_encode($response);
}
?>

Langkah ke-Lima kita buat file dengan nama get_all_products.php

<?php

/*
* Following code will list all the products
*/

// array for JSON response
$response = array();

// include db connect class
require_once __DIR__ . ‘/db_connect.php’;

// connecting to db
$db = new DB_CONNECT();

// get all products from products table
$result = mysql_query(“SELECT *FROM products”) or die(mysql_error());

// check for empty result
if (mysql_num_rows($result) > 0) {
// looping through all results
// products node
$response[“products”] = array();

while ($row = mysql_fetch_array($result)) {
// temp user array
$product = array();
$product[“pid”] = $row[“pid”];
$product[“name”] = $row[“name”];
$product[“price”] = $row[“price”];
$product[“description”] = $row[“description”];
$product[“created_at”] = $row[“created_at”];
$product[“updated_at”] = $row[“updated_at”];

// push single product into final response array
array_push($response[“products”], $product);
}
// success
$response[“success”] = 1;

// echoing JSON response
echo json_encode($response);
} else {
// no products found
$response[“success”] = 0;
$response[“message”] = “No products found”;

// echo no users JSON
echo json_encode($response);
}
?>

Langkah ke-Enam kita buat file dengan nama get_products_detail.php

<?php

/*
* Following code will get single product details
* A product is identified by product id (pid)
*/

// array for JSON response
$response = array();

// include db connect class
require_once __DIR__ . ‘/db_connect.php’;

// connecting to db
$db = new DB_CONNECT();

// check for post data
if (isset($_GET[“pid”])) {
$pid = $_GET[‘pid’];

// get a product from products table
$result = mysql_query(“SELECT *FROM products WHERE pid = $pid”);

if (!empty($result)) {
// check for empty result
if (mysql_num_rows($result) > 0) {

$result = mysql_fetch_array($result);

$product = array();
$product[“pid”] = $result[“pid”];
$product[“name”] = $result[“name”];
$product[“price”] = $result[“price”];
$product[“description”] = $result[“description&