简单数据查询

 


 

说明

查询有两种方式,简单数据查询链式查询,链式查询可以看后面的文章

 

get

通过主键获取一条记录

 

参数

 

get(主键值, [需要的字段], [表名]);

 

例子

 

比如我们有个用户模型,UserModel,用户表的主键是uid

 

$userModel = new UserModel();

//获取单个用户
$uid = 100;
$userModel->get($uid);
$userModel->getRow($uid);

//获取多个用户
//$uids = '1001,1008,1376,3769';
$uids = [1001, 1008, 1376, 3769]; //字符串和数组都可以
$users = $userModel->get($uids);

 

getRow

通过条件获取一条记录

 

参数

 

getRow($where, $fields = '*', $table = '')

 

例子

 

$where = [
    'email' => 'tommy@timophp.com'
];
$row = $userModel->getRow($where);
$user = $userModel->getRow(1009);

 

where条件可以是数组,主键id的值,也可以传字符串,上面例子用的数组,下面举个字符串的例子:

 

$where = 'type BETWEEN 1 AND 3 AND status = 1';

$row = $userModel->getRow($where);
后面两个参数和上面一样

 

mode

设置获取模式,返回不同类型数据

 

参数

mode($fetchMode)

 

例子

 

返回用户对象类型数据

 

$uid = 100;
$userModel = new UserModel()
$user = $userModel->mode(\PDO::FETCH_OBJ)->get($uid, 'uid, nickname, avatar, signature');
echo $user->nickname;

 

如果不设置$userModel->mode(\PDO::FETCH_OBJ),返回就是数组:

 

$user = $userModel->get($uid, 'uid, nickname, avatar, signature');
echo $user['nickname'];

 

all

通过条件获取多条数据

 

参数

 

all($where, $fields = '*', $order = '', $group = '', $limit = 0, $table = '')

 

$where = [
    'level' => ['>', 1]
];
$fields = 'uid, nick_name, email';
$rows = $this->all($where, $fields, 'level DESC, uid DESC');

 

如果查询到数据,返回数组,如果没有数据返回false,参数order、group、limit和我们平时写SQL是一样的

 

返回一列数据

 

$userModel = new UserModel();
$where = [
    'id' => ['<', 6]
];
$users = $userModel->mode(\PDO::FETCH_COLUMN)->all($where, 'id');
print_r($users);

 

输出:
Array
(
    [0] => 1
    [1] => 2
    [2] => 3
    [3] => 4
    [4] => 5
)

 

find

分页获取多条数据

 

参数

 

find($where, $fields = '*', $order = '', $group = '', array &$page, $table = '')

 

例子

 

$where = [
    'album_id' => 10008,
    'status' => 1
];
$page = [
    'p' => 1,
    'limit' => 20
];

$fields = 'qid, name, answer_num, add_time';
$rows = $this->find($where, $fields, 'qid desc', '', $page);
var_dump($page);

 

输出:
array(
    'p' => 1,
    'limit' => 20,
    'total' => 108,
    'total_page' => 6
)
发现没$page数组多了两项total(总条数)、total_page(总页数)

 

join查询

比如链表查询 user user_extend user_count 三张表
$where = [
    'u.type' => ['>', 1],
    'c.status' => 1
];
$fields = 'u.id, u.nickname, e.signature, c.play_num';
$from = 'user u LEFT JOIN user_extend e ON e.uid = u.id LEFT JOIN user_count c ON c.uid = u.id';
$sort = 'u.last_login_time DESC';
$page = [
    'p' => 1,
    'limit' => 10
];
$model = new Model();
$users = $model->find($where, $fields, $sort, '', $page, $from);

 

直接通过SQL语句来查询

通过db来查询

 

$sql = 'SELECT uid, nickname, avatar FROM tb_user where status = 1';

$users = $this->db->all($sql);

$sql = 'SELECT uid, nickname, avatar FROM tb_user where uid = 10009';
$user = $this->db->getOne($sql);

 

没有查询参数

$sql = 'SELECT * FROM __TABLE__ WHERE uid > 100 AND type = 2';

//获取一条
$rows = $this->query($sql)->fetchRow();

//获取多条
$rows = $this->query($sql)->fetchAll();

__TABLE__会把替换为当前模型对应的表名,包括表前缀

当然,你也可以直接把表名写上,如:
$sql = 'SELECT * FROM timo_user WHERE uid > 100 AND type = 2';

 

有查询参数

$sql = 'SELECT * FROM __TABLE__ WHERE uid > ? AND type = ?';
$params = array(100, 2);

//获取一条
$rows = $this->query($sql, $params)->fetchRow();

//获取多条
$rows = $this->query($sql, $params)->fetchAll();

 

获取最后一条查询语句

在模型里面

$last_sql = $this->db->getLastSql();

 

在模型外面

$userModel = new UserModel();
$userModel->find();
$last_sql = $userModel->db()->getLastSql();