select 查询

select

数据库查询

• table [string]

  表名.

• columns [string/array]

  要查询的字段名.

• where (optional) [array]

  查询的条件.

• table [string]

  表名.

• join [array]

  多表查询,不使用可以忽略.

• columns [string/array]

  要查询的字段名.

• where (optional) [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"			}		}	}]

数据类型声明

设置输出的类型

// 可声明的类型: [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 backclass 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"// 	)// )