PHP访问MySql数据库 高级篇 AJAX技术
本文之前，推荐先参阅《php访问mysql 初级篇》和《php访问mysql数据库 中级篇 smarty技术》。
在前面的文章，我们已经开发了一个能够读取数据库并显示数据的程序，且程序达到了良好的界面与逻辑分离。但是这个程序并不能支持我们对数据库进行增加、删除和修改操作。因此在这里增加这些功能。每次增加删除或修改数据时，通过ajax方式向后台发送请求，再根据后台的返回结果调整页面显示。这种方法可以减轻服务器的负担。
下面先简单的介绍下ajax，然后给出完整的示例：
ajax 是一种独立于 web 服务器软件的技术。它不是一种新的语言，而是一种用于创建更好更快以及交互性更强的 web 应用程序的技术。通过 ajax方式，可使用 javascript 的xmlhttprequest 对象来直接与服务器进行通信。这样便可以在不重载页面的情况与 web 服务器交换数据。同时ajax 在浏览器与 web 服务器之间使用异步数据传输（http 请求），这样就可使网页从服务器请求少量的信息，而不是整个页面。ajax手册可以访问
下面是本系列中功能最为全面的程序——从test数据库的t_student表中读取数据然后显示，同时支持对t_student表进行ajax方式的增加、删除和修改操作。在界面功能上也有表格的奇偶行变色及鼠标经过变色，使得程序更加的美观。
程序共分为8个文件，分别为smarty2.php、smarty2.html、smarty2_head.php、smarty2.js和smarty2.css及新增加的insert.php、delete.php及updata.php。
1．smarty2_head.php文件
定义数据库相关的常量，变量数组。数据库名，用户名与密码，表名等在此定义。
<?php
// by morewindows( http://blog.csdn.net/morewindows )
define(db_host, 'localhost');
define(db_user, 'root');
define(db_pass, '111111');
define(db_databasename, 'test');
define(db_tablename, 't_student');
$dbcolarray = array('id', 'name', 'age');
?>
<?php
// by morewindows( http://blog.csdn.net/morewindows )
define(db_host, 'localhost');
define(db_user, 'root');
define(db_pass, '111111');
define(db_databasename, 'test');
define(db_tablename, 't_student');
$dbcolarray = array('id', 'name', 'age');
?>
2．smarty2.php文件
<?php
// by morewindows( http://blog.csdn.net/morewindows )
header("content-type: text/html; charset=utf-8");
require('../../smart_libs/smarty.class.php');
require_once('smarty2_head.php');
date_default_timezone_set("prc");
//mysql_connect
$conn = mysql_connect(db_host, db_user, db_pass) or die("connect failed" . mysql_error());
mysql_select_db(db_databasename, $conn);
//个数
$sql = sprintf("select count(*) from %s", db_tablename);
$result = mysql_query($sql, $conn);
if ($result)
{
$dbcount = mysql_fetch_row($result);
$tpl_db_count = $dbcount[0];
}
else
{
die("query failed");
}
$tpl_db_tablename = db_tablename;
$tpl_db_coltitle = $dbcolarray;
//表中内容
$tpl_db_rows = array();
$sql = sprintf("select %s from %s", implode(",",$dbcolarray), db_tablename);
$result = mysql_query($sql, $conn);
while ($row = mysql_fetch_array($result, mysql_assoc))//等价$row=mysql_fetch_assoc($result)
$tpl_db_rows[] = $row;
mysql_free_result($result);
mysql_close($conn);
$tpl = new smarty;
$tpl->assign('db_tablename', $tpl_db_tablename);
$tpl->assign('db_count', $tpl_db_count);
$tpl->assign('db_coltitle', $tpl_db_coltitle);
$tpl->assign('db_rows', $tpl_db_rows);
$tpl->display('smarty2.html');
?>
<?php
// by morewindows( http://blog.csdn.net/morewindows )
header("content-type: text/html; charset=utf-8");
require('../../smart_libs/smarty.class.php');
require_once('smarty2_head.php');
date_default_timezone_set("prc");
//mysql_connect
$conn = mysql_connect(db_host, db_user, db_pass) or die("connect failed" . mysql_error());
mysql_select_db(db_databasename, $conn);
//个数
$sql = sprintf("select count(*) from %s", db_tablename);
$result = mysql_query($sql, $conn);
if ($result)
{
$dbcount = mysql_fetch_row($result);
$tpl_db_count = $dbcount[0];
}
else
{
die("query failed");
}
$tpl_db_tablename = db_tablename;
$tpl_db_coltitle = $dbcolarray;
//表中内容
$tpl_db_rows = array();
$sql = sprintf("select %s from %s", implode(",",$dbcolarray), db_tablename);
$result = mysql_query($sql, $conn);
while ($row = mysql_fetch_array($result, mysql_assoc))//等价$row=mysql_fetch_assoc($result)
$tpl_db_rows[] = $row;
mysql_free_result($result);
mysql_close($conn);
$tpl = new smarty;
$tpl->assign('db_tablename', $tpl_db_tablename);
$tpl->assign('db_count', $tpl_db_count);
$tpl->assign('db_coltitle', $tpl_db_coltitle);
$tpl->assign('db_rows', $tpl_db_rows);
$tpl->display('smarty2.html');
?>
3．smarty2.html文件
<!doctype html public "-//w3c//dtd xhtml 1.0 transitional//en" "http://www.w3.org/tr/xhtml1/dtd/xhtml1-transitional.dtd">
<html xmlns="">
<head>
<link href="smarty2.css" rel="stylesheet" type="text/css" media="all" />
<script type="text/javascript" src="../jquery-1.7.min.js"></script>
<script type="text/javascript" src="smarty2.js"></script>
<title>{$smarty.const.db_tablename}</title>
</head>
<body>
<h1>表名：{$db_tablename}</h1>
<table id="table" border="1" align="center" cellpadding="10" cellspacing="2" bordercolor="#ffaaoo">
<caption style="font-size:15px">当前记录数：<label id="tablerowcount">{$db_count}</label> <input type="button" value="add" onclick="addfun()" /> </caption>
{foreach $db_coltitle as $col}
<th>{$col}</th>
{/foreach}
<th>操作</th>
{foreach $db_rows as $dbrow}
<tr>
{foreach $dbrow as $k=>$val}
<td>{$val}</td>
{/foreach}
<td>
<input type="button" value="edit" onclick="editfun('{$dbrow['id']}', '{$dbrow['name']}', '{$dbrow['age']}');" />
<input type="button" value="delete" onclick="deletefun('{$dbrow['id']}')" />
</td>
</tr>
{/foreach}
</table>
<p id="editp" style="display:none;color:red;" align="center">
<form>
id:<input type=text id="editp_id" readonly="true" />
name:<input type=text id="editp_name" />
age:<input type=text id="editp_age" />
<input type=button name="updata" value="updata" onclick="updatafun()" />
</form>
</p>
<p id="addp" style="display:none;color:green;" align="center">
<form>
name:<input type=text id="addp_name" />
age:<input type=text id="addp_age" />
<input type=button name="insert" value="insert" onclick="insertfun()" / >
</form>
</p>
</body>
</html>
<!doctype html public "-//w3c//dtd xhtml 1.0 transitional//en" "http://www.w3.org/tr/xhtml1/dtd/xhtml1-transitional.dtd">
<html xmlns="">
<head>
<link href="smarty2.css" rel="stylesheet" type="text/css" media="all" />
<script type="text/javascript" src="../jquery-1.7.min.js"></script>
<script type="text/javascript" src="smarty2.js"></script>
<title>{$smarty.const.db_tablename}</title>
</head>
<body>
<h1>表名：{$db_tablename}</h1>
<table id="table" border="1" align="center" cellpadding="10" cellspacing="2" bordercolor="#ffaaoo">
<caption style="font-size:15px">当前记录数：<label id="tablerowcount">{$db_count}</label> <input type="button" value="add" onclick="addfun()" /> </caption>
{foreach $db_coltitle as $col}
<th>{$col}</th>
{/foreach}
<th>操作</th>
{foreach $db_rows as $dbrow}
<tr>
{foreach $dbrow as $k=>$val}
<td>{$val}</td>
{/foreach}
<td>
<input type="button" value="edit" onclick="editfun('{$dbrow['id']}', '{$dbrow['name']}', '{$dbrow['age']}');" />
<input type="button" value="delete" onclick="deletefun('{$dbrow['id']}')" />
</td>
</tr>
{/foreach}
</table>
<p id="editp" style="display:none;color:red;" align="center">
<form>
id:<input type=text id="editp_id" readonly="true" />
name:<input type=text id="editp_name" />
age:<input type=text id="editp_age" />
<input type=button name="updata" value="updata" onclick="updatafun()" />
</form>
</p>
<p id="addp" style="display:none;color:green;" align="center">
<form>
name:<input type=text id="addp_name" />
age:<input type=text id="addp_age" />
<input type=button name="insert" value="insert" onclick="insertfun()" / >
</form>
</p>
</body>
</html>
4．smarty2.js文件
新增加了表格的鼠标经过行变色效果
//在表格的第一列中查找等于指定id的行
function searchidintable(tablerow, findid)
{
var i;
var tablerownum = tablerow.length;
for (i = 1; i < tablerownum; i++)
if ($("#table tr:eq(" + i + ") td:eq(0)").html() == findid)
return i;
return -1;
}
//用css控制奇偶行的颜色
function settablerowcolor()
{
$("#table tr:odd").css("background-color", "#e6e6fa");
$("#table tr:even").css("background-color", "#fff0fa");
$("#table tr:odd").hover(
function(){$(this).css("background-color", "orange");},
function(){$(this).css("background-color", "#e6e6fa");}
);
$("#table tr:even").hover(
function(){$(this).css("background-color", "orange");},
function(){$(this).css("background-color", "#fff0fa");}
);
}
//响应edit按钮
function editfun(id, name, age)
{
$("#editp").show();
$("#addp").hide();
$("#editp_id").val(id);
$("#editp_name").val(name);
$("#editp_age").val(age);
}
//响应add按钮
function addfun()
{
$("#editp").hide();
$("#addp").show();
}
//记录条数增加
function inctablerowcount()
{
var tc = $("#tablerowcount");
tc.html(parseint(tc.html()) + 1);
}
//记录条数减少
function dectablerowcount()
{
var tc = $("#tablerowcount");
tc.html(parseint(tc.html()) - 1);
}
//增加一行
function addrowintable(id, name, age)
{
//新增加一行
var appendstr = "<tr>";
appendstr += "<td>" + id + "</td>";
appendstr += "<td>" + name + "</td>";
appendstr += "<td>" + age + "</td>";
appendstr += "<td><input type=\"button\" value=\"edit\" onclick=\"editfun(id, name, age);\" />";
appendstr += "<input type=\"button\" value=\"delete\" onclick=\"deletefun(id)\" /></td>";
appendstr += "</tr>";
$("#table").append(appendstr);
inctablerowcount();
}
//修改某一行
function updatarowintable(id, newname, newage)
{
var i = searchidintable($("#table tr"), id);
if (i != -1)
{
$("#table tr:eq(" + i + ") td:eq(1)").html(name != "" ? name : " ");
$("#table tr:eq(" + i + ") td:eq(2)").html(age != "" ? age : " ");
$("#editp").hide();
}
}
//删除某一行
function deleterowintable(id)
{
var i = searchidintable($("#table tr"), id);
if (i != -1)
{
//删除表格中该行
$("#table tr:eq(" + i + ")").remove();
settablerowcolor();
dectablerowcount();
}
}
//增加删除修改数据库函数 通过ajax与服务器通信
function insertfun()
{
var name = $("#addp_name").val();
var age = $("#addp_age").val();
if (name == "" || age == "")
{
alert("请输入名字和年龄!");
return ;
}
//submit to server 返回插入数据的id
$.post("insert.php", {name:name, age:age}, function(data){
if (data == "f")
{
alert("insert date failed");
}
else
{
addrowintable(data, name, age);
settablerowcolor();
$("#addp").hide();
}
});
}
function deletefun(id)
{
if (confirm("确认删除?"))
{
//submit to server
$.post("delete.php", {id:id}, function(data){
if (data == "f")
{
alert("delete date failed");
}
else
{
deleterowintable(id);
}
});
}
}
function updatafun()
{
var id = $("#editp_id").val();
var name = $("#editp_name").val();
var age = $("#editp_age").val();
//submit to server
$.post("updata.php", {id:id, name:name, age:age}, function(data){
if (data == "f")
{
alert("updata date failed");
}
else
{
updatarowintable(id, name, age);
}
});
}
$(document).ready(function()
{
settablerowcolor();
updatatablerowcount();
});
//在表格的第一列中查找等于指定id的行
function searchidintable(tablerow, findid)
{
var i;
var tablerownum = tablerow.length;
for (i = 1; i < tablerownum; i++)
if ($("#table tr:eq(" + i + ") td:eq(0)").html() == findid)
return i;
return -1;
}
//用css控制奇偶行的颜色
function settablerowcolor()
{
$("#table tr:odd").css("background-color", "#e6e6fa");
$("#table tr:even").css("background-color", "#fff0fa");
$("#table tr:odd").hover(
function(){$(this).css("background-color", "orange");},
function(){$(this).css("background-color", "#e6e6fa");}
);
$("#table tr:even").hover(
function(){$(this).css("background-color", "orange");},
function(){$(this).css("background-color", "#fff0fa");}
);
}
//响应edit按钮
function editfun(id, name, age)
{
$("#editp").show();
$("#addp").hide();
$("#editp_id").val(id);
$("#editp_name").val(name);
$("#editp_age").val(age);
}
//响应add按钮
function addfun()
{
$("#editp").hide();
$("#addp").show();
}
//记录条数增加
function inctablerowcount()
{
var tc = $("#tablerowcount");
tc.html(parseint(tc.html()) + 1);
}
//记录条数减少
function dectablerowcount()
{
var tc = $("#tablerowcount");
tc.html(parseint(tc.html()) - 1);
}
//增加一行
function addrowintable(id, name, age)
{
//新增加一行
var appendstr = "<tr>";
appendstr += "<td>" + id + "</td>";
appendstr += "<td>" + name + "</td>";
appendstr += "<td>" + age + "</td>";
appendstr += "<td><input type=\"button\" value=\"edit\" onclick=\"editfun(id, name, age);\" />";
appendstr += "<input type=\"button\" value=\"delete\" onclick=\"deletefun(id)\" /></td>";
appendstr += "</tr>";
$("#table").append(appendstr);
inctablerowcount();
}
//修改某一行
function updatarowintable(id, newname, newage)
{
var i = searchidintable($("#table tr"), id);
if (i != -1)
{
$("#table tr:eq(" + i + ") td:eq(1)").html(name != "" ? name : " ");
$("#table tr:eq(" + i + ") td:eq(2)").html(age != "" ? age : " ");
$("#editp").hide();
}
}
//删除某一行
function deleterowintable(id)
{
var i = searchidintable($("#table tr"), id);
if (i != -1)
{
//删除表格中该行
$("#table tr:eq(" + i + ")").remove();
settablerowcolor();
dectablerowcount();
}
}
//增加删除修改数据库函数 通过ajax与服务器通信
function insertfun()
{
var name = $("#addp_name").val();
var age = $("#addp_age").val();
if (name == "" || age == "")
{
alert("请输入名字和年龄!");
return ;
}
//submit to server 返回插入数据的id
$.post("insert.php", {name:name, age:age}, function(data){
if (data == "f")
{
alert("insert date failed");
}
else
{
addrowintable(data, name, age);
settablerowcolor();
$("#addp").hide();
}
});
}
function deletefun(id)
{
if (confirm("确认删除?"))
{
//submit to server
$.post("delete.php", {id:id}, function(data){
if (data == "f")
{
alert("delete date failed");
}
else
{
deleterowintable(id);
}
});
}
}
function updatafun()
{
var id = $("#editp_id").val();
var name = $("#editp_name").val();
var age = $("#editp_age").val();
//submit to server
$.post("updata.php", {id:id, name:name, age:age}, function(data){
if (data == "f")
{
alert("updata date failed");
}
else
{
updatarowintable(id, name, age);
}
});
}
$(document).ready(function()
{
settablerowcolor();
updatatablerowcount();
});
5．smarty2.css文件
"utf-8";
h1
{
color:red;
text-align:center;
}
table th
{
background-color:#7cfc00;
}
@charset "utf-8";
h1
{
color:red;
text-align:center;
}
table th
{
background-color:#7cfc00;
}
6．新增加的insert.php
将数据插入数据库中，成功返回新插入数据的id号，失败返回"f"。
view plaincopy to clipboardprint?<?php
require_once 'smarty2_head.php';
//mysql_connect
$conn = mysql_connect(db_host, db_user, db_pass) or die("connect failed" . mysql_error());
mysql_select_db(db_databasename, $conn);
//params
$name = $_post['name'];
$age = $_post['age'];
//insert db
$sql = sprintf("insert into %s(name, age) values('%s', %d)", db_tablename, $name, $age);
$result=mysql_query($sql, $conn);
if ($result)
echo mysql_insert_id($conn);
else
echo "f";
mysql_close($conn);
?>
<?php
require_once 'smarty2_head.php';
//mysql_connect
$conn = mysql_connect(db_host, db_user, db_pass) or die("connect failed" . mysql_error());
mysql_select_db(db_databasename, $conn);
//params
$name = $_post['name'];
$age = $_post['age'];
//insert db
$sql = sprintf("insert into %s(name, age) values('%s', %d)", db_tablename, $name, $age);
$result=mysql_query($sql, $conn);
if ($result)
echo mysql_insert_id($conn);
else
echo "f";
mysql_close($conn);
?>
7．新增加的delete.php
根据id删除数据库中一行记录，成功返回"f"，失败返回"t"。
view plaincopy to clipboardprint?<?php
require_once 'smarty2_head.php';
//mysql_connect
$conn = mysql_connect(db_host, db_user, db_pass) or die("connect failed" . mysql_error());
mysql_select_db(db_databasename, $conn);
//params
$id = $_post['id'];
//delete row in db
$sql = sprintf("delete from %s where id=%d", db_tablename, $id);
$result = mysql_query($sql, $conn);
mysql_close($conn);
if ($result)
echo "t";
else
echo "f";
?>
<?php
require_once 'smarty2_head.php';
//mysql_connect
$conn = mysql_connect(db_host, db_user, db_pass) or die("connect failed" . mysql_error());
mysql_select_db(db_databasename, $conn);
//params
$id = $_post['id'];
//delete row in db
$sql = sprintf("delete from %s where id=%d", db_tablename, $id);
$result = mysql_query($sql, $conn);
mysql_close($conn);
if ($result)
echo "t";
else
echo "f";
?>
8．新增加的updata.php
根据id修改数据库中一行记录，成功返回"f"，失败返回"t"。
<?php
require_once 'smarty2_head.php';
//mysql_connect
$conn = mysql_connect(db_host, db_user, db_pass) or die("connect failed" . mysql_error());
mysql_select_db(db_databasename, $conn);
//params
$id = $_post['id'];
$name = $_post['name'];
$age = $_post['age'];
//updata db
$sql = sprintf("update %s set name='%s',age=%d where id=%d", db_tablename, $name, $age, $id);
$result=mysql_query($sql, $conn);
mysql_close($conn);
if ($result)
echo "t";
else
echo "f";
?>
<?php
require_once 'smarty2_head.php';
//mysql_connect
$conn = mysql_connect(db_host, db_user, db_pass) or die("connect failed" . mysql_error());
mysql_select_db(db_databasename, $conn);
//params
$id = $_post['id'];
$name = $_post['name'];
$age = $_post['age'];
//updata db
$sql = sprintf("update %s set name='%s',age=%d where id=%d", db_tablename, $name, $age, $id);
$result=mysql_query($sql, $conn);
mysql_close($conn);
if ($result)
echo "t";
else
echo "f";
?>
程序运行结果如下（win7 +ie9.0）：
本人css学的太菜。所以表格的布局将就点了。
摘自 morewindows
下一篇： 用PHP实现递归循环每一个目录