Introduction

Application Programming Interface (API)

An API is good for communication between an app and a server. When we send a user request to the server using an Android server the response from your request is fast in JSON or XML format. REST is very simple compared to other methods like SOAP, CORBA and WSDL. The RESTful API supports the most commonly used HTTP methods (GET, POST, PUT and DELETE).

How to Communicate Between a Device and MySQL Server using API

MySql Server

Start the Application

Step 1: Create a table in a MySQL database.

  1. CREATE TABLE products_api(
  2. pid int(11) primary key auto_increment,
  3. name varchar(100) not null,
  4. price decimal(10,2) not null,
  5. description text,
  6. created_at timestamp default now(),
  7. updated_at timestamp
  8. );

Step 2: Create two files called database_connect.php.

  1. define('DB_USER', "root"); // db user
  2. define('DB_PASSWORD', ""); // db password
  3. define('DB_DATABASE', "myconnectapi"); // database name
  4. define('DB_SERVER', "localhost"); // db server/ host name
  5. <?php
  6. /**
  7. * A class file to connect to database
  8. */
  9. class DATABASE_CONNECT {
  10. // constructor
  11. function __construct() {
  12. // connecting to database
  13. $this->connect();
  14. }
  15. // destructor
  16. function __destruct() {
  17. // closing db connection
  18. $this->close();
  19. }
  20. function connect() {
  21. $con = mysql_connect(DB_SERVER, DB_USER, DB_PASSWORD) or die(mysql_error());
  22. $db = mysql_select_db(DB_DATABASE) or die(mysql_error()) or die(mysql_error());
  23. return $con;
  24. }
  25. /**
  26. * Function to close db connection
  27. */
  28. function close() {
  29. // closing db connection
  30. mysql_close();
  31. }
  32. }
  33. ?>
  34. $MY_DB = new DB_CONNECT();

Insert product list API

Create an insert_product.php file. When the insert_proudct.php file is called and inserts a product the response is in JSON format.

insert_product.php

  1. <?php
  2. $response = array();
  3. if (isset($_POST['name']) && isset($_POST['price']) && isset($_POST['description'])) {
  4. $name = $_POST['name'];
  5. $price = $_POST['price'];
  6. $description = $_POST['description'];
  7. // include db connect class
  8. require_once __DIR__ . '/ database_connect.php';
  9. // connecting to db
  10. $db = new DB_CONNECT();
  11. $result = mysql_query("INSERT INTO products_api(name, price, description) VALUES('$name', '$price', '$description')");
  12. // check if row inserted or not
  13. if ($result) {
  14. $response["success_msg"] = 1;
  15. $response["message"] = "Product successfully Insert.";
  16. echo json_encode($response);
  17. } else {
  18. // failed to insert row
  19. $response["success_msg "] = 0;
  20. $response["message"] = "Product not insert because Oops! An error occurred.";
  21. // echoing JSON response
  22. echo json_encode($response);
  23. }
  24. }
  25. ?>

The following output will be displayed in your browser.

  1. {
  2. " success_msg ": 1,
  3. "message": "Product successfully insert."
  4. }
  5. {
  6. " success_msg ": 0,
  7. "message": " Product not insert because Oops! An error occurred."
  8. }

Reading product details

product_details.php

  1. <?php
  3. /*
  5. * Following code will get single product details
  7. * A product is identified by product id (pid)
  9. */
  11. // array for JSON response
  13. $response = array();
  15. // include db connect class
  17. require_once __DIR__ . '/database_connect.php';
  19. // connecting to db
  21. $db = new DB_CONNECT();
  23. // check for post data
  25. if (isset($_GET["pid"])) {
  27. $pid = $_GET['pid'];
  29. // get a product from products table
  31. $result = mysql_query("SELECT *FROM products_api WHERE pid = $pid");
  33. if (!emptyempty($result)) {
  35. // check for empty result
  37. if (mysql_num_rows($result) > 0) {
  39. $result = mysql_fetch_array($result);
  41. $product = array();
  43. $product["pid"] = $result["pid"];
  45. $product["name"] = $result["name"];
  47. $product["price"] = $result["price"];
  49. $product["description"] = $result["description"];
  51. $product["created_at"] = $result["created_at"];
  53. $product["updated_at"] = $result["updated_at"];
  55. // success
  57. $response["success"] = 1;
  59. // user node
  61. $response["product"] = array();
  63. array_push($response["product"], $product);
  65. // echoing JSON response
  67. echo json_encode($response);
  69. } else {
  71. // no product found
  73. $response["success"] = 0;
  75. $response["message"] = "No product found";
  77. // echo no users JSON
  79. echo json_encode($response);
  81. }
  83. } else {
  85. // no product found
  87. $response["success"] = 0;
  89. $response["message"] = "No product found";
  91. // echo no users JSON
  93. echo json_encode($response);
  95. }
  97. }
  99. ?>
  101. {
  103. "success": 1,
  105. "product": [
  107. {
  109. "pid": "1",
  111. "name": "demo product",
  113. "price": "300.00",
  115. "description": "demo description",
  117. "created_at": "2012-05-29 01:41:42",

  119. "updated_at": "0000-03-00 06:02:00"
  121. }
  123. ]
  125. }

Deleting a Row in MySQL (Deleting a product)

delete_product.php

  1. <?php
  3. /*
  5. * Following code will delete a product from table
  7. * A product is identified by product id (pid)
  9. */
  11. // array for JSON response
  13. $response = array();
  15. // check for required fields
  17. if (isset($_POST['pid'])) {
  19. $pid = $_POST['pid'];
  21. // include db connect class
  23. require_once __DIR__ . '/db_connect.php';
  25. // connecting to db
  27. $db = new DB_CONNECT();
  29. // mysql update row with matched pid
  31. $result = mysql_query("DELETE FROM products WHERE pid = $pid");
  33. // check if row deleted or not
  35. if (mysql_affected_rows() > 0) {
  37. // successfully updated
  39. $response["success"] = 1;
  41. $response["message"] = "Product successfully deleted";
  43. // echoing JSON response
  45. echo json_encode($response);
  47. } else {
  49. // no product found
  51. $response["success"] = 0;
  53. $response["message"] = "No product found";
  55. // echo no users JSON
  57. echo json_encode($response);
  59. }
  61. }
  63. ?>

The output will be:

  1. {
  3. "success": 1,
  5. "message": "Product successfully deleted"
  7. }