程式師世界 >> 編程語言 >> 網頁編程 >> PHP編程 >> 關於PHP編程 >> PHP訪問MySql數據庫高級篇 AJAX技術

PHP訪問MySql數據庫高級篇 AJAX技術

編輯：關於PHP編程

閱讀本文之前，推薦先參閱《PHP訪問MySql數據庫初級篇》和《PHP訪問MySql數據庫中級篇 Smarty技術》。

在前面的文章，我們已經開發了一個能夠讀取數據庫並顯示數據的程序，且程序達到了良好的界面與邏輯分離。但是這個程序並不能支持我們對數據庫進行增加、刪除和修改操作。因此在這裡增加這些功能。每次增加刪除或修改數據時，通過AJAX方式向後台發送請求，再根據後台的返回結果調整頁面顯示。這種方法可以減輕服務器的負擔。

下面先簡單的介紹下AJAX，然後給出完整的示例：

AJAX 是一種獨立於 Web 服務器軟件的浏覽器技術。它不是一種新的編程語言，而是一種用於創建更好更快以及交互性更強的 Web 應用程序的技術。通過 AJAX方式，可使用 JavaScript 的XMLHttpRequest 對象來直接與服務器進行通信。這樣便可以在不重載頁面的情況與 Web 服務器交換數據。同時AJAX 在浏覽器與 Web 服務器之間使用異步數據傳輸（HTTP 請求），這樣就可使網頁從服務器請求少量的信息，而不是整個頁面。AJAX手冊可以訪問http://api.jquery.com/category/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="http://www.w3.org/1999/xhtml">
<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>

<div id="editdiv" style="display:none;color:red;" align="center">
<form>
id:<input type=text id="editdiv_id" readonly="true" />
name:<input type=text id="editdiv_name" />
age:<input type=text id="editdiv_age" />
<input type=button name="Updata" value="Updata" onclick="updataFun()" />
</form>
</div>
<div id="adddiv" style="display:none;color:green;" align="center">
<form>
name:<input type=text id="adddiv_name" />
age:<input type=text id="adddiv_age" />
<input type=button name="Insert" value="Insert" onclick="insertFun()" / >
</form>
</div>
</body>
</html>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<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>

<div id="editdiv" style="display:none;color:red;" align="center">
<form>
id:<input type=text id="editdiv_id" readonly="true" />
name:<input type=text id="editdiv_name" />
age:<input type=text id="editdiv_age" />
<input type=button name="Updata" value="Updata" onclick="updataFun()" />
</form>
</div>
<div id="adddiv" style="display:none;color:green;" align="center">
<form>
name:<input type=text id="adddiv_name" />
age:<input type=text id="adddiv_age" />
<input type=button name="Insert" value="Insert" onclick="insertFun()" / >
</form>
</div>
</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)
{
    $("#editdiv").show();
    $("#adddiv").hide();

    $("#editdiv_id").val(id);
    $("#editdiv_name").val(name);
    $("#editdiv_age").val(age);
}
//響應add按鈕
function addFun()
{
    $("#editdiv").hide();
    $("#adddiv").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 : " ");
        $("#editdiv").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 = $("#adddiv_name").val();
    var age = $("#adddiv_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();
            $("#adddiv").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 = $("#editdiv_id").val();
    var name = $("#editdiv_name").val();
    var age = $("#editdiv_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)
{
    $("#editdiv").show();
    $("#adddiv").hide();

    $("#editdiv_id").val(id);
    $("#editdiv_name").val(name);
    $("#editdiv_age").val(age);
}
//響應add按鈕
function addFun()
{
    $("#editdiv").hide();
    $("#adddiv").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 : " ");
        $("#editdiv").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 = $("#adddiv_name").val();
    var age = $("#adddiv_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();
         $("#adddiv").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 = $("#editdiv_id").val();
    var name = $("#editdiv_name").val();
    var age = $("#editdiv_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文件

@charset "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）：