微信公眾號開發之資料庫

1.初始化資料庫

我用的是新浪SAE上的MYSQL。

官方文件：https://www.sinacloud.com/doc/sae/php/mysql.html#api-shi-yong-shou-ce

2.查詢語句

連結資料庫：(SAE提供了基於MYSQL模組的SaeMysql類)

$mysql = new SaeMysql();

直接實例化即完成連結操作，無須進行其他操作。

例如：

增加資料：(注意weixin附近的不是單引號，是TAB鍵上面那個鍵的值)

$mysql = new SaeMysql();

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

3.公眾號關鍵字查詢

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

關鍵字查詢

4.辨識新舊用戶

     手機微信用戶端傳輸到微信公眾帳號的使用者資訊為加密的openid，保存在變數$fromUsername中，我們可以將$fromUsername儲存到資料庫中，從而實現CRM（客戶關係管理）。

新建資料表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

先判斷是新客戶還是老客戶，當新客戶發送「你好」時，回覆「歡迎新朋友」；當老客戶發送「你好」時，回覆「歡迎老朋友」。

<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