# Angular+PHP+MariaDB
## [先參照前篇安裝Angular](https://hackmd.io/@109213067/BJBlg0Mio/https%3A%2F%2Fhackmd.io%2FW3lwX8_rSPixiiLQ3U0AVg%3Fview)
## 流程
Angular -> 呼叫data.service.ts -> 呼叫PHP -> 操作MariaDB -> PHP回傳data.service.ts(如果有) -> Angular
## 1. <a href="https://www.digitalocean.com/community/tutorials/how-to-install-mariadb-on-rocky-linux-9">安裝MariaDB</a>
* 登入mariadb

$ mysql -u username -p

* 使用資料庫test

use test;

* 新增資料表job

CREATE TABLE `job` (`id` int(10) NOT NULL PRIMARY KEY auto_increment,`title` varchar(10) NOT NULL,`date` varchar(10) NOT NULL,`content` varchar(50) NOT NULL,`done` varchar(1) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

## 2. 安裝Apache

$ sudo dnf install httpd
$ sudo systemctl start httpd
$ sudo systemctl enable httpd

## 3. 安裝PHP

$ sudo yum install php-{common,gmp,fpm,curl,intl,pdo,mbstring,gd,xml,cli,zip,mysqli}
$ sudo yum module list php
$ sudo yum module enable php:8.1 -y
$ sudo vi /etc/httpd/conf/httpd.conf
在# LoadModule foo_module modules/mod_foo.so這行下面新增:
AddHandler php-script .php
$ sudo systemctl restart httpd
$ sudo systemctl restart php-fpm

## 4. PHP程式
* cd 到/var/www/html
* 新增4個php檔

$ sudo touch data.php
$ sudo touch insert.php
$ sudo touch edit.php
$ sudo touch delete.php

* 4個檔案內容分別如下:
* data.php:

<?php
header('Access-Control-Allow-Origin: *');
header('Content-Type: application/json; charset=UTF-8');
header('Access-Control-Allow-Methods: GET,POST,PUT,DELETE,OPTIONS');
header('Access-Control-Max-Age: 3600');
header('Access-Control-Allow-Headers: Content-Type,Access-Control-Allow-Headers, Authorization, X-Requested-With');
header('Cache-Control: no-cache, must-revalidate');
header('Expires: Sat, 26 Jul 1997 05:00:00 GMT');
header('Access-Control-Allow-Credentials: true');
$servername = "localhost";
$username = "kenny";
$password = "Kenny061256";
$dbname = "test";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
//
// Check connection
if ($conn->connect_error) {
   die("Connection failed: " . $conn->connect_error);
   }

   $sql = "SELECT * FROM job";
   $result = $conn->query($sql);

   $data = array();

   if ($result->num_rows > 0) {
     // output data of each row
       while($row = $result->fetch_assoc()) {
           $data[] = $row;
       }
   } else {
       echo "0 results";
   }

   echo json_encode($data);

   $conn->close();

?>

* insert.php

<?php
header('Access-Control-Allow-Origin: *');
header('Content-Type: application/json; charset=UTF-8');
header('Access-Control-Allow-Methods: GET,POST,PUT,DELETE,OPTIONS');
header('Access-Control-Max-Age: 3600');
header('Access-Control-Allow-Headers: Content-Type,Access-Control-Allow-Headers, Authorization, X-Requested-With');
header('Cache-Control: no-cache, must-revalidate');
header('Expires: Sat, 26 Jul 1997 05:00:00 GMT');
header('Access-Control-Allow-Credentials: true');
$servername = "localhost";
$username = "kenny";
$password = "Kenny061256";
$dbname = "test";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
//
$data = json_decode(file_get_contents("php://input"));
if (!isset($data->title) || !isset($data->date) || !isset($data->content)) {
    echo json_encode(['success' => 0, 'message' => 'some fields are empty!']);
    exit;
};
// Check connection
if ($conn->connect_error) {
   die("Connection failed: " . $conn->connect_error);
   }
   $title = $data->title;
   $date = $data->date;
   $content = $data->content;
   //$title = "123";
   //$date = "111";
   //$content = "333";
   $sql = "insert into `job`(title,date,content,done)values(";
   $sql .= "'".$title."',";
   $sql .= "'".$date."',";
   $sql .= "'".$content."',";
   $sql .= "'N')";
   //$conn->execute($sql);
   $conn->query($sql);
   $conn->close();

?>

* edit.php

<?php
header('Access-Control-Allow-Origin: *');
header('Content-Type: application/json; charset=UTF-8');
header('Access-Control-Allow-Methods: GET,POST,PUT,DELETE,OPTIONS');
header('Access-Control-Max-Age: 3600');
header('Access-Control-Allow-Headers: Content-Type,Access-Control-Allow-Headers, Authorization, X-Requested-With');
header('Cache-Control: no-cache, must-revalidate');
header('Expires: Sat, 26 Jul 1997 05:00:00 GMT');
header('Access-Control-Allow-Credentials: true');
$servername = "localhost";
$username = "kenny";
$password = "Kenny061256";
$dbname = "test";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
//
$data = json_decode(file_get_contents("php://input"));
if (!isset($data->title) || !isset($data->date) || !isset($data->content)) {
    echo json_encode(['success' => 0, 'message' => 'some fields are empty!']);
    exit;
};
// Check connection
if ($conn->connect_error) {
   die("Connection failed: " . $conn->connect_error);
   }
   $id = $data->id;
   $title = $data->title;
   $date = $data->date;
   $content = $data->content;
   //$id = "2";
   //$title = "333";
   //$date = "111";
   //$content = "111";
   $sql = "update `job` set title='";
   $sql .= $title."',date='";
   $sql .= $date."',content='";
   $sql .= $content."' where id=";
   $sql .= $id;
   echo 123;
   //$conn->execute($sql);
   $conn->query($sql);
   $conn->close();

?>

* delete.php

<?php
header('Access-Control-Allow-Origin: *');
header('Content-Type: application/json; charset=UTF-8');
header('Access-Control-Allow-Methods: GET,POST,PUT,DELETE,OPTIONS');
header('Access-Control-Max-Age: 3600');
header('Access-Control-Allow-Headers: Content-Type,Access-Control-Allow-Headers, Authorization, X-Requested-With');
header('Cache-Control: no-cache, must-revalidate');
header('Expires: Sat, 26 Jul 1997 05:00:00 GMT');
header('Access-Control-Allow-Credentials: true');
$servername = "localhost";
$username = "kenny";
$password = "Kenny061256";
$dbname = "test";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
//
$data = json_decode(file_get_contents("php://input"));
$id = $data->id;
//$id = '1';
if (!isset($id)) {
    echo json_encode(['success' => 0, 'message' => 'id field is empty!']);
    exit;
};
    // Check connection
if ($conn->connect_error) {
   die("Connection failed: " . $conn->connect_error);
   }

   $sql = "delete from `job` where id=";
   $sql.= $id;
   $conn->query($sql);

   $conn->close();

?>

* 每個檔案都要加上檔頭(header),否則會有CORS錯誤
## 5. 新增data.service.ts
* data.service.ts會發送POST請求給php檔案,再把結果回傳給component.ts
* 輸入以下指令新增data.service.ts

$ ng generate service data
$ ng g s data(簡寫)

* data.service.ts的內容修改如下:

import { HttpClient, HttpHeaders } from '@angular/common/http';
import { Injectable } from '@angular/core';

@Injectable()
export class DataService {
    constructor(private http: HttpClient) {}
    getData() {
        const httpOptions = {
	        headers: new HttpHeaders({
                'Content-Type': 'application/json',
                'Authorization': 'Bearer ' + localStorage.getItem('token'),
	            'Access-Control-Alllow-Origin': '*',
		        'Access-Control-Allow-Methods': 'GET,POST,PUT,DELETE"'
	        })
	    };
	    return this.http.post('http://localhost/data.php', httpOptions);						
    }
    addData(job: Object){
	    return this.http.post('http://localhost/insert.php', job);
    }
    editData(job: Object){
	    return this.http.post('http://localhost/edit.php', job);
    }
    deleteData(job: Object){
        return this.http.post('http://localhost/delete.php', job);
    }

}

* 這裡定義4個可呼叫函式getData,addData,editData,deleteData,分別會呼叫4個php並回傳結果
## 6. 修改module.ts
* module.ts導入dataService，內容如下:

import { NgModule } from '@angular/core';
import { BrowserModule } from '@angular/platform-browser';

import { AppRoutingModule } from './app-routing.module';
import { AppComponent } from './app.component';
import { EditJobComponent } from './edit-job/edit-job.component';
import { HttpClientModule } from '@angular/common/http';
import { DataService } from './data.service';

@NgModule({
  declarations: [
    AppComponent,
    EditJobComponent
  ],
  imports: [
    BrowserModule,
    AppRoutingModule,
    HttpClientModule
  ],
  providers: [DataService],
  bootstrap: [AppComponent]
})
export class AppModule { }

## 7. 修改job.ts
* 配合資料庫欄位加上id:string屬性，內容如下:

export interface job {
    id: string;
    title: string;
    date: string;
    content: string;
    done: boolean;
}

## 8. 修改app.component.ts和html
* app.component.ts內容如下:

import { Component, OnInit} from '@angular/core';
import { job } from './job';
import { DataService } from './data.service';

@Component({
  selector: 'app-root',
  templateUrl: './app.component.html',
  styleUrls: ['./app.component.css']
})
export class AppComponent implements OnInit{
  //
  data:any;
  constructor(private dataService: DataService) { }
  ngOnInit() {
    this.dataService.getData().subscribe((data) => {
      this.data = data;
    });
  }
  //	
  title = 'firstapp';
  // defined filter words
  search = "";
  // defiend job
  //allJobs = [
    //{title:"hello",date: new Date("2023-01-01"),content:"say hello",done:false},
    //{title:"goodbye",date:new Date("2023-01-02"),content:"say goodbye",done:false},
  //]
  // display job to html
  get myJob() {
    if (this.search=="" || this.search=="reset") {
      return this.data;
      //return this.allJobs;
    }
    let filter = this.search;
    const job = {keywords:filter};
    // this.dataService.searchData(job).subscribe((data) => {
    //   this.data = data;
    // })
    let filterJobs = this.data.filter(function(data:job) {
      return (data.id === filter);
    });
    return filterJobs;
  }
  // function addJob
  addJob(title: string, date:string, content: string) {
    if (title=="" || date=="Invalid Date" || content=="") {
      alert("please fill in all filed!");
      return;
    }
    const job = {title:title,date:date,content:content};
    this.dataService.addData(job).subscribe(response=>{
      //console.log(response);
      alert('add job success!');
      window.location.reload();
    });
  }
  // function removeJob
  removeJob(id: string) {
    const job = {id:id};
    this.dataService.deleteData(job).subscribe(response=>{
      alert('delete job success!');
      window.location.reload();
    });
  }
  convertDate(date: string) {
    return new Date(date);
  }
}

* app.component.html內容如下:

<h1>Todo List</h1>
<h2>Create Job</h2>
<input #myTitle placeholder="job title"/>
<input #myDate placeholder="job id"/>
<input #myContent placeholder="job content"/>
<button (click)="addJob(myTitle.value,myDate.value,myContent.value); myTitle.value='';myDate.value='';myContent.value=''">Create</button>
<table>
    <tr><td>num</td><td>title</td><td>date</td><td>content</td><td>done</td></tr>
    <tr *ngFor="let i of myJob"><td>{{i.id}}</td><td>{{i.title}}</td><td>{{i.date}}</td><td>{{i.content}}</td><td>{{i.done}}</td><td><button (click)="removeJob(i.id)">Delete</button></td><app-edit-job [theJob]="i"></app-edit-job></tr>
</table>
<input #searcher placeholder="job id"/>
<button (click)="search=searcher.value">search</button>
<button (click)="search='reset';searcher.value=''">reset</button>

## 9. 修改edit-job.component.ts和html
* edit-job.components.ts內容如下:

import { Component, Input, OnInit} from '@angular/core';
import { job } from '../job';
import { DataService } from '../data.service';

@Component({
  selector: 'app-edit-job',
  templateUrl: './edit-job.component.html',
  styleUrls: ['./edit-job.component.css']
})
export class EditJobComponent implements OnInit{
  //
  data:any;
  // defined job which is Input from other file
  @Input() theJob!: job;
  constructor(private dataService: DataService) { }
  ngOnInit() {
      this.dataService.getData().subscribe((data) => {
	  this.data = data;
      });
  }
  // defined display
  display = false;
  id = "";
  // edit the job
  editJob(title: string, date:string, content: string) {
    if (title=="" || date=="" || content=="") {
      alert("please fill in all filed!");
      this.display=false;
      return;
    }
    this.id = this.theJob.id;
    const job = {id:this.id,title:title,date:date,content:content};
    this.dataService.editData(job).subscribe(response=>{
      //console.log(response);
      alert('edit job success!');
      window.location.reload();
    });
  }
  convertDate(date: string) {
    return new Date(date);
  }
}

* edit-job.component.html

<div *ngIf="!display">
    <button (click)="display=!display">Edit</button>
</div>

<div *ngIf="display">
<input #myTitle placeholder="job title"/>
<input #myDate placeholder="job date"/>
<input #myContent placeholder="job content"/>
<button (click)="editJob(myTitle.value,myDate.value,myContent.value); myTitle.value='';myDate.value='';myContent.value=''">Edit</button>
<button (click)="display=!display">Cancel</button>
</div>