php 增删改查 博客分类: 境-PHP php增删改查php微小项目
程序员文章站
2024-02-13 10:17:22
...
sql
CREATE TABLE IF NOT EXISTS `o_code` ( `o_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '//id', `o_english` varchar(50) NOT NULL COMMENT '//单词', `o_symbol` varchar(50) DEFAULT NULL COMMENT '//音标', `o_info` varchar(250) DEFAULT NULL COMMENT '//说明', `o_time` datetime NOT NULL COMMENT '//发布时间', `o_out` char(1) NOT NULL COMMENT '//回收站', `o_type` smallint(6) NOT NULL COMMENT '//分类', PRIMARY KEY (`o_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='//代码单词表' AUTO_INCREMENT=1 ; INSERT INTO `o_code` (`o_id`, `o_english`, `o_symbol`, `o_info`, `o_time`, `o_out`, `o_type`) VALUES (1, 'input', '[ˈɪnˌpʊt]', '输入', '2016-01-06 17:49:13', '0', 3), (2, 'form', '[fɔ:rm]', '表单', '2016-01-06 17:55:15', '0', 3), (3, 'button', '[ˈbʌtn]', '按钮', '2016-01-06 17:56:12', '0', 3), (4, 'section', '[ˈsɛkʃən]', '定义文档中的节', '2016-01-06 18:02:36', '0', 3), (5, 'header', '[ˈhɛdɚ]', '定义文档的页眉', '2016-01-06 19:50:02', '0', 3), (6, 'caption', '[ˈkæpʃən]', '定义表格标题', '2016-01-06 19:51:35', '0', 3), (7, 'address', '[ˈædres]', '地址', '2016-01-06 19:53:00', '0', 3), (8, 'article', '[ˈɑ:rtɪkl]', '内容', '2016-01-06 19:53:56', '0', 3), (9, 'footer', '[ˈfʊtɚ]', '底部', '2016-01-06 19:54:50', '0', 3), (10, 'table', '[ˈtebəl]', '表格', '2016-01-06 19:56:03', '0', 3);
mysql.func.php
<?php // 防止恶意调用 if (! defined ( 'IN_TG' )) { exit ( 'Access Defined!' ); } // _connect() 连接MYSQL数据库 function _connect() { // global 表示全局变量的意思,意图是将此变量在函数外部也能访问 global $_conn; if (! $_conn = @mysql_connect ( DB_HOST, DB_USER, DB_PWD )) { exit ( '数据库连接失败' ); } } // _select_db选择一款数据库 function _select_db() { if (! mysql_select_db ( DB_NAME )) { exit ( '找不到指定的数据库' ); } } // 设置字符集 function _set_names() { if (! mysql_query ( 'SET NAMES UTF8' )) { exit ( '字符集错误' ); } } // 获取一条数据 function _query($_sql) { if (! $_result = mysql_query ( $_sql )) { exit ( 'SQL执行失败' ); } return $_result; } // 获取一批数据 function _fetch_array($_sql) { return mysql_fetch_array ( _query ( $_sql ), MYSQL_ASSOC ); } // 关闭数据库 function _close() { if (! mysql_close ()) { exit ( '关闭异常' ); } } ?>
close.inc.php
<?php // 防止恶意调用 if (! defined ( 'IN_TG' )) { exit ( 'Access Defined!' ); } _close (); ?>
common.inc.php
<?php // 防止恶意调用 if (! defined ( 'IN_TG' )) { exit ( 'Access Defined!' ); } // 设置字符集编码 header ( 'Content-Type: text/html; charset=utf-8' ); // 转换硬路径常量 define ( 'ROOT_PATH', substr ( dirname ( __FILE__ ), 0, - 8 ) ); // 引入函数库 require ROOT_PATH . 'includes/mysql.func.php'; // 数据库连接 define ( 'DB_HOST', 'localhost' ); define ( 'DB_USER', 'root' ); define ( 'DB_PWD', '123456' ); define ( 'DB_NAME', 'osweb' ); // 初始化数据库 _connect (); // 连接MYSQL数据库 _select_db (); // 选择指定的数据库 _set_names (); // 设置字符集 ?>
type.func.php
<?php //防止恶意调用 if (! defined ( 'IN_TG' )) { exit ( 'Access Defined!' ); } //存放代码分类 function _type(){ $_type = array('[Z]','[B]','[H]','[D]','[J]','[Q]','[P]','[X]','[S]','[I]','[C]','[M]'); return $_type; } //存放代码分类说明 function _info_type(){ $_type = array('其他','bootstrap','html5','dede','javascript','jquery','php','html4','style','id','class','mysql'); return $_type; } ?>
index.php
<?php define('IN_TG',true); require dirname(__FILE__).'/includes/common.inc.php'; require ROOT_PATH.'includes/type.func.php'; $_type_arr = _type(); $_info_type = _info_type(); ?> <!DOCTYPE html> <html lang="zh-cn"> <head> <meta charset="UTF-8"> <meta name="viewport" content="width=device-width, initial-scale=1,maximum-scale=1, user-scalable=no"> <meta name="robots" content="noindex,nofollow"> <title>代码单词</title> <link href="css/bootstrap.min.css" rel="stylesheet"> <style> body {font-family: "Helvetica Neue", Helvetica, Arial, "Microsoft Yahei UI", "Microsoft YaHei", SimHei, "\5B8B\4F53", simsun, sans-serif;} .english{ color: #333} .english:link{ color: #333; text-decoration: none;} .english:visited{ color: #333; text-decoration: none;} .english:hover{ color: #333; text-decoration: none;} .english:active{ color: #333; text-decoration: none;} </style> </head> <body style="margin: 10px;"> <form action="index.php" method="get"> <div class="input-group"> <input type="text" class="form-control" name="search"> <span class="input-group-btn"> <button class="btn btn-primary" type="submit">搜索</button> </span> </div> </form> <br> <div class="btn-group pull-right"> <a class="btn btn-primary" href="?sort=1">最新</a> <a class="btn btn-primary" href="?sort=2">最旧</a> <a class="btn btn-primary" href="?sort=3">A-Z</a> <a class="btn btn-primary" href="?sort=4">Z-A</a> <a class="btn btn-primary dropdown-toggle" data-toggle="dropdown"> 分类 <span class="caret"></span> </a> <ul class="dropdown-menu"> <?php for($i=1;$i<count($_type_arr);$i++){ echo "<li><a href=\"?type=".($i+1)."\">$_type_arr[$i]$_info_type[$i]</a></li>"; } ?> <li class="divider"></li> <?php echo "<li><a href=\"?type=1\">$_type_arr[0]$_info_type[0]</a></li>"; ?> </ul> </div> <br> <table class="table table-striped table-hover"> <thead> <tr> <th>单词</th> <th>音标</th> <th>说明</th> </tr> </thead> <tbody> <?php $_search = trim($_GET['search']); $_sort = $_GET['sort']; $_type = $_GET['type']; $_page=$_GET['page'];//获取当前的页数 $_page_show =3;//最大显示3页 $_page_size =10;//显示每页的条数 if($_page == null || $_page<=0){ $_page =1; } $_page_begin = ($_page-1) * $_page_size; $_sql_list = "SELECT * FROM o_code WHERE o_out='0' "; if($_search !=null){ $_sql_list.= " AND o_english LIKE '%$_search%' order by o_time DESC LIMIT $_page_begin , $_page_size"; }else if($_sort !=null){ switch ($_sort){ case 1: $_sql_list.= "order by o_time DESC LIMIT $_page_begin , $_page_size"; break; case 2: $_sql_list.= "order by o_time ASC LIMIT $_page_begin , $_page_size"; break; case 3: $_sql_list.= "order by o_english ASC ,o_time DESC LIMIT $_page_begin , $_page_size"; break; case 4: $_sql_list.= "order by o_english DESC ,o_time DESC LIMIT $_page_begin , $_page_size"; break; default: $_sql_list.= "order by o_time DESC LIMIT $_page_begin , $_page_size"; } }else if($_type !=null){ $_sql_list.= " AND o_type = '$_type' order by o_english ASC LIMIT $_page_begin , $_page_size"; }else{ $_sql_list.= "order by o_time DESC LIMIT $_page_begin , $_page_size"; } $_result = _query($_sql_list); while (!!$row = mysql_fetch_array($_result)){ ?> <tr> <td><a href="http://dict.baidu.com/s?wd=<?php echo $row[o_english]?>" target="_blank" class="english"><?php echo $row[o_english]?></a></td> <td><?php echo $row[o_symbol]?></td> <td><?php echo $_type_arr[$row[o_type]-1]; ?><?php echo $row[o_info]?></td> </tr> <?php } ?> </tbody> </table> <ul class="pagination pull-right"> <?php $_sql_count = "SELECT count( o_id ) FROM o_code WHERE o_out='0' "; //获取参数 $_con = ''; if($_search !=null){ $_sql_count.= "AND o_english LIKE '%$_search%'"; $_con = "search=$_search&"; }else if($_sort !=null){ $_con = "sort=$_sort&"; }else if($_type !=null){ $_sql_count.= " AND o_type = '$_type'"; $_con = "type=$_type&"; } //分页 $_answers = mysql_fetch_array(_query($_sql_count)); $_allcount= $_answers[0]; $_page_count = ceil($_allcount/$_page_size); if($_page <= 1 || $_page == '') $_page = 1; if($_page >= $_page_count) $_page = $_page_count; $_pre_page = ($_page == 1)? 1 : $_page - 1; $_next_page= ($_page == $_page_count)? $_page_count : $_page + 1 ; $_pagenav .= "<li><a href='?".$_con."page=1'>共".$_allcount."条</a></li>"; $_pagenav .= "<li><a href='?".$_con."page=$_pre_page'>«</a></li> "; $_page_show_start = (ceil($_page/$_page_show)-1)*$_page_show; $_page_show_str = ''; if($_page_show>$_page_count){ $_page_show = $_page_count; } for($j=1;$j<=$_page_show;$j++){ $_page_show_now = $_page_show_start+$j; if($_page==$_page_show_now){ $_page_show_str .= "<li class='active'><a href='?".$_con."page=$_page_show_now'><strong>$_page_show_now</strong></a></li>"; }else{ $_page_show_str .= "<li><a href='?".$_con."page=$_page_show_now'>$_page_show_now</a></li>"; } } $_pagenav.=$_page_show_str; $_pagenav .= "<li><a href='?".$_con."page=$_next_page'>»</a></li>"; $_pagenav .= "<li><a href='?".$_con."page=$_page_count'>末页</a></li>"; echo $_pagenav; ?> </ul> <?php require ROOT_PATH.'includes/close.inc.php'; ?> <script src="js/jquery.min.js"></script> <script src="js/bootstrap.min.js"></script> </body> </html>
效果图:
admin.php
<?php define('IN_TG',true); require dirname(__FILE__).'/includes/common.inc.php'; require ROOT_PATH.'includes/type.func.php'; $_type_arr = _type(); $_info_type = _info_type(); $_page=$_GET['page']; $_search = trim($_GET['search']); if($_page == null || $_page<=0){ $_page =1; } if($_GET['oid']!=null){ $_oid = $_GET['oid']; $_sql = "UPDATE o_code SET o_out = '1' WHERE o_id = $_oid LIMIT 1"; _query($_sql); } ?> <!DOCTYPE html> <html lang="zh-cn"> <head> <meta charset="UTF-8"> <meta name="viewport" content="width=device-width, initial-scale=1,maximum-scale=1, user-scalable=no"> <meta name="robots" content="noindex,nofollow"> <title>管理</title> <link href="css/bootstrap.min.css" rel="stylesheet"> <style> body {font-family: "Helvetica Neue", Helvetica, Arial, "Microsoft Yahei UI", "Microsoft YaHei", SimHei, "\5B8B\4F53", simsun, sans-serif;} </style> </head> <body style="margin: 10px;"> <form action="admin.php" method="get"> <div class="input-group"> <input type="text" class="form-control" name="search" value="<?php echo $_search ?>"> <span class="input-group-btn"> <button class="btn btn-primary" type="submit">搜索</button> <a class="btn btn-info" href="add.php" target="_blank">新增</a> </span> </div> </form> <br> <table class="table table-striped table-hover"> <thead> <tr> <th>单词</th> <th>音标</th> <th>说明</th> <th>操作</th> </tr> </thead> <tbody> <?php $_page_show =3;//最大显示3页 $_page_size =50;//显示每页的条数 if($_page == null || $_page<=0){ $_page =1; } $_page_begin = ($_page-1) * $_page_size; $_sql_list = "SELECT * FROM o_code WHERE o_out='0' "; if($_search !=null){ $_sql_list.= "AND o_english LIKE '%$_search%' order by o_time DESC LIMIT $_page_begin , $_page_size"; }else{ $_sql_list.= "order by o_time DESC LIMIT $_page_begin , $_page_size"; } $_result = _query($_sql_list); while (!!$row = mysql_fetch_array($_result)){ ?> <tr> <td><?php echo $row[o_english]?></td> <td><?php echo $row[o_symbol]?></td> <td><?php echo $_type_arr[$row[o_type]-1]; ?><?php echo $row[o_info]?></td> <td><a href="update.php?id=<?php echo $row[o_id];?>" target="_blank">修改</a> <a href="?search=<?php echo $_search;?>&page=<?php echo $_page;?>&oid=<?php echo $row[o_id];?>" onclick="javascript:return confirm('您确定要删除该条数据吗?')" title="删除该条数据">删除</a></td> </tr> <?php } ?> </tbody> </table> <ul class="pagination pull-right"> <?php $_sql_count = "SELECT count( o_id ) FROM o_code WHERE o_out='0' "; //获取参数 $_con = ''; if($_search !=null){ $_sql_count.= " AND o_english LIKE '%$_search%'"; $_con = "search=$_search&"; } //分页 $_answers = mysql_fetch_array(_query($_sql_count)); $_allcount= $_answers[0]; $_page_count = ceil($_allcount/$_page_size); if($_page <= 1 || $_page == '') $_page = 1; if($_page >= $_page_count) $_page = $_page_count; $_pre_page = ($_page == 1)? 1 : $_page - 1; $_next_page= ($_page == $_page_count)? $_page_count : $_page + 1 ; $_pagenav .= "<li><a href='?".$_con."page=1'>共".$_allcount."条</a></li>"; $_pagenav .= "<li><a href='?".$_con."page=$_pre_page'>«</a></li> "; $_page_show_start = (ceil($_page/$_page_show)-1)*$_page_show; $_page_show_str = ''; if($_page_show>$_page_count){ $_page_show = $_page_count; } for($j=1;$j<=$_page_show;$j++){ $_page_show_now = $_page_show_start+$j; if($_page==$_page_show_now){ $_page_show_str .= "<li class='active'><a href='?".$_con."page=$_page_show_now'><strong>$_page_show_now</strong></a></li>"; }else{ $_page_show_str .= "<li><a href='?".$_con."page=$_page_show_now'>$_page_show_now</a></li>"; } } $_pagenav.=$_page_show_str; $_pagenav .= "<li><a href='?".$_con."page=$_next_page'>»</a></li>"; $_pagenav .= "<li><a href='?".$_con."page=$_page_count'>末页</a></li>"; echo $_pagenav; ?> </ul> <?php require ROOT_PATH.'includes/close.inc.php'; ?> <script src="js/jquery.min.js"></script> <script src="js/bootstrap.min.js"></script> </body> </html>
效果图:
add.php
<?php define('IN_TG',true); require dirname(__FILE__).'/includes/common.inc.php'; require ROOT_PATH.'includes/type.func.php'; $_type_arr = _type(); $_info_type = _info_type(); if ($_GET['action'] == 'code') { $_english = trim($_POST['english']); $_symbol = trim($_POST['symbol']); $_info = trim($_POST['info']); $_type = trim($_POST['type']); $_sql = "INSERT INTO o_code (o_english, o_symbol, o_info, o_time, o_out, o_type) VALUES ('$_english', '$_symbol', '$_info', NOW(), '0', '$_type')"; _query($_sql); _close(); echo "<script type='text/javascript'>alert('添加成功');location.href='add.php';</script>"; exit(); } ?> <!DOCTYPE html> <html lang="zh-cn"> <head> <meta charset="UTF-8"> <meta name="viewport" content="width=device-width, initial-scale=1,maximum-scale=1, user-scalable=no"> <meta name="robots" content="noindex,nofollow"> <title>添加</title> <link href="css/bootstrap.min.css" rel="stylesheet"> <style> body {font-family: "Helvetica Neue", Helvetica, Arial, "Microsoft Yahei UI", "Microsoft YaHei", SimHei, "\5B8B\4F53", simsun, sans-serif;} </style> </head> <body style="margin: 10px;"> <form class="form-horizontal" action="add.php?action=code" method="post"> <div class="form-group"> <label class="col-sm-2 control-label">单词</label> <div class="col-sm-10"> <input type="text" class="form-control" id="english" name="english" placeholder="请输入单词"> </div> </div> <div class="form-group"> <label class="col-sm-2 control-label">音标</label> <div class="col-sm-10"> <input type="text" class="form-control" name="symbol" placeholder="请输入音标"> </div> </div> <div class="form-group"> <label class="col-sm-2 control-label">说明</label> <div class="col-sm-10"> <input type="text" class="form-control" name="info" placeholder="请输入说明"> </div> </div> <select class="form-control" name="type"> <?php for($i=0;$i<count($_type_arr);$i++){ if($i==0){ echo "<option value='".($i+1)."' selected>$_type_arr[$i] $_info_type[$i]</option>"; }else{ echo "<option value='".($i+1)."'>$_type_arr[$i] $_info_type[$i]</option>"; } } ?> </select> <br> <div class="btn-group pull-right"> <input type="submit" class="btn btn-primary" value="提交"> <input type="reset" class="btn btn-info" value="重置"> </div> </form> <div class="clearfix"></div> <br> <div class="alert alert-info fade in"> <button class="close" data-dismiss="alert"> <span>×</span> </button> <p id="english_text">该窗口显示代码是否存在</p> </div> <?php require ROOT_PATH.'includes/close.inc.php'; ?> <script src="js/jquery.min.js"></script> <script src="js/bootstrap.min.js"></script> <script> function _id(id){ return document.getElementById(id); } $(function(){ _id("english").onblur = function(){ english_onblur(this); }; }); function english_onblur(obj){ var english = _id("english"); var english_text = _id("english_text") $.post('includes/code.ajax.php', { english : obj.value }, function (response, status, xhr) { if(response == '存在'){ english_text.innerHTML= english.value+"在数据库已存在"; }else{ english_text.innerHTML= english.value+"可以使用"; } }); } </script> </body> </html>
code.ajax.php
<?php define('IN_TG',true); //引入公共文件 require dirname(__FILE__).'/common.inc.php'; //转换成硬路径,速度更快 $_english = $_POST['english']; $_sql = "SELECT * FROM o_code WHERE o_english = '$_english ' LIMIT 1"; $_result = _fetch_array($_sql); if($_result){ echo '存在'; } _close(); ?>
效果图:
update.php
<?php define('IN_TG',true); require dirname(__FILE__).'/includes/common.inc.php'; require ROOT_PATH.'includes/type.func.php'; $_type_arr = _type(); $_info_type = _info_type(); $_id = $_GET['id']; if($_GET['id']!=null){ $_sql = " SELECT * FROM o_code WHERE o_id =$_id LIMIT 1 "; $_result = _fetch_array($_sql); if ($_GET['action'] == 'code') { $_english = trim($_POST['english']); $_symbol = trim($_POST['symbol']); $_info = trim($_POST['info']); $_type = trim($_POST['type']); $_sql = "UPDATE o_code SET o_english = '$_english', o_symbol = '$_symbol', o_info = '$_info', o_type = '$_type' WHERE o_id = $_id"; _query($_sql); _close(); echo "<script type='text/javascript'>alert('修改成功');location.href='update.php?id=$_id';</script>"; exit(); } } ?> <!DOCTYPE html> <html lang="zh-cn"> <head> <meta charset="UTF-8"> <meta name="viewport" content="width=device-width, initial-scale=1,maximum-scale=1, user-scalable=no"> <meta name="robots" content="noindex,nofollow"> <title>修改</title> <link href="css/bootstrap.min.css" rel="stylesheet"> <style> body {font-family: "Helvetica Neue", Helvetica, Arial, "Microsoft Yahei UI", "Microsoft YaHei", SimHei, "\5B8B\4F53", simsun, sans-serif;} </style> </head> <body style="margin: 10px;"> <form class="form-horizontal" action="update.php?id=<?php echo $_id?>&action=code" method="post"> <div class="form-group"> <label class="col-sm-2 control-label">单词</label> <div class="col-sm-10"> <input type="text" class="form-control" name="english" value="<?php echo $_result[o_english]?>" placeholder="请输入单词"> </div> </div> <div class="form-group"> <label class="col-sm-2 control-label">音标</label> <div class="col-sm-10"> <input type="text" class="form-control" name="symbol" value="<?php echo $_result[o_symbol]?>" placeholder="请输入音标"> </div> </div> <div class="form-group"> <label class="col-sm-2 control-label">说明</label> <div class="col-sm-10"> <input type="text" class="form-control" name="info" value="<?php echo $_result[o_info]?>" placeholder="请输入说明"> </div> </div> <select class="form-control" name="type"> <?php for($i=0;$i<count($_type_arr);$i++){ if(($i+1)==$_result[o_type]){ echo "<option value='".($i+1)."' selected>$_type_arr[$i] $_info_type[$i]</option>"; }else{ echo "<option value='".($i+1)."'>$_type_arr[$i] $_info_type[$i]</option>"; } } ?> </select> <br> <div class="btn-group pull-right"> <input type="submit" class="btn btn-primary" value="提交"> <input type="reset" class="btn btn-info" value="重置"> </div> </form> <div class="clearfix"></div> <br> <div class="alert alert-info fade in"> <button class="close" data-dismiss="alert"> <span>×</span> </button> <p>你现在对编号 <strong><?php echo $_result[o_id]?></strong> 的 <strong><?php echo $_result[o_english]?></strong> 单词进行修改</p> </div> <?php require ROOT_PATH.'includes/close.inc.php'; ?> <script src="js/jquery.min.js"></script> <script src="js/bootstrap.min.js"></script> </body> </html>
效果图: