YAF根据条件拼接SQL语句

                                                                  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');

   效果截图
   YAF根据条件拼接SQL语句
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);	//5
		die;
	}
	
	/**
	 * @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;
	}

YAF根据条件拼接SQL语句

相关推荐