[Android] JSon trong Android – Part 2: Export from Database by Php JSON

April 28, 2017 nguyenvanquan7826 TUT Json Android 30 responses

Hello everyone, in section 2 This alone will guide you to create Database, write commands to query data from a database and produce JSON.

Articles related to a number of MySQL and php knowledge should you pay attention, if you've never heard of it, it's okay, very simple severance.

I will guide you to build a database on Hostinger for easy and sync.

Create Database

You go to the database -> MySQL Database to create database:

database in Hostinger

Enter a name databse, user name and password is finished.

tao database

After creating, click on the database you just created and choose to interface phpMyAdmin to phpMyAdmin.
Now we begin to create the database tables. Our application is Old Lover (Ex-lover), This will need to create 2 £: A user table stored user list, and a table listing old_lover save user's exes.

Table users include 2 the school is:

nick -> kiểu: varchar(50)	định dạng: utf8_unicode_ci	là khóa chính
pass -> kiểu: varchar(50)	định dạng: utf8_unicode_ci

Table old_lover include 6 School:

id	-> kiểu:int(11)		AUTO_INCREMENT là khóa chính
nick	-> kiểu: varchar(50)	định dạng: utf8_unicode_ci		
name -> kiểu: varchar(50)	định dạng: utf8_unicode_ci
begin_date -> kiểu: varchar(10)
end_date -> kiểu: varchar(10)
phone -> kiểu: varchar(15)

In the days that BEGIN_DATE and end_date in love and day parting =)). we do not need to compare anything but just to show you should use varchar rather than date or datetime nhé.

After creating the database, you go to insert and insert some records to the table. His example like this:
Table user:
table user

Bảng old_lover:
table old_lover

Using php database connection and JSON output

In this section we begin to touch programming language Php, it is similar to java, only thing slightly different command structures slight.

You and the File -> Manage file
file

File management interface display would look like on your computer, you can always write a php file here or create a new folder to contain them well, I will create a directory mobile here, create more folders old-lover trong mobile

old-lover folder

We will go step by step: Configuration -> Connections -> Write query commands -> Export JSON

Write configuration file

Noted: From this point onwards you take the database and host on your blog, you use the database Hostinger, user,… will form u520716618_lover,… as above we've set
We will write 1 file to configure the database name, user, pass for the connecting service. In old-lover folder you created 1 file named config.php with the following content:

<?php
	define("DB_HOST", "localhost");
	define("DB_USER", "mobile-demo-json");
	define("DB_PASSWORD", "mobile-demo-json");
	define("DB_DATABASE", "mobile-demo-json");
?>

In it you need to revise to suit the user name of the database, database name, password that you've put in steps to create a database. Here the current host than where php files are placed localhost, your username and password are mobile-demo-json

Write file database connection

Next we create 1 file named db_connect.php to perform database connection with content:

<?php
     
    class DB_Connect {
     
        // constructor
        function __construct() {
     
        }
     
        // destructor
        function __destruct() {
            // $this->close();
        }
     
        // Connecting to database
        public function connect() {
            require_once 'config.php';
            $con = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);
            mysql_set_charset('utf8', $con);
            mysql_select_db(DB_DATABASE);
            return $con;
        }
     
        public function close() {
            mysql_close();
        }
     
    } 
?>

Write file containing the database manipulation functions

This file includes all functions to manipulate the database as select, insert, update,…
The file you created db_functions.php with content:

<?php

    class DB_Functions {

        private $db;

        // constructor
        function __construct() {
            include_once './db_connect.php';
            $this->db = new DB_Connect();
            $this->db->connect();
        }
        
        public function selectall($sql){
            $result=mysql_query($sql);
            while($row=@mysql_fetch_assoc($result)){
                $result_all[]=$row;
            }
            mysql_free_result($result);
            return @$result_all;
        }
        
        public function selectone($sql){
            $result=mysql_query($sql);
            $resultone=@mysql_fetch_assoc($result);
            mysql_free_result($result);
            return $resultone;
        }
        
        function numrow($sql){
            $result=mysql_query($sql);
            $row=mysql_num_rows($result);
            mysql_free_result($result);
            return $row;
        }

        function update($table,$data,$where){
            $sql='';
            foreach($data as $key => $value){
                $sql .=", $key = '".mysql_real_escape_string($value)."'";
            }

            $sql='UPDATE '.$table. ' SET '.trim($sql, ',').' WHERE ' .$where;
            return mysql_query($sql);
        }

        function insert($table, $data){
            $field_list='';
            $value_list='';
            foreach($data as $key => $value){
                $field_list .=",$key";
                $value_list .=",'".mysql_real_escape_string($value)."'";
            }

            $sql='INSERT INTO '.$table.'('.trim($field_list, ',').') VALUES ('.trim($value_list, ',').')';
            return mysql_query($sql);
        }

        function delete($table,$where){
            $sql="DELETE FROM $table WHERE $where";
            return mysql_query($sql);
        }
    }

?>

These functions make yourself without much explanation, I saw some remarkable things as follows:

  • Jaw __construct() the constructor, and it is to connect to the database, so we should always include (include) file db_connect.php in this.

  • The statement took his record use mysql_fetch_assoc because it allows us to retrieve the records in table column names, if you use mysql_fetch_array it will take both column name and number of the columns in the table and then our JSON output unnecessary surplus, as it becomes heavy.

  • I have used mysql_real_escape_string to match the value of its key accompanying the update function, insert. This allows us to retain the value and avoid syntax errors sql queries even in it have some special characters.

Writing JSON output

This is our final file, file that will interact directly with android.
We will have 4 operate as follows:
– Log in
– Registered
– Get a list of exes
– Add to the list former lover

So we'll have 4 turn to navigate these actions when receiving a request from android.

<?php header('Content-Type: application/json; charset=utf-8'); ?>

<?php
    
    $METHOD_LOGIN = 1;
    $METHOD_REGISTER = 2;
    $METHOD_GET_OLD_LOVER = 3;
    $METHOD_ADD_OLD_LOVER = 4;

    include_once 'db_functions.php';

    $db = new DB_Functions();

    $nick = $_POST['nick'];    
    $method = $_POST['method'];

    // for test from web browser
    $test = $_GET['test'];
    if($test==true){
        $nick = $_GET['nick'];
        $method = $METHOD_GET_OLD_LOVER;
    }
    
    if($method == $METHOD_LOGIN) {
        $sql = "SELECT * FROM user WHERE nick = '$nick'";
        $rs = $db->selectone($sql);

        if($rs['pass'] == $_POST['pass']){
            $result['login'] = true;
        }else{
            $result['login'] = false;
        }
    }

    if($method == $METHOD_REGISTER) {
        $data = array();
        $data['nick'] = $nick;
        $data['pass'] = $_POST['pass'];
        
        $rs = $db->insert('user', $data);
        if($rs){
            $result['register'] = true;
        }else{
            $result['register'] = false;
        }
    }

    if($method == $METHOD_GET_OLD_LOVER) {
        $sql = "SELECT * FROM old_lover WHERE nick = '$nick'";
        $result = $db->selectall($sql);
    }

    if($method == $METHOD_ADD_OLD_LOVER){
        $data = array();
        $data['nick'] = $nick;
        $data['name'] = $_POST['name'];
        $data['phone'] = $_POST['phone'];
        $data['begin_date'] = $_POST['begin_date'];
        $data['end_date'] = $_POST['end_date'];

        $rs = $db->insert('old_lover', $data);
        if($rs){
            $result['add'] = true;
        }else{
            $result['add'] = false;
        }
    }
      
    $json = json_encode($result, JSON_PRETTY_PRINT); 
    // $json = json_encode($result); // use on hostinger
    print_r($json);
?>

The code in this file is also completely understandable, I just note that I have used the variable $ test is taken by secondary GET Affairs, ie get on the path to test data browser. You can test by accessing the following link to test a list of his former lover:
https://www.cachhoc.net/mobile/demo-json/lover.php?test=true&nick = related

That's it then. In the next article we begin to turn the Android operation.

Posts made in the tutorial JSon trong Android by nguyenvanquan7826