微信公众号开发之数据库
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.公众号关键词查询
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 case "text"://文本消息 2 if($keyword == "天气"){ 3 $weatherurl="http://api.map.baidu.com/telematics/v3/weather?location=杭州&ak=f6UkU0QqVAhABUeGKSxZBCeb"; 4 $apistr = file_get_contents($weatherurl); 5 $apiobj = simplexml_load_string($apistr); 6 $placeobj = $apiobj->results->currentCity;//读取城市名 7 $todayobj = $apiobj->results->weather_data->date;//读取当前时间 8 $weatherobj = $apiobj->results->weather_data->weather;//读取天气 9 $windobj = $apiobj->results->weather_data->wind;//读取风力 10 $temobj = $apiobj->results->weather_data->temperature;//读取温度 11 $pm25obj = $apiobj->results->pm25;//读取pm2.5 12 $contentStr = "{$placeobj}{$todayobj},天气:{$weatherobj},风力:{$windobj},温度:{$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,海仙日用百货欢迎您!\n回复数字'1',了解店铺地址.\n回复数字'2',了解商品种类.\n" 19 ."回复'天气',获取杭州天气情况.\n发送语音消息和地理位置信息,有更多惊喜等着你."; 20 }else{ 21 $contentStr = $data[0][content]; 22 } 23 } 24 break;
4.识别新老用户
手机微信客户端传输到微信公众账号的用户信息为加密的openid,保存在变量$fromUsername中,我们可以将$fromUsername保存到数据库中,从而实现CRM(客户关系管理)。
新建数据表crm:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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;
先判断是新客户还是老客户,当新客户发送“你好”时,回复“欢迎新朋友”;当老客户发送“你好”时,回复“欢迎老朋友”。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 if($keyword == "你好" || $keyword == "您好"){ 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 = "欢迎新朋友"; 10 }else{ 11 $contentStr = "欢迎老朋友"; 12 }