Php-mysql Course

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 tag renders as emphasized text, displaying the text oblique?
<strong> <pre> <em>
<p>Web development courses: <em></em></p>
Which CSS property defines the space between the element border and its content?
margin padding position
h3 {
  padding: 2px 0.2em;
Click on the method which returns the first element that matches a specified group of selectors.
getElementsByName() querySelector() querySelectorAll()
// gets first Div with class="cls", and shows its content
var elm = document.querySelector("div.cls");
Indicate the PHP variable that contains data from a form sent with method="post".
if(isset($_POST["field"])) {
  echo $_POST["field"];

Last accessed pages

  1. Volume and Surface Area Calculator for 3D objects (8976)
  2. setTimeout and this with bind() method in JavaScript class (1736)
  3. Get and change IFrame content through a JavaScript script created in another IFrame (10860)
  4. Selection Tools (5794)
  5. Add and Remove HTML elements and Content with jQuery (27311)

Popular pages this month

  1. Courses Web: PHP-MySQL JavaScript Node.js Ajax HTML CSS (445)
  2. Register and show online users and visitors (324)
  3. PHP-MySQL free course, online tutorials PHP MySQL code (243)
  4. JavaScript Course - Free lessons (237)
  5. querySelector and querySelectorAll (223)