# 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
```sql=1
use test;
```
* 新增資料表job
```sql=1
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=1
<?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=1
<?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=1
<?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=1
<?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的內容修改如下:
``` c#=1
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，內容如下:
```c#=1
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屬性，內容如下:
```c#=1
export interface job {
id: string;
title: string;
date: string;
content: string;
done: boolean;
}
```
## 8. 修改app.component.ts和html
* app.component.ts內容如下:
```c#=1
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內容如下:
```html=1
<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內容如下:
```c#=1
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
```html=1
<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>
```
Sign in with Wallet
Connect another wallet