微信小程序私信后端+tp5+数据库

数据表SQL

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for letter
-- ----------------------------
DROP TABLE IF EXISTS `letter`;
CREATE TABLE `letter`  (
  `wxId` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `content` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '0' COMMENT '内容utf8mb4存放emoji',
  `fromUserId` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '0' COMMENT '来自哪个用户Id',
  `toUserId` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '0' COMMENT '发给哪个用户Id',
  `status` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '0' COMMENT '状态',
  `timesp` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '0' COMMENT '时间戳',
  `createAt` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '0' COMMENT '创建时间',
  `updateAt` timestamp NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`wxId`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

SET FOREIGN_KEY_CHECKS = 1;

1.发送私信功能

//发送私信  
    public function send_letter() {
        $arr = Request::instance()->post();
        $userId = $arr['userId'];//用户Id
        $content = emoji_encode($arr['content']);//对内容进行编码；
        $toUserId = $arr['toUserId'];
        $data = ['wxId' => get_id(), 'fromUserId' => $userId, 'content' => $content, 'toUserId' => $toUserId, 'status' => '0', 'timesp' => time(), 'createAt' => get_time()];
        $msgInstert = Db::table('letter')->insert($data);
        if ($msgInstert) {
            return getjson('1', '发送成功', '');
        } else {
            return getjson('0', '发送失败', '');
        }
    }

2.请求私信列表

    //私信列表 
    public function letter_list() {
        $arr = Request::instance()->post();
        $userId = $arr['userId'];//用户Id
        //这里没有办法了，和网上的大神学了下写了句原生  要是有大佬有更好的方案，可以联系我  QQ961052877
        $msg = Db::table('letter')->query("SELECT * FROM ( SELECT wxId, toUserId, content, status, timesp, createAt, updateAt, CASE WHEN fromUserId = '$userId' THEN '发送方' ELSE '接收方' END flag FROM li_private_letter WHERE fromUserId = '$userId' UNION SELECT wxId, fromUserId, content, status, timesp, createAt, updateAt, CASE WHEN fromUserId = '$userId' THEN '发送方' ELSE '接收方' END flag FROM li_private_letter WHERE toUserId = '$userId' ORDER BY toUserId DESC, wxId DESC ) ta GROUP BY toUserId ORDER BY wxId DESC");
        if ($msg) {
            foreach ($msg as $key => $value) {
                $msg[$key] = Db::table('letter')->where('wxId', $msg[$key]['wxId'])->find();
                if ($msg[$key]['fromUserId'] == $msg[$key]['toUserId']) {
                    unset($msg[$key]);
                } else {
                    if ($msg[$key]['fromUserId'] == $userId) {
                        $msg[$key]['userinfo'] = user_avatUrl($msg[$key]['toUserId']);
                    } else {
                        $msg[$key]['userinfo'] = user_avatUrl($msg[$key]['fromUserId']);
                    }
                    $msg[$key]['content'] = emoji_decode($msg[$key]['content']);
                }
            }
            $msg = array_values($msg);
            return getjson('1', '请求成功', $msg);
        } else {
            return getjson('0', '暂无私信', $msg);
        }
        }

3.检索有没有未读私信

    //检索有没有未读私信 
    public function notread_letter() {
        $arr = Request::instance()->post();
        $userId = $arr['userId'];//用户Id
        $msg = Db::table('letter')->where('toUserId', $userId)->where('status', '0')->select();
        if ($msg) {
            return getjson('1', '有私信', '');
        } else {
            return getjson('0', '暂无私信', '');
        }
    }

4.请求一个人的私信内容

//请求一个人的私信内容 
    public function onec_letter() {
        $arr = Request::instance()->post();
        $userId = $arr['userId'];
        $fromUserId = $arr['fromUserId'];
        $page = $arr['page'];
        $check = chcheckOpenId($userId, '私信');
        if ($check == 'error')
            return getjson('0', '用户身份非法', '');
        if ($check == 'closure')
            return getjson('0', '用户功能封禁', '');
        if ($userId == $fromUserId) {
            $msg = Db::connect('databasetwo')->table('li_private_letter')->where('toUserId', $userId)->where('fromUserId', $fromUserId)->page($page, 15)->order('timesp desc')->select();
            if ($msg) {
                foreach ($msg as $key => $value) {
                    Log::write($msg[$key]);
                    $msg[$key]['content'] = emoji_decode($msg[$key]['content']);
                    if ($msg[$key]['status'] == '0') {
                        $msgs = Db::connect('databasetwo')->table('li_private_letter')->where('wxId', $msg[$key]['wxId'])->update(['status' => '1']);
                    } else {
                        
                    }
                }
                $brr['content'] = $msg;
                $brr['myuserinfo'] = user_avatUrl($userId);
                $brr['userinfo'] = user_avatUrl($fromUserId);
                return getjson('1', '请求成功', $brr);
            } else {
                $msg['myuserinfo'] = user_avatUrl($userId);
                return getjson('0', '暂无私信', $msg);
            }
        } else {
//
            $msg = Db::connect('databasetwo')->table('letter')->where(['toUserId' => $userId, 'fromUserId' => $fromUserId])->whereOr("fromUserId='$userId' and  toUserId='$fromUserId'")->page($page, 15)->order('timesp desc')->select();
            Log::write(Db::connect('databasetwo')->table('letter')->getLastSql());
            if ($msg) {
                foreach ($msg as $key => $value) {
                    $msg[$key]['content'] = emoji_decode($msg[$key]['content']);
                    if ($msg[$key]['status'] == '0') {
                        $msgs = Db::connect('databasetwo')->table('letter')->where('wxId', $msg[$key]['wxId'])->update(['status' => '1']);
                    } else {
                        
                    }
                }
                $brr['content'] = $msg;
                $brr['myuserinfo'] = user_avatUrl($userId);//自己的头像
                $brr['userinfo'] = user_avatUrl($fromUserId);//对话者的头像
                return getjson('1', '请求成功', $brr);
            } else {
                $msg['myuserinfo'] = user_avatUrl($userId);
                return getjson('0', '暂无私信', $msg);
            }
        }
    }

getjson看这里：https://mp.csdn.net/mdeditor/89387016#
emoji看这里：https://mp.csdn.net/mdeditor/89422148#