(1) 先建立新的資料庫 (紅色部分依你的需求更改)
建立資料庫，名稱為newdbname
並且使用字元集為 utf8，排序規則是 utf8_general_ci (不分大小寫)。
參考資料:
如何使用指令知道資料庫的字元集和排序規則?
MySQL CHARACTER SET 與 COLLATION
create database newdbname
character set utf8
collate utf8_general_ci;
(2) 開始使用newdbname這個資料庫。
use newdbname;
(3) 建立資料表單 customer
CREATE TABLE customer (
cus_id int NOT NULL,
cus_name varchar(255) NOT NULL,
cus_address varchar(255),
cus_no char(3),
PRIMARY KEY (cus_id) );
參考資料(Create):
https://www.mysql.tw/2013/03/sql-commands-of-ddl-data-definition.html
https://www.mysql.tw/2014/05/mysql-ddl-data-definition-language.html
參考資料(Data type):
https://www.mysql.tw/2017/03/mysql-data-types.html
https://www.mysql.tw/2013/04/mysqltime-datetime-timestamp.html
參考資料(Keys):
https://www.mysql.tw/2013/03/primaryuniqueindex.html
參考資料(Other SQL):
https://www.mysql.tw/2014/05/sql-select.html
https://www.mysql.tw/2013/03/sqlddldmldcltcl.html
(4) 建立資料表單 product
CREATE TABLE product (
prod_id int NOT NULL,
prod_name varchar(255) NOT NULL,
prod_price int,
prod_inventory int,
prod_no char(3),
PRIMARY KEY (prod_id) );
(5) 建立資料表單 order_head
CREATE TABLE order_head (
ord_id int NOT NULL,
ord_cus_id int,
ord_date char(10),
ord_date_delivery char(10),
PRIMARY KEY (ord_id) );
(6) 建立資料表單 order_body
CREATE TABLE order_body (
ordb_id int NOT NULL,
ordb_ord_id int,
ordb_prod_id int,
ordb_amount int,
PRIMARY KEY (ordb_id) );
(7) 測試與PHP連接，建立一個檔案存成test_connect.php，內容如下:
以下是只適用於EasyPHP 14.1VC9的版本
<?php
//接上資料庫
$link = mysql_connect("localhost", "root", "") or die("Could not connect : " . mysql_error());
//選擇資料庫
mysql_select_db("mysql") or die("Could not select database");
//處理SQL
$query = "SELECT * FROM user";
$result = mysql_query($query) or die("Query failed : " . mysql_error());
//關閉連線
mysql_close($link);
?>
以下是適用於EasyPHP 14.1VC9 及14.1VC11的版本
<?php
//接上資料庫
$link = mysqli_connect("localhost", "root", "", "newdbname");
//檢查連線
if (!$link) {
die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully";
//關閉連線
mysqli_close($link);
?>
<html>
<head>
<meta content="text/html;charset=UTF-8"
http-equiv="Content-Type">
<title>Test Customer</title>
</head>
<body>
<form action="cus.php" method="post" name="form1">
Cus_id <input name="cus_id" type="text" value=""><BR>
Cus_name <input name="cus_name" type="text" value=""><BR>
Cus_address <input name="cus_address" type="text" value=""><BR>
<input type="submit" value="send">
</form>
</body></html>
(9) 再建立一個 cus.php，內容如下:
以下是只適用於EasyPHP 14.1VC9的版本
<html>
<head>
<meta content="text/html;charset=UTF-8"
http-equiv="Content-Type">
<title>Process SQL - Customer INPUT</title>
</head>
<body>
<?php
if(count($_POST)>0){
$cus_id=$_POST["cus_id"];
$cus_name=$_POST["cus_name"];
$cus_address=$_POST["cus_address"];
//接上資料庫
$link = mysql_connect("localhost", "root", "") or die("Could not connect : " . mysql_error());
//選擇資料庫
mysql_select_db("newdbname") or die("Could not select database");
//處理SQL
$query = "INSERT INTO customer (cus_id, cus_name,cus_address) VALUES ($cus_id, '$cus_name','$cus_address')";
$result = mysql_query($query) or die("Query failed : " . mysql_error());
echo "SQL: ".$query;
//關閉連線
mysql_close($link);
} else {
echo "no data";
}
?>
</body>
</html>
以下是適用於EasyPHP 14.1VC9 及14.1VC11的版本
<html>
<head>
<meta content="text/html;charset=UTF-8"
http-equiv="Content-Type">
<title>Process SQL - Customer INPUT</title>
</head>
<body>
<?php
if(count($_POST)>0){
$cus_id=$_POST["cus_id"];
$cus_name=$_POST["cus_name"];
$cus_address=$_POST["cus_address"];
$con=mysqli_connect("localhost","root","","newdbname");
// Check connection
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
// Perform queries
$query = "INSERT INTO customer (cus_id, cus_name,cus_address) VALUES ($cus_id, '$cus_name','$cus_address')";
mysqli_query($con,$query) or die(mysqli_error($con));
echo "SQL: ".$query;
mysqli_close($con);
} else {
echo "no data";
}
?>
</body>
</html>
(11) 以下是showcus.php，用來執行SELECT
以下是只適用於EasyPHP 14.1VC9的版本
<html>
<head>
<meta content="text/html;charset=UTF-8"
http-equiv="Content-Type">
<title>Process SQL - Customer show</title>
</head>
<body>
<?php
//接上資料庫
$link = mysql_connect("localhost", "root", "") or die("Could not connect : " . mysql_error());
//選擇資料庫
mysql_select_db("newdbname") or die("Could not select database");
//處理SQL
$query = "SELECT * FROM customer";
$result = mysql_query($query) or die("Query failed : " . mysql_error());
if(!$result) {
die("Database query failed: " . mysql_error());
}
while ($row = mysql_fetch_array($result)) {
echo $row["cus_name"]."<BR>";
}
echo "<P>SQL: ".$query;
//關閉連線
mysql_close($link);
?>
</body>
</html>
以下是適用於EasyPHP 14.1VC9 及14.1VC11的版本
<html>
<head>
<meta content="text/html;charset=UTF-8"
http-equiv="Content-Type">
<title>Process SQL - Customer show</title>
</head>
<body>
<?php
$link = mysqli_connect("localhost", "root", "", "newdbname");
/* check connection */
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
$query = "SELECT * FROM customer";
if ($result = mysqli_query($link, $query)) {
/* fetch associative array */
while ($row = mysqli_fetch_row($result)) {
printf ("%s ~ %s \n", $row[0], $row[1]);
}
/* free result set */
mysqli_free_result($result);
}
/* close connection */
mysqli_close($link);
?>
</body>
</html>
0 留言