# PHP&MYSQL-2
###### tags: `PHP&MYSQL` `MYSQL` `PHP`
---
### 1.建置基本PHP&MYSQL環境
* 新增資料夾
### 2.設定寫入資料 add.php
```php=
<?php
//引入一次sqlConn.php資料檔並選取my_db資料庫
include_once ("sqlConn.php");
$con->select_db("my_db");
//判斷如果按下id是button時才執行
if(isset($_POST['button'])){
//設定$sql的變數資料
$sql="INSERT INTO `persons1` (`FirstName`, `LastName`, `Age`, `personID`)
VALUES ('$_POST[FirstName]', '$_POST[LastName]', '$_POST[Age]', NULL)";
//執行query涵式，就是設定$con為$sql裡的資料
$con->query($sql);
//跳轉回首頁，Location是PHP語法
header("Location:index.php");
};
?>
```
:::info
### :baby_chick: <span class="text1">跳轉頁面</span>
* <span class="code1">header("Location:$url" )</span>
可以直接把頁面轉到指定的URL
* <span class="code1">header("Refresh: 0; url=c.php")</span>
可以設定N秒之後在進行轉跳頁面(修改Refresh後面的數字)
例如要停頓3秒之後跳轉header("Refresh: 3; url=c.php")。
參考:[程式PHP 轉跳頁面。Location與 Refresh的差異。](https://expect7.pixnet.net/blog/post/44832877)
:::
---
### 3.顯示資料在 index.php
```php=
//選取資料位置
<?php
include_once("sqlConn.php");
$con->select_db("my_db");
//顯示資料語法
$result = $con->query("SELECT * FROM persons1");
echo "<a href=\"add.php\">New Record</a>";
//設定表格樣式
echo "<table border='1'>
<tr>
<th>FirstName</th>
<th>LastName</th>
<th>Age</th>
</tr>
";
//抓取資料的迴圈，把result的資料用fetch_array()迴圈叫出來
while($row = $result -> fetch_array()){
echo "<tr>";
echo "<td>" .$row['FirstName'] ."</td>";
echo "<td>" .$row['LastName'] ."</td>";
echo "<td>" .$row['Age'] ."</td>";
echo "</table>"
?>
```
>顯示如下:
>
---
### 4.更新與刪除資料按鈕
1. 製作Update與Delete按鈕
2. 使用PHP語法並指定參數
<span class="code2">`echo "<a href=\"update.php?id=$row[personID]\">UPDATE</a>";`
` echo "<a href=\"del.php?id=$row[personID]\">DELETE</a>";`</span>
3. 製作新增的按鈕
<span class="code1">echo "<a href=\"add.php\">New Record</a>"</span>
3. 完整code如下
```php=
<?php
include_once("sqlConn.php");
$con->select_db("my_db");
//顯示資料語法
$result = $con->query("SELECT * FROM persons1");
echo "<a href=\"add.php\">New Record</a>";
echo "<table border='1'>
<tr>
<th>FirstName</th>
<th>LastName</th>
<th>Age</th>
<th>Update</th>
<th>Delete</th>
</tr>
";
while($row = $result -> fetch_array()){
echo "<tr>";
echo "<td>" .$row['FirstName'] ."</td>";
echo "<td>" .$row['LastName'] ."</td>";
echo "<td>" .$row['Age'] ."</td>";
echo "<td><a href=\"update.php?id=$row[personID]\">UPDATE</a></td>";
echo "<td><a href=\"del.php?id=$row[personID]\">DELETE</a></td>";
echo "</tr>";
}
echo "</table>"
?>
```
---
### 5.製作更新按鈕的頁面
1. 複製add.php頁面內容作修改
2. PHP程式碼加上
<div class="code1">
$reslut=$con->query("SELECT * FROM persons1 WHERE personID=
'$_GET[id]'");
<br>
$row = $reslut->fetch_array();
</div>
3. HTML表格初始值加入
<div class="code1">
value="<?php echo $row['Age']?>"
</div>
完整PHP原始碼:
```php=
<?php
include_once ("sqlConn.php");
$con->select_db("my_db");
/*if(isset($_POST['button'])){
$sql="INSERT INTO `persons1` (`FirstName`, `LastName`, `Age`, `personID`)
VALUES ('$_POST[FirstName]', '$_POST[LastName]', '$_POST[Age]', NULL)";
$con->query($sql);
header("Location:index.php");
};*/
$reslut = $con->query("SELECT * FROM persons1 WHERE personID=
'$_GET[id]'");
$row = $reslut->fetch_array();
?>
```
:point_down: HTML欄位的PHP位置
<body>
<form id="form1" name="form1" method="post" action="">
<table width="400" border="1" cellspacing="0" cellpadding="0">
<tr>
<td>FirstName</td>
<td>LastName</td>
<td>Age</td>
</tr>
<tr>
<td>
<input type="text" name="FirstName" id="FirstName" <div class="code1">value="<?php echo $row['FirstName']?>"</div> /></td>
<td>
<input type="text" name="LastName" id="LastName" <div class="code1">value="<?php echo $row['LastName']?>"</div> /></td>
<td>
<input type="text" name="Age" id="Age" <div class="code1">value="<?php echo $row['Age']?>"</div> /></td>
</tr>
<tr>
<td colspan="3" align="center">
<div class="code1"><input name="personID" type="hidden" id="personID" value="<?php echo $row ['personID']?>" /></div>
<input type="submit" name="button" id="button" value="送出" /></td>
</tr>
</table>
</form>
</body>
<div class="text-s">
1.設定一個隱藏的欄位來抓取personID的值
2.因為上面的PHP程式跑完後不知道要把資料更新到哪裡去，
所以這裡設計一個隱藏欄位告訴PHP資料要指向哪個地方更新
</div>
---
#### 完整程式碼:
:::spoiler
```php=
<?php
include_once ("sqlConn.php");
$con->select_db("my_db");
/*
1.如果按下這個按鈕，執行更新UPDATE ... SET
2.值放在單引號內是SQL語法的寫法
3.WHERE是選定要更改的地方
4.personID=".$_POST['personID'] 點是連結的意思，$_POST這個語法會抓取personID的值*/
if(isset($_POST['button'])){
$sql="UPDATE persons1 SET FirstName='$_POST[FirstName]', LastName='$_POST[LastName]', Age='$_POST[Age]'
WHERE personID=".$_POST['personID'];
//$sql資料庫設定給$con
$con->query($sql);
//跳轉首頁
header("Location:index.php");
};
/*
1.這段是選取要顯示的資料
2.SELECT ... FROM 是SQL語法(選取要顯示的資料)
3.$_GET['id']抓取URL上的id值
4.fetch_array()從MYSQL取得資料*/
$reslut = $con->query("SELECT * FROM persons1 WHERE personID="
.$_GET['id']);
$row = $reslut->fetch_array();
?>
```
```htmlmixed=
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>更新</title>
</head>
<body>
<form id="form1" name="form1" method="post" action="update.php">
<table width="400" border="1" cellspacing="0" cellpadding="0">
<tr>
<td>FirstName</td>
<td>LastName</td>
<td>Age</td>
</tr>
<tr>
<td><label for="FirstName"></label>
<input type="text" name="FirstName" id="FirstName" value="<?php echo $row['FirstName']?>" /></td>
<td><label for="LastName"></label>
<input type="text" name="LastName" id="LastName" value="<?php echo $row['LastName']?>" /></td>
<td><label for="Age"></label>
<!--
1.設定value的預設值為PHP語法
2.呼叫$row['Age']的值
-->
<input type="text" name="Age" id="Age" value="<?php echo $row['Age']?>" /></td>
</tr>
<tr>
<td colspan="3" align="center">
<!--
1.設定一個隱藏的欄位來抓取personID的值
2.因為上面的PHP程式跑完後不知道要把資料更新到哪裡去，所以這裡設計一個隱藏欄位告訴PHP資料要指向哪個地方更新
-->
<input name="personID" type="hidden" id="personID" value="<?php echo $row ['personID']?>" /> <input type="submit" name="button" id="button" value="送出" /></td>
</tr>
</table>
</form>
</body>
</html>
```
:::
---
<style>
h2 {
color: #2383B8;
}
h3 {
color: #1AA340;
}
h4 {
color: white;
background-color: #2383B8;
padding:8px;
}
.code1 {
padding: 2px 4px;
font-size: 90%;
color: #c7254e;
background-color: #f9f2f4;
border-radius: 4px;
font-family:'Fira Code';
}
.code {
padding: 2px 4px;
font-size: 90%;
font-family:'Fira Code';
}
.code2 {
padding: 2px 4px;
color: #c7254e;
background-color: #f9f2f4;
border-radius: 4px;
font-family:'Fira Code';
}
.text-s {
padding: 2px 4px;
color: #c7254e;
background-color: #F5F5F5;
border-radius: 4px;
font-size: 60%;
font-family:'Fira Code';
}
</style>
### 6.刪除頁面 del.php
1.在刪除頁面寫出語法
2.在index.php設計一個防手滑的小把關JS程式
```php=
<?php
include_once ("sqlConn.php");
$con->select_db("my_db");
$sql="DELETE FROM persons1
WHERE personID=".$_GET['id'];
$con->query($sql);
header("Location:index.php");
?>
```
```php=
echo "<td><a href=\"del.php?id=$row[personID]\" >"
```
![Uploading file..._oc83hjf9m]()
Sign in with Wallet
Connect another wallet