问题描述：在很多新手开发过程中，然后也有些比较偷懒的喜欢把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 }
上述代码中 尽管第二个方法比第一个方法 优化了许多，把循环查询编程了一次查询，但是还没有达到最优的效果；还可以进一步优化，目前这里就不讲了，留下思考空间