PHP5与MySQL数据库操作常用代码收集_PHP教程

1 建立数据库表：
代码如下:
create database club;
create table member(
id int(11) not null auto_increment,
no varchar(5) not null,
name varchar(10) not null,
age int(2) not null,
level varchar(10) not null,
sex tinyint(1) not null,
date datetime not null,
primary key(id)
)engine=MyISAM default charset=GB2312;
insert into member(id,no,name,age,level,sex,date)values
(1,'A001','wanxia',30,'hj',1,'2008-04-02 00:00:00'),
(2,'C022','liyan',29,'zs',1,'2007-05-31 00:00:00'),
(3,'A006','zhangyan',36,'hj',1,'2007-06-20 00:00:00'),
(4,'B052','luanying',42,'bj',1,'2007-02-12 00:00:00'),
(5,'A007','duxiang',26,'hj',2,'2008-03-26 00:00:00'),
(6,'C060','liuyu',38,'zs',1,'2008-10-16 00:00:00');


2 读取数据
2.1 建立01.php
代码
代码如下:

<br> <br> <br><meta http-equiv="Content-Type" content="text/html;charset=GB2312"> <br><title>会员列表</title> <br> <br><!--?php <BR-->$link=mysql_connect("localhost","root","123"); //连接mysql服务器 <br>$db=mysql_select_db("club"); //选择数据库 <br>mysql_query("set names utf8",$link); //设定编码方式 <br>$sql="Select * from member"; <br>$result=mysql_query($sql,$link); //执行select查询 <br>$num=mysql_num_rows($result); //获取记录查询 <br>?> <br> <br><h2>健身俱乐部 会员名册</h2> <br><br> <br>点击姓名可查看该会员详细资料，现有会员<!--?php echo $num ?-->人。 <br><br> <br><!--?php <BR-->if($num>0) <br>{ <br>?> <br><br><br><br><br><br><br>while($row=mysql_fetch_array($result)) <br>{ <br>echo ""; <br>} <br>?> <br><table border="1" cellpadding="1" cellspacing="1"> <tbody><tr> <td>序号</td> <td>姓名</td> <td>性别</td> </tr> <!--?php <BR--><tr><td>".$row['id']."</td><td>.$row['name'].">".$row['name']."</td><td>" <br>.($row['sex']==1?"女":"男")."</td></tr></tbody></table> <br><!--?php <BR-->} <br>else <br>{ <br>echo "俱乐部尚未发展会员。"; <br>} <br>?> <br> <br> <br> <br>2.2 建立member.php <br><span style="CURSOR: pointer" onclick="doCopy('code47617')"><u></u></span> 代码如下:<pre class="brush:php;toolbar:false layui-box layui-code-view layui-code-notepad"><ol class="layui-code-ol"><li><br> <br> <br><meta http-equiv="Content-Type" content="text/html;charset=GB2312"> <br><title>会员详细资料</title> <br> <br><!--?php <BR-->$link=mysql_connect("localhost","root","123"); //连接mysql服务器 <br>$db=mysql_select_db("club"); //选择数据库 <br>mysql_query("set names utf8",$link); //设定编码方式 <br>$sql="select no,name,sex,age,level,date_format(date,'%Y-%c-%d') as join_date from member " <br>."where name='".trim($_GET['name'])."'"; <br>$result=mysql_query($sql,$link); //执行在select查询 <br>?> <br> <br><h2>健身俱乐部 会员详细资料</h2> <br><!--?php <BR-->if($row=mysql_fetch_array($result)) <br>{ <br>echo "编号：".$row['no']."<br>"; <br>echo "姓名：".$row['name']."<br>"; <br>echo "性别：".($row['sex']==1?"女":"男")."<br>"; <br>echo "年龄：".$row['age']."<br>"; <br>echo "级别：".$row['level']."<br>"; <br>echo "加入：".$row['join_date']."<br>"; <br>} <br>?> <br> <br> <br><br><img height="270" alt="" src="http://www.bkjia.com/uploads/allimg/131016/102P02393-1.jpg" width="296"><img height="202" alt="" src="http://www.bkjia.com/uploads/allimg/131016/102P0DJ-2.jpg" width="371"><br>3 修改数据 <br>3.1 建立level.php（修改数据） <br><span style="CURSOR: pointer" onclick="doCopy('code87138')"><u></u></span> 代码如下:<pre class="brush:php;toolbar:false layui-box layui-code-view layui-code-notepad"><ol class="layui-code-ol"><li><br> <br> <br><meta http-equiv="Content-Type" content="text/html;charset=GB2312"> <br><title>俱乐部优惠活动</title> <br> <br> <br><h2>俱乐部会员统计表</h2> <br><!--?php <BR-->$link=mysql_connect("localhost","root","123"); //连接mysql服务器 <br>$db=mysql_select_db("club"); //选择数据库 <br>mysql_query("set name utf8",$link); //设定编码方式 <br>$sql="Select level,count(*) as num from member group by level"; <br>$result=mysql_query($sql,$link); //执行select查询 <br>while($row=mysql_fetch_array($result)) <br>{ <br>switch($row['level']){ <br>case 'bj': <br>echo "等级：白金会员     人数：".$row['num']."<br>"; <br>break; <br>case 'hj': <br>echo "等级：黄金会员     人数：".$row['num']."<br>"; <br>break; <br>default: <br>echo "等级：钻石会员     人数：".$row['num']."<br>"; <br>} <br>} <br>?> <br> <br> <br> <br> <br>3.2 建立up_level.php <br><span style="CURSOR: pointer" onclick="doCopy('code68575')"><u></u></span> 代码如下:<pre class="brush:php;toolbar:false layui-box layui-code-view layui-code-notepad"><ol class="layui-code-ol"><li><br> <br> <br><meta http-equiv="Content-Type" content="text/html;charset=GB2312"> <br><title>俱乐部优惠活动</title> <br> <br> <br><!--?php <BR-->$link=mysql_connect("localhost","root","123"); //连接mysql服务器 <br>$db=mysql_select_db("club"); //选择数据库 <br>mysql_query("set name utf8",$link); //设定编码方式 <br>$sql="update member set level='".trim($_POST['new_level']) <br>."' where level='".trim($_POST['old_level'])."'"; <br>$result=mysql_query($sql,$link); //执行select查询 <br>echo mysql_affected_rows($link)."人 从"; <br>switch(trim($_POST['old_level'])){ <br>case 'bj': <br>echo " 白金会员 " ; <br>break; <br>case 'hj': <br>echo " 黄金会员 "; <br>break; <br>default: <br>echo " 钻石会员 "; <br>} <br>echo "成功升级到"; <br>switch(trim($_POST['new_level'])){ <br>case 'bj': <br>echo " 白金会员 " ; <br>break; <br>case 'hj': <br>echo " 黄金会员 "; <br>break; <br>default: <br>echo " 钻石会员 "; <br>} <br>?> <br> <br> <br><br></li><li><p><img height="207" alt="" src="http://www.bkjia.com/uploads/allimg/131016/102P0A53-3.jpg" width="457"><br></p></li><li><p><img height="84" alt="" src="http://www.bkjia.com/uploads/allimg/131016/102P05345-4.jpg" width="298"> <br>4 添加数据 <br>4.1 建立add_member.php <br><span style="CURSOR: pointer" onclick="doCopy('code50821')"><u></u></span> 代码如下:</p><pre class="brush:php;toolbar:false layui-box layui-code-view layui-code-notepad"><ol class="layui-code-ol"><li><br> <br><meta http-equiv="Content-Type" content="text/html;charset=GB2312"> <br><title>新增会员</title> <br> <br><h2>新加入会员</h2> <br> <br> <br> <br> <br>4.2 建立newmember.php <br><span style="CURSOR: pointer" onclick="doCopy('code77524')"><u></u></span> 代码如下:<pre class="brush:php;toolbar:false layui-box layui-code-view layui-code-notepad"><ol class="layui-code-ol"><li><br> <br> <br><meta http-equiv="Content-Type" content="text/html;charset=GB2312"> <br><title>添加会员</title> <br> <br> <br><!--?php <BR-->$link=mysql_connect("localhost","root","123"); //连接mysql服务器 <br>$db=mysql_select_db("club"); //选择数据库 <br>mysql_query("set names GB2312",$link); //设定编码方式 <br>$sql="Insert member(no,name,sex,age,level,date) values('" <br>.trim($_POST['no'])."','".trim($_POST['name'])."','" <br>.trim($_POST['sex'])."','".trim($_POST['age'])."','" <br>.trim($_POST['level'])."',now())"; <br>$result=mysql_query($sql,$link); //执行select查询 <br>$m_id=mysql_insert_id($link); //得到新插入会员记录的id <br>if(trim($_POST['level'])=="hj") //判断新会员优惠 <br>{ <br>$sql="Update member set level='bj' where id='".$m_id."'"; <br>$result=mysql_query($sql,$link); //执行会员升级优惠 <br>$text="已享受优惠升级至白金会员。"; <br>} <br>$sql="Select *,date_format(date,'%Y-%c-%d') as join_date from member " <br>."where id='".$m_id."'"; <br>$result=mysql_query($sql,$link); //执行select查询 <br>if($row=mysql_fetch_array($result)) <br>{ <br>echo "新会员资料：<br>"; <br>echo "编号：".$row['no']."<br>"; <br>echo "姓名：".$row['name']."<br>"; <br>echo "性别：".($row['sex']==1?"女":"男"."<br>"); <br>echo "年龄：".$row['age']."<br>"; <br>echo "级别：".$row['level']."<br>"; <br>echo "加入：".$row['join_date']."<br>"; <br>} <br>echo "新会员".$row['name']."添加成功".$text; <br>?> <br> <br> <br><br><img height="254" alt="" src="http://www.bkjia.com/uploads/allimg/131016/102P01J7-5.jpg" width="263"> <img height="192" alt="" src="http://www.bkjia.com/uploads/allimg/131016/102P03235-6.jpg" width="355"><br><p></p></li><li><p><img height="171" alt="" src="http://www.bkjia.com/uploads/allimg/131016/102P0B17-7.jpg" width="539"> <br>5 创建类数据库连接 <br>5.1 建立cls_mysql.php类文件 <br><span style="CURSOR: pointer" onclick="doCopy('code97414')"><u></u></span> 代码如下:</p><pre class="brush:php;toolbar:false layui-box layui-code-view layui-code-notepad"><ol class="layui-code-ol"><li><br><!--?php <BR-->class cls_mysql <br>{ <br>protected $link_id; <br>function __construct($dbhost,$dbuser,$dbpw,$dbname='',$charset='GB2312') <br>{ <br>if(!($this->link_id=mysql_connect($dbhost,$dbuser,$dbpw))) <br>{ <br>$this->ErrorMsg("Can't pConnect MySQL Server($dbhost)!"); <br>} <br>mysql_query("SET NAMES ".$charset,$this->link_id); <br>if($dbname) <br>{ <br>if(mysql_select_db($dbname,$this->link_id)===false) <br>{ <br>$this->ErrorMsg("Can't slect MYSQL database($dbname)!"); <br>return false; <br>} <br>else <br>{ <br>return true; <br>} <br>} <br>} <br>public function select_database($dbname) <br>{ <br>return mysql_select_db($dbname,$this->link_id); <br>} <br>public function fetch_array($query,$result_type=MYSQL_ASSOC) <br>{ <br>return mysql_fetch_array($query,$result_type); <br>} <br>public function query($sql) <br>{ <br>return mysql_query($sql,$this->link_id); <br>} <br>public function affected_rows() <br>{ <br>return mysql_affected_rows($this->link_id); <br>} <br>public function num_rows($query) <br>{ <br>return mysql_num_rows($query); <br>} <br>public function insert_id() <br>{ <br>return_insert_id($this->link_id); <br>} <br>public function selectLimit($sql,$num,$start=0) <br>{ <br>if($start==0) <br>{ <br>$sql.=' LIMIT '.$num; <br>} <br>else <br>{ <br>$sql.=' LIMIT '.$start.', '.$num; <br>} <br>return $this->query($sql); <br>} <br>public function getOne($sql,$limited=false) <br>{ <br>if($limited=true) <br>{ <br>$sql=trim($sql.' LIMIT 1'); <br>} <br>$res=$this->query($sql); <br>if($res!=false) <br>{ <br>$row=mysql_fetch_row($res); <br>return $row[0]; <br>} <br>else <br>{ <br>return false; <br>} <br>} <br>public function getAll($sql) <br>{ <br>$res=$this->query($sql); <br>if($res!==false) <br>{ <br>$arr=array(); <br>while($row=mysql_fetch_assoc($res)) <br>{ <br>$arr[]=$row; <br>} <br>return $arr; <br>} <br>else <br>{ <br>return false; <br>} <br>} <br>function ErrorMsg($message='',$sql='') <br>{ <br>if($message) <br>{ <br>echo "<b> error info</b>:$message\n\n"; <br>} <br>else <br>{ <br>echo "<b>MySQL server error report:"; <br>print_r($this->error_message); <br>} <br>exit; <br>} <br>} <br>?> <br> <br>5.2 建立test.php <br><span style="CURSOR: pointer" onclick="doCopy('code64302')"><u></u></span> 代码如下:<pre class="brush:php;toolbar:false layui-box layui-code-view layui-code-notepad"><ol class="layui-code-ol"><li><br><!--?php <BR-->include("cls_mysql.php"); <br>?> <br> <br> <br><meta http-equiv="Content-Type" content="text/html;charset=GB2312"> <br><title>Mysql类库测试</title> <br> <br> <br><!--?php <BR-->$sql="Select * from member"; <br>$db=new cls_mysql('localhost','root','123','club','GB2312'); <br>$result=$db->selectLimit($sql,'3'); //从数据库中返回3个会员资料 <br>if($result) <br>{ <br>while($row=$db->fetch_array($result)) <br>{ <br>echo "会员编号： " .$row['no']."，姓名：".$row['name']."<br>"; <br>} <br>} <br>?> <br> <br> <br><br><img height="122" alt="" src="http://www.bkjia.com/uploads/allimg/131016/102P04446-8.jpg" width="268"><br><strong>6 总结</strong> <br>6.1 mysql_connect()：建立与MySQL服务器的连接 <br>6.2 mysql_select_db()：选择数据库 <br>6.3 mysql_query()：执行数据库查询 <br>6.4 mysql_fetch_array()：获取数据库记录 <br>6.5 mysql_num_rows()：获取查询得到的记录数 <br>6.6 mysql_affected_rows()：最近一次操作影响到的行数 <br>6.7 mysql_insert_id()：最近一次插入记录的ID值 <p></p></li><li><p></p></li><li><p align="left"><span id="url" itemprop="url">http://www.bkjia.com/PHPjc/321489.html</span><span id="indexUrl" itemprop="indexUrl">www.bkjia.com</span><span id="isOriginal" itemprop="isOriginal">true</span><span id="isBasedOnUrl" itemprop="isBasedOnUrl">http://www.bkjia.com/PHPjc/321489.html</span><span id="genre" itemprop="genre">TechArticle</span><span id="description" itemprop="description">1 建立数据库表：  代码如下:</span></p><pre class="brush:php;toolbar:false layui-box layui-code-view layui-code-notepad"><ol class="layui-code-ol"><li>create database club; create table member( id int(11) not null auto_increment, no varchar(5) not null, name varchar(10) not null,...<p></p></li><li>                    </li></ol></pre></li></ol></pre></b></li></ol></pre></li></ol></pre></li></ol></pre></li></ol></pre></li></ol></pre></li></ol></pre>