Database operation for WeChat development

This article mainly introduces the relevant information on the database operation of WeChat public platform development. Friends who need it can refer to it

1. Introduction

The functions explained earlier Development is completed by simply calling 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 demonstrate the operation of the MySQL database that everyone is familiar with, and 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 the 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.

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 only one database that 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 databasethat 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 tables ends here. Next, we will write code to explain in detail the use of the database and data tables.

4. Official example (PHP MySQL)

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

mysql/basic.php file Content

<!--?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(){
 
    /*从平台获取查询要连接的数据库名称*/
    $this->database = MYSQLNAME;
 
    /*从环境变量里取出数据库连接需要的参数*/
    $this->host = getenv(&#39;HTTP_BAE_ENV_ADDR_SQL_IP&#39;);
    $this->user = getenv(&#39;HTTP_BAE_ENV_AK&#39;);
    $this->password = getenv(&#39;HTTP_BAE_ENV_SK&#39;);
    $this->port = getenv(&#39;HTTP_BAE_ENV_ADDR_SQL_PORT&#39;);
 
    $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;//执行成功
      }else{
        return 2;//没有行受影响
      }
    }
   
    //$this->mysqli->close();
  }
}
?>

9. Use of test classes

9.1 Test DML operation

Test code:

<!--?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 "执行失败";
}elseif($res==1){
  echo "执行成功";
}else{
  echo "没有行数影响";
}
?>

Test result:

9.2 Test DQL operation

Test code:

<!--?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 &#39;
&#39;;
}
 
$res->free();
?>

Test result:

10. Implement interaction with WeChat (Mysqli extension)

10.1 Pre-operation

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 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 operation

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 operation

Test code:

$update_sql="UPDATE test_mysql SET password='$new_password' WHERE from_user='$fromUsername'";

$res = $this->mysqli_BAE->execute_dml($update_sql);

Test result:

10.5 Test delete operation

Test code:

$delete_sql="DELETE FROM test_mysql WHERE from_user='$fromUsername'";

$res = $this->mysqli_BAE->execute_dml($delete_sql);

Test result:

Interaction test with WeChat was successful.

【Related Recommendations】

1. Special Recommendation:"php Programmer Toolbox" V0.1 version Download

2. WeChat public account platform source code download

3. WeChat Network King v3.4.5 Advanced Commercial Edition WeChat Rubik’s Cube source code

The above is the detailed content of Database operation for WeChat development. For more information, please follow other related articles on the PHP Chinese website!