Powering Your Angular-15 Ionic-7 App: Harnessing the Magic of MySQL and PHP for Data Reading and Display (Part 1)

Welcome to Part 1 of our thrilling series, "Powering Your Angular-15 Ionic-7 App: Harnessing the Magic of MySQL and PHP for Data Reading and Display"! In this installment, we'll dive into the fascinating world of data management and uncover the secrets of integrating MySQL and PHP into our Angular-15 Ionic-7 app.

Data is at the heart of every powerful application, and with Angular-15 and Ionic-7, we have a robust foundation for building dynamic and engaging user experiences. However, to truly unlock the potential of our app, we need a reliable and efficient way to read and display data.


That's where MySQL and PHP come in. MySQL, a widely-used open-source relational database management system, provides a secure and scalable solution for storing and organizing our data. PHP, a popular server-side scripting language, enables us to interact with the MySQL database and retrieve the information we need.


In Part 1 of this series, we'll guide you through the step-by-step process of integrating MySQL and PHP into our Angular-15 Ionic-7 app. You'll learn how to set up the MySQL database, establish a connection with PHP, and create powerful queries to retrieve data.

But that's not all! We'll also explore techniques for efficiently displaying the retrieved data in our app, ensuring a seamless and immersive user experience. From handling asynchronous requests to dynamically rendering content, we'll cover all the essential aspects of data reading and display.

By the end of this tutorial, you'll have the knowledge and skills to harness the magic of MySQL and PHP, empowering your Angular-15 Ionic-7 app with the ability to read and display data with ease.

So, are you ready to dive into the world of data management and unleash the full potential of your app? Join us in Part 1 of "Powering Your Angular-15 Ionic-7 App" as we embark on this exciting journey together.

Stay tuned for the upcoming parts of this series, where we'll delve even deeper into data manipulation, updates, and more. Get ready to take your app to the next level with the power of MySQL and PHP!

Let's get started and unlock the magic of data reading and display in your Angular-15 Ionic-7 app!

Tutorial

I start off by creating a new page using the command ionic generate page pages/tab3 or ionic g page pages/tab3 move the specific tab3 route link from app.route.ts to tabs.route.ts which allows us to see the bottom nav on lecturer page. My tabs.route.ts begins to look like this:

import { Routes } from '@angular/router';
import { TabsPage } from './tabs.page';

export const routes: Routes = [
  {
    path: 'tabs',
    component: TabsPage,
    children: [
      {
        path: 'tab1',
        loadComponent: () =>
          import('../tab1/tab1.page').then((m) => m.Tab1Page),
      },
      {
        path: 'tab2',
        loadComponent: () =>
          import('../tab2/tab2.page').then((m) => m.Tab2Page),
      },

      {
        path: 'tab3',
        loadComponent: () =>
          import('../tab3/tab3.page').then((m) => m.Tab3Page),
      },
      {
        path: 'tab4',
        loadComponent: () =>
          import('../tab4/tab4.page').then((m) => m.Tab4Page),
        ...canActivate(redirectUnauthorizedToLogin),
      },
      {
        path: '',
        redirectTo: '/tabs/tab1',
        pathMatch: 'full',
      },
      {
        path: 'diary',
        loadComponent: () =>
          import('../diary/diary.page').then((m) => m.DiaryPage),
      },
    ],
  },
  {
    path: '',
    redirectTo: '/tabs/tab1',
    pathMatch: 'full',
  },
];

Once the page is routed correctly, I start working on the SQL database. I downloaded and installed MAMP server service from MAMP - Your local web development solution to run a MySQL server using PHP scripts. I used the collegeDatabase.sql file which has the following SQL logic written into it to create three distinct tables for student data, lecturer data, and module data:

-- phpMyAdmin SQL Dump
-- version 3.3.9.2
-- <http://www.phpmyadmin.net>
--
-- Host: localhost
-- Generation Time: Mar 13, 2013 at 04:40 PM
-- Server version: 5.5.9
-- PHP Version: 5.3.6

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `collegeData`
--

-- --------------------------------------------------------

--
-- Table structure for table `lecturerTable`
--

CREATE TABLE `lecturerTable` (
  `staffNumber` int(6) NOT NULL,
  `firstName` varchar(10) NOT NULL,
  `lastName` varchar(15) NOT NULL,
  `moduleNo1` int(6) NOT NULL,
  `moduleNo2` int(6) NOT NULL,
  `email` varchar(30) NOT NULL,
  PRIMARY KEY (`staffNumber`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='This table contains all lecturer records for the example database.';

--
-- Dumping data for table `lecturerTable`
--

INSERT INTO `lecturerTable` VALUES(123001, 'Charlie', 'Cullen', 999001, 999003, 'charlie@here.com');
INSERT INTO `lecturerTable` VALUES(123002, 'Hugh', 'McAtamney', 999002, 999009, 'hugh@there.com');
INSERT INTO `lecturerTable` VALUES(123003, 'Keith', 'Gardiner', 999006, 999008, 'keith@there.com');
INSERT INTO `lecturerTable` VALUES(123004, 'Paula', 'McGloin', 999004, 999005, 'paula@there.com');
INSERT INTO `lecturerTable` VALUES(123005, 'James', 'Wogan', 999007, 999010, 'james@there.com');

-- --------------------------------------------------------

--
-- Table structure for table `moduleTable`
--

CREATE TABLE `moduleTable` (
  `moduleNo` int(6) NOT NULL,
  `moduleName` varchar(30) NOT NULL,
  `credits` int(2) NOT NULL,
  `website` varchar(30) NOT NULL,
  `dueDate` date NOT NULL,
  `location` varchar(25) NOT NULL,
  `room` varchar(10) NOT NULL,
  `lat` varchar(20) NOT NULL,
  `long` varchar(20) NOT NULL,
  PRIMARY KEY (`moduleNo`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='This table contains all module records for the example database.';

--
-- Dumping data for table `moduleTable`
--

INSERT INTO `moduleTable` VALUES(999001, 'Dynamic Web Development', 15, 'www.dynWeb.ie', '2013-05-14', 'Aungier Street', '4037', '53.338545', '-6.26607');
INSERT INTO `moduleTable` VALUES(999002, 'Human Computer Interaction', 10, 'www.hci.ie', '2013-04-09', 'Aungier Street', '2005', '53.338545', '-6.26607');
INSERT INTO `moduleTable` VALUES(999003, 'Introduction to Programming', 15, 'www.jscriptIntro.ie', '2013-01-11', 'Kevin Street', '1045', '53.337015', '-6.267933');
INSERT INTO `moduleTable` VALUES(999004, 'Design Principles', 15, 'www.designIntro.ie', '2013-04-25', 'Bolton Street', '0130', '53.351406', '-6.268724');
INSERT INTO `moduleTable` VALUES(999005, 'Design Practice', 10, 'www.designPract.ie', '2013-01-11', 'Cathal Brugha Street', '0123', '53.352044', '-6.259514');
INSERT INTO `moduleTable` VALUES(999006, 'Digital Audio', 10, 'www.dspAudio.com', '2013-05-10', 'Aungier Street', '3025', '53.338545', '-6.26607');
INSERT INTO `moduleTable` VALUES(999007, 'Digital Signal Processing', 10, 'www.dspGeneral.ie', '2013-04-04', 'Kevin Street', '2103', '53.337015', '-6.267933');
INSERT INTO `moduleTable` VALUES(999008, 'History of Digital Media', 5, 'www.itsbeendone.ie', '2013-03-28', 'Aungier Street', '0120', '53.338545', '-6.26607');
INSERT INTO `moduleTable` VALUES(999009, 'Digital Asset Management', 5, 'www.contentStore.ie', '2013-05-30', 'Bolton Street', '1004', '53.351406', '-6.268724');
INSERT INTO `moduleTable` VALUES(999010, 'Production Skills', 10, 'www.webDevPro.ie', '2013-04-02', 'Aungier Street', '1089', '53.338545', '-6.26607');

-- --------------------------------------------------------

--
-- Table structure for table `studentTable`
--

CREATE TABLE `studentTable` (
  `studentID` int(6) NOT NULL,
  `firstName` varchar(10) NOT NULL,
  `lastName` varchar(15) NOT NULL,
  `moduleNo1` int(6) NOT NULL,
  `moduleNo2` int(6) NOT NULL,
  `courseID` int(6) NOT NULL,
  PRIMARY KEY (`studentID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='This table contains all student records for the example database.';

--
-- Dumping data for table `studentTable`
--

INSERT INTO `studentTable` VALUES(123, 'Kermit', 'Frog', 999003, 999008, 888001);
INSERT INTO `studentTable` VALUES(124, 'Gonzo', 'Great', 999001, 999009, 888001);
INSERT INTO `studentTable` VALUES(125, 'Cookie', 'Monster', 999004, 999005, 888002);
INSERT INTO `studentTable` VALUES(126, 'Fozzie', 'Bear', 999006, 999010, 888001);
INSERT INTO `studentTable` VALUES(127, 'Bunsen', 'Honeydew', 999007, 999009, 888003);
INSERT INTO `studentTable` VALUES(128, 'Miss', 'Piggy', 999002, 999003, 888003);
INSERT INTO `studentTable` VALUES(129, 'Gobo', 'Fraggle', 999008, 999010, 888002);
INSERT INTO `studentTable` VALUES(130, 'Mokey', 'Fraggle', 999002, 999005, 888001);
INSERT INTO `studentTable` VALUES(131, 'Red', 'Fraggle', 999006, 999008, 888003);
INSERT INTO `studentTable` VALUES(132, 'Wembley', 'Fraggle', 999004, 999007, 888003);
INSERT INTO `studentTable` VALUES(133, 'Travelling', 'Matt', 999002, 999003, 888002);
INSERT INTO `studentTable` VALUES(134, 'Convincing', 'John', 999004, 999008, 888001);
INSERT INTO `studentTable` VALUES(135, 'Cotterpin', 'Doozer', 999008, 999009, 888002);
INSERT INTO `studentTable` VALUES(136, 'Judge', 'Dog', 999003, 999007, 888003);
INSERT INTO `studentTable` VALUES(137, 'Doctor', 'Astro', 999005, 999001, 888001);
INSERT INTO `studentTable` VALUES(138, 'Sneaky', 'Snake', 999006, 999008, 888002);
INSERT INTO `studentTable` VALUES(139, 'Sunni', 'Gummi', 999009, 999010, 888002);
INSERT INTO `studentTable` VALUES(140, 'Cubbi', 'Gummi', 999004, 999008, 888001);
INSERT INTO `studentTable` VALUES(141, 'Papa', 'Smurf', 999008, 999009, 888003);
INSERT INTO `studentTable` VALUES(142, 'Lazy', 'Smurf', 999001, 999002, 888001);
INSERT INTO `studentTable` VALUES(143, 'Vanity', 'Smurf', 999008, 999010, 888002);
INSERT INTO `studentTable` VALUES(144, 'Joe', 'Frasier', 999004, 999006, 888003);
INSERT INTO `studentTable` VALUES(145, 'Muhammad', 'Ali', 999003, 999005, 888002);
INSERT INTO `studentTable` VALUES(146, 'George', 'Foreman', 999002, 999003, 888001);
INSERT INTO `studentTable` VALUES(147, 'Larry', 'Holmes', 999001, 999002, 888001);
INSERT INTO `studentTable` VALUES(148, 'Marvin', 'Hagler', 999004, 999005, 888003);
INSERT INTO `studentTable` VALUES(149, 'John', 'Coltrane', 999002, 999006, 888002);
INSERT INTO `studentTable` VALUES(150, 'Sonny', 'Rawlins', 999009, 999010, 888002);
INSERT INTO `studentTable` VALUES(151, 'Coleman', 'Hawkins', 999006, 999007, 888003);
INSERT INTO `studentTable` VALUES(152, 'Wes', 'Montgomery', 999002, 999004, 888001);
INSERT INTO `studentTable` VALUES(153, 'Joe', 'Pass', 999006, 999009, 888001);
INSERT INTO `studentTable` VALUES(154, 'Charlie', 'Christian', 999008, 999010, 888002);
INSERT INTO `studentTable` VALUES(155, 'Stanley', 'Jordan', 999004, 999007, 888003);
INSERT INTO `studentTable` VALUES(156, 'Rory', 'Gallagher', 999006, 999009, 888003);
INSERT INTO `studentTable` VALUES(157, 'Gary', 'Moore', 999001, 999008, 888002);
INSERT INTO `studentTable` VALUES(158, 'Jimi', 'Hendrix', 999004, 999008, 888001);
INSERT INTO `studentTable` VALUES(159, 'Paco', 'Pena', 999005, 999009, 888003);
INSERT INTO `studentTable` VALUES(160, 'Andres', 'Segovia', 999003, 999007, 888003);
INSERT INTO `studentTable` VALUES(161, 'Bootsy', 'Collins', 999004, 999005, 888002);
INSERT INTO `studentTable` VALUES(162, 'George', 'Clinton', 999003, 999010, 888002);

After installing MAMP, I opened the ‘Open WebStart Page’ and navigate to localhost:8888 / localhost / collegedata | phpMyAdmin 5.1.2 and I uploaded the file to the phpMyAdmin console using the create new database function. After the file was uploaded I got the college database as a RDBMS (Relational database management system) in the following figure 1:

Figure 1: College Database on phpMyAdmin as MySQL RDMS

And here's how the MAMP server looks when running: 


Once the database is created, there is now a need to connect to this MySQL database using PHP scripts. I used the following PHP script to gain access to the database:

<?php
//Enable cross domain Communication - Beware, this can be a security risk
header('Access-Control-Allow-Origin: *');
header('Access-Control-Allow-Methods: GET, POST, PUT, DELETE, OPTIONS');

//include db connect class
require_once 'db_connect.php';

// Get access to datbase instance
$db = Database::getInstance();

// Get database connection from database
$conn = $db->getConnection();

//Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Create query using SQL string
$sql_query = "SELECT * FROM lecturerTable";

// Query database using connection
$result = $conn->query($sql_query);

// check for empty result
if (mysqli_num_rows($result) > 0)
 {
	// Create Array for JSON response
	$response = array();

    // Create Array called lecturers inside response Array
    $response["lecturers"] = array();

	// Loop through all results from Database
    while ($row = mysqli_fetch_array($result))
     {
        	// Assign results for each database row, to temp lecturer array
            $lecturer = array();
            $lecturer["staffNumber"] = $row["staffNumber"];
            $lecturer["firstName"] = $row["firstName"];
            $lecturer["lastName"] = $row["lastName"];
            $lecturer["moduleNo1"] = $row["moduleNo1"];
            $lecturer["moduleNo2"] = $row["moduleNo2"];
            $lecturer["email"] =$row["email"];
       // push single lecturer into final response array
        array_push($response["lecturers"], $lecturer);
    }

    // success
    $response["success"] = 1;

    // print JSON response
    print (json_encode($response));

}
else {
    // no lecturers found
    $response["success"] = 0;
    $response["message"] = "No lecturers found";

    // print no lecturers JSON
    print (json_encode($response));
}
?>

I took this script and placed it in the D:\\MAMP\\htdocs\\php_ionic folder where MAMP is installed on the system so that MAMP gains access to the scripts.

Then I come back to VS code editor and enter the link to database on localhost in my environment.ts file like this:

export const environment = {
  production: false,

  // create url variable to hold the php_ionic json-data-lecturers.php file
  urlLecturers: '<http://localhost:8888/php_ionic/json-data-lecturers.php>',

};

After this, I have create an angular service file using the command ionic generate service services/lecturer-service which generates a service class that will be used to access the data from database and display it on the lecturer page. Following is the code I have used to write the lecturer data service:

import { Injectable } from '@angular/core';
import { HttpClient } from '@angular/common/http';
import { Observable } from 'rxjs';
import { environment } from 'src/environments/environment';

@Injectable({
  providedIn: 'root',
})
export class LecturerServiceService {
  // create url variable to hold the php_ionic json-data-students.php file
  urlLecturers = environment.urlLecturers;

  // Inject HttpClient into the constructor
  constructor(private http: HttpClient) {}

  // create a method to get the data from the json-data-students.php file
  getLecturers(): Observable<any> {
    // return type is Observable<any>
    return this.http.get(this.urlLecturers);
  }
}

What I have written above is an Angular service that fetches data from a remote server using HTTP. The service depends on the HttpClient service, which is injected into its constructor.

The service I have created has a property named urlLecturers which holds the URL of the remote server endpoint to fetch data from. The URL is obtained from the environment object, which is imported from the src/environments/environment.ts file.

In the service, I have created a method named getLecturers() which returns an observable of any type. In Angular, an Observable is a representation of a stream of data or events that can be observed over time. It is a powerful tool for working with asynchronous data and event-based programming. It can be created from a variety of sources, including HTTP requests, timers, user inputs, and more.

The above method makes an HTTP GET request to the server using the http.get() method of the injected HttpClient service, passing the urlLecturers property as the URL of the endpoint to fetch data from. The method returns the observable returned by the http.get() method, which can be subscribed to in a component to receive the data returned by the server.

Since we’re using Angular 15 with standalone components, I had to import the HttpClient and HttpClientModule in my main.ts file under providers to make them work properly like this:

import { HttpClient, HttpClientModule } from '@angular/common/http';
...
bootstrapApplication(AppComponent, {
  providers: [ 
    { provide: RouteReuseStrategy, useClass: IonicRouteStrategy },
    importProvidersFrom(
     ...
      HttpClient,
      HttpClientModule,
      ...
  ],
});

This method will further be used to access and display data on the lecturers page. Next I go to the tab3.page.ts file to write the logic to get the lecturer data in the following way:

import { Component } from '@angular/core';
import { IonicModule } from '@ionic/angular';
import { LecturerServiceService } from '../../services/lecturer-service.service';
import { CommonModule } from '@angular/common';
import { FormsModule } from '@angular/forms';
import { OnInit } from '@angular/core';
import { RouterLink } from '@angular/router';

@Component({
  selector: 'app-tab3',
  templateUrl: 'tab3.page.html',
  styleUrls: ['tab3.page.scss'],
  standalone: true,
  imports: [IonicModule, CommonModule, FormsModule, RouterLink],
})
export class Tab3Page implements OnInit {
  // create 2 variables to store results emitted from observable
  lecturers: any;
  newLecturers: any;

  // inject student service into the constructor
  constructor(private lecturerService: LecturerServiceService) {}

  // create a method to get the data from the json-data-students.php file
  getLecturerData() {
    // subscribe to the observable
    // result is the data emitted from the observable
    this.lecturerService.getLecturers().subscribe((result) => {
      // store the data emitted from the observable into the lecturers variable
      this.lecturers = result;
      // console.log(this.lecturers);
      // store the data emitted from the observable into the newLecturers variable
      this.newLecturers = this.lecturers.lecturers;
    });
  }

  // call the getLecturerData() method when the page loads
  ngOnInit() {
    this.getLecturerData();
  }
}

From this typescript logic, I can now use the newLecturers array, which contains all individual lecturer data as objects, to display individual lecturers on the html template i.e. tab3.page.html file in the following way:

<ion-header [translucent]="false">
  <ion-toolbar>
    <ion-title> My Lecturers </ion-title>
    <ion-buttons slot="start">
      <ion-back-button defaultHref="/"></ion-back-button>
    </ion-buttons>
    <ion-buttons slot="end">
      <ion-menu-button menu="main-menu"></ion-menu-button>
    </ion-buttons>
  </ion-toolbar>
</ion-header>

<ion-content [fullscreen]="true">
  <ion-header collapse="condense">
    <ion-toolbar>
      <ion-title size="large"> My Lecturers</ion-title>
    </ion-toolbar>
  </ion-header>
  <!-- display lecturer list as an accordian component which can show lecturer detail when clicked on and expanded -->
  <ion-accordion-group expand="inset" *ngIf="newLecturers">
    <ion-accordion *ngFor="let lecturer of newLecturers">
      <ion-item slot="header" color="light">
        <ion-label>{{lecturer.firstName}} {{lecturer.lastName}}</ion-label>
      </ion-item>
      <div class="ion-padding" slot="content">
        <ion-list>
          <!-- mailto will allow students to email lecturers directly from here -->
          <ion-item href="mailto:{{lecturer.email}}">
            <ion-label>email: {{lecturer.email}} </ion-label>
          </ion-item>
          <ion-item>
            <ion-label>moduleNo1: {{lecturer.moduleNo1}} </ion-label>
          </ion-item>
          <ion-item>
            <ion-label>moduleNo2: {{lecturer.moduleNo2}} </ion-label>
          </ion-item>
          <ion-item>
            <ion-label>staffNumber: {{lecturer.staffNumber}} </ion-label>
          </ion-item>
        </ion-list>
      </div>
    </ion-accordion>
  </ion-accordion-group>
</ion-content>

In the above code, I have used the ion-accordian UI component from Ion-Accordion Components: How to Build & Examples | Ionic (ionicframework.com) with an *ngIf angular directive that checks if the array exists and the *ngFor array to loop through the lecturers and display an expandable accordion style list of lecturers as shown in Figure 2 & 3.


Once the data is displayed in the console using the service created before, it is evident the MAMP is up and running (if it is not, data will not display), PHP script is working fine, SQL database is responding well, and HttpClient modules are working well to make API calls to our database.

Conclusion


Congratulations on reaching the conclusion of Part 1 in our series, "Powering Your Angular-15 Ionic-7 App: Harnessing the Magic of MySQL and PHP for Data Reading and Display"! We've covered a lot of ground in this installment, diving into the world of data management and exploring the integration of MySQL and PHP into our app.

By now, you should have a solid understanding of how to set up a MySQL database, establish a connection with PHP, and retrieve data using powerful queries. Additionally, you've learned valuable techniques for efficiently displaying the retrieved data in your Angular-15 Ionic-7 app, ensuring a seamless user experience.

The power of MySQL and PHP is immense when it comes to data reading and display. Leveraging these technologies enables you to manage and present information in a reliable, scalable, and efficient manner. Whether you're building a small-scale application or a complex system, MySQL and PHP are indispensable tools in your development arsenal.

But our journey doesn't end here. In the upcoming parts of this series, we'll continue to explore the magic of MySQL and PHP, delving deeper into data manipulation, updates, and advanced techniques. You can look forward to mastering the art of data synchronization, form submissions, and much more.

Remember, the key to truly harnessing the power of MySQL and PHP lies in practice and experimentation. Take the knowledge you've gained from Part 1 and apply it to your own app development projects. Embrace the challenges, adapt the concepts to your unique requirements, and unlock the full potential of data management in your Angular-15 Ionic-7 apps.

Thank you for joining us on this exciting journey of powering your app with MySQL and PHP. We hope this series has inspired you to explore new possibilities and create remarkable experiences for your users.

Stay connected and keep an eye out for the upcoming parts of this series. Get ready to take your app to new heights with the magic of MySQL and PHP!

Happy coding! 

Popular Posts

Perform CRUD (Create, Read, Update, Delete) Operations using PHP, MySQL and MAMP : Part 4. My First Angular-15 Ionic-7 App

Visualize Your Data: Showcasing Interactive Charts for Numerical Data using Charts JS Library (Part 23) in Your Angular-15 Ionic-7 App

How to Build a Unit Converter for Your Baking Needs with HTML, CSS & Vanilla JavaScript