Database for WeChat public account development and public development

Database for public account development and database for public development

1. initialize the database

I am using MYSQL on Sina SAE.

Official documents: https://www.sinacloud.com/doc/sae/php/mysql.html#api-shi-yong-shou-ce

2. query statement

Link to the database: (SAE provides the MYSQL-based SaeMysql class)

$ Mysql = new SaeMysql ();

Directly instantiate the link without any additional operations.

For example:

Add data: (Note that weixin is not a single quotation mark and is the value of the key above the TAB key)

$ Mysql = new SaeMysql ();

$ SQL = "insert into 'weixin' ('id', 'title', 'content') values (null," haha ",:" gaga ")";

3. Keyword query for Public Accounts

1 case "text": // text message 2 if ($ keyword = "Weather") {3 $ weatherurl = "http://api.map.baidu.com/telematics/v3/weather? Location = Hangzhou & ak = f6UkU0QqVAhABUeGKSxZBCeb "; 4 $ apistr = file_get_contents ($ weatherurl); 5 $ apiobj = simplexml_load_string ($ apistr ); 6 $ placeobj = $ apiobj-> results-> currentCity; // read the city name 7 $ todayobj = $ apiobj-> results-> weather_data-> date; // read the current time 8 $ weatherobj = $ apiobj-> results-> weather_data-> weather; // read weather 9 $ export bj = $ apiobj-> results-> weather_data-> wind; // read wind power 10 $ temobj = $ apiobj-> results-> weather_d Ata-> temperature; // read temperature 11 $ pm25obj = $ apiobj-> results-> pm25; // read pm2.512 $ contentStr = "{$ placeobj} {$ todayobj }, WEATHER: {$ weatherobj}, wind: {$ 20.bj}, temperature: {$ temobj}, pm2.5: {$ pm25obj }. "; 13} else {14 $ mysql = new SaeMysql (); 15 $ SQL = "select * from 'weixin 'where 'title' like' % {$ keyword} % '"; 16 $ data = $ mysql-> getData ($ SQL ); 17 if ($ data = null) {18 $ contentStr = "Hi, thank you! \ N reply number '1' to learn the store address. \ n reply number '2' to learn about the product type. \ n "19. "Reply to 'Weather 'To get weather conditions in Hangzhou. \ n sends voice messages and geographic location information. More surprises are waiting for you. "; 20} else {21 $ contentStr = $ data [0] [content]; 22} 23} 24 break;Keyword Query

4. Identify new and old users

The user information transmitted from the mobile client to the public account is encrypted openid and saved in the variable $ fromUsername. We can save $ fromUsername to the database to Implement CRM (Customer Relationship Management ).

Create a data table crm:

1 create table 'app _ landiljy '. 'crm '(2 'id' int not null AUTO_INCREMENT, 3 'user' VARCHAR (255) character set utf8 COLLATE utf8_general_ci not null, 4 primary key ('id') 5) ENGINE = INNODB;Create table

First, judge whether it is a new customer or an old customer. When the new customer sends "hello", the reply "welcome new friends"; when the old customer sends "hello", the reply "Welcome old friends ".

1 if ($ keyword = "hello" | $ keyword = "hello") {2 $ mysql = new SaeMysql (); 3 $ SQL = "select * from 'crm 'where 'user' = '{$ fromUsername}'"; 4 $ data = $ mysql-> getData ($ SQL ); 5 $ user = $ data [0] [user]; 6 if (empty ($ user) {7 $ SQL = "insert into 'crm '('id ', 'user') values (null, '{$ fromUsername}') "; 8 $ mysql-> runSql ($ SQL); 9 $ contentStr =" Welcome to new friends "; 10} else {11 $ contentStr = "Welcome to Old Friends"; 12}Crm