PHP連接MySQL資料庫
macOS Monterey 12.6
XAMPP 8.1.6
檔案在XAMPP的路徑:htdocs/example/
github
連接
.gitignore 忽略那些不該上傳的 Git 檔案
#conn.php
<?php
$server_name = 'localhost';
$username = 'huang2';
$password = '0000';
$db_name = 'huang2';
$conn = new mysqli($server_name, $username, $password, $db_name);
if($conn->connect_error) {
die('資料庫連線錯誤:' . $conn->connect_error);
}
$conn->query('SET NAMES UTF8');
$conn->query('SET time_zone = "+8:00"');
?>
取值
#index.php
<?php
require_once('conn.php');
$result = $conn->query("select * from users;");
if(!$result){
die($conn->error);
}
while ($row = $result->fetch_assoc()){
echo "id:" . $row['cID'] . '<br>';
echo "username:" . $row['cName'] . '<br>';
}
?>
新增資料
新增一個 apple
#add.php
<?php
require_once('conn.php');
$result = $conn->query("insert into users(
cName) values('apple')");
if(!$result) {
die($conn->error);
}
print_r($result);
?>
input 新增使用者
#index.php
<?php
require_once('conn.php');
$result = $conn->query("select * from users;");
if(!$result){
die($conn->error);
}
while ($row = $result->fetch_assoc()){
echo "id:" . $row['cID'] . '<br>';
echo "username:" . $row['cName'] . '<br>';
}
?>
<h2>新增 user</h2>
<form method="POST" action="add.php">
username: <input name="username"/>
<input type="submit"/>
</form>
後面新增一個表單
action=”add.php”是目標網頁
#add.php
<?php
require_once('conn.php');
if (empty($_POST['username'])){
die('請輸入 username');
}
$username = $_POST['username'];
$sql = sprintf(
"insert into users(cName) values('%s')",
$username
);
//這是抓蟲用的
echo 'SQL:' .$sql . "<br>";
$result = $conn->query($sql);
if (!$result) {
die($conn->error);
}
echo "新增成功!"
?>
sprintf 格式化
先到 index.php
新增 abc
新增成功會跳到add.php=
再跳回 index.php
看一下資料庫內容
資料庫
以上都要手動改網址返回
在底下加個a連結
會方便許多
或是自動跳轉
在 add.php 最後加上一行。
header("Location: index.php");
刪除資料
先加個刪除鍵
echo "<a href='delete.php?id=x'>刪除</a>";
echo "<br>";
#index.php
while ($row = $result->fetch_assoc()){
echo "id:" . $row['cID'] . '<br>';
echo "username:" . $row['cName'] . '<br>';
echo "<a href='delete.php?cID=".$row['cID'] . "'>刪除</a>";
echo "<br>";
}
新增 delete.php 檔案
<?php
require_once('conn.php');
if (empty($_GET['cID'])){
die('刪除失敗,沒有咬到id');
}
$id = $_GET['cID'];
$sql = sprintf(
"delete from users where cID = %d",
$id
);
echo $sql;
$result = $conn->query($sql);
if (!$result) {
die($conn->error);
}
if($conn->affected_rows >= 1) {
echo "<br>";
echo "成功刪除 " . $conn->affected_rows . " 筆資料。";
}else{
echo "查無資料";
}
//header("Location: index.php");
?>
<br>
<a href="index.php">返回 index.php </a>
$conn->affected_rows 會顯示被刪除的筆數的數量
編輯資料
在 index.php 裡面再加入一個表單
#index.php
<?php
require_once('conn.php');
$result = $conn->query("select * from users;");
if(!$result){
die($conn->error);
}
while ($row = $result->fetch_assoc()){
echo "id:" . $row['cID'] . '<br>';
echo "username:" . $row['cName'] . '<br>';
echo "<a href='delete.php?cID=".$row['cID'] . "'>刪除</a>";
echo "<br>";
}
?>
<h2>新增 user</h2>
<form method="POST" action="add.php">
username: <input name="username"/>
<input type="submit"/>
</form>
<h2>編輯資料</h2>
<form method="POST" action="update.php">
id: <input name="cID"/>
username: <input name="cName"/>
<input type="submit"/>
</form>
新增 update.php
<?php
require_once('conn.php');
if (empty($_POST['cID']) || empty($_POST['cName'])){
die('請輸入 id 與 username');
}
$id = $_POST['cID'];
$username = $_POST['cName'];
$sql = sprintf(
"update users set cName='%s' where cID=%d",
$username,
$id
);
//錯誤訊息
$result = $conn->query($sql);
if (!$result) {
die($conn->error);
}
echo "修改成功!";
// header("Location: index.php");
?>
<br>
<a href="index.php">返回 index.php </a>