WHERE Syntax

Some of Medoo's functions are required the $where the argument to filter records like SQL WHERE clause, which is powerful but with a lot of complex syntaxes, logical relativity, and potential security problems about SQL injection. But Medoo provided a powerful and easy way to build a WHERE query clause and prevent injection.

Basic condition

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

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

$database->select("account", "user_name", [
	"birthday[><]" => [date("Y-m-d", mktime(0, 0, 0, 1, 1, 2015)), date("Y-m-d")]
]);

WHERE ("birthday" BETWEEN '2015-01-01' AND '2017-01-01')
WHERE ("birthday" NOT BETWEEN '2015-01-01' AND '2017-01-01')

You can use not only a single string or number value but also an 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 the complex relationship between data and data. You can use AND and OR to build complex relativity condition queries.

Basic

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

// Medoo will connect the relativity condition with AND by default. The following usage is the same as above.
$database->select("account", "user_name", [
	"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'

Because Medoo uses array data construction to describe the relativity condition, arrays with duplicate keys will be overwritten.

// This will be an 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 (# with a blank space). 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"
		]
	]
]);

WHERE (
	("user_name" = 'foo' OR "email" = 'foo@bar.com')
	AND
	("user_name" = 'bar' OR "email" = 'bar@foo.com')
)

Columns Relationship

$database->select("post", [
	"[>]account" => "user_id",
], [
	"post.content"
], [
	// Connect two columns with condition signs like [=], [>], [<], [!=] as one of array value.
	"post.restrict[<]account.age"
]);

WHERE "post"."restrict" < "account"."age"

LIKE condition

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

// 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%'

Group

$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%'

Compound

$database->select("person", "id", [
	"content[~]" => ["AND" => ["lon", "on"]]
]);

WHERE ("content" LIKE '%lon%' AND "content" LIKE '%on%')

$database->select("person", "id", [
	"content[~]" => ["OR" => ["lon", "on"]]
]);

WHERE ("content" LIKE '%lon%' OR "content" LIKE '%on%')

SQL Wildcard

You can use SQL wildcards to match more complex situations.

$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

$database->select("account", "user_id", [
	// Single condition.
	"ORDER" => "user_id",

	// Multiple condition.
	"ORDER" => [
		// Order by column with sorting by custom 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

The full-text searching feature is supported by the MySQL database for advanced search results.

Search mode list

natural	IN NATURAL LANGUAGE MODE
natural+query	IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION
boolean	IN BOOLEAN MODE
query	WITH QUERY EXPANSION

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

		// [optional] Search mode.
		"mode" => "natural"
	]
]);

WHERE MATCH (content, title) AGAINST ('foo' IN NATURAL LANGUAGE MODE)

Using Regular Expression

$data = $database->select('account', [
	'user_id',
	'user_name'
], [
	'user_name[REGEXP]' => '[a-z0-9]*'
]);

WHERE "user_name" REGEXP '[a-z0-9]*'

Using SQL functions

You can now use SQL functions with the raw object for complex usage. Read more from https://medoo.in/api/raw.

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

WHERE "datetime" = NOW()

LIMIT and OFFSET

$database->select("account", "user_id", [
	// Get the first 100 rows.
	'LIMIT' => 100

	// Start from the top 20 rows and get the next 100.
	'LIMIT' => [20, 100],

	// For Oracle and MSSQL databases, you also need to use ORDER BY together.
	'ORDER' => 'location'
]);

GROUP and HAVING

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

	// GROUP by the array of values.
	'GROUP' => [
		'type',
		'age',
		'gender'
	],

	'HAVING' => [
		'user_id[>]' => 500
	]
]);