home | O'Reilly's CD bookshelfs | FreeBSD | Linux | Cisco | Cisco Exam  


Web Database Applications with PHP \& MySQLWeb Database Applications with PHP \& MySQLSearch this book

3.9. Functions

Functions and operators can be used in SQL statements. This section lists these functions and operators and provides examples. A full list of functions with examples is available in Section 7.4 of the manual.html file distributed with MySQL.

3.9.1. Arithmetic and comparison operators

Table 3-2 shows examples of the basic arithmetic and comparison operators in SELECT statements. The basic arithmetic operators are *, +, /, and -, as well as the parentheses ( ) to control the order of evaluation of an expression.

Table 3-2. Using the arithmetic and comparison operators

Statement

Output

SELECT 8+3*2;
14
SELECT (8+3)*2;
22
SELECT 2=2;
1
SELECT 1!=2;
1
SELECT 2<=2;
1
SELECT 3<=2;
0

The comparison operators include =, !=, <, >, <=, and >=. Four examples are shown in Table 3-2. If an expression evaluates as true, the output is 1; if an expression evaluates as false, the output is 0. To test for equality, a single equals sign is used; this contrasts with PHP, where the double equals (==) is used for equality tests, and a single equals sign is used for assignment.

To test whether two items are equal, the != operator is provided. Less-than-or-equal-to is represented by <=, and greater-than-or-equal-to is represented by >=. Parentheses can explicitly express the evaluation order.

3.9.1.1. String-comparison operators and functions

Table 3-3 shows examples of the MySQL string-comparison operators and functions. Many of the MySQL string functions shown here are similar to PHP functions, which were introduced in Chapter 2.

Table 3-3. Using string comparison functions and operators

Statement

Output

SELECT 'Apple' LIKE 'A%';
1
SELECT 'Apple' LIKE 'App%';
1
SELECT 'Apple' LIKE 'A%l%';
1
SELECT concat('con','cat');
'concat'
SELECT length('Apple');
5
SELECT locate('pp','Apple');
2
SELECT substring('Apple',2,3);
'ppl'
SELECT ltrim('  Apple');
'Apple'
SELECT rtrim('Apple  ');
'Apple'
SELECT trim('  Apple  ');
'Apple'
SELECT space(3);
'   '
SELECT strcmp('a','a');
0
SELECT strcmp('a','b');
-1
SELECT strcmp('b','a');
1
SELECT lower('Apple');
'apple'
SELECT upper('Apple');
'APPLE'

The string functions work as follows:

While not detailed in Table 3-3, regular expressions can be used through the function regexp( ). For more on regular expressions in PHP, see Chapter 2.

3.9.1.2. Mathematical functions

We make little use of the mathematical functions provided by MySQL in this book. However, Table 3-4 shows selected MySQL mathematical functions and their output.

Table 3-4. Using the MySQL mathematical functions

Statement

Output

SELECT abs(-33);
33
SELECT abs(33);
33
SELECT mod(10,3);
1
SELECT 10 % 3;
1
SELECT floor(3.14159);
3
SELECT ceiling(3.14159);
4
SELECT round(3.14159);
3
SELECT log(100);
4.605170
SELECT log10(100);
2
SELECT pow(2,3);
8
SELECT sqrt(36);
6
SELECT sin(pi( ));
0.000000
SELECT cos(pi( ));
-1.000000
SELECT tan(pi( ));
-0.000000
SELECT rand( );
0.8536
SELECT truncate(3.14159,3);
3.141
SELECT format(12345.23,0);
12,345
SELECT format(12345.23, 1);
12,345.2

Several of the functions in Table 3-4 require some explanation:

  • The abs( ) operator returns the absolute value of a number; that is, it removes the negative sign from negative numbers.

  • The modulo operator—which has two identical variants, % and mod( )—divides the first number by the second number and outputs the remainder.

  • The floor( ) and ceiling( ) functions are complementary: floor( ) returns the largest integer not greater than the parameter; ceiling( ) returns the smallest integer not less than the parameter.

  • The round( ) function rounds to the nearest integer.

  • Both the natural logarithm, log( ), and base-10 logarithm, log10( ), are available.

  • The pow( ) function raises the first number to the power of the second.

  • sqrt( ) takes the square root of the parameter.

  • The trigonometry functions sin( ), cos( ), and tan( ) take values expressed in radians as parameters. The complementary arc sin, arc cos, and arc tan are available as asin( ), acos( ), and atan( ).

  • The rand( ) function returns a pseudorandom number in the range 0 to 1.

  • The truncate( ) function removes decimal places without rounding.

  • The format( ) function isn't really a mathematical function but is instead used for returning numbers in a predefined format. The first parameter is the number, and the second parameter is the number of decimal places to return. The first parameter is rounded so that, for example, 123.56 formatted to one decimal place is 123.6. This function is seldom used in web database applications, because formatting is usually performed in PHP scripts.



Library Navigation Links

Copyright © 2003 O'Reilly & Associates. All rights reserved.