WeChat public account development database

1.Initialize the database

I use MYSQL on Sina SAE.

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

2. Query statement

Linked database: (SAE provides the SaeMysql class based on the MYSQL module)

$mysql = new SaeMysql();

Direct instantiation completes the link operation, no other operations are required.

For example:

Add data: (Note that the words near weixin are not single quotes, but the value of the key above the TAB key)

$mysql = new SaeMysql();

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

3. Public account keyword query

<span style="color: #008080;"> 1</span> <span style="color: #0000ff;">case</span> "text":<span style="color: #008000;">//</span><span style="color: #008000;">文本消息</span>
<span style="color: #008080;"> 2</span>                     <span style="color: #0000ff;">if</span>(<span style="color: #800080;">$keyword</span> == "天气"<span style="color: #000000;">){
</span><span style="color: #008080;"> 3</span>                         <span style="color: #800080;">$weatherurl</span>="http://api.map.baidu.com/telematics/v3/weather?location=杭州&ak=f6UkU0QqVAhABUeGKSxZBCeb"<span style="color: #000000;">;
</span><span style="color: #008080;"> 4</span>                         <span style="color: #800080;">$apistr</span> = <span style="color: #008080;">file_get_contents</span>(<span style="color: #800080;">$weatherurl</span><span style="color: #000000;">);
</span><span style="color: #008080;"> 5</span>                         <span style="color: #800080;">$apiobj</span> = <span style="color: #008080;">simplexml_load_string</span>(<span style="color: #800080;">$apistr</span><span style="color: #000000;">);
</span><span style="color: #008080;"> 6</span>                         <span style="color: #800080;">$placeobj</span> = <span style="color: #800080;">$apiobj</span>->results->currentCity;<span style="color: #008000;">//</span><span style="color: #008000;">读取城市名</span>
<span style="color: #008080;"> 7</span>                         <span style="color: #800080;">$todayobj</span> = <span style="color: #800080;">$apiobj</span>->results->weather_data-><span style="color: #008080;">date</span>;<span style="color: #008000;">//</span><span style="color: #008000;">读取当前时间</span>
<span style="color: #008080;"> 8</span>                         <span style="color: #800080;">$weatherobj</span> = <span style="color: #800080;">$apiobj</span>->results->weather_data->weather;<span style="color: #008000;">//</span><span style="color: #008000;">读取天气</span>
<span style="color: #008080;"> 9</span>                         <span style="color: #800080;">$windobj</span> = <span style="color: #800080;">$apiobj</span>->results->weather_data->wind;<span style="color: #008000;">//</span><span style="color: #008000;">读取风力</span>
<span style="color: #008080;">10</span>                         <span style="color: #800080;">$temobj</span> = <span style="color: #800080;">$apiobj</span>->results->weather_data->temperature;<span style="color: #008000;">//</span><span style="color: #008000;">读取温度</span>
<span style="color: #008080;">11</span>                         <span style="color: #800080;">$pm25obj</span> = <span style="color: #800080;">$apiobj</span>->results->pm25;<span style="color: #008000;">//</span><span style="color: #008000;">读取pm2.5</span>
<span style="color: #008080;">12</span>                         <span style="color: #800080;">$contentStr</span> = "{<span style="color: #800080;">$placeobj</span>}{<span style="color: #800080;">$todayobj</span>},天气:{<span style="color: #800080;">$weatherobj</span>},风力:{<span style="color: #800080;">$windobj</span>},温度:{<span style="color: #800080;">$temobj</span>},pm2.5:{<span style="color: #800080;">$pm25obj</span>}."<span style="color: #000000;">;
</span><span style="color: #008080;">13</span>                     }<span style="color: #0000ff;">else</span><span style="color: #000000;">{
</span><span style="color: #008080;">14</span>                         <span style="color: #800080;">$mysql</span> = <span style="color: #0000ff;">new</span><span style="color: #000000;"> SaeMysql();
</span><span style="color: #008080;">15</span>                         <span style="color: #800080;">$sql</span> = "select * from `weixin` where `title` like '%{<span style="color: #800080;">$keyword</span>}%'"<span style="color: #000000;">;
</span><span style="color: #008080;">16</span>                         <span style="color: #800080;">$data</span> = <span style="color: #800080;">$mysql</span>->getData(<span style="color: #800080;">$sql</span><span style="color: #000000;">);
</span><span style="color: #008080;">17</span>                         <span style="color: #0000ff;">if</span>(<span style="color: #800080;">$data</span> == <span style="color: #0000ff;">null</span><span style="color: #000000;">){
</span><span style="color: #008080;">18</span>                             <span style="color: #800080;">$contentStr</span> = "Hi,海仙日用百货欢迎您!\n回复数字'1',了解店铺地址.\n回复数字'2',了解商品种类.\n"
<span style="color: #008080;">19</span>                             ."回复'天气',获取杭州天气情况.\n发送语音消息和地理位置信息,有更多惊喜等着你."<span style="color: #000000;">;
</span><span style="color: #008080;">20</span>                         }<span style="color: #0000ff;">else</span><span style="color: #000000;">{
</span><span style="color: #008080;">21</span>                             <span style="color: #800080;">$contentStr</span> = <span style="color: #800080;">$data</span>[0<span style="color: #000000;">][content];
</span><span style="color: #008080;">22</span> <span style="color: #000000;">                        }
</span><span style="color: #008080;">23</span> <span style="color: #000000;">                    }
</span><span style="color: #008080;">24</span>                     <span style="color: #0000ff;">break</span>;

Keyword query

4. Identify new and old users

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

Create new data table crm:

<span style="color: #008080;">1</span> <span style="color: #0000ff;">CREATE</span> <span style="color: #0000ff;">TABLE</span><span style="color: #000000;">  `app_landiljy`.`crm` (
</span><span style="color: #008080;">2</span> `id` <span style="color: #0000ff;">INT</span> <span style="color: #808080;">NOT</span> <span style="color: #0000ff;">NULL</span><span style="color: #000000;"> AUTO_INCREMENT ,
</span><span style="color: #008080;">3</span> `<span style="color: #ff00ff;">user</span>` <span style="color: #0000ff;">VARCHAR</span>( <span style="color: #800000; font-weight: bold;">255</span> ) <span style="color: #0000ff;">CHARACTER</span> <span style="color: #0000ff;">SET</span> utf8 COLLATE utf8_general_ci <span style="color: #808080;">NOT</span> <span style="color: #0000ff;">NULL</span><span style="color: #000000;"> ,
</span><span style="color: #008080;">4</span> <span style="color: #0000ff;">PRIMARY</span> <span style="color: #0000ff;">KEY</span><span style="color: #000000;"> (  `id` )
</span><span style="color: #008080;">5</span> ) ENGINE <span style="color: #808080;">=</span> INNODB;

create table

First determine whether it is a new customer or an old customer. When a new customer sends "Hello", reply "Welcome new friend"; when an old customer sends "Hello", reply "Welcome old friend".

<span style="color: #008080;"> 1</span> <span style="color: #0000ff;">if</span>(<span style="color: #800080;">$keyword</span> == "你好" || <span style="color: #800080;">$keyword</span> == "您好"<span style="color: #000000;">){
</span><span style="color: #008080;"> 2</span>                         <span style="color: #800080;">$mysql</span> = <span style="color: #0000ff;">new</span><span style="color: #000000;"> SaeMysql();
</span><span style="color: #008080;"> 3</span>                         <span style="color: #800080;">$sql</span> = "select * from `crm` where `user` = '{<span style="color: #800080;">$fromUsername</span>}'"<span style="color: #000000;">;
</span><span style="color: #008080;"> 4</span>                         <span style="color: #800080;">$data</span> = <span style="color: #800080;">$mysql</span>->getData(<span style="color: #800080;">$sql</span><span style="color: #000000;">);
</span><span style="color: #008080;"> 5</span>                         <span style="color: #800080;">$user</span> = <span style="color: #800080;">$data</span>[0<span style="color: #000000;">][user];
</span><span style="color: #008080;"> 6</span>                         <span style="color: #0000ff;">if</span>(<span style="color: #0000ff;">empty</span>(<span style="color: #800080;">$user</span><span style="color: #000000;">)){
</span><span style="color: #008080;"> 7</span>                              <span style="color: #800080;">$sql</span>="insert into `crm`(`id`,`user`) values(null,'{<span style="color: #800080;">$fromUsername</span>}')"<span style="color: #000000;">;
</span><span style="color: #008080;"> 8</span>                              <span style="color: #800080;">$mysql</span>->runSql(<span style="color: #800080;">$sql</span><span style="color: #000000;">);
</span><span style="color: #008080;"> 9</span>                              <span style="color: #800080;">$contentStr</span> = "欢迎新朋友"<span style="color: #000000;">;
</span><span style="color: #008080;">10</span>                         }<span style="color: #0000ff;">else</span><span style="color: #000000;">{
</span><span style="color: #008080;">11</span>                              <span style="color: #800080;">$contentStr</span> = "欢迎老朋友"<span style="color: #000000;">;
</span><span style="color: #008080;">12</span>                         }

crm