Medoo Where的使用:条件

SQL中使用where可能会有一些不安全的动态参数传入或者一些复杂的SQL语句,但是Medoo提供了一种强大而且容易的方法来构建WHERE查询句子来防止注入。

where:在 Medoo Select的使用:查询数据 已涉及到where的使用

where一般使用在selectupdatedelete等语句中,在medoo里都是以参数的形式展现,并未封装为方法,下面直接开始举例讲解用法。


基本条件很简单, 容易理解。 您可以使用运算符作为获取数字范围的高级过滤器。
 $database-> select("account", "user_name", [
	 "email"=>"[email protected]"
 ]);
 // WHERE email ='[email protected]'
 
 $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
 
 // [> <]和[<>]也可用于datetime
 $database-> select("account", "user_name", [
	 "birthday [> <]"=> [date("Ymd", mktime(0, 0, 0, 1, 1, 2015)), date("Ymd")]
 ]);
 // WHERE"create_date"BETWEEN'2015-01-01'AND'2015-05-01'(now)
 
 //你不仅可以使用单个字符串或数字值, 还可以使用数组
 $database-> select("account", "user_name", [
	 "OR"=> [
		 "user_id"=> [2, 123, 234, 54], 
		 "email"=> ["[email protected]", "[email protected]", "[email protected]"]
	 ]]
 ]);
 // WHERE
 // user_id IN(2,123,234,54)OR
 // email IN('[email protected]', '[email protected]', '[email protected]')
 
 // 多条件查询
 $database-> select("account", "user_name", [
	 "AND"=> [
		 "user_name [!]"=>"foo", 
		 "user_id [!]"=> 1024, 
		 "email [!]"=> ["[email protected]", "[email protected]", "[email protected]"], 
		 "city [!]"=> null, 
		 "promote [!]"=> true
	 ]]
 ]);
 // WHERE
 //'user_name'!='foo'AND
 //'user_id'!= 1024 AND
 //'email' NOT IN('[email protected]', '[email protected]', '[email protected]')AND
 //'city' IS NOT NULL
 //'promote'!= 1
 
 //或从select()或get()函数获取
 $database-> select("account", "user_name", [
	 "user_id"=> $database-> select("post", "user_id", ["comments [>]"=> 40])
 ]);
 // WHERE user_id IN(2, 51, 321, 3431)

相对性条件可以描述数据和数据之间的复杂关系。 您可以使用ANDOR构建复杂的相对论条件查询。
// [基本条件查询]
 $database-> select("account", "user_name", [
	 "AND"=> [
		 "user_id [>]"=> 200, 
		 "age [<>]"=> [18, 25], 
		 "gender"=>"女性"
	 ]]
 ]);
 // 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"=>"女性"
	 ]]
 ]);
 // WHERE user_id> 200 OR age BETWEEN 18 AND 25 OR gender ='female'
 
 // [复合条件]
 $database-> has("account", [
	 "AND"=> [
		 "OR"=> [
			 "user_name"=>"foo", 
			 "email"=>"[email protected]"
		 ], 
		 "password"=>"12345"
	 ]]
 ]);
 // WHERE(user_name ='foo' OR email ='[email protected]')AND password ='12345'
 
 // [重要]
 //因为Medoo使用数组数据构造来描述相对性条件, 
 //具有重复键的数组将被覆盖。
 //
 //下面这种用法是错误的:
 $database-> select("account", '*', [
	 "AND"=> [
		 "OR"=> [
			 "user_name"=>"foo", 
			 "email"=>"[email protected]"
		 ], 
		 "OR"=> [
			 "user_name"=>"bar", 
			 "email"=>"[email protected]"
		 ]]
	 ]]
 ]);
 // [X] SELECT * FROM"account"WHERE("user_name"='bar'OR"email"='[email protected]')
 
 //要纠正, 只需为每个AND和OR键名赋值一个注释。 
 $database-> select("account", '*', [
	 "AND#实际上, 此注释功能可用于每个AND和OR相对性条件"=> [
		 "OR #the first condition"=> [
			 "user_name"=>"foo", 
			 "email"=>"[email protected]"
		 ], 
		 "OR #the second condition"=> [
			 "user_name"=>"bar", 
			 "email"=>"[email protected]"
		 ]]
	 ]]
 ]);
 // SELECT * FROM"account"
 // WHERE(
 //(
 //"user_name"='foo'OR"email"='[email protected]'
 //)
 // AND
 //(
 //"user_name"='bar'OR"email"='[email protected]'
 //)
 //)

LIKE条件语法:[~]
    // 默认情况下,使用%在前后包含关键词
    $database->select("person", "id", [
        "city[~]" => "lon"
    ]);
 
    //WHERE "city" LIKE '%lon%'
 
 
    // 数组形式,查询多个关键词
    $database->select("person", "id", [
        "city[~]" => ["lon", "foo", "bar"]
    ]);
 
    //WHERE "city" LIKE '%lon%' OR "city" LIKE '%foo%' OR "city" LIKE '%bar%'
 
 
    // 不包含 [!~]
    $database->select("person", "id", [
        "city[!~]" => "lon"
    ]);
 
    //WHERE "city" NOT LIKE '%lon%'
 
 
    // 使用SQL自带的一些通配符
    // 你可以使用sql自带的一些通配符来完成较复杂的查询
    $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...
    ]);

 $database-> select("account", "user_id", [
 
	 //单个条件
	 "ORDER"=>"user_id", 
 
	 //多条件
	 "ORDER"=> [
		 //按列排序, 按自定义顺序排序。
		 "user_id"=> [43, 12, 57, 98, 144, 1], 
 
		 //按列排序
		 "register_date", 
 
		 //按降序排序的列排序
		 "profile_id"=>"DESC", 
 
		 //按升序排序的列排序
		 "date"=>"ASC"
	 ]]
 ]);

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

在一些特殊的情况下,你可能需要使用SQL系统函数,只需要字段名前加上#号即可

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

    $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