Explain the usage of WHERE clause in Mysql _mysql

Source: Internet
Author: User
Tags php script

We have seen the SQL SELECT command to fetch data from the MySQL table. We can use a conditional clause in the WHERE clause to filter out the results. Using the WHERE clause, we can specify a selected criterion to select the desired record from the table.
Syntax:

The following is a generic SQL WHERE clause to get the syntax for the data from the Select command of the MySQL table:

SELECT field1, Field2,... fieldn table_name1, table_name2 ...
[WHERE condition1 [and [OR]] condition2 .....

    • You can use one or more comma-delimited tables, including various use WHERE clause conditions. But the select command for the WHERE clause is an optional part.
    • You can specify any condition by using the WHERE clause.
    • You can specify more than one condition to use the and OR operator.
    • An SQL command that can use a scatter delete or update in a WHERE clause to specify conditions.

Just like in a programming language, if a condition's where clause. This clause is used to compare the field value of the table in MySQL with the given value. If the external given value is equal to the available field value in the MySQL table, then return the row.

Here is the operator of the manifest that can use the WHERE clause.

Suppose field A has a value of 10 and field B has a value of 20:

The WHERE clause is very useful when you want to get a row in the selection table, especially if you are using a MySQL join. Join is discussed in another chapter.

It is a common practice to use primary keys to find records so that the search is quick.

If a given condition does not conform to a record in any table, the query does not return any rows.
To get data from a command prompt:

This uses the WHERE clause of the SQL SELECT command to get the selected data MySQL table tutorials_tbl
Instance:

The following example returns the Tutorials_tbl table in which the author's name is Sanjay all records:

root@host# mysql-u root-p password;
Enter password:*******
mysql> use tutorials;
Database changed
mysql> SELECT * from tutorials_tbl WHERE tutorial_author= ' Sanjay ';
+-------------+----------------+-----------------+-----------------+
| tutorial_id | tutorial_title | tutorial_ Author | submission_date |
+-------------+----------------+-----------------+-----------------+
|      3 | JAVA Tutorial | Sanjay     | 2007-05-21 |
+-------------+----------------+-----------------+-----------------+
1 rows in Set (0.01 sec)

mysql>

Unless the comparison to perform a like comparison string is case-insensitive. You can search for case-sensitive, using the binary keyword as follows.

root@host# mysql-u root-p password;
Enter password:*******
mysql> use tutorials;
Database changed
mysql> SELECT * from tutorials_tbl \
     WHERE BINARY tutorial_author= ' Sanjay ';
Empty Set (0.02 sec)

mysql>

To get data using a PHP script:

You can use the WHERE clause of the SELECT command for the same SQL to Functionmysql_query () in PHP. After this function is used to execute the SQL command, another PHP function, mysql_fetch_array (), can be used to get all the selected data. This function returns rows as an associative array, a numeric array, or both. This function returns False if there are no more rows.
Instance:

The following example returns the Tutorials_tbl table in which the author's name is Sanjay all records:

<?php
$dbhost = ' localhost:3036 ';
$dbuser = ' root ';
$dbpass = ' Rootpassword ';
$conn = mysql_connect ($dbhost, $dbuser, $dbpass);
if (! $conn)
{
 die (' Could not connect: '. Mysql_error ());
}
$sql = ' SELECT tutorial_id, Tutorial_title, 
        Tutorial_author, submission_date from
    tutorials_tbl
    WHERE Tutorial_author= "Sanjay";
by Www.jb51.net

mysql_select_db (' tutorials ');
$retval = mysql_query ($sql, $conn);
if (! $retval)
{
 die (' could not get data: '. mysql_error ());
}
while ($row = Mysql_fetch_array ($retval, MYSQL_ASSOC))
{
  echo Tutorial ID: {$row [' tutorial_id ']} <br> ".
     " Title: {$row [' Tutorial_title ']} <br> ".
     " Author: {$row [' Tutorial_author ']} <br> ".
     " Submission Date: {$row [' submission_date ']} <br> '.
     ' --------------------------------<br> ";
} 
echo "fetched data successfully\n";
Mysql_close ($conn);
? >

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.