# PHP&MYSQL

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

---

### 1.創建sqlConn
儲存為sqlConn.php(或sqlConn.inc)

<?php
    $servsename = "localhost";
    $username = "admin";
    $password = "123456";

    $con = new mysqli($servsename, $username, $password);

    //Check connection
    if(mysqli_connect_errno()){
        echo "Connection Failed" . $con -> connect_error;
        }
?>

---

### 2.創建資料庫&資料夾
最常用的就是char 與int 了。int (Integer)是用來儲存整數型態，而char 就是用來存放字元、字串的重要型態了
* char 固定大小浪費空間，所需計算時間少。
* varchar 不固定長度，必須要花費較多的CPU計算時間。(動態儲存)
>詳細可參考[SQL：char 與 varchar 欄位型態考量](https://blog.xuite.net/tolarku/blog/29465482-SQL%EF%BC%9Achar+%E8%88%87+varchar+%E6%AC%84%E4%BD%8D%E5%9E%8B%E6%85%8B%E8%80%83%E9%87%8F)

<?php
//1.引用一次sqlconn.php的資料
include_once("sqlConn.php");

//2.創建資料庫，取名叫my_db
if($con ->query("CREATE DATABASE my_db")){
	echo "database created";
	}else{
	echo"Error creating database:" . $con-> error;	
	}

//3.創建資料表(Table)
$con-> select_db("my_db");  //選取資料庫my_db

//創建資料表，取名persons
$sql = "
CREATE TABLE persons   
(
personID int NOT NULL AUTO_INCREMENT,
PRIMARY KEY(personID),
FirstName varchar(15),
LastName varchar(15),
Age int
)";

//Execute query
$con -> query($sql);

$con -> close();	
?>


| SQL語法功能 | 說明 |
| -------- | -------- |
| <span class="code1">int</span> | 整數 |
|<span class="code1">NOT NULL|沒有空白空格</span>|
|<span class="code1">AUTO_INCREMENT</span>|自動編碼(需搭配PRIMARY KEY(id)使用)|
|<span class="code1">query("CREATE DATABASE")</span>|創建資料庫|
|UTF8-unicode-ci|UTF8編碼，減少不必要的亂碼|

---

### 3.建立資料

<?php
    include_once("sqlConn.php");
    $con -> select_db("my_db");
    $con -> query("
    INSERT INTO persons(FirstName, LastName, Age)
            VALUES('Peter','Griffin','35')
            ");
    $con -> query("
    INSERT INTO persons(FirstName, LastName, Age)
            VALUES('Gleen','Quamire','33')
            ");		
    $con -> close();			
?>


| SQL語法功能 | 說明 |
| -------- | -------- |
| <span class="code1">INSERT INTO 資料表名稱(資料1, 資料2, 資料3)VALUES(值1,值2,值3)</span> | 把資料寫入 |
|<span class="code1">$con -> close()</span>|設定$con關閉
|<span class="code1">$con->("SET NAMES utf8")</span>|設定資料表為utf8編碼|

---

### 4.顯示資料

<?php
    //連結sqlConn.php，選擇my_db資料表
    include_once("sqlConn.php"); 
    $con -> select_db("my_db");
    
    //定義$con 資料為選取全部(*)從persons 資料表
    $result = $con -> query("SELECT * FROM persons");

    echo "<table border='1'>
    <tr>
    <th>FirstName</th>
    <th>LastName</th>
    <th>Age</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 "</tr>";
    }
    echo "</table>"
?>

#### SELECT * FROM / WHERE...LIKE
:::info
WHERE...LIKE 抓取資料的用法不需要使用等於= 的符號，數值之間使用單引號''隔開

$result = $con -> query("SELECT * FROM persons
                WHERE FirstName LIKE '%e%' AND Age>'34'");
                //包含e的字元並且Age大於34

:::

### 5.選取資料 更新&刪除
使用UPDATE SET & DELETE FROM
WHERE 的資料屬性要放對，不然會改錯資料或誤刪喲～

###### <span class="code1">↓ UPDATE SET</span>

$result = $con -> query("UPDATE persons SET Age='36'
                    WHERE FirstName ='Peter' AND LastName='Griffin'");	

###### <span class="code1">↓ DELETE FROM</span>

$result = $con -> query("DELETE FROM persons
                    WHERE LastName='Griffin'");		

---
<style>
h2 {
color: #2383F8;
}
h3 {
color: #1AA340;
}
h4 {
color: #FFFFFF;
background-color: #2383F8;
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';
}
</style>