MySQL Introduction

MySQL是一種relational資料庫，當安裝XAMPP時也同時安裝，可以直接使用。使用前先打開XAMPP Control Panel並點選MySQL的Start。

php6_1_mysqlConnection.php

	<head>
		<title>MySQL Connection</title>
	</head>
	<body>
		<?php
			$link = mysqli_connect("localhost", "root", "");
			//$link = new mysqli("127.0.0.1", "root", "");
			if($link === false){
				die("Error: Could not connect.".mysqli_connect_error());
			}
			echo "Connect successfully.".mysqli_get_host_info($link);
			mysqli_close($link);
		?>
	</body>

使用mysqli_connect("hostname","username", "password", "database")函數連結至資料庫，第一個參數指連結至localhost(也可以使用127.0.0.1)，因為使用預設的設定，所以後續參數使用root且沒有password。
使用mysqli_connect_error()顯示錯誤訊息，mysqli_get_host_info($link)顯示host的資訊。
連結至資料庫可使用以下方式:

mysqli_connect()。
new mysqli()。
使用PDO。
php6_1_PDO.php
<head> <title>MySQL PDO Connection</title> </head> <body> <?php $link = new PDO("mysql:host=127.0.0.1;", "root", ""); if(!$link){ die("Error: Could not connect.".mysqli_connect_error()); } echo "Connect successfully.<br>"; $link = null; echo "Disconnected." ?> </body>

Database

連結資料庫後可以建立一個database，使用sql的CREATE DATABASE語法。
php3_4_time.php

	<head>
		<title>MySQL Create Database</title>
	</head>
	<body>
		<?php
			$link = mysqli_connect("127.0.0.1", "root", "");
			if($link === false){
				die("Error: Could not connect.".mysqli_connect_error());
			}
			$sql = "CREATE DATABASE test";
			if(mysqli_query($link, $sql)){
				echo "Database created successfully.";
			}else{
				echo("Error: Could not execute $sql.".mysqli_error($link));
			}
			mysqli_close($link);
		?>
	</body>

使用mysqli_query()方法查詢資料庫，mysqli_error($link)傳回錯誤。
如果連續執行兩次，第二次會出現錯誤，因為database已存在。
若要刪除database，使用DROP DATABASE test。

Create Table

建立了database之後，可以在其內建立table，用以儲存資料，可使用sql的CREATE TABLE語法。
php6_2_createTable.php

	<head>
		<title>MySQL Create Table</title>
	</head>
	<body>
		<?php
			$link = mysqli_connect("127.0.0.1", "root", "", "test");
			if($link === false){
				die("Error: Could not connect.".mysqli_connect_error());
			}
			$sql = "CREATE TABLE characters(
				id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
				name VARCHAR(50) NOT NULL UNIQUE,
				race VARCHAR(50) NOT NULL,
				occupation VARCHAR(50) NOT NULL,
				level INT NOT NULL
			)";
			//$sql = "DROP TABLE characters";
			if(mysqli_query($link, $sql)){
				echo "Table created successfully.";
			}else{
				echo("Error: Could not execute $sql.".mysqli_error($link));
			}
			mysqli_close($link);
		?>
	</body>

因為要建立的table位於test(已建立)這個database內，所以在connect的時候參數要加上database(test)的名稱。
sql CREATE TABLE語法:

在CREATE TABLE後加上table的名稱(charaters)。
id為整數(int)，不能為空，為primary key且會自動增加。
name, race為字元，括號內數字為長度，name為唯一(unique)。

若要刪除table，使用DROP TABLE characters語法。

Insert

有了table之後便可以開始加入資料，使用sql的INSERT INTO語法。

php6_3_insert.php

	<head>
		<title>MySQL Create Database</title>
	</head>
	<body>
		<?php
			$link = mysqli_connect("127.0.0.1", "root", "", "test");
			if($link === false){
				die("Error: Could not connect.".mysqli_connect_error());
			}
			$sql = "INSERT INTO characters(name, race, occupation, level) VALUES ('Tom', 'Human', 'Warrior', 1)";
			if(mysqli_query($link, $sql)){
				echo "Database created successfully.";
			}else{
				echo("Error: Could not execute $sql.".mysqli_error($link));
			}
			$sql = "INSERT INTO characters(name, race, occupation, level) VALUES ('John', 'Dwarf', 'Hunter', 2),('Mary', 'Elf', 'Mage', 5),('Lance', 'Human', 'Priest', 7)";
			if(mysqli_query($link, $sql)){
				echo "Database created successfully.";
			}else{
				echo("Error: Could not execute $sql.".mysqli_error($link));
			}
			$sql = "SELECT * FROM characters";
			if($result = mysqli_query($link, $sql)){
				if(mysqli_num_rows($result) > 0){
					echo "<table><tr>";
						echo "<th>Id</th><th>Name</th><th>Race</th><th>Occupation</th><th>Level</th>";
					echo "</tr>";
					while($row = mysqli_fetch_array($result)){
						echo "<tr>";
							echo "<td>".$row['id']."</td>";
							echo "<td>".$row['name']."</td>";
							echo "<td>".$row['race']."</td>";
							echo "<td>".$row['occupation']."</td>";
							echo "<td>".$row['level']."</td>";
						echo "</tr>";
					}
					echo "</table>";
					mysqli_free_result($result);//Free result set
				}else{
					echo "No records matched.";
				}
			}else{
				echo "Error: Could not execute $sql.".mysqli_error($link);
			}
			mysqli_close($link);
		?>
	</body>

使用INSERT INTO tablename(attributes) VALUES(values)來加入一筆資料，若是VALUES(values),(values),(values)可加入多筆資料。
因為id設定為AUTO_INCREMENT，所以不需要寫入，會自動產生。
為了顯示內容資料，使用SELECT * FROM characters語法來選擇所有資料。

mysqli_num_rows($result)可取得行數。
mysqli_fetch_array($result)可取得一筆資料，且該資料儲存為array，並將pointer移往下一筆資料。亦可使用$row[0],$row[1],$row[2]等來取得資料。
mysqli_free_result($result)可釋放$result的記憶體。

INSERT FORM

當然希望可以從網頁讓使用者輸入資料然後儲存到資料庫，所以需要設計輸入表單。
php6_3_insertForm.php

	<head>
		<title>MySQL Insert Form</title>
	</head>
	<body>
		<form action="php6_3_formInsertion.php" method = "post">
			<p>
				<label for="name">Name:</label>
				<input type="text" name="name" id="name">
			</p>
			<p>
				<label for="race">Race:</label>
				<input type="text" name="race" id="race">
			</p>
			<p>
				<label for="name">Occupation:</label>
				<input type="text" name="occupation" id="occupation">
			</p>
			<p>
				<label for="name">Level:</label>
				<input type="text" name="level" id="level">
			</p>
			<input type="submit" value="Add">
		</form>
	</body>

然後設計對應的php檔案來處理輸入。

php6_3_formInsertion.php

	<?php
		$link = mysqli_connect("127.0.0.1", "root", "", "test");
		if($link === false){
			die("Error: Could not connect.".mysqli_connect_error());
		}
		$name = mysqli_real_escape_string($link, $_REQUEST['name']);
		$race = mysqli_real_escape_string($link, $_REQUEST['race']);
		$occupation = mysqli_real_escape_string($link, $_REQUEST['occupation']);
		$level = mysqli_real_escape_string($link, $_REQUEST['level']);
		//echo "$name  $race  $occupation $level<br>";
		$sql = "INSERT INTO characters(name, race, occupation, level) VALUES ('$name', '$race', '$occupation', '$level')";
		if(mysqli_query($link, $sql)){
			$last_id = mysqli_insert_id($link);
			echo "Record inserted successfully and inserted ID is ".$last_id;
		}else{
			echo("Error: Could not execute $sql.".mysqli_error($link));
		}
		
		$sql = "SELECT * FROM characters";
		if($result = mysqli_query($link, $sql)){
			if(mysqli_num_rows($result) > 0){
				echo "<table style='border:1px solid gold;'><tr>";
					echo "<th style='border:1px solid red;'>Id</th><th style='border:1px solid red;'>Name</th><th style='border:1px solid red;'>Race</th><th style='border:1px solid red;'>Occupation</th><th style='border:1px solid red;'>Level</th>";
				echo "</tr>";
				while($row = mysqli_fetch_array($result)){
					echo "<tr style='border:1px solid red;'>";
						echo "<td  style='border:1px solid blue;'>".$row['id']."</td>";
						echo "<td  style='border:1px solid blue;'>".$row['name']."</td>";
						echo "<td  style='border:1px solid blue;'>".$row['race']."</td>";
						echo "<td  style='border:1px solid blue;'>".$row['occupation']."</td>";
						echo "<td  style='border:1px solid blue;'>".$row['level']."</td>";
					echo "</tr>";
				}
				echo "</table>";
				mysqli_free_result($result);//Free result set
			}else{
				echo "No records matched.";
			}
		}else{
			echo "Error: Could not execute $sql.".mysqli_error($link);
		}
		mysqli_close($link);
	?>

mysqli_real_escape_string()函數可產生可使用於sql語法的合法sql字串，儘管如此，在VALUES中還是需要使用引號('$name', '$race', '$occupation', '$level')。
使用mysqli_insert_id($link)方法取得輸入資料的ID，可協助我們追蹤資訊或做後續用途。

Prepared Statement

Prepared Statement是先將要輸入的資料格式準備好，然後再根據格式加入資料的方式。
php6_3_prepared.php

	<?php
		$link = mysqli_connect("127.0.0.1", "root", "", "test");
		if($link === false){
			die("Error: Could not connect.".mysqli_connect_error());
		}
		
		//echo "$name  $race  $occupation $level<br>";
		$sql = "INSERT INTO characters(name, race, occupation, level) VALUES (?, ?, ?, ?)";
		if($stmt = mysqli_prepare($link, $sql)){
			mysqli_stmt_bind_param($stmt, "sssi", $name, $race, $occupation, $level);
			$name = $_REQUEST['name'];
			$race = $_REQUEST['race'];
			$occupation = $_REQUEST['occupation'];
			$level = $_REQUEST['level'];
			
			if(mysqli_stmt_execute($stmt)){
				echo "Insert successfully";
			}else{
				echo "Error: Could not execute query: $sql. ". mysqli_error($link);
			}
		}else{
			echo("Error: Could not prepare query: $sql. ".mysqli_error($link));
		}
		// to show all the data in the table characters
		$sql = "SELECT * FROM characters";
		if($result = mysqli_query($link, $sql)){
			if(mysqli_num_rows($result) > 0){
				echo "<table style='border:1px solid gold;'><tr>";
					echo "<th style='border:1px solid red;'>Id</th><th style='border:1px solid red;'>Name</th><th style='border:1px solid red;'>Race</th><th style='border:1px solid red;'>Occupation</th><th style='border:1px solid red;'>Level</th>";
				echo "</tr>";
				while($row = mysqli_fetch_array($result)){
					echo "<tr style='border:1px solid red;'>";
						echo "<td  style='border:1px solid blue;'>".$row['id']."</td>";
						echo "<td  style='border:1px solid blue;'>".$row['name']."</td>";
						echo "<td  style='border:1px solid blue;'>".$row['race']."</td>";
						echo "<td  style='border:1px solid blue;'>".$row['occupation']."</td>";
						echo "<td  style='border:1px solid blue;'>".$row['level']."</td>";
					echo "</tr>";
				}
				echo "</table>";
				mysqli_free_result($result);//Free result set
			}else{
				echo "No records matched.";
			}
		}else{
			echo "Error: Could not execute $sql.".mysqli_error($link);
		}
		mysqli_close($link);
	?>

一樣使用php6_3_insertForm.php所產生的表格來輸入要增加的資料，要記得修改action="php6_3_prepared.php"，因為此次要使用另一個方式來insert輸入的資料。
Prepared Statment的sql語法中，VALUES的值皆設為?，再在之後加入。
使用mysqli_prepare($link, $query)來準備SQL語法並等著被執行。
使用mysqli_stmt_bind_param($stmt, $type, $var)來連結變數與一個prepared statement。
$type字串用來指稱每個變數的型態。

i: integer。
d: double。
s: string。
b: binary(image, PDF file, etc.)。

所以"sssi"的意思便是前三個輸入為字串，最後一個為整數。
使用mysqli_stmt_execute($stmt)來執行準備好的query($stmt)，成功時會將資料insert到資料庫。

Select, Where, Limit & Order by

之前已經使用過SELECT * FROM characters的sql語法來選擇table characters內的所有資料，使用關鍵字SELECT選擇，*號表示所有。若是要使用其他條件來篩選資料，可使用關鍵字WHERE，若是要限制產生數量，則使用關鍵字LIMIT。
php6_5_select.php

	<?php
		$link = mysqli_connect("127.0.0.1", "root", "", "test");
		if($link === false){
			die("Error: Could not connect.".mysqli_connect_error());
		}
			
		$sql = "SELECT * FROM characters WHERE race='Human' ORDER BY level DESC LIMIT 1, 3 ";
		if($result = mysqli_query($link, $sql)){
			if(mysqli_num_rows($result) > 0){
				echo "<table style='border:1px solid gold;'><tr>";
					echo "<th style='border:1px solid red;'>Id</th><th style='border:1px solid red;'>Name</th><th style='border:1px solid red;'>Race</th><th style='border:1px solid red;'>Occupation</th><th style='border:1px solid red;'>Level</th>";
				echo "</tr>";
				while($row = mysqli_fetch_array($result)){
					echo "<tr style='border:1px solid red;'>";
						echo "<td  style='border:1px solid blue;'>".$row['id']."</td>";
						echo "<td  style='border:1px solid blue;'>".$row['name']."</td>";
						echo "<td  style='border:1px solid blue;'>".$row['race']."</td>";
						echo "<td  style='border:1px solid blue;'>".$row['occupation']."</td>";
						echo "<td  style='border:1px solid blue;'>".$row['level']."</td>";
					echo "</tr>";
				}
				echo "</table>";
				mysqli_free_result($result);//Free result set
			}else{
				echo "No records matched.";
			}
		}else{
			echo "Error: Could not execute $sql.".mysqli_error($link);
		}
		mysqli_close($link);
	?>

WHERE, ORDER BY跟LIMIT不需同時出現，但出現順序須為WHERE, ORDER BY, LIMIT。
ORDER BY可使用ASC(升冪)或DESC(降冪)修飾。
LIMIT 1, 3表示1,2,3三筆資料，從0開始算起，若是僅有一個數字(e.g. LIMIT 3)，表示限定前3筆資料。

And & Or

php6_5_selectAndOr.php

	<?php
		$link = mysqli_connect("127.0.0.1", "root", "", "test");
		if($link === false){
			die("Error: Could not connect.".mysqli_connect_error());
		}
		//$sql = "SELECT * FROM characters WHERE race='Human' OR occupation = 'Warrior' OR level < 10" ;
		$sql = "SELECT * FROM characters WHERE occupation IN ('Warrior','Mage','Priest') AND level BETWEEN 6 AND 10" ;//name BETWEEN 'A' AND 'Z'
		if($result = mysqli_query($link, $sql)){
			if(mysqli_num_rows($result) > 0){
				echo "<table style='border:1px solid gold;'>";
					echo "<tr>";
						echo "<th style='border:1px solid red;'>Id</th>";
						echo "<th style='border:1px solid red;'>Name</th>";
						echo "<th style='border:1px solid red;'>Race</th>";
						echo "<th style='border:1px solid red;'>Occupation</th>";
						echo "<th style='border:1px solid red;'>Level</th>";
					echo "</tr>";
				while($row = mysqli_fetch_array($result)){
					echo "<tr style='border:1px solid red;'>";
						echo "<td  style='border:1px solid blue;'>".$row['id']."</td>";
						echo "<td  style='border:1px solid blue;'>".$row['name']."</td>";
						echo "<td  style='border:1px solid blue;'>".$row['race']."</td>";
						echo "<td  style='border:1px solid blue;'>".$row['occupation']."</td>";
						echo "<td  style='border:1px solid blue;'>".$row['level']."</td>";
					echo "</tr>";
				}
				echo "</table>";
				mysqli_free_result($result);//Free result set
			}else{
				echo "No records matched.";
			}
		}else{
			echo "Error: Could not execute $sql.".mysqli_error($link);
		}
		mysqli_close($link);
	?>

使用AND與OR關鍵字來產生邏輯性選擇。
使用IN關鍵字來選擇某些特定數值，使用BETWEEN關鍵字來選擇某範圍內的值。

Update & Delete

使用UPDATE關鍵字來更新資料，使用DELETE關鍵字來刪除資料。

UPDATE

php6_6_update.php

	<?php
		$link = mysqli_connect("127.0.0.1", "root", "", "test");
		if($link === false){
			die("Error: Could not connect.".mysqli_connect_error());
		}
			
		$sql = "UPDATE characters SET occupation='Priest' WHERE name='Alex'";
		if(mysqli_query($link, $sql)){
			echo "Updated successfully";
		}else{
			echo "Error: Could not execute $sql. ".mysqli_error($link);
		}
		
		$sql = "SELECT * FROM characters ORDER BY occupation";
		if($result = mysqli_query($link, $sql)){
			if(mysqli_num_rows($result) > 0){
				echo "<table style='border:1px solid gold;'>";
				echo "<tr>";
					echo "<th style='border:1px solid red;'>Id</th>";
					echo "<th style='border:1px solid red;'>Name</th>";
					echo "<th style='border:1px solid red;'>Race</th>";
					echo "<th style='border:1px solid red;'>Occupation</th>";
					echo "<th style='border:1px solid red;'>Level</th>";
				echo "</tr>";
				while($row = mysqli_fetch_array($result)){
					echo "<tr style='border:1px solid red;'>";
						echo "<td  style='border:1px solid blue;'>".$row['id']."</td>";
						echo "<td  style='border:1px solid blue;'>".$row['name']."</td>";
						echo "<td  style='border:1px solid blue;'>".$row['race']."</td>";
						echo "<td  style='border:1px solid blue;'>".$row['occupation']."</td>";
						echo "<td  style='border:1px solid blue;'>".$row['level']."</td>";
					echo "</tr>";
				}
				echo "</table>";
				mysqli_free_result($result);//Free result set
			}else{
				echo "No records matched.";
			}
		}else{
			echo "Error: Could not execute $sql.".mysqli_error($link);
		}
		mysqli_close($link);
	?>

使用SET關鍵字設定某欄位的值，使用WHERE來選擇被修改的資料。

DELETE

php6_6_delete.php

	<?php
		$link = mysqli_connect("127.0.0.1", "root", "", "test");
		if($link === false){
			die("Error: Could not connect.".mysqli_connect_error());
		}
			
		$sql = "DELETE FROM characters WHERE id=11";
		if(mysqli_query($link, $sql)){
			echo "Delete data successfully";
		}else{
			echo "Error: Could not execute $sql. ".mysqli_error($link);
		}
		
		$sql = "SELECT * FROM characters";
		if($result = mysqli_query($link, $sql)){
			if(mysqli_num_rows($result) > 0){
				echo "<table style='border:1px solid gold;'>";
					echo "<tr>";
						echo "<th style='border:1px solid red;'>Id</th>";
						echo "<th style='border:1px solid red;'>Name</th>";
						echo "<th style='border:1px solid red;'>Race</th>";
						echo "<th style='border:1px solid red;'>Occupation</th>";
						echo "<th style='border:1px solid red;'>Level</th>";
					echo "</tr>";
				while($row = mysqli_fetch_array($result)){
					echo "<tr style='border:1px solid red;'>";
						echo "<td  style='border:1px solid blue;'>".$row['id']."</td>";
						echo "<td  style='border:1px solid blue;'>".$row['name']."</td>";
						echo "<td  style='border:1px solid blue;'>".$row['race']."</td>";
						echo "<td  style='border:1px solid blue;'>".$row['occupation']."</td>";
						echo "<td  style='border:1px solid blue;'>".$row['level']."</td>";
					echo "</tr>";
				}
				echo "</table>";
				mysqli_free_result($result);//Free result set
			}else{
				echo "No records matched.";
			}
		}else{
			echo "Error: Could not execute $sql.".mysqli_error($link);
		}
		mysqli_close($link);
	?>

使用DELETE FROM characters WHERE id=11語法來刪除，其中WHERE用來選擇欲刪除之資料。
再次提醒若要刪除整個TABLE或是DATABASE，使用DROP關鍵字。

Alter

若是要改變欄位，可以使用關鍵字ALTER。
php6_6_alter.php

	<?php
		$link = mysqli_connect("127.0.0.1", "root", "", "test");
		if($link === false){
			die("Error: Could not connect.".mysqli_connect_error());
		}
		$sql = "ALTER TABLE characters ADD camp VARCHAR(30) DEFAULT 'Alliance'" ;
		if(mysqli_query($link, $sql)){
			echo "Alter successfully";
		}else{
			echo "Could not alter.";
		}
		$sql = "SELECT * FROM characters" ;
		if($result = mysqli_query($link, $sql)){
			if(mysqli_num_rows($result) > 0){
				echo "<table style='border:1px solid gold;'>";
					echo "<tr>";
						echo "<th style='border:1px solid red;'>Id</th>";
						echo "<th style='border:1px solid red;'>Name</th>";
						echo "<th style='border:1px solid red;'>Race</th>";
						echo "<th style='border:1px solid red;'>Occupation</th>";
						echo "<th style='border:1px solid red;'>Level</th>";
						echo "<th style='border:1px solid red;'>Camp</th>";
					echo "</tr>";
				while($row = mysqli_fetch_array($result)){
					echo "<tr style='border:1px solid red;'>";
						echo "<td  style='border:1px solid blue;'>".$row['id']."</td>";
						echo "<td  style='border:1px solid blue;'>".$row['name']."</td>";
						echo "<td  style='border:1px solid blue;'>".$row['race']."</td>";
						echo "<td  style='border:1px solid blue;'>".$row['occupation']."</td>";
						echo "<td  style='border:1px solid blue;'>".$row['level']."</td>";
						echo "<td  style='border:1px solid blue;'>".$row['camp']."</td>";
					echo "</tr>";
				}
				echo "</table>";
				mysqli_free_result($result);//Free result set
			}else{
				echo "No records matched.";
			}
		}else{
			echo "Error: Could not execute $sql.".mysqli_error($link);
		}
		mysqli_close($link);
	?>

使用ADD關鍵字來增加欄位。
使用DEFAULT關鍵字來給初始值。

只在此山中，雲深不知處

聽首歌