We have seen SQL SELECT order to bring information from MySQLi table. We can utilize a contingent condition called WHERE proviso to sift through outcomes. Utilizing WHERE condition, we can determine a determination models to choose required records from a table.
Syntax
Here is nonexclusive SQL grammar of SELECT order with WHERE proviso to get information from MySQLi table −
SELECT field1, field2,...fieldN table_name1, table_name2...
[WHERE condition1 [AND [OR]] condition2.....
You can utilize at least one tables isolated by comma to incorporate different conditions utilizing a WHERE provision, however WHERE proviso is a discretionary piece of SELECT order.
- You can determine any condition utilizing WHERE statement.
- You can indicate more than one conditions utilizing AND OR administrators.
- A WHERE provision can be utilized alongside DELETE or UPDATE SQL order likewise to determine a condition.
The WHERE provision works like an if condition in any programming language. This proviso is utilized to contrast given worth and the field esteem accessible in MySQLi table. Whenever given an incentive from outside is equivalent to the accessible field an incentive in MySQLi table, at that point it restores that column.
Here is the rundown of administrators, which can be utilized with WHERE proviso.
Expect field A holds 10 and field B holds 20, at that point −
Operator | Description | Example |
---|---|---|
= | Checks if the values of two operands are equal or not, if yes then condition becomes true. | (A = B) is not true. |
!= | Checks if the values of two operands are equal or not, if values are not equal then condition becomes true. | (A != B) is true. |
> | Checks if the value of left operand is greater than the value of right operand, if yes then condition becomes true. | (A > B) is not true. |
< | Checks if the value of left operand is less than the value of right operand, if yes then condition becomes true. | (A < B) is true. |
>= | Checks if the value of left operand is greater than or equal to the value of right operand, if yes then condition becomes true. | (A >= B) is not true. |
<= | Checks if the value of left operand is less than or equal to the value of right operand, if yes then condition becomes true. | (A <= B) is true. |
The WHERE condition is helpful when you need to bring chosen lines from a table, particularly when you use MySQLi Join. Joins are examined in another part.
It is a typical practice to look through records utilizing Primary Key to make search quick.
Whenever given condition doesn't coordinate any record in the table, at that point question would not restore any line.
Fetching Data from Command Prompt
This will utilize SQL SELECT order with WHERE statement to bring chosen information from MySQLi table tutorials_inf.
Example
Following model will restore all the records from tutorials_inf table for which name is sai −
root@host# mysql -u root -p password;
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql>SELECT * from tutorials_inf where name = 'sai';
+----+------+
| id | name |
+----+------+
| 1 | SAI |
+----+------+
1 row in set (0.00 sec)
mysql>
Except if playing out a LIKE correlation on a string, the examination isn't case touchy. You can put forth your inquiry defense touchy utilizing BINARY watchword as follows −
root@host# mysql -u root -p password;
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql> SELECT * from tutorials_inf \WHERE BINARY name = 'sai';
Empty set (0.02 sec)
mysql>
Fetching Data Using PHP Script:
You can utilize same SQL SELECT order with WHERE CLAUSE into PHP work mysqli_query().
Example
Following model will restore all the records from tutorials_inf table for which name is sai −
<?php
$dbhost = 'localhost:3306';
$dbuser = 'root';
$dbpass = '';
$dbname = 'TUTORIALS';
$conn = mysqli_connect($dbhost, $dbuser, $dbpass,$dbname);
if(! $conn ) {
die('Could not connect: ' . mysqli_error());
}
echo 'Connected successfully<br>';
$sql = 'SELECT * from tutorials_inf where name="sai"';
$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0) {
while($row = mysqli_fetch_assoc($result)) {
echo "Name: " . $row["name"]. "<br>";
}
} else {
echo "0 results";
}
mysqli_close($conn);
?>
The example yield ought to be this way −
Connected successfully
Name: SAI