---
# System prepended metadata

title: PHP&MYSQL-2
tags: [PHP, PHP&MYSQL, MYSQL]

---

# PHP&MYSQL-2

###### tags: `PHP&MYSQL` `MYSQL` `PHP`

---
![](https://i.imgur.com/WDt1qAj.png)

### 1.建置基本PHP&MYSQL環境
![](https://i.imgur.com/p64a2ft.png)
![](https://i.imgur.com/1hUewcZ.png)
![](https://i.imgur.com/4T6WLQ8.png)
![](https://i.imgur.com/ZWPf2ra.png)
* 新增資料夾
![](https://i.imgur.com/r7IyiNB.png)


### 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>"
?>
```
>顯示如下:
>![](https://i.imgur.com/wtnKFgA.png)


---

### 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]()
