Common PHP5 and MySQL database operations code collection
- 2020-03-31 20:28:37
- OfStack
1. Database table:
< img Alt = "" border = 0 height = 156 SRC =" http://files.jb51.net/upload/2010-3/20100321153520830.jpg "width = 531 >
2 read data
2.1 establish 01. PHP
code
2.2 establish member. PHP
< img Alt = "" border = 0 height = 270 SRC =" http://files.jb51.net/upload/2010-3/20100321153520531.jpg "width = 296 > < img Alt =" "border = 0 height = 202 SRC = "http://files.jb51.net/upload/2010-3/20100321153520223.jpg" width = 371 >
3. Modify data
3.1 establish level.php (modify data)
3.2 establish up_level. PHP
4.2 establish newmember. PHP
< img Alt = "" border = 0 height = 254 SRC =" http://files.jb51.net/upload/2010-3/20100321153520928.jpg "width = 263 > < img Alt =" "border = 0 height = 192 SRC = "http://files.jb51.net/upload/2010-3/20100321153520156.jpg" width = 355 >
5.2 set up the test. The PHP
< img Alt = "" border = 0 height = 122 SRC =" http://files.jb51.net/upload/2010-3/20100321153520822.jpg "width = 268 >
6 summarizes
6.1 mysql_connect() : establishes a connection to the MySQL server
6.2 mysql_select_db() : select the database
6.3 mysql_query() : execute the database query
6.4 mysql_fetch_array() : gets the database record
6.5 mysql_num_rows() : gets the number of records from the query
6.6 mysql_affected_rows() : the number of rows affected by the last operation
6.7 mysql_insert_id() : the ID value of the last insert record
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');
< img Alt = "" border = 0 height = 156 SRC =" http://files.jb51.net/upload/2010-3/20100321153520830.jpg "width = 531 >
2 read data
2.1 establish 01. PHP
code
<html>
<head>
<meta http-equiv="Content-Type" content="text/html;charset=GB2312"/>
<title> The member list </title>
</head>
<?php
$link=mysql_connect("localhost","root","123"); //Connect to mysql server
$db=mysql_select_db("club"); //Select database
mysql_query("set names utf8",$link); //Set the encoding mode
$sql="Select * from member";
$result=mysql_query($sql,$link); //Perform a select query
$num=mysql_num_rows($result); //Retrieve record query
?>
<body>
<h1> Fitness club Register of members </h1>
<br />
Click the name to view the details of the existing member <?php echo $num ?> People.
<br />
<?php
if($num>0)
{
?>
<table border="1" cellpadding="1" cellspacing="1">
<tr>
<td> The serial number </td>
<td> The name </td>
<td> gender </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?" female ":" male ")."</td></tr>";
}
?>
</table>
<?php
}
else
{
echo " The club has not yet taken on members. ";
}
?>
</body>
</html>
2.2 establish member. PHP
<html>
<head>
<meta http-equiv="Content-Type" content="text/html;charset=GB2312"/>
<title> Membership details </title>
</head>
<?php
$link=mysql_connect("localhost","root","123"); //Connect to mysql server
$db=mysql_select_db("club"); //Select database
mysql_query("set names utf8",$link); //Set the encoding mode
$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); //Execute in the select query
?>
<body>
<h1> Fitness club Membership details </h1>
<?php
if($row=mysql_fetch_array($result))
{
echo " No. : ".$row['no']."<br />";
echo " Name: ".$row['name']."<br />";
echo " Gender: ".($row['sex']==1?" female ":" male ")."<br />";
echo " Age: ".$row['age']."<br />";
echo " Levels: ".$row['level']."<br />";
echo " Add: ".$row['join_date']."<br />";
}
?>
</body>
</html>
< img Alt = "" border = 0 height = 270 SRC =" http://files.jb51.net/upload/2010-3/20100321153520531.jpg "width = 296 > < img Alt =" "border = 0 height = 202 SRC = "http://files.jb51.net/upload/2010-3/20100321153520223.jpg" width = 371 >
3. Modify data
3.1 establish level.php (modify data)
<html>
<head>
<meta http-equiv="Content-Type" content="text/html;charset=GB2312" />
<title> Club discount </title>
</head>
<body>
<h1> Statistics of club members </h1>
<?php
$link=mysql_connect("localhost","root","123"); //Connect to mysql server
$db=mysql_select_db("club"); //Select database
mysql_query("set name utf8",$link); //Set the encoding mode
$sql="Select level,count(*) as num from member group by level";
$result=mysql_query($sql,$link); //Perform a select query
while($row=mysql_fetch_array($result))
{
switch($row['level']){
case 'bj':
echo " Level: platinum member The number: ".$row['num']."<br />";
break;
case 'hj':
echo " Level: gold membership The number: ".$row['num']."<br />";
break;
default:
echo " Level: diamond member The number: ".$row['num']."<br />";
}
}
?>
<form action="up_level.php" name="level" method="post">
Membership upgrade: from
<select name="old_level">
<option value="hj"> Gold membership </option>
<option value="bj"> Platinum member </option>
</select>
To upgrade to
<select name="new_level">
<option value="bj"> Platinum member </option>
<option value="zs"> Diamond membership </option>
</select>
<input type="submit" value=" determine "/>
</form>
</body>
</html>
3.2 establish up_level. PHP
<html>
<head>
<meta http-equiv="Content-Type" content="text/html;charset=GB2312" />
<title> Club discount </title>
</head>
<body>
<?php
$link=mysql_connect("localhost","root","123"); //Connect to mysql server
$db=mysql_select_db("club"); //Select database
mysql_query("set name utf8",$link); //Set the encoding mode
$sql="update member set level='".trim($_POST['new_level'])
."' where level='".trim($_POST['old_level'])."'";
$result=mysql_query($sql,$link); //Perform a select query
echo mysql_affected_rows($link)." people from ";
switch(trim($_POST['old_level'])){
case 'bj':
echo " Platinum member " ;
break;
case 'hj':
echo " Gold membership ";
break;
default:
echo " Diamond membership ";
}
echo " Successfully upgraded to ";
switch(trim($_POST['new_level'])){
case 'bj':
echo " Platinum member " ;
break;
case 'hj':
echo " Gold membership ";
break;
default:
echo " Diamond membership ";
}
?>
</body>
</html>
< img Alt = "" border = 0 height = 207 SRC =" http://files.jb51.net/upload/2010-3/20100321153520635.jpg "width = 457 >
< img Alt = "" border = 0 height = 84 SRC =" http://files.jb51.net/upload/2010-3/20100321153520356.jpg "width = 298 >
4. Add data
4.1 establish add_member. PHP
<html>
<meta http-equiv="Content-Type" content="text/html;charset=GB2312"/>
<title> New members </title>
<body>
<h1> New member </h1>
<form action="newmember.php" method="post" name="add_member">
No. : <input type="text" name="no" width="40"/><br />
Name: <input type="text" name="name" width="40"/><br />
Gender:
<input type="radio" name="sex" value="1" /> female
<input type="radio" name="sex" value="2" /> male <br />
Age: <input type="text" name="age" width="40" /><br />
Levels:
<select name="level">
<option value="hj"> Gold membership </option>
<option value="bj"> Platinum member </option>
<option value="zs"> Diamond membership </option>
</select><br />
<input type="submit" value=" determine " />
</form>
</body>
</html>
4.2 establish newmember. PHP
<html>
<head>
<meta http-equiv="Content-Type" content="text/html;charset=GB2312" />
<title> Add member </title>
</head>
<body>
<?php
$link=mysql_connect("localhost","root","123"); //Connect to mysql server
$db=mysql_select_db("club"); //Select database
mysql_query("set names GB2312",$link); //Set the encoding mode
$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); //Perform a select query
$m_id=mysql_insert_id($link); //Gets the id of the newly inserted member record
if(trim($_POST['level'])=="hj") //Judge new member offers
{
$sql="Update member set level='bj' where id='".$m_id."'";
$result=mysql_query($sql,$link); //Implement member upgrade offer
$text=" Has been upgraded to platinum membership. ";
}
$sql="Select *,date_format(date,'%Y-%c-%d') as join_date from member "
."where id='".$m_id."'";
$result=mysql_query($sql,$link); //Perform a select query
if($row=mysql_fetch_array($result))
{
echo " Information of new members: <br />";
echo " No. : ".$row['no']."<br />";
echo " Name: ".$row['name']."<br />";
echo " Gender: ".($row['sex']==1?" female ":" male "."<br />");
echo " Age: ".$row['age']."<br />";
echo " Levels: ".$row['level']."<br />";
echo " Add: ".$row['join_date']."<br />";
}
echo " New members ".$row['name']." Add a success ".$text;
?>
</body>
</html>
< img Alt = "" border = 0 height = 254 SRC =" http://files.jb51.net/upload/2010-3/20100321153520928.jpg "width = 263 > < img Alt =" "border = 0 height = 192 SRC = "http://files.jb51.net/upload/2010-3/20100321153520156.jpg" width = 355 >
< img Alt = "" border = 0 height = 171 SRC =" http://files.jb51.net/upload/2010-3/20100321153520391.jpg "width = 539 >
Create a class database connection
5.1 create the cls_mysql.php class file
<?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>:$messagenn";
}
else
{
echo "<b>MySQL server error report:";
print_r($this->error_message);
}
exit;
}
}
?>
5.2 set up the test. The PHP
<?php
include("cls_mysql.php");
?>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html;charset=GB2312" />
<title>Mysql The class library test </title>
</head>
<body>
<?php
$sql="Select * from member";
$db=new cls_mysql('localhost','root','123','club','GB2312');
$result=$db->selectLimit($sql,'3'); //Returns three member profiles from the database
if($result)
{
while($row=$db->fetch_array($result))
{
echo " Member no. : " .$row['no']." The name: ".$row['name']."<br />";
}
}
?>
</body>
</html>
< img Alt = "" border = 0 height = 122 SRC =" http://files.jb51.net/upload/2010-3/20100321153520822.jpg "width = 268 >
6 summarizes
6.1 mysql_connect() : establishes a connection to the MySQL server
6.2 mysql_select_db() : select the database
6.3 mysql_query() : execute the database query
6.4 mysql_fetch_array() : gets the database record
6.5 mysql_num_rows() : gets the number of records from the query
6.6 mysql_affected_rows() : the number of rows affected by the last operation
6.7 mysql_insert_id() : the ID value of the last insert record