Using WHERE and Conditionals to search for specific values

To search for specific values, to extract only those records that fulfill a specified criterion, add a WHERE clause to the SELECT query.
The WHERE clause is used to filter records, and is added after the name of the table.
  - Syntax:
SELECL column_name(s) FROM table_name WHERE condition(s)
"condition(s)" - is one ore more conditionals that specifies the select criteria. These conditionals can use comparison, arithmetic and logical operators.
The table below lists the most common operators in MySQL WHERE expressions:
= Equals WHERE `id`=3
< Less than WHERE `id`<3
> Greater than WHERE `id`>3
<= Less than or equal to WHERE `id`<=3
>= Greater than or equal to WHERE `id`>=3
!= (also <>) Not equal to WHERE `id`!=3
IS NULL Does not have a value WHERE `email` IS NULL
BETWEEN min AND max Within a specific range (min and max) WHERE `id` BETWEEN 2 and 4
NOT BETWEEN min AND max Not within a specific range (min and max) WHERE `id` NOT BETWEEN 2 and 4
IN(val1, val2, val3) Found within a list of values WHERE `id` IN(1, 3, 4)
OR Where one of two conditionals is true WHERE `id`>3 OR `name`='MarPlo'
AND Where both conditionals are true WHERE `id`>3 AND `name`='MarPlo'

  - Example (selects all rows from "users" table where "name=MarPlo" and output the "id" and "email"):
// connect to the "tests" database
$conn = new mysqli('localhost', 'root', '', 'tests');

// check connection
if (mysqli_connect_errno()) {
  exit('Connect failed: '. mysqli_connect_error());

$name = 'Marplo';            // sets the name in a variable

// SELECT sql query
$sql = "SELECT `id`, `email` FROM `users` WHERE `name`='$name'"; 

// perform the query and store the result
$result = $conn->query($sql);

// if the $result contains at least one row
if ($result->num_rows > 0) {
  // output data of each row from $result
  while($row = $result->fetch_assoc()) {
    echo '<br /> id: '. $row['id']. ' - email: '. $row['email'];
else {
  echo '0 results';

- As you can see, you don't have to select a column on which you are performing a WHERE condition. The reason for this is that the columns listed after SELECT dictate only what columns to return and the columns listed in a WHERE dictate which rows to return.
- Notice, in the $sql query is used a variable ($name) which contains the name, this variable is aded within single quotes in the query becouse MySQL need to get its value as a string.
The example above will output:
id: 2 - email:

• The WHERE clause can also be used with ORDER BY and LIMIT options to order and limit the data in the recordset.
                Example:       $sql = "SELECT `name`, `pass` FROM `users` WHERE `id`>2 ORDER BY `name` LIMIT 2";

• You can perform mathematical calculations within your queries using the mathematic addition (+), subtraction (-), multiplication (*), division (/), and modulo (%) characters.
                Example:       $sql = "SELECT `name`, `pass` FROM `users` WHERE `id`=($x + $y)";


LIKE and NOT LIKE terms are used for string matching in combination with the following two wildcard characters: These terms are used with the WHERE clause, and the search is case-insensitive.

Examples of queries with LIKE /NOT LIKE

// ...

#sql = "SELECT * FROM `table_name` WHERE `name` LIKE 'mar%'";

// ...
- This WHERE clause matches MarPlo, marius, Mars, and so on, but not Omar.

// ...

#sql = "SELECT * FROM `table_name` WHERE `name` NOT LIKE '%mar'";

// ...
- This WHERE clause matches Mar, Omar, Romar, and so on, but not Mars. ('%mar%' matches Mars, Marius, Romars, etc.).

// ...

#sql = "SELECT * FROM `table_name` WHERE `name` NOT LIKE 'mar%'";

// ...
- This query will return all rows whose "name" value not begins with Mar.

// ...

#sql = "SELECT * FROM `table_name` WHERE `name` LIKE 'mar_'";

// ...
- This query will return all rows whose "name" value begins with Mar and has four characters (Mars, Mara, mar8, etc.).

Queries with LIKE terms are generally slower because they can't take advantage of indexes.
To search for a literal percentage sign or underscore in text, precede it with a backslash ( \% or \_ ).
The underscore can be used in combination with itself; (example,   LIKE '_ _'   would find any two-letter combination).
The LIKE keyword must always be followed by a string, even if the search term is limited to numbers.

Daily Test with Code Example

Which type of <input> creates a date input control, such as a pop-up calendar?
type="text" type="date" type="button"
<input type="date" name="set_date" value="2012-10-15" />
Which CSS property adds shadow effects to the text of an element?
font-style color text-shadow
h2 {
  text-shadow: 2px 3px 3px #a0a1fe;
Click on the function that adds new elements to the end of an array.
pop() shift() push()
var pags = ["lessons", "courses"];
pags.push("download", "tutorials");
alert(pags[2]);            // download
Which function sorts an array by key, in ascending order, maintaining key to data correlations?
asort() ksort() sort()
$lang =[10=>"PHP", 20=>"JavaScript", "site"=>"");
var_export($lang);     // array ("site"=>"", 10=>"PHP", 20=>"JavaScript")

Last accessed pages

  1. SHA256 Encrypt hash in JavaScript (12797)
  2. PhpSpreadsheet - Read, Write Excel and LibreOffice Calc files (20928)
  3. OOP - Classes and objects - Create Class (1958)
  4. Get Attribute (ID, Class, Name, Title, Src) with jQuery (67151)
  5. innerHTML and outerHTML to Get and Replace HTML content (24743)

Popular pages this month

  1. Courses Web: PHP-MySQL JavaScript Node.js Ajax HTML CSS (149)
  2. Register and show online users and visitors (95)
  3. PHP-MySQL free course, online tutorials PHP MySQL code (88)
  4. Read Excel file data in PHP - PhpExcelReader (87)
  5. JavaScript Course - Free lessons (73)