1. Introduction

The function development explained earlier is completed by simply calling the API, without operating the database. In the subsequent development of advanced functions, a database will need to be used, so in this article, a brief introduction to the operation of the MySQL database will be provided for readers' reference.

2. Idea analysis

Baidu Developer Center provides powerful cloud databases (including MySQL, MongoDB, Redis). In this tutorial, we will introduce the MySQL that everyone is familiar with. The database performs operation demonstrations to realize the interaction between WeChat and the database.

It is very simple to use cloud database in BAE application. The name in the database list is the dbname when connecting to the database. The username, password, connection address and port are retrieved through environment variables in the application.

You can use the standard PHP Mysql or PHP Mysqli extension to access the database. These two extensions are already provided in BAE's PHP and can be used directly by the application.

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

3. Create BAE MySQL database

3.1 Log in to Baidu Developer Center-> Management Center-> Select Application-> Cloud Environment-> Service Management-> MySQL (Cloud Database) -> Create Database

3.2 Create database

#Note: Each application has and only one database enjoys the 1G free quota, and the other databases do not enjoy the free quota discount. . This offer can only be used again if the database that has used the free quota is deleted.

3.3 Successfully created

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

Click "phpMyadmin" to access the database.

3.4 phpMyadmin interface

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

3.5 Create a table

Enter the field name and field type. After completing the input, click "Save" below to complete the creation of the table.

3.6 Creation Complete

Modify the id field as the primary key and add AUTO_INCREMENT; modify the from_user field to unique (UNIQUE) to complete the modification of the table.

The table creation operation can also be completed using the following SQL statement:

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. Next, we will write code to explain in detail the use of the database and data table.

4. Official example (PHP MySQL)

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

mysql/basic.php file content

<?php /**
 * MySQL示例，通過該示例可熟悉BAE平臺MySQL的使用（CRUD）
 */
require_once("../configure.php");
    /*替換為你自己的數(shù)據(jù)庫名（可從管理中心查看到）*/
    $dbname = MYSQLNAME;
     
    /*從環(huán)境變量里取出數(shù)據(jù)庫連接需要的參數(shù)*/
    $host = getenv(&#39;HTTP_BAE_ENV_ADDR_SQL_IP&#39;);
    $port = getenv(&#39;HTTP_BAE_ENV_ADDR_SQL_PORT&#39;);
    $user = getenv(&#39;HTTP_BAE_ENV_AK&#39;);
    $pwd = getenv(&#39;HTTP_BAE_ENV_SK&#39;);
    
    /*接著調(diào)用mysql_connect()連接服務(wù)器*/
    $link = @mysql_connect("{$host}:{$port}",$user,$pwd,true);
    if(!$link) {
      die("Connect Server Failed: " . mysql_error());
    }
    /*連接成功后立即調(diào)用mysql_select_db()選中需要連接的數(shù)據(jù)庫*/
    if(!mysql_select_db($dbname,$link)) {
      die("Select Database Failed: " . mysql_error($link));
    }
    /*至此連接已完全建立，就可對當(dāng)前數(shù)據(jù)庫進(jìn)行相應(yīng)的操作了*/
    /*?。?！注意，無法再通過本次連接調(diào)用mysql_select_db來切換到其它數(shù)據(jù)庫了！?。?/
    /* 需要再連接其它數(shù)據(jù)庫，請再使用mysql_connect+mysql_select_db啟動另一個連接*/
     
    /**
    * 接下來就可以使用其它標(biāo)準(zhǔn)php mysql函數(shù)操作進(jìn)行數(shù)據(jù)庫操作
    */
    
    //創(chuàng)建一個數(shù)據(jù)庫表
    $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 />";
????}
????
????//插入數(shù)據(jù)
????$sql?=?"insert?into?test_mysql(no,?name)?values(2007,'this?is?a?test?message'),
????????????(2008,'this?is?another?test?message'),
????????????(2009,'xxxxxxxxxxxxxx')";
????$ret?=?mysql_query($sql,?$link);
????if?($ret?===?false)?{
????????die("Insert?Failed:?"?.?mysql_error($link));
????}?else?{
????????echo?"Insert?Succeed<br>";
????}
????
????//刪除數(shù)據(jù)
????$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>";
????}
????
????//修改數(shù)據(jù)
????$sql?=?"update?test_mysql?set?name?=?'yyyyyy'?where?no?=?2009";
????$ret?=?mysql_query($sql,?$link);
????if?($ret?===?false)?{
????????die("Update?Failed:?"?.?mysql_error($link));
????}?else?{
????????echo?"Update?Succeed<br>";
????}
????
????
????//檢索數(shù)據(jù)
????$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>";
????????}
????}
????
????//刪除表
????$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     /***配置數(shù)據(jù)庫名稱***/
    define("MYSQLNAME", "qzMlSkByflhScPCOFtax");

?>

Test use:

execution succeed.

5. Modify it into a callable function form (PHP MySQL)

5.1 Create a data table

//創(chuàng)建一個數(shù)據(jù)庫表
function?_create_table($sql){
????mysql_query($sql)?or?die('創(chuàng)建表失敗，錯誤信息：'.mysql_error());
????return?"創(chuàng)建表成功";
}

5.2 Insert Data

//插入數(shù)據(jù)
function?_insert_data($sql){
??????if(!mysql_query($sql)){
????????return?0;????//插入數(shù)據(jù)失敗
????}else{
??????????if(mysql_affected_rows()>0){
??????????????return?1;????//插入成功
??????????}else{
??????????????return?2;????//沒有行受到影響
??????????}
????}
}

5.3 Delete data

//刪除數(shù)據(jù)
function?_delete_data($sql){
??????if(!mysql_query($sql)){
????????return?0;????//刪除失敗
??????}else{
??????????if(mysql_affected_rows()>0){
??????????????return?1;????//刪除成功
??????????}else{
??????????????return?2;????//沒有行受到影響
??????????}
????}
}

5.4 Modify data

//修改數(shù)據(jù)
function?_update_data($sql){
??????if(!mysql_query($sql)){
????????return?0;????//更新數(shù)據(jù)失敗
????}else{
??????????if(mysql_affected_rows()>0){
??????????????return?1;????//更新成功;
??????????}else{
??????????????return?2;????//沒有行受到影響
??????????}
????}
}

5.5 Retrieve data

//檢索數(shù)據(jù)
function?_select_data($sql){
????$ret?=?mysql_query($sql)?or?die('SQL語句有錯誤，錯誤信息：'.mysql_error());
????return?$ret;
}

5.6 Delete data table

//刪除表
function?_drop_table($sql){
????mysql_query($sql)?or?die('刪除表失敗，錯誤信息：'.mysql_error());
????return?"刪除表成功";
}

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

6. 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 creation table

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

//創(chuàng)建表
$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);

測試正確結(jié)果：

?

到phpMyAdmin中查看

故意將SQL語句寫錯

測試錯誤結(jié)果：

6.3 測試插入數(shù)據(jù)

測試語句如下：

//插入數(shù)據(jù)
$insert_sql?=?"insert?into?test_mysql(from_user,?account,?password,?update_time)?values('David','860510',?'abcabc',?'2013-09-29?17:14:28')";

$res?=?_insert_data($insert_sql);
if($res?==?1){
????echo?"插入成功";
}else{
????echo?"插入失敗";
}

測試結(jié)果：

6.4 測試更新數(shù)據(jù)

測試語句如下：

//更新數(shù)據(jù)
$update_sql?=?"update?test_mysql?set?account?=?860512?where?account?=?860510";

$res?=?_update_data($update_sql);
if($res?==?1){
????echo?"更新成功";
}elseif($res?==?0){
????echo?"更新失敗";
}elseif($res?==?2){
????echo?"沒有行受到影響";
}

測試結(jié)果：

再次更新：

6.5 測試刪除數(shù)據(jù)

測試語句如下：

//刪除數(shù)據(jù)
$delete_sql?=?"delete?from?test_mysql?where?account?=?860512";

$res?=?_delete_data($delete_sql);
if($res?==?1){
????echo?"刪除成功";
}elseif($res?==?0){
????echo?"刪除失敗";
}elseif($res?==?2){
????echo?"沒有該條記錄";
}

測試結(jié)果：

再次刪除：

6.6 測試檢索數(shù)據(jù)

再次執(zhí)行上面的插入操作做檢索測試，測試語句如下：

//檢索數(shù)據(jù)
$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>";

}

測試結(jié)果：

6.7 測試刪除表

測試語句如下：

//刪除表$drop_sql?=?"drop?table?if?exists?test_mysql";echo?_drop_table($drop_sql);

測試結(jié)果：

MySQL 函數(shù)測試全部成功。

七、實現(xiàn)與微信的交互（Mysql 擴(kuò)展）

保證數(shù)據(jù)庫中存在test_msyql表，這里測試微信對MySQL數(shù)據(jù)庫的增刪改查操作，不考慮特殊情況，只按照下面的方法測試：

1.?綁定+賬戶+密碼
如：綁定+860512+abc123

2.?查詢
如：查詢

3.?修改+舊密碼+新密碼
如：修改+abc123+123456

4.?刪除
如：刪除

7.1?引入mysql_bae.func.php 文件

//引入數(shù)據(jù)庫函數(shù)文件require_once?'mysql_bae.func.php';

7.2 前置操作

A. 將輸入的語句拆分成數(shù)組，以“+”號分隔

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

B. 獲取當(dāng)前時間

//獲取當(dāng)前時間$nowtime=date("Y-m-d?G:i:s");

C. 判斷用戶是否已經(jīng)綁定

//判斷是否已經(jīng)綁定
$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 測試插入操作

測試代碼：

if(trim($keywords[0]?==?'綁定')){
????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?=?"綁定成功";
????????}elseif($res?==?0){
????????????$contentStr?=?"綁定失敗";
????????}
????}else{
????????$contentStr?=?"該賬戶已綁定";
????}
}

測試結(jié)果：

7.4 測試查詢操作

測試代碼：

if(trim($keywords[0]?==?'查詢')){
????$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="賬戶:$rows[account]\n"."密碼：$rows[password]\n"."From_user：$rows[from_user]\n"."更新時間：$rows[update_time]";
????}else{
????$contentStr="您還未綁定賬戶，查詢不到相關(guān)信息，請先綁定，謝謝！";
????}
}

測試結(jié)果：

7.5 測試更新操作

測試代碼：

if(trim($keywords[0]?==?"修改")){
????$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?=?"修改成功";
????????}elseif($res?==?0){
????????????$contentStr?=?"修改失敗";
????????}
????}else{
????????$contentStr?=?"原密碼有誤，請確認(rèn)后重試";
????}
}

測試結(jié)果：

7.6 測試刪除操作

測試代碼：

if(trim($keywords[0]?==?"刪除")){
????$delete_sql="DELETE?FROM?test_mysql?WHERE?from_user='$fromUsername'";
????$res?=?_delete_data($delete_sql);
????if($res?==?1){
????????$contentStr?=?"刪除成功";
????}elseif($res?==?0){
????????$contentStr?=?"刪除失敗";
????}
}

測試結(jié)果：

與微信的交互測試成功。

八、PHP Mysqli 擴(kuò)展，封裝成類

將Mysqli 擴(kuò)展封裝成類使用，代碼如下：

<?php require_once &#39;includes/configure.php&#39;;

class MySQLi_BAE{

    private $mysqli;
    private $host;
    private $user;
    private $password;
    private $port;
    private $database;

    //在類之外訪問私有變量時使用
    function __get($property_name){
        if(isset($this->$property_name)){
????????????return($this->$property_name);
????????}else{
????????????return(NULL);
????????}????
????}

????function?__set($property_name,?$value){
????????$this->$property_name=$value;
????}

????function?__construct(){

????????/*從平臺獲取查詢要連接的數(shù)據(jù)庫名稱*/
????????$this->database?=?MYSQLNAME;

????????/*從環(huán)境變量里取出數(shù)據(jù)庫連接需要的參數(shù)*/
????????$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("操作失敗".$this->mysqli->error);
????????return?$res;
????????
????????//$this->mysqli->close();
????}

????//dml?statement
????function?execute_dml($query){
????????
????????$res?=?$this->mysqli->query($query)?or?die("操作失敗".$this->mysqli->error);
????????
????????if(!$res){
????????????return?0;//失敗
????????}else{
????????????if($this->mysqli->affected_rows?>?0){
????????????????return?1;//執(zhí)行成功
????????????}else{
????????????????return?2;//沒有行受影響
????????????}
????????}
????
????????//$this->mysqli->close();
????}
}
?>

九、測試類的使用

9.1 測試DML操作

測試代碼：

<?php require_once "MySQLi_BAE.class.php";

$mysqli_BAE=new MySQLi_BAE();


//**************dml*******************
$sql="insert into test_mysql (from_user, account, password, update_time) values(&#39;David&#39;,&#39;860510&#39;, &#39;abcabc&#39;, &#39;2013-09-27 17:14:28&#39;)";

//$sql="update test_mysql set account = 860512 where account = 860510";

//$sql="delete from test_mysql where account = 860512";

$res=$mysqli_BAE->execute_dml($sql);

if($res==0){
????echo?"執(zhí)行失敗";
}elseif($res==1){
????echo?"執(zhí)行成功";
}else{
????echo?"沒有行數(shù)影響";
}
?>

測試結(jié)果：

9.2 測試DQL操作

測試代碼：

<?php require_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();
?>

測試結(jié)果：

十、實現(xiàn)與微信的交互（Mysqli 擴(kuò)展）

10.1 前置操作

A. 引入MySQLi_BAE.class.php 文件

//引入數(shù)據(jù)庫函數(shù)文件require_once?"MySQLi_BAE.class.php";

B. 實例化對象

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

10.2 測試插入操作

測試代碼：

$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);

測試結(jié)果：

10.3 測試查詢操作

測試代碼：

$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);

測試結(jié)果：

10.4 測試更新操作

測試代碼：

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

測試結(jié)果：

10.5 測試刪除操作

測試代碼：

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

測試結(jié)果：

與微信交互測試成功。?

更多WeChat public platform development database operation相關(guān)文章請關(guān)注PHP中文網(wǎng)！