-
-
Save summer10920/94a9b3385ed7940b88ccc44e3b36a11e to your computer and use it in GitHub Desktop.
jQuery Ajax 練習 (整合後端 php 撈取 mysql )
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<?php | |
$db = new PDO("mysql:host=127.0.0.1;dbname=jq_sample;charset=utf8", "test", "test"); | |
date_default_timezone_set('asia/Taipei'); | |
// 取得 PDO 物件,另外順便校正 PHP 時差 | |
// 這裡用 switch 是因為還有其他 Ajax 提交,因此利用 GET 來做區分判斷處理。 | |
switch ($_GET['do']) { | |
case 'select': | |
$sql = "SELECT * FROM ajax_animal limit " . $_POST['start'] . ",10"; | |
$rows = $db->query($sql)->fetchAll(); | |
// print_r($_POST); | |
// print_r($rows); | |
if($rows){ | |
foreach ($rows as $row) { | |
echo ' | |
<tr> | |
<td>' . $row['id'] . '</td> | |
<td class="name">' . $row['name'] . '</td> | |
<td>' . $row['weight'] . '</td> | |
<td>' . $row['info'] . '</td> | |
<td>' . $row['date'] . '</td> | |
<td> | |
<button class="mdy">修改</button> | |
<button onclick="del(this)">刪除</button> | |
</td> | |
</tr> | |
'; | |
} | |
}else echo 'fail'; | |
// SQL 內取得所有動物資料,由 foreach 規劃完整 tr>td,使前端單純 HTML 替換即可。 | |
break; | |
case 'update': | |
$sql = "UPDATE ajax_animal SET name='" . $_POST['name'] . "',weight='" . $_POST['weight'] . "',info='" . $_POST['info'] . "',date=NOW() WHERE id=" . $_POST['id']; | |
$result = $db->query($sql); | |
// 成功時,我們 HTML 生成所需要的更新日期之文字,透過 Ajax 回傳給前端 | |
if ($result) echo date("Y-m-d H:i:s"); | |
// if($result) echo "OK"; | |
break; | |
case 'delete': | |
$sql = "DELETE FROM ajax_animal WHERE id=" . $_POST['id']; | |
$result = $db->query($sql); | |
if ($result) echo "deleted"; | |
break; | |
case 'insert': | |
$sql = "INSERT INTO ajax_animal VALUES(null,'" . $_POST['name'] . "','" . $_POST['weight'] . "','" . $_POST['info'] . "',NOW())"; | |
$result = $db->query($sql); | |
if ($result) echo "inserted"; | |
break; | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- phpMyAdmin SQL Dump | |
-- version 4.8.5 | |
-- https://www.phpmyadmin.net/ | |
-- | |
-- 主機: 127.0.0.1 | |
-- 產生時間: | |
-- 伺服器版本: 10.1.40-MariaDB | |
-- PHP 版本: 7.3.5 | |
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; | |
SET AUTOCOMMIT = 0; | |
START TRANSACTION; | |
SET time_zone = "+00:00"; | |
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; | |
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; | |
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; | |
/*!40101 SET NAMES utf8mb4 */; | |
-- | |
-- 資料庫: `jq_sample` | |
-- | |
CREATE DATABASE IF NOT EXISTS `jq_sample` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; | |
USE `jq_sample`; | |
-- | |
-- 資料表結構 `ajax_animal` | |
-- | |
CREATE TABLE `ajax_animal` ( | |
`id` smallint(5) UNSIGNED NOT NULL, | |
`name` text COLLATE utf8mb4_unicode_ci NOT NULL, | |
`weight` int(11) NOT NULL, | |
`info` text COLLATE utf8mb4_unicode_ci NOT NULL, | |
`date` datetime NOT NULL | |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; | |
-- | |
-- 傾印資料表的資料 `ajax_animal` | |
-- | |
INSERT INTO `ajax_animal` (`id`, `name`, `weight`, `info`, `date`) VALUES | |
(1, '藪貓', 9, '食肉目 貓科 藪貓屬', '2020-12-20 08:21:08'), | |
(2, '耳廓狐', 17, '食肉目 犬科 狐屬', '2020-12-19 15:57:56'), | |
(3, '河馬', 120, '鯨偶蹄目 河馬科 河馬屬', '2020-11-07 08:52:06'), | |
(4, '印度象', 1258, '長鼻目 象科 象屬', '2020-11-07 08:52:06'), | |
(5, '浣熊', 30, '食肉目 浣熊科 浣熊屬', '2020-11-07 09:13:58'), | |
(6, '斑馬', 53, '奇蹄目 馬科 馬屬', '2020-11-07 08:52:06'), | |
(7, '瞪羚', 32, '鯨偶蹄目 牛科 瞪羚屬', '2020-11-07 08:52:06'), | |
(8, '土狼', 32, '食肉目 鬣狗科 土狼屬', '2020-11-07 08:52:06'), | |
(9, '水獺', 32, '食肉目 鼬科 小爪水獺屬', '2020-11-07 08:52:06'), | |
(10, '美洲豹', 999999, '食肉目 貓科 豹屬', '2020-11-07 08:52:06'), | |
(11, '山貓', 999999999, '食肉目 貓科 虎貓屬', '2020-11-07 08:52:06'), | |
(12, '馬來貘', 80, '奇蹄目 貘科 貘屬', '2020-11-07 09:13:33'), | |
(13, '馬島獴', 17, '食肉目 食蟻狸科 馬島獴屬', '2020-11-07 08:52:06'), | |
(14, '花鹿', 120, '鯨偶蹄目 鹿科 花鹿屬', '2020-11-07 08:52:06'), | |
(15, '眼鏡王蛇', 1258, '有鱗目 眼鏡蛇科 眼鏡王蛇屬', '2020-11-07 08:52:06'), | |
(16, '食蟻獸', 40, '披毛目 食蟻獸科 小食蟻獸屬', '2020-11-07 09:13:58'), | |
(17, '孔雀', 532, '雞形目 雉科 孔雀屬', '2020-11-07 10:54:58'), | |
(18, '袋獾', 32, '袋鼬目 袋鼬科 袋獾屬', '2020-11-07 10:55:05'), | |
(19, '傘蜥蜴', 555, '有鱗目 飛蜥科 傘蜥蜴屬', '2020-11-07 10:55:26'), | |
(20, '朱䴉', 32, '鵜形目 䴉科 朱䴉屬', '2020-11-07 08:52:06'), | |
(21, '羊駝', 999999, '鯨偶蹄目 駱駝科 小羊駝屬', '2020-11-07 08:52:06'), | |
(22, '美洲紅䴉', 55, '鵜形目 䴉科 美洲䴉屬', '2020-11-07 09:22:04'), | |
(23, '美洲河狸', 55, '嚙齒目 河狸科 河狸屬', '2020-11-07 09:24:31'), | |
(24, '黑尾土撥鼠', 999999999, '嚙齒目 松鼠科 草原犬鼠屬', '2020-11-07 08:52:06'), | |
(25, '獅子', 55, '食肉目 貓科 豹屬', '2020-12-20 09:38:19'), | |
(26, '原牛', 120, '鯨偶蹄目 牛科 牛屬', '2020-12-20 09:39:03'), | |
(27, '阿拉伯大羚羊', 2223, '鯨偶蹄目 牛科 長角羚屬', '2020-12-20 09:40:53'), | |
(28, '日本黑熊', 222, '食肉目 熊科 熊屬', '2020-12-20 09:49:00'), | |
(29, '駝鹿', 22, '鯨偶蹄目 鹿科 駝鹿屬', '2020-12-20 09:50:23'); | |
-- | |
-- 已傾印資料表的索引 | |
-- | |
-- | |
-- 資料表索引 `ajax_animal` | |
-- | |
ALTER TABLE `ajax_animal` | |
ADD PRIMARY KEY (`id`); | |
-- | |
-- 在傾印的資料表使用自動增長(AUTO_INCREMENT) | |
-- | |
-- | |
-- 使用資料表自動增長(AUTO_INCREMENT) `ajax_animal` | |
-- | |
ALTER TABLE `ajax_animal` | |
MODIFY `id` smallint(5) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=30; | |
COMMIT; | |
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; | |
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; | |
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<!DOCTYPE html> | |
<html lang="en"> | |
<head> | |
<meta charset="UTF-8"> | |
<meta name="viewport" content="width=device-width, initial-scale=1.0"> | |
<title>jqajax-animal</title> | |
<script src="https://code.jquery.com/jquery-3.5.0.min.js"></script> | |
<style> | |
.insertzone { | |
position: fixed; | |
background: #333333AA; | |
width: 100%; | |
height: 100%; | |
left: 0; | |
top: 0; | |
display: flex; | |
justify-content: center; | |
align-items: center; | |
flex-basis: 50%; | |
color: white; | |
font-weight: bolder; | |
text-align: center; | |
} | |
.insertzone>form { | |
width: 100%; | |
} | |
</style> | |
</head> | |
<body> | |
<table width="100%"> | |
<thead> | |
<tr> | |
<td>編號</td> | |
<td>動物名</td> | |
<td>重量</td> | |
<td>簡介</td> | |
<td>更新日期</td> | |
<td>操作</td> | |
</tr> | |
<tr> | |
<td colspan="6"> | |
<hr> | |
</td> | |
</tr> | |
</thead> | |
<tbody> | |
</tbody> | |
<tfoot> | |
<tr> | |
<td colspan="6" style="text-align: center;padding:10px"> | |
<button onclick="activeForm()">新增</button> | |
<button onclick="loading()">加載</button> | |
</td> | |
</tr> | |
</tfoot> | |
</table> | |
<div class="insertzone" style="display: none;"> | |
<!-- | |
這裡不先寫好HTML是因為我們網頁不會重整,因此第二次進行新增時HTML子元素需要清掉 | |
因此直接由JavaScript來設計較適宜 | |
--> | |
</div> | |
<script> | |
/*select*/ | |
let start = 0; | |
function loading() { | |
$.post("api.php?do=select", { start }, function (result) { | |
if (result != "fail") { | |
$("tbody").append(result); | |
$(".mdy").click(chginput);//因為是後來生成的 HTML,你必須重新使 DOM 路徑被認識(或者走 HTML 的 onclick 比較快) | |
start += 10; | |
} | |
}); | |
} | |
loading(); | |
/*update before DOM transform*/ | |
function chginput() { | |
let item = $(this).parent().siblings(); | |
item.parent().html(` | |
<td><input type="hidden" name="id" value="${item.eq(0).text()}">${item.eq(0).text()}</td> | |
<td><input type="text" name="name" value="${item.eq(1).text()}"></td> | |
<td><input type="text" name="weight" value="${item.eq(2).text()}"></td> | |
<td><input type="text" name="info" value="${item.eq(3).text()}"></td> | |
<td>${item.eq(4).text()}</td> | |
<td> | |
<button onclick="chgtxt(this)">儲存</button> | |
</td> | |
`); | |
//像這裡就直接指定 onclick,否則你必須要在宣告一次 click | |
//HTML的onclick不像js event事件能自身帶this,所以要塞入this才能傳遞 | |
} | |
/*update after DOM transform*/ | |
function chgtxt(who) { | |
const data = $(who).parents("tr").find("input").serialize(); | |
$.post("api.php?do=update", data, function (cdate) { | |
let item = $(who).parent().siblings(); | |
const | |
id = item.eq(0).text(), | |
name = item.eq(1).children().val(), | |
weight = item.eq(2).children().val(), | |
info = item.eq(3).children().val(); | |
item.parent().html(` | |
<td>${id}</td> | |
<td>${name}</td> | |
<td>${weight}</td> | |
<td>${info}</td> | |
<td>${cdate}</td> | |
<td> | |
<button class="mdy">修改</button> | |
<button onclick="del(this)">刪除</button> | |
</td> | |
`); | |
$(".mdy").click(chginput);//這裡新的 HTML 已經跟前面出現過的脫節,所以還要重新再綁一次 | |
}); | |
} | |
/*delete*/ | |
function del(who) { | |
let id = $(who).parent().siblings().eq(0).text(); | |
// $.post("api.php?do=delete",{"id":id},function(result){ //DATA=JSON | |
$.post("api.php?do=delete", { id }, function (result) { | |
if (result) $(who).parent().parent().remove(); //有回傳才做事 | |
}); | |
} | |
/*insert*/ | |
function activeForm() { | |
$(".insertzone").html(` | |
<form action=""> | |
<h1>新增動物資料</h1> | |
<hr> | |
<p>動物:<input type="text" name="name"></p> | |
<p>重量:<input type="text" name="weight"></p> | |
<p>簡介:<input type="text" name="info"></p> | |
<hr> | |
<p> | |
<!-- 注意button沒有type會形同submit --> | |
<button type="button" onclick="sendForm(this)">新增</button> | |
<button type="button" onclick="closeAddform()">取消</button> | |
</p> | |
</form> | |
`); | |
$(".insertzone").fadeIn(); | |
} | |
function closeAddform() { | |
$(".insertzone").fadeOut(); | |
} | |
function sendForm(who) { | |
const data = $(who).parents("form").find("input").serialize(); | |
$.post("api.php?do=insert", data, function (result) { | |
//由於是分流載入而考慮情況較多。最快就是歸零重新載入初始select | |
$("tbody").empty(); | |
start = 0; | |
loading(); | |
$(".insertzone").fadeOut(); | |
}); | |
} | |
</script> | |
</body> | |
</html> |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment