Common Code collection for PHP5 and MySQL database operations

Php is popular recently, so more and more friends are learning php. Here we will sort out some database operations. For more information, see.

Php is popular recently, so more and more friends are learning php. Here we will sort out some database operations. For more information, see.

1. Create a database table:
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, '2017-04-02 00:00:00 '),
(2, 'c022', 'liyan ', 29, 'zs', 1, '2017-05-31 00:00:00 '),
(3, 'a006 ', 'zhangyan', 36, 'Hj ', 1, '2017-06-20 00:00:00 '),
(4, 'b052 ', 'luanying', 42, 'bj ', 1, '2017-02-12 00:00:00 '),
(5, 'a007 ', 'duxiang', 26, 'Hj ', 2, '2017-03-26 00:00:00 '),
(6, 'c060', 'liuyu ', 38, 'zs', 1, '2017-10-16 00:00:00 ');


2. Read data
2.1 create 01.php
Code
The Code is as follows:



Member List

$ Link = mysql_connect ("localhost", "root", "123"); // connect to the mysql server
$ Db = mysql_select_db ("club"); // select a database
Mysql_query ("set names utf8", $ link); // sets the encoding method.
$ SQL = "Select * from member ";
$ Result = mysql_query ($ SQL, $ link); // execute select query
$ Num = mysql_num_rows ($ result); // retrieve record Query
?>

Fitness Club member roster


Click the name to view the details of the member. Person.


If ($ num> 0)
{
?>











While ($ row = mysql_fetch_array ($ result )){Echo" ";}?>

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

}
Else
{
Echo "the club has not yet developed members. ";
}
?>



2.2 create member. php
The Code is as follows:



Member details

$ Link = mysql_connect ("localhost", "root", "123"); // connect to the mysql server
$ Db = mysql_select_db ("club"); // select a database
Mysql_query ("set names utf8", $ link); // sets the 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); // execute the query in select
?>

Fitness Club member details
If ($ row = mysql_fetch_array ($ result ))
{
Echo "no.:". $ row ['no']."
";
Echo "name:". $ row ['name']."
";
Echo "Gender:". ($ row ['sex'] = 1? "Female": "male ")."
";
Echo "age:". $ row ['age']."
";
Echo "level:". $ row ['level']."
";
Echo "added:". $ row ['join _ date']."
";
}
?>




3. modify data
3.1 create level. php (modify data)
The Code is as follows:



Club promotions


Club member statistical table
$ Link = mysql_connect ("localhost", "root", "123"); // connect to the mysql server
$ Db = mysql_select_db ("club"); // select a database
Mysql_query ("set name utf8", $ link); // sets the 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 "grade: Platinum Member count:". $ row ['num']."
";
Break;
Case 'Hj ':
Echo "grade: Gold member count:". $ row ['num']."
";
Break;
Default:
Echo "grade: diamond member count:". $ row ['num']."
";
}
}
?>




3.2 Create up_level.php
The Code is as follows:



Club promotions


$ Link = mysql_connect ("localhost", "root", "123"); // connect to the mysql server
$ Db = mysql_select_db ("club"); // select a database
Mysql_query ("set name utf8", $ link); // sets the 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). "from ";
Switch (trim ($ _ POST ['old _ level']) {
Case 'bj ':
Echo "platinum member ";
Break;
Case 'Hj ':
Echo "golden member ";
Break;
Default:
Echo "diamond member ";
}
Echo "successfully upgraded ";
Switch (trim ($ _ POST ['new _ level']) {
Case 'bj ':
Echo "platinum member ";
Break;
Case 'Hj ':
Echo "golden member ";
Break;
Default:
Echo "diamond member ";
}
?>

4. Add data
4.1 create add_member.php
The Code is as follows:


New member

New member




4.2 create newmember. php
The Code is as follows:



Add Member


$ Link = mysql_connect ("localhost", "root", "123"); // connect to the mysql server
$ Db = mysql_select_db ("club"); // select a database
Mysql_query ("set names GB2312", $ link); // sets the 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); // obtain the id of the newly inserted member record.
If (trim ($ _ POST ['level']) = "hj") // determine the new member discount
{
$ SQL = "Update member set level = 'bj 'where id = '". $ m_id ."'";
$ Result = mysql_query ($ SQL, $ link); // executes the membership upgrade offer
$ Text = "you have been given a special offer to upgrade 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); // execute select query
If ($ row = mysql_fetch_array ($ result ))
{
Echo "new member information:
";
Echo "no.:". $ row ['no']."
";
Echo "name:". $ row ['name']."
";
Echo "Gender:". ($ row ['sex'] = 1? "Female": "male "."
");
Echo "age:". $ row ['age']."
";
Echo "level:". $ row ['level']."
";
Echo "added:". $ row ['join _ date']."
";
}
Echo "new member". $ row ['name']. "added successfully". $ text;
?>

5. Create a database-like connection
5.1 create a cls_mysql.php File
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 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"Error info: $ Message \ n ";
}
Else
{
Echo"MySQL server error report :";
Print_r ($ this-> error_message );
}
Exit;
}
}
?>

5.2 create test. php
The Code is as follows:
Include ("cls_mysql.php ");
?>



Mysql class library Test


$ SQL = "Select * from member ";
$ Db = new cls_mysql ('localhost', 'root', '123', 'Club ', 'gb2312 ');
$ Result = $ db-> selectLimit ($ SQL, '3'); // returns three member information from the database.
If ($ result)
{
While ($ row = $ db-> fetch_array ($ result ))
{
Echo "Member no.:". $ row ['no']. ", name:". $ row ['name']."
";
}
}
?>




6. Conclusion
6.1 mysql_connect (): establishes a connection with the MySQL server
6.2 mysql_select_db (): select a database
6.3 mysql_query (): Execute Database Query
6.4 mysql_fetch_array (): Obtain database records
6.5 mysql_num_rows (): gets the number of records queried.
6.6 mysql_affected_rows (): number of rows affected by the last operation
6.7 mysql_insert_id (): ID value of the last inserted record