select
数据库查询
select($table, $columns, $where)
table [string]
表名.
columns [string/array]
要查询的字段名.
where (optional) [array]
查询的条件.
select($table, $join, $columns, $where)
table [string]
表名.
join [array]
多表查询,不使用可以忽略.
columns [string/array]
要查询的字段名.
where (optional) [array]
查询的条件.
返回: [array]
你可以使用*来匹配所有字段, 但如果你指名字段名可以很好的提高性能.
$database = new medoo();
$datas = $database->select("account", [
"user_name",
"email"
], [
"user_id[>]" => 100
]);
// $datas = array(
// [0] => array(
// "user_name" => "foo",
// "email" => "foo@bar.com"
// ),
// [1] => array(
// "user_name" => "cat",
// "email" => "cat@dog.com"
// )
// )
foreach($datas as $data)
{
echo "user_name:" . $data["user_name"] . " - email:" . $data["email"] . "
";
}
// Select all columns
$datas = $database->select("account", "*");
// Select a column
$datas = $database->select("account", "user_name");
// $datas = array(
// [0] => "foo",
// [1] => "cat"
// )
表关联
多表查询SQL较为复杂，使用Medoo可以轻松的解决它
// [>] == LEFT JOIN
// [<] == RIGH JOIN
// [<>] == FULL JOIN
// [><] == INNER JOIN
$database->select("post", [
// Here is the table relativity argument that tells the relativity between the table you want to join.
// The row author_id from table post is equal the row user_id from table account
"[>]account" => ["author_id" => "user_id"],
// The row user_id from table post is equal the row user_id from table album.
// This is a shortcut to declare the relativity if the row name are the same in both table.
"[>]album" => "user_id",
// [post.user_id is equal photo.user_id and post.avatar_id is equal photo.avatar_id]
// Like above, there are two row or more are the same in both table.
"[>]photo" => ["user_id", "avatar_id"],
// If you want to join the same table with different value,
// you have to assign the table with alias.
"[>]account (replyer)" => ["replyer_id" => "user_id"],
// You can refer the previous joined table by adding the table name before the column.
"[>]account" => ["author_id" => "user_id"],
"[>]album" => ["account.user_id" => "user_id"],
// Multiple condition
"[>]account" => [
"author_id" => "user_id",
"album.user_id" => "user_id"
]
], [
"post.post_id",
"post.title",
"account.user_id",
"account.city",
"replyer.user_id",
"replyer.city"
], [
"post.user_id" => 100,
"ORDER" => ["post.post_id" => "DESC"],
"LIMIT" => 50
]);
// SELECT
// `post`.`post_id`,
// `post`.`title`,
// `account`.`city`
// FROM `post`
// LEFT JOIN `account` ON `post`.`author_id` = `account`.`user_id`
// LEFT JOIN `album` USING (`user_id`)
// LEFT JOIN `photo` USING (`user_id`, `avatar_id`)
// WHERE
// `post`.`user_id` = 100
// ORDER BY `post`.`post_id` DESC
// LIMIT 50
数据映射
根据您所需要的数据结构，自定义输出的数据格式
$data = $database->select("post", [
"[>]account" => ["user_id"]
], [
"post.post_id",
"post.content",
"userData" => [
"account.user_id",
"account.email",
"meta" => [
"account.location",
"account.gender"
]
]
], [
"LIMIT" => [0, 2]
]);
echo json_encode($data);
// Outputed data
[
{
post_id: "1",
content: "Hello world!",
userData: {
user_id: "1",
email: "foo@example.com",
meta: {
location: "New York",
gender: "male"
}
}
},
{
post_id: "2",
content: "Hey everyone",
userData: {
user_id: "2",
email: "bar@example.com",
meta: {
location: "London",
gender: "female"
}
}
}
]
数据类型声明
设置输出的类型
// 可声明的类型: [String | Bool | Int | Number | Object | JSON]
// [String] 默认类型.
// [Object] is a PHP object data decoded by serialize(), and will be unserialize()
// [JSON] is a valid JSON, and will be json_decode()
$data = $database->select("post", [
"[>]account" => ["user_id"]
], [
"post.post_id",
"profile" => [
"account.age [Int]",
"account.is_locked [Bool]",
"account.userData [JSON]"
]
], [
"LIMIT" => [0, 2]
]);
echo json_encode($data);
// Output data
[
{
post_id: "1",
profile: {
age: 20,
is_locked: true,
userData: ["foo", "bar", "tim"]
}
},
{
post_id: "2",
profile: {
age: 25,
is_locked: false,
userData: ["mydata1", "mydata2"]
}
}
]
// Store an object into database, and get it back
class Foo {
var $bar = "cat";
public function __wakeup()
{
$this->bar = "dog";
}
}
$object_data = new Foo();
$database->insert("account", [
"data" => $object_data
]);
$data = $database->select("account", [
"data [object]"
], [
"id" => 10
]);
echo $data[ 0 ][ "data" ]->bar;
// The object's __wakeup function will be called and update the value
// So the output will be "dog"
"dog"
字段别名
你可以使用别名，以防止字段冲突
$data = $database->select("account", [
"user_id",
"nickname(my_nickname)"
], [
"LIMIT" => 20
]);
// $data = array(
// [0] => array(
// "user_id" => "1",
// "my_nickname" => "foo"
// ),
// [1] => array(
// "user_id" => "2",
// "my_nickname" => "bar"
// )
// )
$data = $database->select("post (content)", [
"[>]account (user)" => "user_id",
], [
"content.user_id (author_id)",
"user.user_id"
], [
"LIMIT" => 20
]);
// SELECT
// "content"."user_id" AS author_id,
// "user"."user_id"
// FROM
// "post" AS "content"
// LEFT JOIN "account" AS "user" USING ("user_id")
// LIMIT 2
// $data = array(
// [0] => array(
// "author_id" => "1",
// "user_id" => "321"
// ),
// [1] => array(
// "author_id" => "2",
// "user_id" => "322"
// )
// )