Scribd
Upload
42 views12 pages

13 MySQL - Join Table

The document describes creating two tables - a students table and a users table - in an Ionic database to store student and user data. It includes the table structures, sample data, and SQL codes to create the tables and insert data. It also describes creating an Ionic app to display a complete list of students by joining the data from the two tables using a jointable service.

Uploaded by

UMAIRAH BINTI ABDUL KADIR
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
42 views12 pages

13 MySQL - Join Table

The document describes creating two tables - a students table and a users table - in an Ionic database to store student and user data. It includes the table structures, sample data, and SQL codes to create the tables and insert data. It also describes creating an Ionic app to display a complete list of students by joining the data from the two tables using a jointable service.

Uploaded by

UMAIRAH BINTI ABDUL KADIR
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 12

Multipe Table – CRUD operation

home.page.html completelist.page.html

Students

id name email phone


29 Nurul Izani izani@yahoo.com 01010101230
37 Siti Atikah atikah@gmail.com 0101010122
39 sofea sofea@gmail.com 019-292929
48 Irene emiza@gmail.com 01121101102

Users
id studentId country capital
1 1 Malaysia Johor
2 29 Brunei Banda Seri Begawan
3 2 Singapore Singapore
Create 2 tables – students and users in the ionic database

Students table

Students’s table structure


Codes

-- phpMyAdmin SQL Dump


-- version 5.0.3
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Jun 14, 2022 at 01:24 AM
-- Server version: 10.4.14-MariaDB
-- PHP Version: 7.2.34

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";


START TRANSACTION;
SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;


/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `ionic`
--

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

--
-- Table structure for table `students`
--

CREATE TABLE `students` (


  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `email` varchar(100) NOT NULL,
  `phone` varchar(15) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `students`
--

INSERT INTO `students` (`id`, `name`, `email`, `phone`) VALUES


(29, 'Nurul Izani Ali', 'izani@yahoo.com', '01010101230'),
(37, 'Siti atikah Elias', 'atikah@gmal.com', '0101010122'),
(39, 'Sofea Aisyah', 'sofea@gmail.com', '019-292929'),
(48, 'Emiza', 'emiza@gmail.com', '0112110102'),
(56, 'Irene', 'irene@gmail.com', '011-2345010'),
(57, 'Izzudin', 'izzu@gmail.com', '019-5640201');
--
-- Indexes for dumped tables
--

--
-- Indexes for table `students`
--
ALTER TABLE `students`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `students`
--
ALTER TABLE `students`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=58;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;


/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
Users table

Users’s table structure


Codes

-- phpMyAdmin SQL Dump


-- version 5.0.3
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Jun 14, 2022 at 01:24 AM
-- Server version: 10.4.14-MariaDB
-- PHP Version: 7.2.34

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";


START TRANSACTION;
SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;


/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `ionic`
--

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

--
-- Table structure for table `users`
--

CREATE TABLE `users` (


  `id` int(11) NOT NULL,
  `studentId` int(11) NOT NULL,
  `country` varchar(100) NOT NULL,
  `capital` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`id`, `studentId`, `country`, `capital`) VALUES


(1, 1, 'Malaysia', 'Johor'),
(2, 29, 'Brunei', 'Bandar Seri Begawan'),
(3, 2, 'Singapore', 'Singapore');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `users`
--
ALTER TABLE `users`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `users`
--
ALTER TABLE `users`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;


/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Open your ionic folder in htdocs/root folder


If you have created the students CRUD app before, you may edit your .htaccess to add the following
codes:

.htaccess

RewriteEngine on
RewriteRule ^api/students/?$ api/app.php
RewriteRule ^api/students/([0-9]+)?$ api/app.php?id=$1
RewriteRule ^api/stud2/?$ api/app2.php
RewriteRule ^api/stud2/([0-9]+)?$ api/app2.php?id=$1

Create a new file, called app2.php in the api folder. Add these codes.

app2.php

<?php
    require ("headers.php");
   
    if($_SERVER['REQUEST_METHOD'] === 'GET'){
        //echo 'get';
        if(isset($_GET['id'])){
            $id = $conn->real_escape_string($_GET['id']);
            $sql = $conn->query("select * from students where id='$id'");
            $data= $sql->fetch_assoc();
           
        }
        else {
            $data = array();
            $query = "SELECT
            students.name,
            students.phone,
            students.email,
            users.country,
            users.capital
            FROM students INNER JOIN users
            ON users.studentId=students.id";
           
            $sql = $conn->query($query);
            while($d = $sql->fetch_assoc()){
                $data[] = $d;              
            }          
        }       
        exit (json_encode($data)); //return json data       
    }
   
?>
Create a new service called as jointable service.
ionic g service services/jointable

Add the following codes to jointable.ts

jointable.ts

import { Injectable } from '@angular/core';


import {HttpClient} from '@angular/common/http';

export interface StudentUser{


  id: string;
  name: string;
  email: string;
  phone: string;
  country: string;
  capital: string;
}
@Injectable({
  providedIn: 'root'
})
export class JointableService {

  private url = 'http://localhost/ionic/api/stud2/';

  constructor(
    private http: HttpClient
  ) { }

  getAll(){
    return this.http.get<[StudentUser]>(this.url);
  }
  get(id: string){
    return this.http.get<StudentUser>(this.url + '/'+ id);
  } 
}
Create a new page called as completelist
ionic g page completelist

Add the following codes to completelist.ts

completelist.page.ts

import { Component, OnInit } from '@angular/core';


import { StudentUser, JointableService  } from
'../services/jointable.service';

@Component({
  selector: 'app-completelist',
  templateUrl: './completelist.page.html',
  styleUrls: ['./completelist.page.scss'],
})
export class CompletelistPage implements OnInit {

  completestudentlist: StudentUser[];

  constructor(
    private service: JointableService
  ) { }

  ngOnInit() {
    this.service.getAll().subscribe(response => {
      //console.log(response);
      this.completestudentlist = response;
    });
  }

Completelist.page.html

<ion-header [translucent]="true">
  <ion-toolbar color="secondary">
    <ion-buttons slot="start">
      <ion-back-button defaultHref="home"></ion-back-button>
    </ion-buttons>
    <ion-title>
      Complete List
    </ion-title>
  </ion-toolbar>
</ion-header>
<ion-content class="ion-padding">
<ion-list *ngFor = "let student of completestudentlist">

    <ion-item >
      <ion-avatar slot="start">
        <div class="avatar">
          {{student.name.substring(3,6).toUpperCase()}}
        </div>
      </ion-avatar>
      <ion-label>
        <h3 class="title">{{student.name}}</h3>
        <p class="subtext">{{'Email: ' + student.email}}</p>
        <p class="subtext">{{'Phone: ' + student.phone}}</p>
        <p class="subtext">{{'Country: ' + student.country}}</p>
        <p class="subtext">{{'Capital: ' + student.capital}}</p>
      </ion-label>
    </ion-item>
</ion-list>
</ion-content>

completelist.page.scss

.avatar {
  background-color: blueviolet;
  width: 100%;
  height: 100%;
  border-radius: 50px;
  display: flex;
  justify-content: center;
  align-items: center;
  color:aliceblue;

.subtext{
  font-size: 80%;
  margin-top: 0;
  color: #8b979f;
}

.title{
  font-weight: 600;
  font-size: 16px;
}
You can edit your home.page.ts to add the link to the completelist page as below.

home.page.html

<ion-header [translucent]="true">
  <ion-toolbar color="secondary">
    <ion-buttons slot="end">
      <ion-button color="dark" (click)="addStudent()">
        <ion-icon name="add-circle"></ion-icon>
      </ion-button>
    </ion-buttons>
    <ion-title>
      Home
    </ion-title>
  </ion-toolbar>
</ion-header>

<ion-content class="ion-padding">
<ion-fab vertical="bottom" horizontal="end" slot="fixed">
    <ion-fab-button [routerLink]="['/completelist']">
      <ion-icon name="globe"></ion-icon>
    </ion-fab-button>
</ion-fab>

<ion-list *ngFor = "let student of students">


  <ion-item-sliding>
    <ion-item >
      <ion-avatar slot="start">
        <div class="avatar">
          {{student.name.substring(3,6).toUpperCase()}}
        </div>
      </ion-avatar>
      <ion-label>
        <h3 class="title">{{student.name}}</h3>
        <p class="subtext">{{'Email: ' + student.email}}</p>
        <p class="subtext">{{'Phone: ' + student.phone}}</p>
      </ion-label>
    </ion-item>
    <ion-item-options>
      <ion-item-option color="success" (click)="updateStudent(student)">
        <ion-icon name="pencil"></ion-icon>
      </ion-item-option>
      <ion-item-option color="danger" (click)="removeStudent(student.id)">
        <ion-icon name="trash"></ion-icon>
      </ion-item-option>
    </ion-item-options>
  </ion-item-sliding>
</ion-list>
</ion-content>

You might also like