PHP MySQL - WHERE and LIKE

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:

Operator	Meaning	Example
=	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 NOT NULL	Has a value	WHERE `id` IS NOT NULL
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"):

<?php
// 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';
}

$conn->close();
?>

- 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:
• 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, NOT LIKE

LIKE and NOT LIKE terms are used for string matching in combination with the following two wildcard characters:

• %   - matches any sequence of characters or none.
• _   (an underscore), matches exactly one character.

These terms are used with the WHERE clause, and the search is case-insensitive.

Examples of queries with LIKE /NOT LIKE

<?php
// ...

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

// ...
?>

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

<?php
// ...

#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.).

<?php
// ...

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

// ...
?>

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

<?php
// ...

#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.

<ul>
 <li>http://coursesweb.net/html/</li>
 <li>http://coursesweb.net/css/</li>
</ul>

.some_class {
  display: list-item;
}

var obj = {
 "courses": ["php", "javascript", "ajax"]
};
var jsonstr = JSON.stringify(obj);
alert(jsonstr);    // {"courses":["php","javascript","ajax"]}

$strhtml = '<body><div id="dv1">CoursesWeb.net</div></body>';
$dochtml = new DOMDocument();
$dochtml->loadHTML($strhtml);
$elm = $dochtml->getElementById("dv1");
echo $elm->nodeValue;    // CoursesWeb.net