优化循环中的sql语句

77 阅读 0 评论 51 点赞

我是靠谱客的博主呆萌棒棒糖，最近开发中收集的这篇文章主要介绍优化循环中的sql语句，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

问题描述：在很多新手开发过程中，然后也有些比较偷懒的喜欢把sql语句写在循环中，这样开发起来比较快；代码易懂简介清晰，对于访问量少的这么写当然没有问题了，可以一旦访问量比较高的时候那就呵呵了...

代码如下：

 1     /**
 2      * @desc 获取评论列表 
 3      * @version 3.0
 4      * @author wzh
 5      * @date 2017-02-20
 6      */
 7     public function getCommentList(){
 8         $cat_id = (int) $this -> input -> get('cat_id');
 9         $cat_id_2 = (int) $this -> input -> get('cat_id_2');
10         $id = (int) $this -> input -> get('id');
11         $page = (int) $this -> input -> get('page');        
12         $page = $page == 0 ? 1 : $page;
13         $pagenum = $pagenum == 0 ? 10 : $pagenum;
14         $total = DB::result_first("select count(*) from app_video_comment where video_id = $id and comment_id = 0 and is_delete = 0");
15         if(!$total){
16             $this -> error('暂无数据');
17         }
18         $data['total'] = $total;
19         $data['maxpage'] = ceil($total / $pagenum);
20         $data['page'] = $page;
21         $start = ($page - 1) * $pagenum;
22         $sql = " select id,uid,comment_count,zan_count,content,video_id,addtime from app_video_comment where video_id = $id and comment_id = 0 and is_delete = 0 ";
23         $sql .= " order by comment_count desc,zan_count desc limit $start,$pagenum ";
24         $list = (array) DB::fetch_array($sql);
25 
26         foreach ($list as $key => $value) {
27             $user = DB::row_first("select uid,avatar,nickname from app_user where uid = '{$value['uid']}' ");
28             $value['nickname'] = $user['nickname'];
29             $value['avatar'] = $user['avatar'] == '' ? $this -> domain . '/static/images/defaultavatar.jpg' : $this -> domain . $value['avatar'];
30             //查看是否已经赞过了
31             $value['comment_status'] = (int) DB::row_first("select count(*) from app_video_comment_zan where uid = '$uid' and comment_id = '{$value['id']}' ");
32         }
33         $data['comment_list'] = $list;
34         $this -> json_return($data);
35     }
36     /**
37      * @desc 获取评论列表 
38      * @version 3.0
39      * @author wzh
40      * @date 2017-02-20
41      */
42     public function getCommentList2(){
43         $cat_id = (int) $this -> input -> get('cat_id');
44         $cat_id_2 = (int) $this -> input -> get('cat_id_2');
45         $id = (int) $this -> input -> get('id');
46         $page = (int) $this -> input -> get('page');        
47         $page = $page == 0 ? 1 : $page;
48         $pagenum = $pagenum == 0 ? 10 : $pagenum;
49         $total = DB::result_first("select count(*) from app_video_comment where video_id = $id and comment_id = 0 and is_delete = 0");
50         if(!$total){
51             $this -> error('暂无数据');
52         }
53         $data['total'] = $total;
54         $data['maxpage'] = ceil($total / $pagenum);
55         $data['page'] = $page;
56         $start = ($page - 1) * $pagenum;
57         $sql = " select id,uid,comment_count,zan_count,content,video_id,addtime from app_video_comment where video_id = $id and comment_id = 0 and is_delete = 0 ";
58         $sql .= " order by comment_count desc,zan_count desc limit $start,$pagenum ";
59         $list = (array) DB::fetch_array($sql);
60         $uidArr = $commentArr = array();
61         foreach ($list as $value) {
62             $uidArr['uid'] = (int) $value['uid'];
63             $commentArr[] = (int) $value['id'];
64         }
65         $uidstr = empty($uidArr) ? 0 : implode(',', $uidArr);
66         $commentstr = empty($commentArr) ? 0 : implode(',',$commentArr);
67 
68         $uidList = DB::fetch_array("select uid,avatar,nickname from app_user where uid in ($uidstr)");
69         $uidArr = array();
70         foreach ($uidList as $value) {
71             $uidArr[$value['uid']] = $value;
72         }
73 
74         $commentList = DB::fetch_array("select comment_id,count(*) as cnt from app_video_comment_zan where uid = '$uid' and comment_id in ($commentstr) group by comment_id ");
75         $commentArr = array();
76         foreach ($commentList as $value) {
77             $commentArr[$value['comment_id']] = $value['cnt'];
78         }
79         foreach ($list as $key => $value) {
80             
81             $value['nickname'] = $uidArr[$value['uid']]['nickname'];
82             $value['avatar'] = $uidArr[$value['uid']]['avatar'] == '' ? $this -> domain . '/static/images/defaultavatar.jpg' : $this -> domain . $uidArr[$value['uid']]['avatar'];
83             //查看是否已经赞过了
84             $value['comment_status'] = (int) $commentArr[$value['comment_id']];
85         }
86         $data['comment_list'] = $list;
87         $this -> json_return($data);
88     }