May 16, 2021 MariaDB
Where clause filters various statements such as SELECT, UPDATE, DELETE, and INSERT. T hey proposed criteria for designation of actions. T hey usually appear after the table name in the statement, with the following conditions. T he WHERE clause is essentially like an if statement.
See the general syntax of the WHERE clause given below -
[COMMAND] field,field2,... FROM table_name,table_name2,... WHERE [CONDITION]
Note the following features of the WHERE clause:
It is optional.
It allows any condition to be specified.
It allows you to specify multiple conditions by using the AND or OR operator.
Case sensitive applies only to statements that are compared using LIKE.
The WHERE clause allows the following operators to be used -
The operator |
---|
= != |
> < |
>= <= |
Where clauses can be used in command prompts or PHP scripts.
At the command prompt, simply use the standard command -
root@host# mysql -u root -p password; Enter password:******* mysql> use PRODUCTS; Database changed mysql> SELECT * from products_tbl WHERE product_manufacturer = 'XYZ Corp'; +-------------+----------------+----------------------+ | ID_number | Nomenclature | product_manufacturer | +-------------+----------------+----------------------+ | 12345 | Orbitron 4000 | XYZ Corp | +-------------+----------------+----------------------+ | 12346 | Orbitron 3000 | XYZ Corp | +-------------+----------------+----------------------+ | 12347 | Orbitron 1000 | XYZ Corp | +-------------+----------------+----------------------+
View examples with AND conditions -
SELECT * FROM products_tbl WHERE product_name = 'Bun Janshu 3000'; AND product_id <= 344;
This example combines AND and OR conditions
SELECT * FROM products_tbl WHERE (product_name = 'Bun Janshu 3000' AND product_id < 344) OR (product_name = 'Bun Janshu 3000');
Apply the function () function mysql_query where clause is used -
<?php $dbhost = 'localhost:3036'; $dbuser = 'root'; $dbpass = 'rootpassword'; $conn = mysql_connect($dbhost, $dbuser, $dbpass); if(! $conn ) { die('Could not connect: ' . mysql_error()); } $sql = 'SELECT product_id, product_name, product_manufacturer, ship_date FROM products_tbl WHERE product_manufacturer = "XYZ Corp"'; mysql_select_db('PRODUCTS'); $retval = mysql_query( $sql, $conn ); if(! $retval ) { die('Could not get data: ' . mysql_error()); } while($row = mysql_fetch_array($retval, MYSQL_ASSOC)) { echo "Product ID :{$row['product_id']} <br> ". "Name: {$row['product_name']} <br> ". "Manufacturer: {$row['product_manufacturer']} <br> ". "Ship Date: {$row['ship_date']} <br> ". "--------------------------------<br>"; } echo "Fetched data successfully "; mysql_close($conn); ?>
After a successful data retrieval, you will see the following output -
产品编号:12345 命名:Orbitron 4000 制造商:XYZ公司 交货日期:17年1月1日 ---------------------------------------------- 产品编号:12346 命名:Orbitron 3000 制造商:XYZ公司 交货日期:17年1月2日 ---------------------------------------------- 产品编号:12347 命名:Orbitron 1000 制造商:XYZ公司 交货日期:17年1月2日 ---------------------------------------------- 成功的mysql>读取的数据