67, Obafemi Awolowo way, Ikeja Lagos +234 802 302 0838 info@ionicdon.com

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

Create an app (writeReadApp)

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

ionicdon read write and fetch data

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)

ionicdon read write and fetch data for MySQL in ionic




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?

ionicdon read write and fetch data for MySQL in ionic

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

ionicdon read write and fetch data for MySQL in ionic




Learn more

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

This Post Has 23 Comments

  1. Olajumoke Ariyo

    8 Jul 2018 Reply

    I would like you to show us how we can return data from database in
    $sql = “SELECT * FROM user WHERE username = ‘$username’ and password = ‘$epassword'”;

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

    $row = mysqli_fetch_array($result,MYSQLI_ASSOC);

    $active = $row[‘active’];

    $count = mysqli_num_rows($result);

    // If result matched myusername and mypassword, table row must be 1 row

    if($count >0) {

    $response = “Your Login success”;
    //return [‘operation’ => ‘Your Login success’, ‘data’ => $row];

    }else {

    $response= “Your Login Email or Password is invalid”;

    }

    echo json_encode( $response);
    on this tutorial adminion on How to setup login/register in ionic3 using PHP, MySQL

    and display the data in

    if(res.operation == “Your Login success”){

    //this.authService.setToken(data.token, data.id, data.name, data.email);

    let alert = this.alertCtrl.create({

    title:”CONGRATS”,

    subTitle:(res),

    buttons: [‘OK’]

    });

    alert.present();
    }
    What to really learn how to do that. I am new to ionic and I will appreciate if you can help with it.

    1. adminion

      8 Jul 2018 Reply

      Hi Ariyo

      Read through the tutorial first, it surely answer your question, but if you still cannot find your bearing, you can revert

      Thanks

      1. Olajumoke Ariyo

        8 Jul 2018 Reply

        I have tried it but still got lost along the way. below is what I tried in login.php

        $sql = “SELECT * FROM user WHERE username = ‘$username’ and password = ‘$epassword'”;

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

        //$row = mysqli_fetch_array($result,MYSQLI_ASSOC);

        $active = $row[‘active’];

        $count = mysqli_num_rows($result);

        // If result matched myusername and mypassword, table row must be 1 row
        $response = array();
        if($count >0) {

        while($row = mysqli_fetch_array($result))
        {
        array_push($response, array(
        “id”=>$row[0],
        “name”=>$row[1],
        “gender”=>$row[2],
        “phone”=>$row[3],
        “email”=>$row[4],
        “sch_name”=>$row[5],
        “username”=>$row[6]

        ));

        }

        //$response = “Your Login success”;
        //return [‘operation’ => ‘Your Login success’, ‘data’ => $row];

        }else {

        $response= “Your Login Email or Password is invalid”;

        }

        //echo json_encode( $response);

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

        How do I modify login.ts to call the json?

        if(res.operation == “Your Login success”){

        //this.authService.setToken(data.token, data.id, data.name, data.email);

        let alert = this.alertCtrl.create({

        title:”CONGRATS”,

        subTitle:(res),

        buttons: [‘OK’]

        });

        alert.present();
        }

      1. Nedlogs

        21 Jul 2018 Reply

        thanks is it possible to put search filter?

        1. adminion

          24 Jul 2018 Reply

          Yes, it is possible

  3. Leroro

    23 Jul 2018 Reply

    Please do tutorial on edit and delete

    1. adminion

      24 Jul 2018 Reply

      I will do that very soon, be on the look out, also help other readers by sharing our post

    2. adminion

      10 Dec 2018 Reply

      Done, have you seen it ?

      thanks for the suggestion

  4. victor manuel

    28 Jul 2018 Reply

    solution my error!!!!

    core.js:1449 ERROR SyntaxError: Unexpected token < in JSON at position 0
    at JSON.parse ()
    at Response.Body.json (http.js:1091)
    at MapSubscriber.project (home.ts:29)
    at MapSubscriber._next (map.js:79)
    at MapSubscriber.Subscriber.next (Subscriber.js:93)
    at XMLHttpRequest.onLoad (http.js:1591)
    at t.invokeTask (polyfills.js:3)
    at Object.onInvokeTask (core.js:4751)
    at t.invokeTask (polyfills.js:3)
    at r.runTask (polyfills.js:3)

    1. Tolu Faleye

      1 Aug 2018 Reply

      @Victor, go through the tutorial step by step, you should not have any problem, initially I did copy and paste but later found out that apostrophe does not appear same when you copy it to code editor
      best of luck

  6. Maykel Mattar

    23 Aug 2018 Reply

    When i apply on localhost and on the ionicdon website it works but on a website hosted by 00webhost it doesn’t work it just keep me waiting .Any helps.

    1. adminion

      23 Aug 2018 Reply

      check your database connection credentials with your host company, you might need to setup user and privileges

      1. estebandyro

        6 Dec 2018 Reply

        Thank you very much for the tutorial, but I have the same problem. I can only connect to my localhost and your link. I can not connect to an external database of the web “bythehost”. Where did you host your website with the database? What do you recommend me? Thanks for your help.

        1. adminion

          10 Dec 2018 Reply

          ionicdon is hosted on hostdepartment.com server, but I have a lot of clients hosting on different servers around the world and they are doing fine, it does not really matter where you host, in as much as their service meets your need.

  7. zrn

    10 Nov 2018 Reply

    What do you mean “upload the files into your online server”? How to do that?

  8. zrn

    11 Nov 2018 Reply

    I’ve tried this code & successfully register and log in, I also can display the data on the profile page but the data is not in the database. Do you know why?

    1. adminion

      10 Dec 2018 Reply

      Where else could the data be when it is not in the database, I sincerely don’t know what you are talking about, may be you are not looking at the right database, see where your app is pointing to for data connection

  9. erd

    12 Dec 2018 Reply

    can you make a tutorial for posting image and fetch data in ionic

    1. adminion

      29 Dec 2018 Reply

      Erd, watch out for next tutorial, it is about camera app which includes using camera in your ionic app to snap picture , upload the picture to an online server and displaying image from server in an ionic app.

      thank you for being there

Leave a Reply

Close Menu