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 adds a new line into a paragraph?
<b> <br> <p>
First line ...<br>
Other line...
Which CSS property can be used to add space between letters?
text-size word-spacing letter-spacing
#id {
  letter-spacing: 2px;
What JavaScript function can be used to get access to HTML element with a specified ID?
getElementById() getElementsByTagName() createElement()
var elm = document.getElementById("theID");
var content = elm.innerHTML;
Click on the "echo" correct instruction.
echo "" echo ""; echo """;
echo "Address URL:";

Last accessed pages

  1. Working with HTML attributes in PHP (13578)
  2. Redirects (4874)
  3. Get Duration of Audio /Video file before Upload (15355)
  4. PHP PDO - Select query, fetch (29234)
  5. Disable button and Enable it after specified time (17415)

Popular pages this month

  1. Courses Web: PHP-MySQL JavaScript Node.js Ajax HTML CSS (270)
  2. Read Excel file data in PHP - PhpExcelReader (63)
  3. The Mastery of Love (59)
  4. PHP Unzipper - Extract Zip, Rar Archives (58)
  5. PHP-MySQL free course, online tutorials PHP MySQL code (42)