Use of the Where method of the thinkphp

Use of the Where () condition in 1.Thinkphp

There are always people who think that the thinkphp where () is to write I want to add, query, modify, delete data conditions, very simple, actually I want to tell you, where () is write conditional statement, but he is not simple, strict conditional statements, can effectively prevent SQL injection, You can also enhance the rigor of SQL statements and facilitate search queries

1.1 Query Method classification

The use of the Where method is the essence of the thinkphp query language, which can complete query operations including common queries, expression queries, shortcut queries, interval queries, and combined queries . The arguments of the Where method support strings and arrays , although objects can also be used but are not recommended.

1.1.1 String condition

Direct query and manipulation using string conditions (the conditional statements in the TP frame controller can be found in /runtime/logs/.

Example: $User = M ("User"); Instantiating a User object
$User->where (' type=1 and Status=1 ')->select ();

Generate SQL statement: SELECT * from user WHERE type=1 and Status=1;

　　

In TP version 3.1 or above, it is recommended to cooperate with the preprocessing mechanism to ensure a more secure and effective anti-SQL injection

$Model->where ("id=%d and Username= '%s ' and xx= '%f '", Array ($id, $username, $xx))->select ();

Or: $Model->where ("id=%d and Username= '%s ' and xx= '%f '", $id, $username, $xx)->select ();

1.1.2 Array Conditions

The simplest array condition query:

$User = M ("User"); Instantiating a User object
$map [' name '] = ' thinkphp ';
$map [' status '] = 1;
Passing query criteria into Query method
$User->where ($map)->select ();

Generated SQL statement: SELECT * from user WHERE ' name ' = ' thinkphp ' and Status=1

　　

1.1.3 An expression query

$map [' field 1 '] = Array (' expression ', ' query condition 1 ');
$map [' field 2 '] = Array (' expression ', ' query Condition 2 ');
$Model->where ($map)->select ();

　　

1.1.4 query for operator conditions in an expression

TP operator

	SQL operator	example	actual query condition
eq	=	$map [' id '] = array (' eq ', 100);	Equivalent to: $map [' id '] = 100;
Neq	!=	$map [' id '] = array (' NEQ ', 100);	ID! = 100
Gt	>	$map [' id '] = array (' GT ', 100);	ID > 100
Egt	>=	$map [' id '] = array (' EGT ', 100);	ID >= 100
Lt	<	$map [' id '] = array (' lt ', 100);	ID < 100
Elt	<=	$map [' id '] = array (' ELT ', 100);	ID <= 100
Like	Like	$map < ' username ' > = array (' Like ', ' admin% ');	Username like ' admin% '
Between	Between and	$map [' id '] = array (' Between ', ' 1,8 ');	ID between 1 and 8
Not between	Not between and	$map [' id '] = array (' Not between ', ' 1,8 ');	ID not between 1 and 8
Inch	Inch	$map [' id '] = array (' In ', ' 1,5,8 ');	ID in (1,5,8)
Not in	Not in	$map [' id '] = array (' Not ', ' 1,5,8 ');	ID not in (1,5,8)
and (default)	and	$map [' id '] = Array (array (' GT ', 1), Array (' LT ', 10));	(ID > 1) and (ID < 10)
Or	Or	$map [' id '] = array (' GT ', 3), array (' LT ', ten), ' or ');	(ID > 3) OR (ID < 10)
XOR (different OR)	Xor	Only one of the two inputs is true, the result is true, otherwise false, the example is slightly.	1 XOR 1 = 0
Exp	Synthetic expressions	$map [' id '] = Array (' exp ', ' in (1,3,8) ');	$map [' id '] = array (' In ', ' 1,3,8 ');

Use of the Where method of the thinkphp