php 增删改查

onestopweb

浏览: 688744 次

最近访客更多访客>>

vincepisces

heishehui

wtomato

hushouhao

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

博客分类：

境-PHP

php 增删改查 php微小项目

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'>&laquo;</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'>&raquo;</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'>&laquo;</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'>&raquo;</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>&times;</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>&times;</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>

效果图：