PHP头条
热点:

PHP访问MySql数据库 高级篇 AJAX技术


阅读本文之前,推荐先参阅《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):

 \

 

本人CSS学的太菜。所以表格的布局将就点了。

 

摘自 MoreWindows

www.phpzy.comtrue/phprm/19187.htmlTechArticlePHP访问MySql数据库 高级篇 AJAX技术 阅读本文之前,推荐先参阅《PHP访问MySql数据库 初级篇》和《PHP访问MySql数据库 中级篇 Smarty技术》。 在前面的文章,我们已经开发了一个能够读取数...

相关文章

    暂无相关文章

PHP之友评论

今天推荐