MySQL database search - The SQL function SELECT

The SQL function SELECT enables you to search for certain database entries through the tables of a MySQL database. Its SQL syntax is as follows:

SELECT * FROM table_name WHERE conditions_over_table_fields

where table_name is the name of the table you wish to search in, not put between quotes.
where conditions_over_table_fields denotes a condition to be met by the entries which will be selected.

The example below will help you understand better; it performs a search in the database your_database which was defined in our SQL tutorial about MySQL database entries.
All the entries belonging to the table your_table and whose ClientID is 1 will be returned (that is to say, exactly the first entry of this table will be returned).

Learn the PHP & MySQL code:

<?php
mysql_query('SELECT * FROM your_table WHERE ClientID = 1',$connection); ?>

Remarks:

Text values must be put between quotes, but numeric values must not.
Several operators can be used in order to define conditions_over_table_fields; among them are the operators =, !=, >, <, >=, <=, BETWEEN, IN, LIKE. The operators BETWEEN, IN and LIKE will be explained below.
The asterisk * is there to specify that you want to retrieve all the fields associated with a result entry; it might happen that you are interested in only certain fields, in which case you can specify which fields are to to be returned instead of using an asterisk:

<?php
$result = mysql_query('SELECT Client_Address FROM your_table WHERE ClientID = 2',$connection); ?>

More generally, the SQL query can take the form:

SELECT (field_name1,...,field_namen) FROM your_table WHERE ClientID = 2

The result will then be returned as a table whose columns will be these fields that were selected during the search and that satisfy the search criteria. In order to pass on this SQL object to your PHP script, you must use the mysql_fetch_row PHP function (by calling mysql_fetch_row($result)), which returns a PHP array containing the first row of the $result table. Calling mysql_fetch_row a second time will fetch the second row of the $result table, etc ...

MySQL database search - The BETWEEN SQL operator

The BETWEEN SQL operator looks for values contained within a certain range; the values tested can be numbers, text or dates. When you want to select those values which are outside a certain range, you can use the SQL NOT BETWEEN operator instead. The SQL syntax is as follows:

SELECT * FROM table_name WHERE field_name BETWEEN value_inf_field AND value_sup_field

where value_inf_field and value_sup_field are non quoted numbers, quoted strings or dates defining the desired search range.

MySQL database search - The IN SQL operator

The IN SQL operator can be used when you know the set of the exact values to search for. The SQL syntax is as follows:

SELECT * FROM table_name WHERE field_name IN (value1, value2, ..., valuen)

where the valuei's are the values that must be searched for, passed as strings (i.e. put between quotes), dates or numbers (in which case they remain non quoted).

MySQL database search - The LIKE SQL operator

The LIKE SQL operator is here to help you look for entries whose fields contain specific patterns. The SQL syntax of this command is as follows:

SELECT * FROM table_name WHERE field_name LIKE pattern

where pattern is the pattern that you are looking for within your field; for instance, it can be of the form '%tio%', in which case all the fields field_name containing the sequence of letters 'tio' will be selected. If however you type '%tio' (respectively 'tio%'), then you will search for all the fields which begin (respectively end) with the sequence of characters 'tio' with possibly some other characters after (respectively before).

Throughout this SQL tutorial we have seen how to perform a SQL search on a MySQL database using the SELECT ... WHERE SQL operator.
We also introduced the SQL operators BETWEEN, IN and LIKE which allow you to search for entries whose fields hold certain characteristics.

Next tutorial: CSS tables (CSS box structure)
Previous tutorial: MySQL database entries

Back to computer forums