PHP5與MySQL資料庫操作常用代碼收集

最後更新：2018-12-08 來源：互聯網

上載者：User

創建阿里雲帳戶，並獲得超過 40 款產品的免費試用版；而企業帳戶則可以享有總值 $1200 的免費試用版。立即註冊！

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
代碼複製代碼代碼如下:<html>
<head>
<meta http-equiv="Content-Type" content="text/html;charset=GB2312"/>
<title>會員列表</title>
</head>
<?php
$link=mysql_connect("localhost","root","123"); //串連mysql伺服器
$db=mysql_select_db("club"); //選擇資料庫
mysql_query("set names utf8",$link); //設定編碼方式
$sql="Select * from member";
$result=mysql_query($sql,$link); //執行select查詢
$num=mysql_num_rows($result); //擷取記錄查詢
?>
<body>
<h1>健身俱樂部會員名冊</h1>
<br />
點擊姓名可查看該會員詳細資料，現有會員<?php echo $num ?>人。
<br />
<?php
if($num>0)
{
?>
<table border="1" cellpadding="1" cellspacing="1">
<tr>
<td>序號</td>
<td>姓名</td>
<td>性別</td>
</tr>
<?php
while($row=mysql_fetch_array($result))
{
echo "<tr><td>".$row['id']."</td><td><a href=member.php?name="
.$row['name'].">".$row['name']."</a></td><td>"
.($row['sex']==1?"女":"男")."</td></tr>";
}
?>
</table>
<?php
}
else
{
echo "俱樂部尚未發展會員。";
}
?>
</body>
</html>

2.2 建立member.php 複製代碼代碼如下:<html>
<head>
<meta http-equiv="Content-Type" content="text/html;charset=GB2312"/>
<title>會員詳細資料</title>
</head>
<?php
$link=mysql_connect("localhost","root","123"); //串連mysql伺服器
$db=mysql_select_db("club"); //選擇資料庫
mysql_query("set names utf8",$link); //設定編碼方式
$sql="select no,name,sex,age,level,date_format(date,'%Y-%c-%d') as join_date from member "
."where name='".trim($_GET['name'])."'";
$result=mysql_query($sql,$link); //執行在select查詢
?>
<body>
<h1>健身俱樂部會員詳細資料</h1>
<?php
if($row=mysql_fetch_array($result))
{
echo "編號：".$row['no']."<br />";
echo "姓名：".$row['name']."<br />";
echo "性別：".($row['sex']==1?"女":"男")."<br />";
echo "年齡：".$row['age']."<br />";
echo "層級：".$row['level']."<br />";
echo "加入：".$row['join_date']."<br />";
}
?>
</body>
</html>

3 修改資料
3.1 建立level.php（修改資料）複製代碼代碼如下:<html>
<head>
<meta http-equiv="Content-Type" content="text/html;charset=GB2312" />
<title>俱樂部優惠活動</title>
</head>
<body>
<h1>俱樂部會員統計表</h1>
<?php
$link=mysql_connect("localhost","root","123"); //串連mysql伺服器
$db=mysql_select_db("club"); //選擇資料庫
mysql_query("set name utf8",$link); //設定編碼方式
$sql="Select level,count(*) as num from member group by level";
$result=mysql_query($sql,$link); //執行select查詢
while($row=mysql_fetch_array($result))
{
switch($row['level']){
case 'bj':
echo "等級：白金會員人數：".$row['num']."<br />";
break;
case 'hj':
echo "等級：黃金會員人數：".$row['num']."<br />";
break;
default:
echo "等級：鑽石會員人數：".$row['num']."<br />";
}
}
?>
<form action="up_level.php" name="level" method="post">
會員優惠升級：從
<select name="old_level">
<option value="hj">黃金會員</option>
<option value="bj">白金會員</option>
</select>
升級至
<select name="new_level">
<option value="bj">白金會員</option>
<option value="zs">鑽石會員</option>
</select>
<input type="submit" value="確定"/>
</form>
</body>
</html>

3.2 建立up_level.php 複製代碼代碼如下:<html>
<head>
<meta http-equiv="Content-Type" content="text/html;charset=GB2312" />
<title>俱樂部優惠活動</title>
</head>
<body>
<?php
$link=mysql_connect("localhost","root","123"); //串連mysql伺服器
$db=mysql_select_db("club"); //選擇資料庫
mysql_query("set name utf8",$link); //設定編碼方式
$sql="update member set level='".trim($_POST['new_level'])
."' where level='".trim($_POST['old_level'])."'";
$result=mysql_query($sql,$link); //執行select查詢
echo mysql_affected_rows($link)."人從";
switch(trim($_POST['old_level'])){
case 'bj':
echo " 白金會員 " ;
break;
case 'hj':
echo " 黃金會員 ";
break;
default:
echo " 鑽石會員 ";
}
echo "成功升級到";
switch(trim($_POST['new_level'])){
case 'bj':
echo " 白金會員 " ;
break;
case 'hj':
echo " 黃金會員 ";
break;
default:
echo " 鑽石會員 ";
}
?>
</body>
</html>

4 添加資料
4.1 建立add_member.php

複製代碼代碼如下:<html>
<meta http-equiv="Content-Type" content="text/html;charset=GB2312"/>
<title>新增會員</title>
<body>
<h1>新加入會員</h1>
<form action="newmember.php" method="post" name="add_member">
編號：<input type="text" name="no" width="40"/><br />
姓名：<input type="text" name="name" width="40"/><br />
性別：
<input type="radio" name="sex" value="1" />女
<input type="radio" name="sex" value="2" />男<br />
年齡：<input type="text" name="age" width="40" /><br />
層級：
<select name="level">
<option value="hj">黃金會員</option>
<option value="bj">白金會員</option>
<option value="zs">鑽石會員</option>
</select><br />
<input type="submit" value="確定" />
</form>
</body>
</html>

4.2 建立newmember.php 複製代碼代碼如下:<html>
<head>
<meta http-equiv="Content-Type" content="text/html;charset=GB2312" />
<title>添加會員</title>
</head>
<body>
<?php
$link=mysql_connect("localhost","root","123"); //串連mysql伺服器
$db=mysql_select_db("club"); //選擇資料庫
mysql_query("set names GB2312",$link); //設定編碼方式
$sql="Insert member(no,name,sex,age,level,date) values('"
.trim($_POST['no'])."','".trim($_POST['name'])."','"
.trim($_POST['sex'])."','".trim($_POST['age'])."','"
.trim($_POST['level'])."',now())";
$result=mysql_query($sql,$link); //執行select查詢
$m_id=mysql_insert_id($link); //得到新插入會員記錄的id
if(trim($_POST['level'])=="hj") //判斷新會員優惠
{
$sql="Update member set level='bj' where id='".$m_id."'";
$result=mysql_query($sql,$link); //執行會員升級優惠
$text="已享受優惠升級至白金會員。";
}
$sql="Select *,date_format(date,'%Y-%c-%d') as join_date from member "
."where id='".$m_id."'";
$result=mysql_query($sql,$link); //執行select查詢
if($row=mysql_fetch_array($result))
{
echo "新會員資料：<br />";
echo "編號：".$row['no']."<br />";
echo "姓名：".$row['name']."<br />";
echo "性別：".($row['sex']==1?"女":"男"."<br />");
echo "年齡：".$row['age']."<br />";
echo "層級：".$row['level']."<br />";
echo "加入：".$row['join_date']."<br />";
}
echo "新會員".$row['name']."添加成功".$text;
?>
</body>
</html>

5 建立類資料庫連接
5.1 建立cls_mysql.php類檔案

複製代碼代碼如下:<?php
class cls_mysql
{
protected $link_id;
function __construct($dbhost,$dbuser,$dbpw,$dbname='',$charset='GB2312')
{
if(!($this->link_id=mysql_connect($dbhost,$dbuser,$dbpw)))
{
$this->ErrorMsg("Can't pConnect MySQL Server($dbhost)!");
}
mysql_query("SET NAMES ".$charset,$this->link_id);
if($dbname)
{
if(mysql_select_db($dbname,$this->link_id)===false)
{
$this->ErrorMsg("Can't slect MYSQL database($dbname)!");
return false;
}
else
{
return true;
}
}
}
public function select_database($dbname)
{
return mysql_select_db($dbname,$this->link_id);
}
public function fetch_array($query,$result_type=MYSQL_ASSOC)
{
return mysql_fetch_array($query,$result_type);
}
public function query($sql)
{
return mysql_query($sql,$this->link_id);
}
public function affected_rows()
{
return mysql_affected_rows($this->link_id);
}
public function num_rows($query)
{
return mysql_num_rows($query);
}
public function insert_id()
{
return_insert_id($this->link_id);
}
public function selectLimit($sql,$num,$start=0)
{
if($start==0)
{
$sql.=' LIMIT '.$num;
}
else
{
$sql.=' LIMIT '.$start.', '.$num;
}
return $this->query($sql);
}
public function getOne($sql,$limited=false)
{
if($limited=true)
{
$sql=trim($sql.' LIMIT 1');
}
$res=$this->query($sql);
if($res!=false)
{
$row=mysql_fetch_row($res);
return $row[0];
}
else
{
return false;
}
}
public function getAll($sql)
{
$res=$this->query($sql);
if($res!==false)
{
$arr=array();
while($row=mysql_fetch_assoc($res))
{
$arr[]=$row;
}
return $arr;
}
else
{
return false;
}
}
function ErrorMsg($message='',$sql='')
{
if($message)
{
echo "<b> error info</b>:$message\n\n";
}
else
{
echo "<b>MySQL server error report:";
print_r($this->error_message);
}
exit;
}
}
?>

5.2 建立test.php 複製代碼代碼如下:<?php
include("cls_mysql.php");
?>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html;charset=GB2312" />
<title>Mysql類庫測試</title>
</head>
<body>
<?php
$sql="Select * from member";
$db=new cls_mysql('localhost','root','123','club','GB2312');
$result=$db->selectLimit($sql,'3'); //從資料庫中返回3個會員資料
if($result)
{
while($row=$db->fetch_array($result))
{
echo "會員編號： " .$row['no']."，姓名：".$row['name']."<br />";
}
}
?>
</body>
</html>

6 總結
6.1 mysql_connect()：建立與MySQL伺服器的串連
6.2 mysql_select_db()：選擇資料庫
6.3 mysql_query()：執行資料庫查詢
6.4 mysql_fetch_array()：擷取資料庫記錄
6.5 mysql_num_rows()：擷取查詢得到的記錄數
6.6 mysql_affected_rows()：最近一次操作影響到的行數
6.7 mysql_insert_id()：最近一次插入記錄的ID值

本文章原先以中文撰寫並發佈於 aliyun.com，亦設英文版本，僅作資訊用途。本網站不對文章的準確性，完整性或可靠性或其任何翻譯作出任何明示或暗示的陳述或保證。如對該文章有任何疑慮或投訴，請傳送電郵至 info-contact@alibabacloud.com 並提供相關疑慮或投訴的詳細說明。職員會於 5 個工作天內與您聯絡，一經驗證之後，即會刪除該侵權內容。

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

Get Started for Free

Sales Support

1 on 1 presale consultation

Chat Contact Sales
After-Sales Support

24/7 Technical Support 6 Free Tickets per Quarter Faster Response

Open a Ticket
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.

Learn More