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;
先判断是新客户还是老客户,当新客户发送“你好”时,回复“欢迎新朋友”;当老客户发送“你好”时,回复“欢迎老朋友”。
<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> }