WeChat 공개 계정 개발 데이터베이스

1.데이터베이스 초기화

Sina SAE에서 MYSQL을 사용하고 있습니다.

공식 문서: https://www.sinacloud.com/doc/sae/php/mysql.html#api-shi-yong-shou-ce

2. 쿼리문

연결된 데이터베이스: (SAE는 MYSQL 모듈 기반의 SaeMysql 클래스를 제공합니다)

$mysql = 새 SaeMysql();

직접 인스턴스화를 통해 링크 작업이 완료되며 다른 작업은 필요하지 않습니다.

예:

데이터 추가: (weixin 근처의 단어는 작은따옴표가 아니라 TAB 키 위의 키 값입니다)

$mysql = 새 SaeMysql();

$sql = "`weixin`(`id`,`title`,`content`) 값에 삽입(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. 신규 및 기존 사용자 식별

모바일 WeChat 클라이언트에서 WeChat 공개 계정으로 전송되는 사용자 정보는 암호화된 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>                         }

CRM