Collection of common codes for PHP5 and MySQL database operations_PHP Tutorial

1 Create database table:

Copy code The code is as follows:

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 Read data
2.1 Create 01.php
Code

Copy code The code is as follows:

< ;title>Member List

$link=mysql_connect("localhost","root","123"); //Connect to mysql Server
$db=mysql_select_db("club"); //Select database
mysql_query("set names utf8",$link); //Set encoding method
$sql="Select * from member ";
$result=mysql_query($sql,$link); //Execute select query
$num=mysql_num_rows($result); //Get record query
?>
< body>

Fitness Club Member List

Click on the name to view the member's details, existing membersPeople.


if($num>0)
{
?>






while($row=mysql_fetch_array($result))
{
echo "";
}
?>

Serial number	Name	Gender
.$row['name'].">". $row['name']."	" .($row['sex']==1?"Female":"Male")."

}
else
{
echo "The club has not yet developed members.";
}
?>

2.2 Create a member. php

Copy code The code is as follows:

< ;?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 encoding method
$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); //Executed in select query
?>

Fitness club member details

if($row=mysql_fetch_array($ result))
{
echo "Number: ".$row['no']."
";
echo "Name: ".$row['name']. "
";
echo "Gender:".($row['sex']==1?"Female":"Male")."
";
echo "Age:".$row['age']."
";
echo "Level:".$row['level']."
";
echo "Join:".$row['join_date']."
";
}
?>

3 Modify data
3.1 Create level.php (modify data)

Copy code The code is as follows:

< ;title>Club promotions

Club membership statistics table

$link=mysql_connect("localhost","root","123"); //Connect to mysql server
$db=mysql_select_db("club"); //Select database
mysql_query("set name utf8",$link); //Set encoding method
$sql="Select level,count(*) as num from member group by level";
$result=mysql_query($sql,$link) ; //Execute select query
while($row=mysql_fetch_array($result))
{
switch($row['level']){
case 'bj':
echo "Level: Platinum members Number of people: ".$row['num']."
";
break;
case 'hj':
echo "Level: Gold members Number of people: ".$row['num']."
";
break;
default:
echo "Level: Diamond Member Number of people:".$row['num']. "
";
}
}
?>

Member discount upgrade: from

Gold Member
Platinum Member

Upgrade to

Platinum Member
Diamond member

3.2 Create up_level.php

Copy code The code is as follows:

$link=mysql_connect("localhost","root","123"); //Connect to mysql server
$db=mysql_select_db("club"); //Select database
mysql_query("set name utf8",$link); //Set encoding method
$sql="update member set level='".trim($_POST['new_level'])
."' where level='". trim($_POST['old_level'])."'";
$result=mysql_query($sql,$link); //Execute select query
echo mysql_affected_rows($link)."人cong";
switch(trim($_POST['old_level'])){
case 'bj':
echo "Platinum Member" ;
break;
case 'hj':
echo "Gold Member";
break;
default:
echo "Diamond Member";
}
echo "Successfully upgraded to";
switch(trim($_POST[' new_level'])){
case 'bj':
echo "Platinum Member";
break;
case 'hj':
echo "Gold Member";
break;
default:
echo "Diamond Member";
}
?>

4 Add data
4.1 Create add_member.php

Copy code The code is as follows:

New member

Number:

Name:

Gender:
Female
Male

Age:< br />
Level:

Gold Member
Platinum Member
Diamond Member


< ;input type="submit" value="OK" />

4.2 Create newmember.php

Copy code The code is as follows:

$link=mysql_connect("localhost","root","123"); //Connect to the mysql server
$db=mysql_select_db( "club"); //Select database
mysql_query("set names GB2312",$link); //Set encoding method
$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); //Execute select query
$m_id=mysql_insert_id($link); //Get the id of the newly inserted member record
if( trim($_POST['level'])=="hj") //Judge new member discounts
{
$sql="Update member set level='bj' where id='".$m_id. "'";
$result=mysql_query($sql,$link); //Execute membership upgrade discount
$text="Have enjoyed the discount and upgraded to platinum member.";
}
$ sql="Select *,date_format(date,'%Y-%c-%d') as join_date from member "
."where id='".$m_id."'";
$result= mysql_query($sql,$link); //Execute select query
if($row=mysql_fetch_array($result))
{
echo "New member information:
";
echo "Number: ".$row['no']."
";
echo "Name: ".$row['name']."
" ;
echo "Gender:".($row['sex']==1?"Female":"Male"."
");
echo "Age:".$ row['age']."
";
echo "Level:".$row['level']."
";
echo "Join:" .$row['join_date']."
";
}
echo "New member".$row['name']."Added successfully".$text;
?>

5 Create a class database connection
5.1 Create the cls_mysql.php class file

Copy the code The code is as follows:

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 select 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 " error info:$messagenn";
}
else
{
echo "MySQL server error report:";
print_r($this->error_message);
}
exit;
}
}
?>

5.2 Create test.php

Copy code The code is as follows:

include(" cls_mysql.php");
?>






$sql=" Select * from member";
$db=new cls_mysql('localhost','root','123','club','GB2312');
$result=$db->selectLimit($ sql,'3'); //Return 3 member information from the database
if($result)
{
while($row=$db->fetch_array($result))
{
echo "Member number: " .$row['no'].", Name: ".$row['name']."
";
}
}
?>

6 Summary
6.1 mysql_connect(): Establish a connection with the MySQL server
6.2 mysql_select_db(): Select a database
6.3 mysql_query(): Execute a database query
6.4 mysql_fetch_array(): Get database records
6.5 mysql_num_rows(): Get the number of records obtained by the query
6.6 mysql_affected_rows(): The number of rows affected by the latest operation
6.7 mysql_insert_id(): The ID value of the most recently inserted record

http://www.bkjia.com/PHPjc/321489.htmlwww.bkjia.comtruehttp: //www.bkjia.com/PHPjc/321489.htmlTechArticle1 Create database table: Copy the code as follows: create database club; create table member( id int(11) not null auto_increment, no varchar(5) not null, name varchar(10) not null,...