WeChat public platform development-Database Operations

[PHP public platform development series] 01. Configuration Interface
02. Public platform sample code analysis
03. subscribe Processing
04. simple reply function development
05. Development of the weather forecast function
06. Development of translation functions
07. chatbot function development
08. custom menu functions
09. Database Operations
Address: http://www.phpchina.com/archives/view-43411-1.html
This series is provided by PHPChina's special author @ David _tang. For more information, see the author information and the address of this article. I. Introduction

The Functional Development described above is completed by Simple API calls and database operations are not performed. In the subsequent advanced function development, you need to use the database. Therefore, in this article, we will give a brief introduction to MySQL database operations for your reference.

Ii. Train of Thought Analysis

The Baidu Developer Center provides powerful cloud databases (including MySQL, MongoDB, and Redis). In this tutorial, we will perform Operation demonstrations on familiar MySQL databases, interaction with databases.

It is very easy to use apsaradb In the BAE application. The name in the Database List is the dbname used to connect to the database. The user name, password, connection address, and port are extracted from the application using environment variables.

You can use the standard PHP Mysql or PHP Mysqli extension to access the database. The bae php already provides these two extensions, which can be directly used by applications.

Official documentation, please refer to: http://developer.baidu.com/wiki/index.php? Title = docs/cplat/rt/mysql

3. Create a BAE MySQL database

3.1 log onto Baidu Developer Center-> Management Center-> select Application-> cloud environment-> Service Management-> MySQL (cloud database)-> Create Database

3.2 Create a database

Note:Each application has a 1 GB free quota for only one database, and other databases do not enjoy a free quota. You can use this offer again only when you delete a database with a free quota.

3.3 Creation successful

Here we can see the name of the database, that is, dbname, which will be used later.

Click "phpMyadmin" to access the database.

3.4 phpMyadmin Interface

Create a data table, enter the table name and number of fields, and click "execute" to create a table.

3.5 create a table

Enter the field name and field type. after entering the field, click "save" below to create the table.

3.6 creation complete

Modify the id field to the primary key and add AUTO_INCREMENT. Modify the from_user field to be UNIQUE (UNIQUE) to modify the table.

You can also use the following SQL statement to create a table:

CREATE TABLE IF NOT EXISTS `test_mysql` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `from_user` varchar(40) DEFAULT NULL,  `account` varchar(40) DEFAULT NULL,  `password` varchar(40) DEFAULT NULL,  `update_time` datetime DEFAULT NULL,  PRIMARY KEY (`id`),  UNIQUE KEY `from_user` (`from_user`));

PhpMyAdmin operation

The creation of the database and data table ends here. The following code describes how to use the database and data table.

Iv. Official example (PHP MySQL)

The demo (PHP MySQL) officially provided by BAE is as follows:

Mysql/basic. php file content

<? Php/*** MySQL example. You can familiarize yourself with the use of MySQL on the BAE platform (CRUD) */require_once (".. /configure. php ");/* replace it with your own database name (which can be viewed from the Management Center) */$ dbname = MYSQLNAME; /* obtain the database connection parameters from the environment variables */$ host = getenv ('HTTP _ BAE_ENV_ADDR_ SQL _IP '); $ port = getenv ('HTTP _ BAE_ENV_ADDR_ SQL _PORT '); $ user = getenv ('HTTP _ BAE_ENV_AK '); $ pwd = getenv ('HTTP _ BAE_ENV_SK');/* Then call mysql_connect () connect to the server */$ link = @ mysql_connect ("{$ host }:{ $ port}", $ user, $ pwd, true); if (! $ Link) {die ("Connect Server Failed:". mysql_error ();}/* Call mysql_select_db () immediately after successful connection to select the database to be connected */if (! Mysql_select_db ($ dbname, $ link) {die ("Select Database Failed :". mysql_error ($ link);}/* Now the connection has been fully established, you can perform corresponding operations on the current database *//*!!! Note: you cannot use this connection to call mysql_select_db to switch to another database !!! * // * Connect to another database, use mysql_connect + mysql_select_db to start another connection * // ***. Then you can use other standard php mysql functions to perform database operations * // create a database table $ SQL =" create table if not exists test_mysql (id int primary key auto_increment, no int, name varchar (1024), key idx_no (no) "; $ ret = mysql_query ($ SQL, $ link); if ($ ret = false) {die ("Create Table Failed :". mysql_error ($ link);} else {echo "Create Table Succeed <br/>";} // insert data $ SQL = "insert into test_mysql (no, name) values (2007, 'This is a test message'), (2008, 'this is another test message'), (2009, 'xxxxxxxxxxxxxxxxxx') "; $ ret = mysql_query ($ SQL, $ link); if ($ ret = false) {die (" Insert Failed :". mysql_error ($ link);} else {echo "Insert Succeed <br/>" ;}// delete data $ SQL = "delete from test_mysql where no = 2008 "; $ ret = mysql_query ($ SQL, $ link); if ($ ret = false) {die (" Delete Failed :". mysql_error ($ link);} else {echo "Delete Succeed <br/> ";} // modify data $ SQL = "update test_mysql set name = 'yyyy' where no = 2009"; $ ret = mysql_query ($ SQL, $ link ); if ($ ret = false) {die ("Update Failed :". mysql_error ($ link);} else {echo "Update Succeed <br/>" ;}// retrieve data $ SQL = "select id, no, name from test_mysql "; $ ret = mysql_query ($ SQL, $ link); if ($ ret = false) {die ("Select Failed :". mysql_error ($ link);} else {echo "Select Succeed <br/>"; while ($ row = mysql_fetch_assoc ($ ret )) {echo "{$ row ['id']} {$ row ['no']} {$ row ['name']} <br/> ";}} // delete a table $ SQL = "drop table if exists test_mysql"; $ ret = mysql_query ($ SQL, $ link); if ($ ret = false) {die ("Drop Table Failed :". mysql_error ($ link);} else {echo "Drop Table Succeed <br/>" ;}?>

Configure. php file content

<? Php/*** configure the database name ***/define ("MYSQLNAME", "qzMlSkByflhScPCOFtax");?>

Test use:

Execution successful.

5. change to a callable function form (PHP MySQL)

5.1 create a data table

// Create a database table function _ create_table ($ SQL) {mysql_query ($ SQL) or die ('table creation failed, error message :'. mysql_error (); return "table created successfully ";}

5.2 Insert data

// Insert data function _ insert_data ($ SQL) {if (! Mysql_query ($ SQL) {return 0; // data insertion Failed} else {if (mysql_affected_rows ()> 0) {return 1; // insert successful} else {return 2; // No rows are affected }}}

5.3 delete data

// Delete data function _ delete_data ($ SQL) {if (! Mysql_query ($ SQL) {return 0; // deletion failed} else {if (mysql_affected_rows ()> 0) {return 1; // deletion successful} else {return 2; // No rows are affected }}}

5.4 modify data

// Modify the data function _ update_data ($ SQL) {if (! Mysql_query ($ SQL) {return 0; // data update failed} else {if (mysql_affected_rows ()> 0) {return 1; // update successful ;} else {return 2; // No rows are affected }}}

5.5 Data Retrieval

// Retrieve data function _ select_data ($ SQL) {$ ret = mysql_query ($ SQL) or die ('SQL statement error, error message :'. mysql_error (); return $ ret ;}

5.6 delete a data table

// Delete the table function _ drop_table ($ SQL) {mysql_query ($ SQL) or die ('failed to delete the table, error message :'. mysql_error (); return "Table deleted successfully ";}

Combine the above functions with the database connection code to generate the mysql_bae.func.php file for the following test.

Vi. Test MySQL function usage

6.1 create a new file dev_mysql.php in the same directory and introduce the mysql_bae.func.php File

require_once './mysql_bae.func.php';

6.2 test table Creation

Delete the test_mysql table created using phpMyAdmin. The test statement is as follows:

// CREATE a TABLE $ create_ SQL = "CREATE TABLE IF NOT EXISTS 'test _ mysql' ('id' int (11) NOT NULL AUTO_INCREMENT, 'From _ user' varchar (40) default null, 'account' varchar (40) default null, 'Password' varchar (40) default null, 'Update _ time' datetime default null, primary key ('id '), unique key 'from _ user' ('from _ user') "; echo _ create_table ($ create_ SQL );

Test result:

Go to phpMyAdmin to view

Intentionally write SQL statements incorrectly

Test Error result:

6.3 test data insertion

The test statement is as follows:

// Insert data $ insert_ SQL = "insert into test_mysql (from_user, account, password, update_time) values ('David', '123', 'abc ', '2017-09-29 17:14:28 ') "; $ res = _ insert_data ($ insert_ SQL); if ($ res = 1) {echo" inserted successfully ";} else {echo "insertion failed ";}

Test results:

6.4 test and update data

The test statement is as follows:

// Update data $ update_ SQL = "update test_mysql set account = 860512 where account = 860510"; $ res = _ update_data ($ update_ SQL); if ($ res = 1) {echo "updated successfully";} elseif ($ res = 0) {echo "update failed";} elseif ($ res = 2) {echo "No rows affected ";}

Test results:

Update again:

6.5 test data deletion

The test statement is as follows:

// Delete data $ delete_ SQL = "delete from test_mysql where account = 860512"; $ res = _ delete_data ($ delete_ SQL); if ($ res = 1) {echo "deleted successfully";} elseif ($ res = 0) {echo "failed to delete";} elseif ($ res = 2) {echo "this record does not exist ";}

Test results:

Delete again:

6.6 Test Data Retrieval

Execute the preceding insert operation again to perform a search test. The test statement is as follows:

// Retrieve data $ select_ SQL = "select * from test_mysql"; $ result = _ select_data ($ select_ SQL); while ($ rows = mysql_fetch_array ($ result, MYSQL_ASSOC )) {echo $ rows [id]. "--". $ rows [from_user]. "--". $ rows [account]. "--". $ rows [password]. "--". $ rows [update_time]; echo "<br/> ";}

Test results:

6.7 test table Deletion

The test statement is as follows:

// Delete the table $ drop_ SQL = "drop table if exists test_mysql"; echo _ drop_table ($ drop_ SQL );

Test results:

All MySQL function tests are successful.

VII. Interaction (Mysql extension)

Make sure that the test_msyql table exists in the Database. Here we test the addition, deletion, modification, and query operations on the MySQL database. In special cases, we only perform the test using the following method:

1. Bind + account + password for example: bind + 860512 + abc1232. query for example: Query 3. Modify + old password + new password for example: Modify + abc123 + 1234564. Delete For example: Delete

7.1 introduce the mysql_bae.func.php File

// Introduce the database function file require_once 'mysql _ bae. func. php ';

7.2 prerequisites

A. Split the input statement into an array separated by "+"

$keywords = explode("+",$keyword);

B. Obtain the current time

// Obtain the current time $ nowtime = date ("Y-m-d G: I: s ");

C. Determine whether the user has been bound

// Determine whether $ select_ SQL = "SELECT id from test_mysql WHERE from_user = '$ fromUsername'"; $ res = _ select_data ($ select_ SQL ); $ rows = mysql_fetch_array ($ res, MYSQL_ASSOC); if ($ rows [id] <> '') {$ user_flag = 'y ';}

7.3 Test the insert operation

Test code:

If (trim ($ keywords [0] = 'bine') {if ($ user_flag <> 'y') {$ insert_ SQL = "INSERT INTO test_mysql (from_user, account, password, update_time) VALUES ('$ fromusername',' $ keywords [1] ',' $ keywords [2] ',' $ nowtime ')"; $ res = _ insert_data ($ insert_ SQL); if ($ res = 1) {$ contentStr = "bound successfully";} elseif ($ res = 0) {$ contentStr = "binding failed" ;}} else {$ contentStr = "this account has been bound ";}}

Test results:

7.4 Test query operations

Test code:

If (trim ($ keywords [0] = 'query') {$ select_ SQL = "SELECT * FROM test_mysql WHERE from_user = '$ fromusername '"; $ select_res = _ select_data ($ select_ SQL); $ rows = mysql_fetch_assoc ($ select_res); if ($ rows [id] <> '') {$ contentStr =" account: $ rows [account] \ n ". "password: $ rows [password] \ n ". "From_user: $ rows [from_user] \ n ". "Update Time: $ rows [update_time]";} else {$ contentStr = "You have not bound an account. You cannot find the relevant information. Please bind it first. Thank you! ";}}

Test results:

7.5 Test update operations

Test code:

If (trim ($ keywords [0] = "modify") {$ old_password = $ keywords [1]; $ new_password = $ keywords [2]; $ select_password_ SQL = "SELECT * FROM test_mysql WHERE from_user = '$ fromUsername'"; $ select_res = _ select_data ($ select_password_ SQL); $ rows = mysql_fetch_assoc ($ select_res ); if ($ old_password = $ rows [password]) {$ update_ SQL = "UPDATE test_mysql SET password = '$ new_password' WHERE from_user = '$ fromUsername '"; $ res = _ update_data ($ update_ SQL); if ($ res = 1) {$ contentStr = "modified successfully";} elseif ($ res = 0) {$ contentStr = "failed to modify" ;}} else {$ contentStr = "the original password is incorrect. Please confirm and try again ";}}

Test results:

7.6 Test the delete operation

Test code:

If (trim ($ keywords [0] = "DELETE") {$ delete_ SQL = "DELETE FROM test_mysql WHERE from_user = '$ fromusername '"; $ res = _ delete_data ($ delete_ SQL); if ($ res = 1) {$ contentStr = "deleted successfully";} elseif ($ res = 0) {$ contentStr = "deletion failed ";}}

Test results:

The interaction test is successful.

8. PHP Mysqli extension, encapsulated into classes

Encapsulate Mysqli extension into a class. The Code is as follows:

<? Phprequire_once 'pair des/configure. php'; class MySQLi_BAE {private $ mysqli; private $ host; private $ user; private $ password; private $ port; private $ database; // use function _ get ($ property_name) {if (isset ($ this-> $ property_name) when accessing private variables outside the class )) {return ($ this-> $ property_name);} else {return (NULL) ;}} function _ set ($ property_name, $ value) {$ this-> $ property_name = $ value;} function _ construct () {/* query from the platform Query the name of the database to be connected */$ this-> database = MYSQLNAME; /* obtain the database connection parameters from the environment variables */$ this-> host = getenv ('HTTP _ BAE_ENV_ADDR_ SQL _IP '); $ this-> user = getenv ('HTTP _ BAE_ENV_AK '); $ this-> password = getenv ('HTTP _ BAE_ENV_SK '); $ this-> port = getenv ('HTTP _ BAE_ENV_ADDR_ SQL _PORT '); $ this-> mysqli = new mysqli ($ this-> host, $ this-> user, $ this-> password, $ this-> database, $ this-> port); if ($ this-> mysqli-> connect_error) {die ("Connect Server Failed :". $ this-> mysqli-> error);} $ this-> mysqli-> query ("set names utf8");} // dql statement function execute_dql ($ query) {$ res = $ this-> mysqli-> query ($ query) or die ("operation failed ". $ this-> mysqli-> error); return $ res; // $ this-> mysqli-> close ();} // dml statement function execute_dml ($ query) {$ res = $ this-> mysqli-> query ($ query) or die ("operation failed ". $ this-> mysqli-> error); if (! $ Res) {return 0; // Failed} else {if ($ this-> mysqli-> affected_rows> 0) {return 1; // execution successful} else {return 2; // No rows are affected} // $ this-> mysqli-> close () ;}}?>

9. Use of test classes

9.1 test DML operations

Test code:

<? Phprequire_once "MySQLi_BAE.class.php"; $ mysqli_BAE = new MySQLi_BAE (); // *************** dml ********************** $ SQL =" insert into test_mysql (from_user, account, password, update_time) values ('David', '201312', 'abcabc', '2017-09-27 17:14:28 ')"; // $ SQL = "update test_mysql set account = 860512 where account = 860510"; // $ SQL = "delete from test_mysql where account = 860512 "; $ res = $ mysqli_BAE-> execute_dml ($ SQL); I F ($ res = 0) {echo "execution failed";} elseif ($ res = 1) {echo "execution successful ";} else {echo "no impact on the number of rows" ;}?>

Test results:

9.2 test DQL operations

Test code:

<?phprequire_once "MySQLi_BAE.class.php";$mysqli_BAE=new MySQLi_BAE();//**************dql******************$sql="select * from test_mysql";$res=$mysqli_BAE->execute_dql($sql);while($row=$res->fetch_row()){        foreach($row as $key=>$val){        echo "$val--";    }    echo '<br/>';}$res->free();?>

Test results:

10. Interaction with implementation (Mysqli extension)

10.1 prerequisites

A. Introduce the MySQLi_BAE.class.php File

// Introduce the database function file require_once "MySQLi_BAE.class.php ";

B. instantiate the object

public function __construct(){    $this->mysqli_BAE=new MySQLi_BAE();}

10.2 test the insert operation

Test code:

$insert_sql="INSERT INTO test_mysql(from_user, account, password, update_time) VALUES('$fromUsername','$keywords[1]','$keywords[2]','$nowtime')";$res = $this->mysqli_BAE->execute_dml($insert_sql);

Test results:

10.3 test query operations

Test code:

$select_sql="SELECT * FROM test_mysql WHERE from_user='$fromUsername'";$select_res=$this->mysqli_BAE->execute_dql($select_sql);$rows=$select_res->fetch_array(MYSQLI_ASSOC);

Test results:

10.4 test update operations

Test code:

$update_sql="UPDATE test_mysql SET password='$new_password' WHERE from_user='$fromUsername'"; $res = $this->mysqli_BAE->execute_dml($update_sql);

Test results:

10.5 test the delete operation

Test code:

$delete_sql="DELETE FROM test_mysql WHERE from_user='$fromUsername'";$res = $this->mysqli_BAE->execute_dml($delete_sql);

Test results:

The interaction test is successful.