/** * 过滤数组中无效的键值(多维数据) * * @param array $params 要过滤的参数 * @param array $allow 要过滤的值 * * @return void */ function invalidDataFilterRecursive(array &$params, array $allow = array(null)) { if (!empty($params) && is_array($params) && !empty($allow) && is_array($allow)) { foreach ($params as $key => $val) { if (is_array($val)) { invalidDataFilterRecursive($params[$key], $allow); } else { foreach ($allow as $v) { if ($val === $v) { unset($params[$key]); } } } } } }
/** * 搜索 * * @param string $table 表名 * @param array $params 条件参数 * @param bool $isRow 是否只显示一条记录(主要给 getRow 函数用) * * @return array */ public function get($table, array $params = array(), $isRow = false) { $total = 0; $debug = array(); // 过滤掉 值为 null 的 key invalidDataFilterRecursive($params, array(null)); $fields = !isset($params['fields']) ? '*' : $params['fields']; // 需要获得的字段 $keyName = !isset($params['keyName']) ? null : $params['keyName']; // 使用某个字段做为数组中的 KEY(注意要保持唯一,否则会丢数据) $isPage = !isset($params['isPage']) ? null : $params['isPage']; // 是否需要分页,默认不分页 $pageNo = !isset($_GET['page']) ? 0 : intval($_GET['page']); // 当前页码(默认第1页) $pageSize = !isset($params['pageSize']) ? null : intval($params['pageSize']); // 每页显示数据条数(有分页的情况下,默认 10 条) $orderBy = !isset($params['orderBy']) ? null : $params['orderBy']; // 排序 $groupBy = !isset($params['groupBy']) ? null : $params['groupBy']; // 分组 $join = !isset($params['join']) ? null : (!is_array($params['join']) ? null : $params['join'] ); // inner join 查询 $like = !isset($params['like']) ? null : (!is_array($params['like']) ? null : $params['like'] ); // 模糊查询 $where = !isset($params['where']) ? null : (!is_array($params['where']) ? null : $params['where'] ); // where 条件 $whereOr = !isset($params['whereOr']) ? null : (!is_array($params['whereOr']) ? null : $params['whereOr'] ); // OR 条件 $whereIn = !isset($params['whereIn']) ? null : (!is_array($params['whereIn']) ? null : $params['whereIn'] ); // In 条件 $whereNotIn = !isset($params['whereNotIn']) ? null : (!is_array($params['whereNotIn']) ? null : $params['whereNotIn'] ); // Not In 条件 $likeSql = ""; // join $joinSql = ""; if ($join && is_array($join)) { foreach ($join as $key => $val) { if ($val['on']) { $as = empty($val['as']) ? ' AS ' . $key : 'AS ' . $val['as']; // 别名 $type = empty($val['type']) ? 'INNER' : $val['type']; // join 类型 (left、 inner、 right) $joinSql .= ' ' . $type . " JOIN `" . $key . "` " . $as . " ON ". $val['on']; } } } if ($like && is_array($like)) { foreach ($like as $key => $val) { $likeSql .= " AND `" . $key . "` like '%{$val}%'"; } } $whereSql = ""; if ($where && is_array($where)) { foreach ($where as $key => $val) { // 联表查询时可能会写 “表名.字段” $tempArr = explode('.', $key); $tempTableAs = empty($tempArr[1]) ? '' : '`' . $tempArr[0] . '`.'; $tempTableKey = empty($tempArr[1]) ? trim($key) : trim($tempArr[1]); // preg_match_all('/(\W+))/', $tempTableKey, $data, PREG_SET_ORDER); // 这种写法不支持在字段上使用函数 preg_match_all('/[!=|<=|>=|<|>|<>]+/', $tempTableKey, $data, PREG_SET_ORDER); if (!empty($data)) { $tempWhere = $data[0][0]; // 过滤后的字段名 $tempKey = trim(str_replace($tempWhere, '', $tempTableKey)); $whereSql .= " AND " . $tempTableAs . '`' . trim($tempKey) . '` ' . $tempWhere . " '{$val}'"; // 正常的 “=” 模式 } else { // 检查字段中是否使用了函数,即使用 “(.*)” 做为标准 preg_match_all('/(\(.*\))+/', $tempTableKey, $keyData, PREG_SET_ORDER); // 带函数的写法 if (!empty($keyData)) { $whereSql .= " AND " . $tempTableAs . trim($tempTableKey) . " = '{$val}'"; } else { $whereSql .= " AND " . $tempTableAs . '`' . trim($tempTableKey) . "` = '{$val}'"; } } } } $whereOrSql = ""; if ($whereOr && is_array($whereOr)) { $whereOrSql .= " AND ("; foreach ($whereOr as $key => $val) { $whereOrSql .= " `" . $key . "`='{$val}' OR"; } $whereOrSql = substr($whereOrSql, 0, -3); $whereOrSql .= " ) "; } $whereInSql = ""; if ($whereIn && is_array($whereIn)) { foreach ($whereIn as $key => $val) { $whereInSql .= " AND `" . $key . "` IN('" . implode("', '", $val) . "')"; } } $whereNotInSql = ""; if ($whereNotIn && is_array($whereNotIn)) { foreach ($whereNotIn as $key => $val) { $whereNotInSql .= " AND `" . $key . "` NOT IN('" . implode("', '", $val) . "')"; } } $orderBySql = ""; if ($orderBy && !empty($orderBy)) { $orderBySql = " ORDER BY " . $orderBy; } $groupBySql = ""; if ($groupBy && !empty($groupBy)) { $groupBySql = " Group BY " . $groupBy; } $dataCount = 0; if ($isPage && !$isRow) { $sql = "SELECT count(*) FROM `{$table}` {$joinSql} WHERE 1 {$likeSql}{$whereSql}{$whereOrSql}{$whereInSql}{$whereNotInSql}{$groupBySql}"; $dataCount = $this->getOne($sql); $dataCount = intval($dataCount); $debug[] = $sql; } $limit = ""; if ($isPage && !$isRow) { $pageSize = !intval($pageSize) ? 10 : $pageSize; if ($pageNo == 0 || $dataCount == 0) { $pageNo = "0, "; } else { $no = intval(($pageNo - 1) * $pageSize); $maxNo = (ceil($dataCount / $pageSize) - 1 ) * $pageSize; $no = $no > $maxNo ? $maxNo : $no; $pageNo = $no . ", "; } $limit = " LIMIT {$pageNo}{$pageSize}"; } // 不分页的情况,但指定了获取数据的条数 if ($pageSize && !$isPage) { $limit = " LIMIT {$pageSize}"; } // 如果只需要显示一条数据到此结束运行 if ($isRow) { $sql = "SELECT {$fields} FROM `{$table}` {$joinSql} WHERE 1 {$likeSql}{$whereSql}{$whereOrSql}{$whereInSql}{$whereNotInSql}{$groupBySql}{$orderBySql} LIMIT 1"; $info = $this->getRow($sql); return empty($info) ? array() : $info; } $sql = "SELECT {$fields} FROM `{$table}` {$joinSql} WHERE 1 {$likeSql}{$whereSql}{$whereOrSql}{$whereInSql}{$whereNotInSql}{$groupBySql}{$orderBySql}{$limit}"; $list = $this->getAll($sql); // 使用字段值做为 KEY if ($keyName) { $tempResult = array(); foreach ($list as $key => $val) { $tempKey = isset($val[$keyName]) ? $val[$keyName] : $key ; $tempResult[$tempKey] = $val; } $list = $tempResult; } $debug[] = $sql; unset($fields, $keyName, $isPage, $pageNo, $pageSize, $like, $where, $whereOr, $whereIn, $whereNotIn, $orderBy, $groupBy, $sql); return array( 'list' => empty($list) ? array() : $list, 'total' => intval($dataCount), 'debug' => $debug, ); }