1. What is WAMP Server
WAMP is acronym for Windows, Apache, MySQL and PHP, Perl, Python. WAMP software is one click installer which creates an environment for developing PHP, MySQL web application. By installing this software you will be installing Apache, MySQL and PHP. Alternatively you can use XAMP Server also.
2. Installing and Running WAMP Server
Download & Install WAMP server from www.wampserver.com/en/. Once you have installed wamp server, launch the program from Start -> All Programs -> WampServer -> StartWampServer.
You can test your server by opening the address http://localhost/ in your browser.
Also you can check phpmyadmin by opening http://localhost/phpmyadmin
Following is a screen cast of Downloading and Installing WAMP Server.
3. Creating and Running PHP Project
Now you have the environment ready to develop a PHP & MySQL project. Go to the location where you installed WAMP server (In my case i installed in C:\wamp\) and go to www folder and create a new folder for your project. You have to place all your project files inside this folder.
Create a folder called android_connect and create a new php file called test.php and try out simple php code. After placing following code try to openhttp://localhost/android_connect/test.php and you should see a message called “Welcome, I am connecting Android to PHP, MySQL“.
test.php
<?php echo "Welcome, I am connecting Android to PHP, MySQL" ; ?> |
Following is a screen cast of Creating and Running a simple PHP project.
4. Creating MySQL Database and Tables
In this tutorial i am creating a simple database with one table. Through out this tutorial i am using same table to perform example operations. Now open phpmyadmin by opening the address http://localhost/phpmyadmin/ in your browser. You can use the PhpMyAdmin tool to create a database and a table.
I am creating a database named androidhive and a table called products.
CREATE DATABASE androidhive; |
CREATE TABLE products( pid int ( 11 ) primary key auto_increment, name varchar( 100 ) not null , price decimal( 10 , 2 ) not null , description text, created_at timestamp default now(), updated_at timestamp ); |
Following is a screen cast of Creating database and tables in phpmyadmin
5. Connecting to MySQL database using PHP
Now the actual server side coding starts. Create a PHP class to connect to MySQL database. The main purpose of this class is to open a connection to database and close the connection whenever its not needed. So create two files called db_config.php and db_connect.php
db_config.php – will have database connection variables
db_connect.php – a class file to connect to database
Following is code for two php files
db_config.php
<?php /* * All database connection variables */ define( 'DB_USER' , "root" ); // db user define( 'DB_PASSWORD' , "" ); // db password (mention your db password here) define( 'DB_DATABASE' , "androidhive" ); // database name define( 'DB_SERVER' , "localhost" ); // db server ?> |
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(); } } ?> |
Usage: When ever you want to connect to MySQL database and do some operations use the db_connect.php class like this
$db = new DB_CONNECT(); // creating class object(will open database connection) |
6. Basic MySQL CRUD Operations using PHP
In this tutorial i am covering basic CRUD (Create, Read, Update, Delete) operations on MySQL database using PHP.
If you are a novice about PHP and MySQL i suggest, you to learn basic PHP and SQL here.
6.a) Creating a row in MySQL (Creating a new product row)
In your PHP project create a new php file called create_product.php and place the following code. This file is mainly for creating a new product in products table.
In the following code i am reading product data via POST and storing them in products table. At the end i am echoing appropriate JSON as response.
<?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 ); } ?> |
For the above code JSON response will be like
When POST param(s) is missing
{ "success" : 0 , "message" : "Required field(s) is missing" } |
When product is successfully created
{ "success" : 1 , "message" : "Product successfully created." } |
When error occurred while inserting data
{ "success" : 0 , "message" : "Oops! An error occurred." } |
6.b) Reading a Row from MySQL (Reading product details)
Create a new php file called get_product_details.php and write the following code. This file will get single product details by taking product id (pid) as post parameter.
<?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" ]; $product [ "created_at" ] = $result [ "created_at" ]; $product [ "updated_at" ] = $result [ "updated_at" ]; // success $response [ "success" ] = 1; // user node $response [ "product" ] = array (); array_push ( $response [ "product" ], $product ); // 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 { // 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 ); } ?> |
The json response for the above file will be
When successfully getting product details
{ "success" : 1 , "product" : [ { "pid" : "1" , "name" : "iPHone 4S" , "price" : "300.00" , "description" : "iPhone 4S white" , "created_at" : "2012-04-29 01:41:42" , "updated_at" : "0000-00-00 00:00:00" } ] } |
When no product found with matched pid
{ "success" : 0 , "message" : "No product found" } |
6.c) Reading All Rows from MySQL (Reading all products)
We need a json to list all the products on android device. So create a new php file namedget_all_products.php and write following code.
<?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 [ "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 ); } ?> |
And the JSON response for above code
Listing all Products
{ "products" : [ { "pid" : "1" , "name" : "iPhone 4S" , "price" : "300.00" , "created_at" : "2012-04-29 02:04:02" , "updated_at" : "0000-00-00 00:00:00" }, { "pid" : "2" , "name" : "Macbook Pro" , "price" : "600.00" , "created_at" : "2012-04-29 02:04:51" , "updated_at" : "0000-00-00 00:00:00" }, { "pid" : "3" , "name" : "Macbook Air" , "price" : "800.00" , "created_at" : "2012-04-29 02:05:57" , "updated_at" : "0000-00-00 00:00:00" }, { "pid" : "4" , "name" : "OS X Lion" , "price" : "100.00" , "created_at" : "2012-04-29 02:07:14" , "updated_at" : "0000-00-00 00:00:00" } ], "success" : 1 } |
When products not found
{ "success" : 0 , "message" : "No products found" } |
6.d) Updating a Row in MySQL (Updating product details)
Create a php file named update_product.php to update product details. Each product is identified by pid.
<?php /* * Following code will update a product information * A product is identified by product id (pid) */ // array for JSON response $response = array (); // check for required fields if (isset( $_POST [ 'pid' ]) && isset( $_POST [ 'name' ]) && isset( $_POST [ 'price' ]) && isset( $_POST [ 'description' ])) { $pid = $_POST [ 'pid' ]; $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 update row with matched pid $result = mysql_query( "UPDATE products SET name = '$name', price = '$price', description = '$description' WHERE pid = $pid" ); // check if row inserted or not if ( $result ) { // successfully updated $response [ "success" ] = 1; $response [ "message" ] = "Product successfully updated." ; // echoing JSON response echo json_encode( $response ); } else { } } else { // required field is missing $response [ "success" ] = 0; $response [ "message" ] = "Required field(s) is missing" ; // echoing JSON response echo json_encode( $response ); } ?> |
The json reponse of above code, when product is updated successfully
{ "success" : 1 , "message" : "Product successfully updated." } |
6.e) Deleting a Row in MySQL (Deleting a product)
The last operation is deletion on database. Create a new php file called delete_product.php and paste the following code. The main functionality of this file is to delete a product from database.
<?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 ); } ?> |
When product successfully deleted
{ "success" : 1 , "message" : "Product successfully deleted" } |
When product not found
{ "success" : 0 , "message" : "No product found" } |
Until now, we built a simple api for our products table. We are now done with the server side coding (PHP) and its time to take a break and start our actual android application coding.
'공부 > Android' 카테고리의 다른 글
어플리케이션 zipalign (apk zipalign) (0) | 2013.02.24 |
---|---|
안드로이드 php, mysql 연동하기 _ 2 (3) | 2013.01.20 |
안드로이드 웹뷰를 통해 외부어플로 공유하기 (카카오톡 공유, 내보내기) (1) | 2012.11.17 |
안드로이드 타이틀 없이 화면 채우기(화면 꽉 채우기) (0) | 2012.10.28 |
Gridview 썸네일 이미지 클릭시 확대하기 (0) | 2012.08.23 |