Question

select

数据库查询

select($table, $columns, $where)

table [string]

表名。

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
// [>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"
			}
		}
	}
]

字段别名

你可以使用别名，以防止字段冲突

$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"
// 	)
// )