YAF根据条件拼接SQL语句
1、建user表
CREATE TABLE `user` (`id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID自增',`name` varchar(50) NOT NULL DEFAULT '' COMMENT '用户名',`age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',`type` tinyint(1) unsigned NOT NULL DEFAULT '1' COMMENT '类型 1:普通用户 2:VIP用户 3:管理员 默认1',`status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '状态 1:正常 2:违规删除 默认1',PRIMARY KEY (`id`),KEY `id` (`id`) USING BTREE) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户表';
2、往表中插入数据后（截图）
INSERT INTO `user` VALUES ('1', '王1', '17', '1', '1');INSERT INTO `user` VALUES ('2', '王2', '18', '1', '1');INSERT INTO `user` VALUES ('3', '王3', '20', '2', '1');INSERT INTO `user` VALUES ('4', '王4', '19', '1', '1');INSERT INTO `user` VALUES ('5', '王5', '16', '2', '1');INSERT INTO `user` VALUES ('6', '王6', '21', '1', '1');INSERT INTO `user` VALUES ('7', '王7', '21', '2', '1');INSERT INTO `user` VALUES ('8', '王8', '19', '2', '1');INSERT INTO `user` VALUES ('9', '王9', '19', '2', '1');INSERT INTO `user` VALUES ('10', '王10', '23', '1', '2');
效果截图
3、PHP代码
/*** @todo 找出* 状态为正常，* 姓名中包含王字，* 年龄在17-19岁 * 普通用户 或 VIP用户* 按照主键ID降序* 的 记录数与总数*/function selectAction(){$where = $orwhere = [];$where[] = ['status', 1];//$where[] = ['type', '=', 1];//与上一句等价$where[] = ['name', 'like', '%王%'];$where[] = ['age', 'in', [17,18,19]];$orwhere = $where;$where[] = ['type', 1];$orwhere[] = ['type', 2];$whereSql = $this->wheresql($where);$orWhereSql = $this->wheresql($orwhere);$field = "id,name,age,type,status";$sql = " select {$field}"." from user"." where 1=1 {$whereSql} or 1=1 {$orWhereSql}"." order by id desc ";//."limit 0,2";$mUser = new UserModel();$list = $mUser->query($sql);$sqlCount = " select count(*) as count "." from user"." where 1=1 {$whereSql} or 1=1 {$orWhereSql}";$count = $mUser->query($sqlCount);$count = $count[0]['count'];//打印的结果echo "{$sql}<br/>";//select id,name,age,type,status from user where 1=1 AND status = "1" AND name like "%王%" AND age in (17,18,19) AND type = "1" or 1=1 AND status = "1" AND name like "%王%" AND age in (17,18,19) AND type = "2" order by id desc echo "{$sqlCount}<br/>";// select count(*) as count from user where 1=1 AND status = "1" AND name like "%王%" AND age in (17,18,19) AND type = "1" or 1=1 AND status = "1" AND name like "%王%" AND age in (17,18,19) AND type = "2"var_export($list);echo "<br/>";/* array ( 0 => array ( 'id' => 9, 'name' => '王9', 'age' => 19, 'type' => 2, 'status' => 1, ), 1 => array ( 'id' => 8, 'name' => '王8', 'age' => 19, 'type' => 2, 'status' => 1, ), 2 => array ( 'id' => 4, 'name' => '王4', 'age' => 19, 'type' => 1, 'status' => 1, ), 3 => array ( 'id' => 2, 'name' => '王2', 'age' => 18, 'type' => 1, 'status' => 1, ), 4 => array ( 'id' => 1, 'name' => '王1', 'age' => 17, 'type' => 1, 'status' => 1, ), ) */var_export($count);//5die;}/*** @todo YAF where条件数组转字符串* @param unknown $where* @return string*/function wheresql($where){$whereSql = '';if( $where ){//拼接where条件foreach ( $where as $k => $v){if(count($v) == 2){//默认是等于号$whereSql .= ' AND '.$v[0].' = "'.$v[1].'"';}else{if(strtoupper($v[1]) == "IN"){//加入为in的判断$whereSql .= ' AND '.$v[0].' in (';for ( $x=0; $x<count($v[2]); $x++ ){if($x==(count($v[2])-1)){//最后一个不要点$whereSql .= $v[2][$x];}else{$whereSql .= $v[2][$x].',';}}$whereSql .=') ';}else{$whereSql .= ' AND '.$v[0].' '.$v[1].' "'.$v[2].'"';}}}}return $whereSql;}
