我们在SQL语句中,会传很多个参数,很难例如我们在如下语句中使用userId的位置有很多(语句并不完整):
SELECT A.id,
A.title,
MAX(A.group_id) AS group_id,
A.group_title,
'' AS group_picture,
A.source_url,
A.user_id,
A.share_count,
A.play_count,
A.comment_count,
A.thumb_count,
A.type,
A.cover_pic,
CASE WHEN UNIX_TIMESTAMP()-A.create_time<=60 THEN '刚刚'
WHEN UNIX_TIMESTAMP()-A.create_time<=3600 THEN CONCAT(ROUND((UNIX_TIMESTAMP()-A.create_time)/60),'分钟前')
WHEN UNIX_TIMESTAMP()-A.create_time<=86400 THEN CONCAT(ROUND((UNIX_TIMESTAMP()-A.create_time)/3600),'小时前')
WHEN UNIX_TIMESTAMP()-A.create_time<=86400*7 THEN CONCAT(ROUND((UNIX_TIMESTAMP()-A.create_time)/86400),'天前')
WHEN UNIX_TIMESTAMP()-A.create_time>86400*7 THEN FROM_UNIXTIME(SUBSTRING(A.create_time,1,10),''%m-%d %H:%i')
END AS time_describe,
A.width,
A.height,
A.images,
A.is_thumbed,
A.is_followed,
A.is_collected,
IFNULL(A.video_duration,'00:00') AS video_duration,
IFNULL(A.is_essence,0) AS is_essence,
IFNULL(A.is_sticked,'0') AS is_sticked,
CASE WHEN A.user_id=:userId THEN '1' ELSE '0' END AS is_auther,
IFNULL(A.is_founder,'0') AS is_founder,
IFNULL(A.group_is_followed,'0') AS group_is_followed,
IFNULL(B.user_nickname,'匿名') AS user_nickname,
IFNULL(B.user_pic,'http://coverpic.xiaomatv.cn/user_unset2.jpg') AS user_pic
FROM (SELECT A.id,
A.title,
IFNULL(G.group_id,0) AS group_id,
IFNULL(G.group_title,'') AS group_title,
A.video_url source_url,
A.ownner_id user_id,
A.share_count,
A.play_count,
A.comment_count,
A.thumb_count,
1 AS type,
A.cover_pic,
A.create_time,
A.width,
A.height,
'' AS images,
IFNULL(E.is_thumbed,0) is_thumbed,
IFNULL(C.is_followed,0) is_followed,
IFNULL(D.is_collected,0) is_collected,
IFNULL(A.video_duration,'00:00') AS video_duration,
G.group_picture,
G.group_is_followed,
G.is_founder,
F.is_sticked,
F.is_essence
FROM video A INNER JOIN
(SELECT followed_id AS user_id
FROM user_follow
WHERE user_id=:userId AND
action=1) B ON B.user_id=A.ownner_id LEFT JOIN
(SELECT video_id,
action AS is_thumbed
FROM video_thumb_log
WHERE action=1 AND
user_id=:userId
GROUP BY video_id) E ON E.video_id=A.id LEFT JOIN
(SELECT followed_id,
action AS is_followed
FROM user_follow
WHERE action=1 AND
user_id=:userId
GROUP BY followed_id) C ON C.followed_id=A.ownner_id LEFT JOIN
(SELECT video_id,
action AS is_collected
FROM user_collection
WHERE action=1 AND
user_id=:userId
GROUP BY video_id) D ON D.video_id=A.id LEFT JOIN
(SELECT B.id group_id,
B.title group_title,
B.picture AS group_picture,
C.group_is_followed,
CASE WHEN B.user_id=346916 THEN '1' ELSE '0' END AS is_founder
FROM t_label B LEFT JOIN
(SELECT label_id,
action AS group_is_followed
FROM t_user_follow_label
WHERE user_id=:userId AND
action=1) C ON C.label_id=B.id
WHERE B.type=5) G ON G.group_id=A.group_id LEFT JOIN
(SELECT SUM( CASE WHEN operation_type=1 THEN 1 ELSE 0 END ) AS is_sticked,
SUM( CASE WHEN operation_type=2 THEN 1 ELSE 0 END ) AS is_essence,
post_id AS video_id
FROM t_group_sticky_post
WHERE type=2 AND action=1
GROUP BY post_id,group_id) F ON F.video_id=A.id
WHERE A.status=0 AND A.is_private=0
UNION
SELECT A.id,
A.title,
B.group_id,
B.group_title,
A.video_url source_url,
A.ownner_id user_id,
A.share_count,
A.play_count,
A.comment_count,
A.thumb_count,
1 AS type,
A.cover_pic,
A.create_time,
A.width,
A.height,
'' AS images,
IFNULL(E.is_thumbed,0) is_thumbed,
IFNULL(C.is_followed,0) is_followed,
IFNULL(D.is_collected,0) is_collected,
IFNULL(A.video_duration,'00:00') AS video_duration,
B.group_picture,
B.group_is_followed,
B.is_founder,
F.is_sticked,
F.is_essence
FROM video A INNER JOIN
(SELECT B.id group_id,
B.title group_title,
B.picture AS group_picture,
1 AS group_is_followed,
CASE WHEN B.user_id=:userId THEN '1' ELSE '0' END AS is_founder
FROM t_label B INNER JOIN
(SELECT label_id
FROM t_user_follow_label
WHERE user_id=:userId AND
action=1) C ON C.label_id=B.id
WHERE B.type=5) B ON B.group_id=A.group_id LEFT JOIN
(SELECT video_id,
action AS is_thumbed
FROM video_thumb_log
WHERE action=1 AND
user_id=:userId
GROUP BY video_id) E ON E.video_id=A.id LEFT JOIN
(SELECT followed_id,
action AS is_followed
FROM user_follow
WHERE action=1 AND
user_id=:userId
GROUP BY followed_id) C ON C.followed_id=A.ownner_id LEFT JOIN
(SELECT video_id,
action AS is_collected
FROM user_collection
WHERE action=1 AND
user_id=:userId
GROUP BY video_id) D ON D.video_id=A.id LEFT JOIN
(SELECT SUM( CASE WHEN operation_type=1 THEN 1 ELSE 0 END ) AS is_sticked,
SUM( CASE WHEN operation_type=2 THEN 1 ELSE 0 END ) AS is_essence,
post_id AS video_id
FROM t_group_sticky_post
WHERE type=2 AND action=1
GROUP BY post_id,group_id) F ON F.video_id=A.id
WHERE A.status=0 AND A.is_private=0
UNION
SELECT A.id,
IFNULL(A.post_content,'') AS title,
A.group_id,
B.group_title,
'' AS source_url,
A.user_id,
A.share_count,
A.read_count play_count,
A.comment_count,
A.thumb_count,
2 AS type,
'' AS cover_pic,
A.create_time,
0 AS width,
0 AS height,
IFNULL(A.post_pic,'[]') AS images,
IFNULL(E.is_thumbed,0) AS is_thumbed,
IFNULL(C.is_followed,0) AS is_followed,
IFNULL(D.is_collected,0) AS is_collected,
'' AS video_duration,
B.group_picture,
B.group_is_followed,
B.is_founder,
F.is_sticked,
F.is_essence
FROM t_group_post A INNER JOIN
(SELECT B.id,
B.title group_title,
B.picture AS group_picture,
1 AS group_is_followed,
CASE WHEN B.user_id=:userId THEN '1' ELSE '0' END AS is_founder
FROM t_label B INNER JOIN
(SELECT label_id
FROM t_user_follow_label
WHERE user_id=:userId AND
action=1) C ON C.label_id=B.id
WHERE B.type=5) B ON B.id=A.group_id LEFT JOIN
(SELECT post_id,
action AS is_thumbed
FROM t_post_thumb_log
WHERE action=1 AND
user_id=:userId
GROUP BY post_id) E ON E.post_id=A.id LEFT JOIN
(SELECT followed_id,
action AS is_followed
FROM user_follow
WHERE action=1 AND
user_id=:userId
GROUP BY followed_id) C ON C.followed_id=A.user_id LEFT JOIN
(SELECT post_id,
action AS is_collected
FROM t_post_collection
WHERE action=1 AND
user_id=:userId
GROUP BY post_id) D ON D.post_id=A.id LEFT JOIN
(SELECT SUM( CASE WHEN operation_type=1 THEN 1 ELSE 0 END ) AS is_sticked,
SUM( CASE WHEN operation_type=2 THEN 1 ELSE 0 END ) AS is_essence,
post_id AS video_id
FROM t_group_sticky_post
WHERE type=2 AND action=1
GROUP BY post_id,group_id) F ON F.video_id=A.id LEFT JOIN
t_group_property P ON P.group_id=A.group_id
WHERE A.status=0 AND P.is_private=0) A LEFT JOIN user B ON B.id=A.user_id
WHERE A.id>0
GROUP BY A.id,A.type
ORDER BY A.create_time DESC
LIMIT :start , :last
这时候我们只能选择bindParam这个方法,但是却有一个需要注意的地方:
bindParam和bindValue的不同之处, bindParam要求第二个参数是一个引用变量(reference);
废话少说,上干货(代码):
/**执行数据库的预处理操作*/
public function select($sql,$param){
try{
$keys=array_keys($param);
$vals=array_values($param);
$stmt = $this->data->prepare($sql);
for($i=0;$i<count($keys);$i++){
$stmt->bindParam($keys[$i],$vals[$i],is_int($vals[$i]) ? PDO::PARAM_INT : PDO::PARAM_STR);
}
$stmt->execute();
$stmt->setFetchMode(PDO::FETCH_ASSOC);
$list=$stmt->fetchAll();
return $list;
}catch(PDOException $e){
echo $e->getMessage();
}
}