微信公众号开发之数据库

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