Get Started

Where Syntax

Query

Transaction

Advanced

PDO object

Debug

Information

version: 1.1.3

WHERE Syntax

Some of Medoo functions are required $where argument to filter record like SQL WHERE clause which is powerful but with a lot of complex syntax, logical relativity, and potential security problem about SQL injection. But Medoo provided a powerful and extremely easy way to build WHERE query clause and prevent injection.

Basic condition

The basic condition is simple enough to understand. You can use additional symbol to get advanced filter range for number.

$database->select("account", "user_name", [
	"email" => "foo@bar.com"
]);
// WHERE email = 'foo@bar.com'

$database->select("account", "user_name", [
	"user_id" => 200
]);
// WHERE user_id = 200

$database->select("account", "user_name", [
	"user_id[>]" => 200
]);
// WHERE user_id > 200

$database->select("account", "user_name", [
	"user_id[>=]" => 200
]);
// WHERE user_id >= 200

$database->select("account", "user_name", [
	"user_id[!]" => 200
]);
// WHERE user_id != 200

$database->select("account", "user_name", [
	"age[<>]" => [200, 500]
]);
// WHERE age BETWEEN 200 AND 500

$database->select("account", "user_name", [
	"age[><]" => [200, 500]
]);
// WHERE age NOT BETWEEN 200 AND 500

// [><] and [<>] is also available for datetime
$database->select("account", "user_name", [
	"birthday[><]" => [date("Y-m-d", mktime(0, 0, 0, 1, 1, 2015)), date("Y-m-d")]
]);
//WHERE "create_date" BETWEEN '2015-01-01' AND '2015-05-01' (now)

// You can use not only single string or number value, but also array
$database->select("account", "user_name", [
	"OR" => [
		"user_id" => [2, 123, 234, 54],
		"email" => ["foo@bar.com", "cat@dog.com", "admin@medoo.in"]
	]
]);
// WHERE
// user_id IN (2,123,234,54) OR
// email IN ('foo@bar.com','cat@dog.com','admin@medoo.in')

// [Negative condition]
$database->select("account", "user_name", [
	"AND" => [
		"user_name[!]" => "foo",
		"user_id[!]" => 1024,
		"email[!]" => ["foo@bar.com", "cat@dog.com", "admin@medoo.in"],
		"city[!]" => null,
		"promoted[!]" => true
	]
]);
// WHERE
// `user_name` != 'foo' AND
// `user_id` != 1024 AND
// `email` NOT IN ('foo@bar.com','cat@dog.com','admin@medoo.in') AND
// `city` IS NOT NULL
// `promoted` != 1

// Or fetched from select() or get() function
$database->select("account", "user_name", [
	"user_id" => $database->select("post", "user_id", ["comments[>]" => 40])
]);
// WHERE user_id IN (2, 51, 321, 3431)

Relativity condition

The relativity condition can describe complex relationship between data and data. You can use AND and OR to build complex relativity condition query.

// [Basic]
$database->select("account", "user_name", [
	"AND" => [
		"user_id[>]" => 200,
		"age[<>]" => [18, 25],
		"gender" => "female"
	]
]);
// WHERE user_id > 200 AND age BETWEEN 18 AND 25 AND gender = 'female'

$database->select("account", "user_name", [
	"OR" => [
		"user_id[>]" => 200,
		"age[<>]" => [18, 25],
		"gender" => "female"
	]
]);
// WHERE user_id > 200 OR age BETWEEN 18 AND 25 OR gender = 'female'

// [Compound]
$database->has("account", [
	"AND" => [
		"OR" => [
			"user_name" => "foo",
			"email" => "foo@bar.com"
		],
		"password" => "12345"
	]
]);
// WHERE (user_name = 'foo' OR email = 'foo@bar.com') AND password = '12345'

// [IMPORTANT]
// Because Medoo is using array data construction to describe relativity condition,
// array with duplicated key will be overwritten.
//
// This will be error:
$database->select("account", '*', [
	"AND" => [
		"OR" => [
			"user_name" => "foo",
			"email" => "foo@bar.com"
		],
		"OR" => [
			"user_name" => "bar",
			"email" => "bar@foo.com"
		]
	]
]);
// [X] SELECT * FROM "account" WHERE ("user_name" = 'bar' OR "email" = 'bar@foo.com')

// To correct that, just assign a comment for each AND and OR key name. The comment content can be everything.
$database->select("account", '*', [
	"AND #Actually, this comment feature can be used on every AND and OR relativity condition" => [
		"OR #the first condition" => [
			"user_name" => "foo",
			"email" => "foo@bar.com"
		],
		"OR #the second condition" => [
			"user_name" => "bar",
			"email" => "bar@foo.com"
		]
	]
]);
// SELECT * FROM "account"
// WHERE (
// 	(
// 		"user_name" = 'foo' OR "email" = 'foo@bar.com'
// 	)
// 	AND
// 	(
// 		"user_name" = 'bar' OR "email" = 'bar@foo.com'
// 	)
// )

LIKE condition

LIKE condition can be use it like basic condition or relativity condition with just adding [~] syntax now.

// By default, the keyword will be quoted with % front and end to match the whole word.
$database->select("person", "id", [
	"city[~]" => "lon"
]);

WHERE "city" LIKE '%lon%'

// Array support
$database->select("person", "id", [
	"city[~]" => ["lon", "foo", "bar"]
]);

WHERE "city" LIKE '%lon%' OR "city" LIKE '%foo%' OR "city" LIKE '%bar%'

// Negative condition [!~]
$database->select("person", "id", [
	"city[!~]" => "lon"
]);

WHERE "city" NOT LIKE '%lon%'

// SQL LIKE Wildcard
// You can use SQL wildcard to match more complex situation
$database->select("person", "id", [
	"city[~]" => "%stan" // Kazakhstan,  Uzbekistan, Türkmenistan
]);

$database->select("person", "id", [
	"city[~]" => "Londo_" // London, Londox, Londos...
]);

$database->select("person", "id", [
	"name[~]" => "[BCR]at" // Bat, Cat, Rat
]);

$database->select("person", "id", [
	"name[~]" => "[!BCR]at" // Eat, Fat, Hat...
]);

Order condition (Updated since v1.1)

$database->select("account", "user_id", [

	// Single condition
	"ORDER" => "user_id",

	// Multiple condition
	"ORDER" => [
		// Order by column with sorting by customized order.
		"user_id" => [43, 12, 57, 98, 144, 1],

		// Order by column
		"register_date",

		// Order by column with descending sorting
		"profile_id" => "DESC",

		// Order by column with ascending sorting
		"date" => "ASC"
	]
]);

Full text searching

// [MATCH]
$database->select("post_table", "post_id", [
	"MATCH" => [
		"columns" => ["content", "title"],
		"keyword" => "foo"
	]
]);
// WHERE MATCH (content, title) AGAINST ('foo')

Using SQL functions

In some of special case, you may need to use SQL functions to process data. Just assign # in front of the column name, then the value will not be quoted.

$data = $database->select('account', [
	'user_id',
	'user_name'
], [
	'#datetime' => 'NOW()'
]);

// SELECT "user_id","user_name"
// FROM "account"
// WHERE "datetime" = NOW()

// [IMPORTANT] Keep in mind that, the value will not be quoted should be matched as XXX() uppercase.
// The following sample will be failed.
$database->select('account', [
	'user_id',
	'user_name'
], [
    '#datetime2' => 'now()',

    'datetime3' => 'NOW()',

    '#datetime4' => 'NOW'
]);

Additional condition

$database->select("account", "user_id", [
	"GROUP" => "type",

	// Must have to use it with GROUP together
	"HAVING" => [
		"user_id[>]" => 500
	],

	// LIMIT => 20
	"LIMIT" => [20, 100]
]);
//	SELECT user_id FROM account
//	GROUP BY type
//	HAVING user_id > 500
//	LIMIT 20,100