Introduction HTML CSS PHP MySQL Other

MySQL Conditionals

Conditionals, like operators in PHP, allow you to make your queries more specific.

List of conditionals

=	equals
!=	not equals
<	greater than
>	less than
<=	less than or equal to
>=	greater than or equal to
NULL	has any value
NOT NULL	has no value
BETWEEN	a value inside a range of numbers
NOT BETWEEN	a value outside a range of numbers
BETWEEN	a value not between 2 numbers
LIKE	search for a pattern
OR	where 1 of 2 conditions is true
AND	where more than 1 condition is true
NOT	where a condition is not true

Notes on LIKE searches:

	Queries with LIKE in them are generally slower becuase they can't take advantage of indexes. Indexes are a way of formatting a column so that it is easy to perform text searches.
	The LIKE and NOT LIKE clauses are usually accompanied by wildcard characters that narrow a search. The underscore (_) will pick while the percent sign (%) will bring up 0 or more characters. Wildcards can be used at the front or back of a string, or at both ends. The underscore can be used multiple times.
	LIKE and NOT LIKE can also be used with numeric columns - although they are usually not.
	To actually search for text that has the percent sign or underscore in it then you will need to escape it like you escape single quote by placing a backslash before it.

The following examples will use the following database.

DOES NOT EQUAL

MySQL Code

	$query="SELECT firstname, lastname, state FROM users WHERE firstname != 'John'";

Results

	Martin Gross 16 Florida Brian Grossman 31 Maine Eric Friendman 27 Texas

GREATER THAN

MySQL Code

	$query="SELECT firstname, lastname, state FROM users WHERE age > 18";

Results

	John Kennedy 35 Massachusetts Brian Grossman 31 Maine Eric Friendman 27 Texas

AND

MySQL Code

	$query="SELECT firstname, lastname, state FROM users WHERE firstname = 'John' AND state = 'Ohio'";

Results

	John Smith Ohio

LIKE with wildcard before

MySQL Code

	SELECT firstname, lastname, state FROM users WHERE lastname LIKE '%man'

Results

	Brian Grossman Maine Eric Friedman Texas

LIKE with wildcard before and after

This query returns any row where the string of letters "as" is anywhere in the state name.

MySQL Code

	SELECT firstname, lastname, state FROM users WHERE state LIKE '%as%'

Results

	John Kennedy Massachusetts Eric Friedman Texas

Bookmark this page:

Shared Hosts
Domain Registrars
PPC Networks
Logos
Affiliate Networks
Resources