返回文档

WHERE Syntax

Some of Medoo functions are required the $where 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 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 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 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 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

$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

Full-text searching feature is supported by MySQL database for an advanced search result.

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 of 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 array of values.
	'GROUP' => [
		'type',
		'age',
		'gender'
	],
 
	'HAVING' => [
		'user_id[>]' => 500
	]

]);

返回文档

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

where.md

where.md

WHERE Syntax

Basic Condition

[><] and [<>] is also available for datetime.

You can use not only single string or number value, but also array

Negative condition

Or fetched from select() or get() function.

Relativity Condition

Basic

Compound

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

To correct that, just assign a comment for each AND and OR key name (# with a blank space). The comment content can be everything.

Columns Relationship

LIKE Condition

Group

Negative condition

Compound

SQL Wildcard

Order Condition

Full Text Searching

Search mode

Using Regular Expression

Using SQL Functions

LIMIT And OFFSET

GROUP And HAVING

Files

where.md

Latest commit

History

where.md

File metadata and controls

WHERE Syntax

Basic Condition

[><] and [<>] is also available for datetime.

You can use not only single string or number value, but also array

Negative condition

Or fetched from select() or get() function.

Relativity Condition

Basic

Compound

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

To correct that, just assign a comment for each AND and OR key name (# with a blank space). The comment content can be everything.

Columns Relationship

LIKE Condition

Group

Negative condition

Compound

SQL Wildcard

Order Condition

Full Text Searching

Search mode

Using Regular Expression

Using SQL Functions

LIMIT And OFFSET

GROUP And HAVING