Get Started

Medoo 1.4

Where Syntax

Query

Fetch

Transaction

Advanced

PDO object

Debug

Information

version: 1.4.5

select

Select data from database

select($table, $columns, $where)
select($table, $join, $columns, $where)
Return: [array]
You can use "*" as columns parameter to fetch all columns, but we recommended providing all targeted columns for better performance and readability.
$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"] . "<br/>";
}

// Select all columns
$datas = $database->select("account", "*");

// Select a column
$datas = $database->select("account", "user_name");

// $datas = array(
// 	[0] => "foo",
// 	[1] => "cat"
// )

Table joining

SQL JOIN clause can combine rows together between two table. Medoo provide simple syntax for JOIN clause.

// [>] == 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 mapping

Customize output data construction - The key name for wrapping data has no relation with columns itself and it is multidimensional.

$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);

// Output 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 type declaration

Set the type of output data

// Supported data type: [String | Bool | Int | Number | Object | JSON]
// [String] is the default type for all output data.
// [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"

Alias

You can use alias as a new column or table name instead of original one. This is useful for table joining to prevent name conflict.

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