How to write, read and display data from MySQL database in ionic app

In this tutorial we are going to look at how to write, read and display data from MySQL database in ionic application, we will create an app to display names and capitals of African countries, we will key in few countries into the database for initial display and post the rest through our post page, to achieve this, we will need to do the following

1. Create an app (writeReadApp)
2. Create the home and post html pages
3. Create a table in our database and populate it with few countries and capitals
4. Create post data and fetch data php files
5. Write methods for post and fetch data
6. Test our app

Are you ready???

Recommendation

I would advise that you go through my earlier post on How to setup login/register in ionic3 using PHP, MySQL to get a grab of database creation, table creation and navigation from one page to another, otherwise, if you are comfortable with the earlier mentioned topics, let’s ride on

1. Create an app (writeReadApp)

Change to command prompt and type the code below to create an app in ionic

ionic start writeReadApp blank

Type Y at the prompt  Would you like to integrate your new app with Cordova to target native iOS and Android? (y/N) y and press enter key

 

 

Done?

Change directory to our newly created app = writeReadApp and run your app

                ionic serve –l

give yourself a thumb-up, if you have what I have below

2.  Create the home and post html pages

Open you code editor and change the ion-title to Home and change the contents between <ion-content> </ion-content> tag to the code below

<ion-header>

  <ion-navbar>

    <ion-title>

      Home

    </ion-title>

  </ion-navbar>

</ion-header>

<ion-content padding>

  <button ion-button  round outline block (click)=”Post()”>Post</button>

  <ion-row>

      <ion-col col-6>

        Country

      </ion-col>

      <ion-col col-6>

        Capital

      </ion-col>

  </ion-row>

</ion-content>

Save and run your app again, are we on the same page? See below to confirm

 

Your table does not have border and the texts are not aligned to center? Don’t worry, all you need doing is to update your home.scss to look like what I have below

    page-home {

    ion-col{

        border: 1px #000000 solid;

        text-align: center;

    }

}

Is your mind now at rest?

Well done!!

 

Now, let’s create post page, from CLI using command ionic g page post as shown below

Now, open app.modules.ts under src/app/  and do the following

1. Import PostPage from its location
2. Add PostPage to declaration and entryComponents section

 

Your app.module.ts will look like this:( see How To Navigate Between Pages In Ionic 2+ Application

For details)

 

Now, open your post.html and insert the following code between <ion-content> </ion-content> tag

<ion-content padding>

  <ion-list no-line>

    <ion-item>

      <ion-input type=”text” placeholder=”Country” name=”country” #country></ion-input>

    </ion-item>

      <ion-item>

      <ion-input type=”email” placeholder=”Capital” name=”capital” #capital></ion-input>

    </ion-item>

     </ion-list>

  <div >

  <button ion-button  round outline block (click)=”Post()”>Post</button>

  </div>

</ion-content>

 

Finally for this section, let’s write a method to navigate to post page from our home page, to achieve this add the following code to home.ts under constructor

post(){

  this.navCtrl.push(PostPage);

  }

 

Don’t forget to import Register page into home.ts by adding the following code to the top of the page

import { PostPage} from ‘../post/post’;

Your post page should look like this

3.   Create a table in our database and populate it with few countries and their capitals

Now, let head to our database and create a table called users with the following column and data type:

• Id – int(11)
• country – text(255)
• capital– text(255)

as shown below

Now, let’s populate our database with the following information

Nigeria	Abuja
Ghana	Accra
Cameroun	Yaounde

 

4.     Create post_data  and fetch_data  php files

In this section, we are going to create three(3) files namely

• dbconnect.php to connect our app to the database
• post_data.php to write to our database
• fetch_php to fetch information from the database

Open your favorite text editor, open a new file and add the following code

<?php

  define(‘HOST’,’localhost’);

  define(‘USER’,’your_user_name’);

  define(‘PASS’,’your_password);

  define(‘DB’,database_name’);

  $con = mysqli_connect(HOST,USER,PASS,DB);

   if (!$con){

                 die(“Error in connection” . mysqli_connect_error()) ;

  }

?>

Save this file as dbconnect.php

 

Open another new file and add the following code

<?php

if (isset($_SERVER[‘HTTP_ORIGIN’])) {

        header(“Access-Control-Allow-Origin: {$_SERVER[‘HTTP_ORIGIN’]}”);

        header(‘Access-Control-Allow-Credentials: true’);

        header(‘Access-Control-Max-Age: 86400’);    // cache for 1 day

    }

    // Access-Control headers are received during OPTIONS requests

    if ($_SERVER[‘REQUEST_METHOD’] == ‘OPTIONS’) 

        if (isset($_SERVER[‘HTTP_ACCESS_CONTROL_REQUEST_METHOD’]))

            header(“Access-Control-Allow-Methods: GET, POST, OPTIONS”);        

        if (isset($_SERVER[‘HTTP_ACCESS_CONTROL_REQUEST_HEADERS’]))

            header(“Access-Control-Allow-Headers:        {$_SERVER[‘HTTP_ACCESS_CONTROL_REQUEST_HEADERS’]}”);

         exit(0);

    }

 

  require “dbconnect.php”;

    $data = file_get_contents(“php://input”);

    if (isset($data)) {

        $request = json_decode($data);

        $country = $request->country;

         $capital = $request->capital;                

                }

$country = stripslashes($country);

$capital = stripslashes($capital);

 

/////// Check to see that the database does not contain repeated information

 $sql = “SELECT id FROM countries WHERE country = ‘$country'”;

      $result = mysqli_query($db,$sql);

      $row = mysqli_fetch_array($result,MYSQLI_ASSOC);

      $active = $row[‘active’];

    $count = mysqli_num_rows($result);

      // If result matched country and $country, table row must be 1 row                   

      if($count >0) {

      $response=  “Country already exists in our database”;

      }else {

  $sql = “SELECT id FROM countries WHERE  capital = ‘$capital'”;

      $result = mysqli_query($db,$sql);

      $row = mysqli_fetch_array($result,MYSQLI_ASSOC);

      $active = $row[‘active’];

      $count = mysqli_num_rows($result);

      // If result matched capital and $capital, table row must be 1 row           

      if($count >0) {

      $response= “capital already exists, there are no two countries with the same capital”;

      }                 

                  else

 {

$sql = “INSERT INTO users (country, capital)

VALUES (‘$country’, ‘$capital)”;

if ($con->query($sql) === TRUE) {

                $response= “post successfull”;

} else {

   $response= “Error: ” . $sql . “<br>” . $db->error;

}

 }} 

                echo json_encode( $response);

$db->close();

?>

 

Save the file as post_data.php.

 

Let’s create fetch_data.php file, open another new file and add the following code

 

<?php
if (isset($_SERVER[‘HTTP_ORIGIN’])) {
header(“Access-Control-Allow-Origin: {$_SERVER[‘HTTP_ORIGIN’]}”);
header(‘Access-Control-Allow-Credentials: true’);
header(‘Access-Control-Max-Age: 86400’); // cache for 1 day
}

// Access-Control headers are received during OPTIONS requests
if ($_SERVER[‘REQUEST_METHOD’] == ‘OPTIONS’) {

if (isset($_SERVER[‘HTTP_ACCESS_CONTROL_REQUEST_METHOD’]))
header(“Access-Control-Allow-Methods: GET, POST, OPTIONS”);

if (isset($_SERVER[‘HTTP_ACCESS_CONTROL_REQUEST_HEADERS’]))
header(“Access-Control-Allow-Headers: {$_SERVER[‘HTTP_ACCESS_CONTROL_REQUEST_HEADERS’]}”);

exit(0);
}

require “dbconnect.php”;

$sql =”SELECT * FROM countries ;”;

$result = mysqli_query($con, $sql);

$response = array();

while($row = mysqli_fetch_array($result))
{
array_push($response, array(“id”=>$row[0],
                                                      “country”=>$row[1],
                                                     “capital”=>$row[2] ));

}

echo json_encode(array(“server_response”=> $response));
mysqli_close($db)

?>

Save the file as fetch_data.php.

 

Finally, upload the files into your online server.

 

5.   Write methods for post and fetch data

Now that we have created the php files to post and fetch data, next thing is to write a method in our application to execute post, fetch and display data information from our database.

Let’s get started, open home.ts file and change the content to the following

import { Component } from ‘@angular/core’;

import { NavController } from ‘ionic-angular’;

import {PostPage} from ‘../post/post’;

import { LoadingController } from ‘ionic-angular’;

import {Http, Headers, RequestOptions}  from “@angular/http”;

import ‘rxjs/add/operator/map’;

@Component({

  selector: ‘page-home’,

  templateUrl: ‘home.html’

})

export class HomePage {

  items:any;

  country:any;

  capital:any;

  constructor(public navCtrl: NavController,  private http: Http, public loading: LoadingController) {

  }

  ngOnInit(){  

   var headers = new Headers();

    headers.append(“Accept”, ‘application/json’);

    headers.append(‘Content-Type’, ‘application/json’ );

    let options = new RequestOptions({ headers: headers });

    let loader = this.loading.create({

        content: ‘Processing please wait…’,

      });

     loader.present().then(() => 

    this.http.post(‘http://ionicdon.com/mobile/fetch_data.php’,options)

    .map(res => res.json())

    .subscribe(res => {

     loader.dismiss()

    this.items=res.server_response;

    });

    });

     }   

  Post(){

    this.navCtrl.push(PostPage);

  }

}

 

And update home.html to the following codes:

<ion-header>

  <ion-navbar>

    <ion-title>

      Home

    </ion-title>

  </ion-navbar>

</ion-header>

<ion-content padding>

  <button ion-button  round outline block (click)=”Post()”>Post</button>

<ion-row class= “header”>

      <ion-col col-6>

        Country

      </ion-col>

      <ion-col col-6>

        Capital

      </ion-col>

  </ion-row>

  <ion-item *ngFor=”let item of items” >

  <ion-row>

    <ion-col col-6>

      {{item.country}}

    </ion-col>

    <ion-col col-6>

  {{item.capital}}

    </ion-col>

</ion-row>

 </ion-item>

</ion-content>

 

Finally let’s update out home.scss file to what we have below

    page-home {

    ion-col{

        border: 1px #000000 solid;

        text-align: center;

    }

    .header {

               border: 1px #B20000 solid;

            background: #cdcdcd;

            font-style: italic;

            font-weight: bold;

            text-align: center;

         }

}

 

 

Just one moment more, update the contents of our post.ts to the following

import { Component, ViewChild } from ‘@angular/core’;

import { IonicPage, NavController, NavParams, AlertController } from ‘ionic-angular’;

import { HomePage } from ‘../home/home’;

import {Http, Headers, RequestOptions}  from “@angular/http”;

import { LoadingController } from ‘ionic-angular’;

import ‘rxjs/add/operator/map’;

/**

 * Generated class for the PostPage page.

 *

 * See https://ionicframework.com/docs/components/#navigation for more info on

 * Ionic pages and navigation.

 */

@IonicPage()

@Component({

  selector: ‘page-post’,

  templateUrl: ‘post.html’,

})

export class PostPage {

  @ViewChild(“country”) country;

@ViewChild(“capital”) capital;

  constructor(public navCtrl: NavController, public navParams: NavParams, public alertCtrl: AlertController,  private http: Http,  public loading: LoadingController) {

  }

  ionViewDidLoad() {

    console.log(‘ionViewDidLoad PostPage’);

  }

  Post(){

    //// check to confirm the country and capital fields are filled

       if(this.country.value==”” ){

      let alert = this.alertCtrl.create({

     title:”ATTENTION”,

    subTitle:”Country field is empty”,

    buttons: [‘OK’]

    });

     alert.present();

     } else

    if(this.capital.value==””){

      let alert = this.alertCtrl.create({

    title:”ATTENTION”,

    subTitle:”Capital field is empty”,

    buttons: [‘OK’]

    }

    alert.present();

   }

    else

    {

   var headers = new Headers();

       headers.append(“Accept”, ‘application/json’);

       headers.append(‘Content-Type’, ‘application/json’ );

       let options = new RequestOptions({ headers: headers });

  

     let data = {

           country: this.country.value,

           capital: this.capital.value  

         };

    let loader = this.loading.create({

       content: ‘Processing please wait…’,

     });

      loader.present().then(() => {

   this.http.post(‘http://ionicdon.com/mobile/post_data.php’,data, options)

   .map(res => res.json())

   .subscribe(res => {

  

    loader.dismiss()

   if(res==”Post successfull”){

     let alert = this.alertCtrl.create({

       title:”CONGRATS”,

       subTitle:(res),

       buttons: [‘OK’]

       });

       alert.present();

    this.navCtrl.push(HomePage);

     }else

   {

    let alert = this.alertCtrl.create({

    title:”ERROR”,

    subTitle:(res),

    buttons: [‘OK’]

    });

      alert.present();

     }

   });

   });

    }

     }

  }

 

6.   Test our app

If you have been able to follow thgrough, I salute you for your tenacity, well done, now let us test our application. Go to command prompt, change directory to your app folder and type

ionic serve –l

 

Do you have what I have below?

Thumbup to you, now click post button and enter the following information

Country	Capital
Algeria	Algiers
Angola	Luanda

 

Congratulations for completing this informative tutorial, I hope you got the result I have below

Learn more

How to edit and delete displayed data from MySQL database in ionic app