Micro-trust public platform Development database Operation _mysql

First, Introduction

The functional development described earlier is done by the simple call API, which does not operate on the database. In the next advanced feature development, you need to use the database, so in this article, the MySQL database operation to do a brief introduction for the reader's reference.

Second, the analysis of ideas

Baidu Developer Center provides a powerful cloud database (including MySQL, MongoDB, Redis), in this section of the tutorial, we will be familiar with the MySQL database operation demo, to achieve the micro-letter and database interaction.

Using a cloud database in BAE applications is simple, and the name in the database list is the dbname that connects to the database. User name, password, connection address, and port are taken out of the environment variable in the application.

You can use standard PHP Mysql or PHP mysqli extensions to access the database, BAE's PHP has provided these two extensions, applications can be used directly.

Third, the creation of the BAE MySQL database

3.1 Login Baidu Developer Center-> Management Center-> Select Application-> Cloud Environment-> service Management-> MySQL (Cloud database)-> CREATE database

3.2 Creating a Database

Note: each application has and only one database to enjoy 1G free quota, the remaining database does not enjoy free quota discount. You can use this benefit again only if you delete the database that you have used free quotas for.

3.3 Create success

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

New datasheet, enter table name and number of fields, click "Execute" to create the table.

3.5 Creating a Table

Enter the field name and field type, after entering, click "Save" below to complete the creation of the table.

3.6 Create complete

Modify the ID field as the primary key and add auto_increment; Modify the From_user field to unique (unique) to complete the table modification.

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

CREATE TABLE IF not EXISTS ' Test_mysql '
 (' id ' int (one) not NULL auto_increment,
 ' from_user ' varchar () DEFAULT NU LL, 
' account ' varchar default NULL, 
' password ' varchar default NULL,
 ' Update_time ' datetime DEFAULT NULL, PRIMARY key (' id '), UNIQUE key 
' From_user ' (' From_user ');

phpMyAdmin operation

The database and datasheet are created to this end, the following will write code on the database and the use of the datasheet to do detailed explanation.

Iv. Official examples (PHP MySQL)

BAE's official demo (PHP MySQL) sample is as follows:

mysql/basic.php File Contents -->

<!--? php/** * MySQL example, this example is familiar with the use of BAE platform MySQL (CRUD)/require_once (".
  /configure.php ");
   
  /* Replace with your own database name (can be found from the central Administration)/$dbname = Mysqlname;
  /* Remove the required parameters for the database connection from the environment variable * * $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 the mysql_connect () connection 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 () Select the database to which you want to connect */if (!mysql_select_db ($dbname, $link)) {die ("Select Database Failed:".
  Mysql_error ($link)); }/* This connection is fully established so that the current database can be operated on accordingly. *//*!!! Note that it is no longer possible to switch to another database via this connection call mysql_select_db!!! * * * need to connect to another database, please use mysql_connect+mysql_select_db to start another connection//** * Next you can use the other standard PHP MySQL function operation for database operation. Create a database table $sql = "CREATE table if not exists test_mysql (ID int primary key auto_increment, no int, NA
    Me 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, ' The ' is a test message '), (2008, ' It is anot
  Her test message '), (2009, ' xxxxxxxxxxxxxx ');
  $ret = mysql_query ($sql, $link); if ($ret = = False) {die ("Insert Failed:".)
  Mysql_error ($link));
  else {echo ' Insert succeed ';
  }//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 ';
  }//Modify Data $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 '; }//Retrieving 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 ';
    while ($row = Mysql_fetch_assoc ($ret)) {echo {$row [' id ']} {$row [' no ']} {$row [' name ']} ';
  }//Delete 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 '; }?>

configure.php File Contents

<!--? php
 
  /*** configuration database name ***/
  define ("Mysqlname", "Qzmlskbyflhscpcoftax");
 
? -->

Test use:

Executed successfully.

Modified into a callable function form (PHP MySQL)

5.1 Creating data tables

Create a database table
function _create_table ($sql) {
  mysql_query ($sql) or Die (' CREATE table failure, error message: '. mysql_error ());
  Return "CREATE table succeeded";
}

5.2 Inserting data

Insert Data
function _insert_data ($sql) {
   if (!mysql_query ($sql)) {return
    0;  Insert data failed
  }else{
     if (mysql_affected_rows () >0) {return
       1;  Insert Success
     }else{return
       2;  No rows Affected
     }}
  }

5.3 Deleting data

Delete Data
function _delete_data ($sql) {
   if (!mysql_query ($sql)) {return
    0;  Delete Failed
   }else{
     if (mysql_affected_rows () >0) {return
       1;  Delete successful
     }else{return
       2;  No rows Affected
     }}
  }

5.4 Modifying data

Modify Data
function _update_data ($sql) {
   if (!mysql_query ($sql)) {return
    0;  Update data failed
  }else{
     if (mysql_affected_rows () >0) {return
       1;  Update succeeded;
     } else{return
       2;  No rows Affected
     }}
  }

5.5 Retrieving data

Retrieving Data
function _select_data ($sql) {
  $ret = mysql_query ($sql) or Die (' SQL statement has error, error message: '. mysql_error ());
  return $ret;
}

5.6 Deleting data tables

Delete Table
function _drop_table ($sql) {
  mysql_query ($sql) or Die (' Delete table failed, error message: '. mysql_error ());
  Return "Delete table succeeded";
}

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

Six, test MySQL function use

6.1 New file dev_mysql.php in the same directory and introduce mysql_bae.func.php file

Require_once './mysql_bae.func.php ';

6.2 Test Creation Table

Delete the Test_mysql table created above using phpMyAdmin, and the test statement is as follows:

CREATE TABLE
$create _sql = "CREATE table IF not EXISTS ' test_mysql ' (
 ' id ' int (one) not NULL auto_increment,
 ' From_use R ' varchar default NULL,
 ' account ' varchar default NULL,
 ' password ' varchar default NULL,
 ' Update_time ' datetime DEFAULT NULL,
 PRIMARY key (' id '),
 UNIQUE key ' From_user ' (' from_user ')
';
 
Echo _create_table ($create _sql);

Test the correct result:

View in phpMyAdmin

Intentionally incorrectly writing an SQL statement

Test Error results:

6.3 Test Insert Data

The test statement is as follows:

Insert Data
$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 "Insert succeeded";
} else{
  echo "Insert Failed";
}

Test results:

6.4 Test 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 "update succeeded";
} ElseIf ($res = = 0) {
  echo "update failed";
} ElseIf ($res = = 2) {
  echo "no rows affected";
}

Test results:

Update again:

6.5 Test Delete Data

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 "delete succeeded";
} ElseIf ($res = = 0) {
  echo "delete failed";
} ElseIf ($res = = 2) {
  echo "does not have this record";
}

Test results:

Delete again:

6.6 Test Retrieval data

Perform the above insert operation again to do the retrieval test, the test statement is as follows:

Retrieving 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 "
";
 
}

Test results:

6.7 Test Delete Table

The test statement is as follows:

Delete Table $drop_sql = "DROP table if exists test_mysql";

Echo _drop_table ($drop _sql);

Test results:

MySQL function test all succeeded.

Vii. implementing interactions with micro-letters (MYSQL extensions)

To ensure that there are TEST_MSYQL tables in the database, here test the micro-letter to the MySQL database additions and deletions to check operations, regardless of special circumstances, only the following methods to test:

1. Binding + account + password
such as: Binding +860512+abc123
 
2. Query
such as: Query
 
3. Modify + old password + new password
such as: Modify +abc123+123456
 
4. Delete
: Delete

7.1 Introduction of mysql_bae.func.php Files

Introducing Database function files

Require_once ' mysql_bae.func.php ';

7.2 Pre-operation

A. Split the entered statement into an array, separated by a "+" number

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

B. Get the current time

Gets the current time $nowtime=date ("y-m-d g:i:s");

C. Determining whether a user has been bound

Determines whether
the $select _sql= "select id from test_mysql WHERE from_user= ' $fromUsername '" is already bound;
$res =_select_data ($select _sql);
$rows =mysql_fetch_array ($res, MYSQL_ASSOC);
if ($rows [id] <> ') {
    $user _flag= ' y ';     
}

7.3 Test Insert operation

Test code:

if (Trim ($keywords [0] = = ' binding ') {
  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 = "bind successfully";
    } ElseIf ($res = = 0) {
      $contentStr = "bind failed";
    }
  } else{
    $contentStr = "This account is 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 the account, the query does not have the relevant information, please bind first, thank you!" ";
  }
}

Test results:

7.5 Test Update operation

Test code:

if (Trim ($keywords [0] = = "Modified") {
  $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 = "Modify Failed";
    }
  } else{
    $contentStr = "The original password is incorrect, please confirm and try again";
  }

Test results:

7.6 Test 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 = "Delete succeeded";
  } ElseIf ($res = = 0) {
    $contentStr = "Delete Failed";
  }
}

Test results:

The interaction test with the micro-letter was successful.

Eight, PHP mysqli expansion, encapsulation into a class

Encapsulate the MYSQLI extensions into class use with the following code:

<!--? php require_once ' includes/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)) to access private variables outside of a class {return ($this-> $prope
    Rty_name);
    }else{return (NULL);
  } function __set ($property _name, $value) {$this-> $property _name= $value;
 
    The function __construct () {*/* Gets the database name of the query to be connected from the platform * * * * $this->database = mysqlname;
    * * Take the parameters of the database connection from the environment variable * * * $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-&GT;MYSQLI-&GT;ERROR);
  } $this->mysqli->query ("Set names UTF8"); //DQL statement function execute_dql ($query) {$res = $this->mysqli->query ($query) or Die ("Operation failed". $t
    HIS-&GT;MYSQLI-&GT;ERROR);
     
    return $res;
  $this->mysqli->close (); //DML statement function Execute_dml ($query) {$res = $this->mysqli->query ($query) or Die ("Operation failed". $t
     
    HIS-&GT;MYSQLI-&GT;ERROR);
      if (! $res) {return 0;//failed}else{if ($this->mysqli->affected_rows > 0) {return 1;//executed successfully
  }else{return 2;//no rows affected}//$this->mysqli->close (); }}?>

Ix. the use of test class

9.1 Test DML operations

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 (' David ', ' 860510 ', ' abcabc ', ' 2013-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);
 
if ($res ==0) {
  echo execution failed;
} ElseIf ($res ==1) {
  echo "successful execution";
} Else{
  echo "has no row count effect"
;
>

Test results:

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 '
;
}
 
$res->free ();
? >

Test results:

X. Realizing the interaction with micro-letters (mysqli extension)

10.1 Pre-operation

A. Introduction of MySQLi_BAE.class.php Documents

The introduction of database function file require_once "MySQLi_BAE.class.php";

B. Instantiating objects

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 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 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 results:

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 results:

Successful with the micro-letter interaction test.

XI. Complete Code Acquisition

Please visit the official forum of le-si

URL: Http://pan.baidu.com/s/1c0s3Jby

12. Concern

Please pay attention to Zhuo Kam Suzhou Micro-Credit public account, Zhuo Kam Suzhou based on the BAE platform development, for the mainstream of micro-letter function development test.

You can pay attention to the Zhuo Kam Suzhou Public account for functional testing, as well as access to new application development.

1. Login to the micro-letter client, Address Book-> add friends-> Find public number-> Zhuojinsz, find and attention.

2. Scan two-dimensional code:

Zhuo Jin Suzhou function list:

Thank you for reading, I hope to help you, thank you for your support for this site!