Using Functions

To wrap up this chapter, you'll learn about a number of functions that you can use in your MySQL queries. You have already seen twoNOW() and SHA()but those are just the tip of the iceberg. Most of the functions you'll see here are used with SELECT queries to format and alter the returned data, but you may use MySQL functions in any number of different queries.

To use any function, you need to modify your query so that you specify to which column or columns the function should be applied.

 SELECT FUNCTION(column) FROM tablename 

To specify multiple columns, you can write a query like either of these:

• SELECT *, FUNCTION(column) FROM SELECT column1, FUNCTION(column2), tablename

Aliases

An alias is merely a symbolic renaming of a table or column, giving you a new way to refer to something. Aliases are created using the term AS:

 SELECT registration_date AS reg FROM  users WHERE user_id=2 

Aliases are case-sensitive strings composed of numbers, letters, and the underscore but are normally kept to a very short length, allowing you to write queries more succinctly. As you'll see in the following examples, the aliases will be reflected in the headings of the returned results.

If you've defined an alias on a table or a column, the entire query must consistently use that same alias rather than the original name. For example,

 SELECT registration_date AS reg FROM  users ORDER BY reg 

While the function names themselves are case-insensitive, I will continue to write them in an all-capitalized format, to help distinguish them from table and column names (as I also capitalize SQL terms). One important rule with functions is that you cannot have spaces between the function name and the opening parenthesis in MySQL, although spaces within the parentheses are acceptable.

When using functions to format returned data, you'll normally want to make uses of aliases, a concept discussed in the sidebar.

Text functions

The first group of functions I will demonstrate are those meant for manipulating the various text and character columns. Most of the functions in this category are listed in Table 4.8.

Table 4.8. MySQL's text-based functions.

Text Functions
FUNCTION	USAGE	PURPOSE
CONCAT()	CONCAT(x, y, ...)	Creates a new string of the form xy.
LENGTH()	LENGTH(column)	Returns the length of the value stored in the column.
LEFT()	LEFT(column, x)	Returns the leftmost x characters from a column's value.
RIGHT()	RIGHT(column, x)	Returns the rightmost x characters from a column's value.
trIM()	trIM(column)	Trims excess spaces from the beginning and end of the stored value.
UPPER()	UPPER(column)	Capitalizes the entire stored string.
LOWER()	LOWER(column)	Turns the stored string into an all-lowercase format.
SUBSTRING()	SUBSTRING(column, start, length)	Returns length characters from column beginning with start (indexed from 0).

CONCAT(), perhaps the most useful of the text functions, deserves special attention. The CONCAT() function accomplishes concatenation, for which PHP uses the period (see Chapter 1, "Introduction to PHP"). The syntax for concatenation requires you to place, within parentheses, the various values you want assembled, in order and separated by commas:

 SELECT CONCAT(column1, column2) FROM  While you canand normally willapply CONCAT() to columns, you can also incorporate strings, entered within single quotation marks. To format a person's name as Surname, First from two columns, you would use
 
 SELECT CONCAT(last_name, ', ',  first_name) FROM users 

 
Because concatenation normally returns a new form of a column, it's an excellent time to use an alias.
 
 SELECT CONCAT(last_name, ', ',  first_name) AS Name FROM users 

 
To format text
 
1. 
Concatenate the names without using an alias (Figure 4.33).

 
 SELECT CONCAT(last_name, ', ',  first_name) FROM users 

 
Figure 4.33. This simple concatenation pulls out the users' full names. Make a note of the column's heading.
 

 Two points are demonstrated here. First, the users' first names, last names, and a space are concatenated together to make one string. Second, as the figure shows, if you don't use an alias, the returned data's column heading is very literal and often unwieldy.

2. 
Concatenate the names while using an alias (Figure 4.34).

 
 SELECT CONCAT(last_name, ', ',  first_name) AS Name FROM users 

 
Figure 4.34. By using an alias, the returned data is under the column heading of Name.
 

 To use an alias, just add AS aliasname after the item to be renamed. The alias will be the new title for the returned data.

3. 
Find the longest last name (Figure 4.35).

 
 SELECT LENGTH(last_name) AS L,  last_name FROM users ORDER BY L  DESC LIMIT 1; 

 
Figure 4.35. The LENGTH() function returns the length of a column's value.
 

 This query first determines the length of each last name and calls that L. Then the whole list is sorted by that value from highest to lowest, and the string length and first name of the first row is returned.

 
 Tips
 
A query like that in Step 3 (also Figure 4.35) may be useful for helping to fine-tune your column lengths once your database has some records in it.
You can use most of the MySQL functions while running queries other than SELECT. Most frequently, you might use a function to format or trim data during an INSERT.
Functions can be equally applied to both columns and manually entered strings. For example, the following is perfectly acceptable:
 SELECT UPPER('makemebig') 

CONCAT() has a corollary function called CONCAT_WS(), which stands for with separator. The syntax is CONCAT_WS(separator, column1, column2, ...). The separator will be inserted between each of the columns listed.

 
Numeric functions
 
Besides the standard math operators that MySQL uses (for addition, subtraction, multiplication, and division), there are about two dozen functions dedicated to formatting and performing calculations on numeric values. Table 4.9 lists the most common of these, some of which I will demonstrate shortly.
 
Table 4.9. Some of MySQL's numeric functions.
Numeric Functions
FUNCTION
USAGE
PURPOSE
ABS()
ABS(x)
Returns the absolute value of x.
CEILING()
CEILING(x)
Returns the next-highest integer based upon the value of x.
FLOOR()
FLOOR(x)
Returns the integer value of x.
FORMAT()
FORMAT(x, y)
Returns x formatted as a number with y decimal places and commas inserted every three spaces.
MOD()
MOD(x, y)
Returns the remainder of dividing x by y (either or both can be a column).
RAND()
RAND()
Returns a random number between 0 and 1.0.
ROUND()
ROUND(x, y)
Returns the number x rounded to y decimal places.
SIGN()
SIGN(x)
Returns a value indicating whether a number is negative (1), zero (0), or positive (+1).
SQRT()
SQRT(x)
Calculates the square root of x.

 
I want to specifically mention three of these functions: FORMAT(), ROUND(), and RAND(). The firstwhich is not technically number-specificturns any number into a more conventionally formatted layout. For example, if you stored the cost of a car as 20198.20, FORMAT(car_cost, 2) would turn that number into the more common 20,198.20.
 
ROUND() will take one value, presumably from a column, and round that to a specified number of decimal places. If no decimal places are indicated, it will round the number to the nearest integer. If more decimal places are indicated than exist in the original number, the remaining spaces are padded with zeros (to the right of the decimal point).
 
The RAND() function, as you might infer, is used for returning random numbers.
 
 SELECT RAND() 

 
A further benefit to the RAND() function is that it can be used with your queries to return the results in a random order.
 
 SELECT * FROM tablename ORDER BY RAND() 

 
To use numeric functions
 
1. 
Display a number, formatting the amount as dollars (Figure 4.36).

 
 SELECT CONCAT('$', FORMAT(5639.6,  2)) AS cost; 

 
Figure 4.36. Using an arbitrary example, this query shows how the FORMAT() function works.
 

 Using the FORMAT() function, as just described, with CONCAT(), you can turn any number into a currency format as you might display it in a Web page.

2. 
Retrieve a random email address from the table (Figures 4.37 and 4.38).

 
 SELECT email FROM users ORDER BY  RAND() LIMIT 1; 

 
Figure 4.37. The RAND() function can be used to return a random record from the database.
 

 
Figure 4.38. Subsequent executions of the same query (compare with Figure 4.37) return different random results.
 

 In MySQL, what happens with this query is this: All of the email addresses are selected; the order they are in is shuffled (ORDER BY RAND()); and then the first one is returned. Running this same query multiple times will produce different random results. Notice that you do not specify to which column the RAND() is applied.

 
 Tips
 
Along with the mathematical functions listed here, there are several trigonometric, exponential, and other types of numeric functions available.
The MOD() function is the same as using the percent sign:
 SELECT MOD(9,2) SELECT 9%2 

It returns the remainder of a division (1 in the above examples).

 
Date and time functions
 
The date and time column types in MySQL are particularly flexible and utilitarian. But because many database users are not familiar with all of the available date and time functions, these options are frequently underused.
 
Whether you want to make calculations based upon a date or return only the month name from a stored value, MySQL has a function for that purpose. Table 4.10 lists most of these.
 
Table 4.10. Most of MySQL's date- and time-related functions.
Date and Time Functions
FUNCTION
USAGE
PURPOSE
HOUR()
HOUR(column)
Returns just the hour value of a stored date.
MINUTE()
MINUTE(column)
Returns just the minute value of a stored date.
SECOND()
SECOND(column)
Returns just the second value of a stored date.
DAYNAME()
DAYNAME(column)
Returns the name of the day for a date value.
DAYOFMONTH()
DAYOFMONTH(column)
Returns just the numerical day value of a stored date.
MONTHNAME()
MONTHNAME(column)
Returns the name of the month in a date value.
MONTH()
MONTH(column)
Returns just the numerical month value of a stored date.
YEAR()
YEAR(column)
Returns just the year value of a stored date.
ADDDATE()
ADDDATE(column, INTERVAL x type)
Returns the value of x units added to column (see the sidebar on page 165).
SUBDATE()
SUBDATE(column, INTERVAL x type)
Returns the value of x units subtracted from column (see the sidebar on page 165).
CURDATE()
CURDATE()
Returns the current date.
CURTIME()
CURTIME()
Returns the current time.
NOW()
NOW()
Returns the current date and time.
UNIX_TIMESTAMP()
UNIX_TIMESTAMP(date)
Returns the number of seconds since the epoch until the current moment or until the date specified.

 
As you can tell, the many date and time functions range from those returning portions of a date column to those that return the current date or time. These are all best taught by example.
 
To use date and time functions
 
1. 
Display the first and last name for every user registered on the 21st of the month (Figure 4.39).

 
 SELECT first_name, last_name FROM  users WHERE DAY(registration_date)  =21; 

 
Figure 4.39. The date functions can be used to format columns or limit which records are returned (as in this example).
 

 The DAY() function returns the day of the month part of a date column. So seeing if the returned result is equal to some value is an easy way to restrict what records are selected.

2. 
Show the current date and time, according to MySQL (Figure 4.40).

 
 SELECT CURDATE(), CURTIME(); 

 
Figure 4.40. This query, not run on any particular table, returns the current date and time.
 

 To show what date and time MySQL currently thinks it is, you can select the CURDATE() and CURTIME() functions, which return these values. This is another example of a query that can be run without referring to a particular table name.

 
 Tips
 
The date and time returned by MySQL's date and time functions correspond to those on the server, not on the client accessing the database.
Be careful when using the ADDDATE() function to include all three Ds. It's all too easy to write ADDATE(), causing an error. If you continue to make this mistake, use DATE_ADD() instead.

 
 
ADDDATE() and SUBDATE()
 
The ADDDATE() and SUBDATE() functions, which are synonyms for DATE_ADD() and DATE_SUB(), perform calculations upon date values. The syntax for using them is
 
 ADDDATE(date, INTERVAL x type) 

 
In the example, date can be either an entered date or a value retrieved from a column. The x value differs, depending upon which type you specify. The available types are SECOND, MINUTE, HOUR, DAY, MONTH, and YEAR. There are even combinations of these: MINUTE_SECOND, HOUR_MINUTE, DAY_HOUR, and YEAR_MONTH.
 
To add two hours to a date, you would write
 
 ADDDATE(date, INTERVAL 2 HOUR) 

 
To add two weeks from December 31, 2005:
 
 ADDDATE('2005-12-31', INTERVAL 14 DAY) 

 
To subtract 15 months from a date:
 
 SUBDATE(date, INTERVAL '1-3' YEAR_MONTH) 

 
This last query tells MySQL that you want to subtract one year and three months from the value stored in the date column.

 
Formatting the date and time
 
There are two additional date and time functions that you might find yourself using more than all of the others combined: DATE_FORMAT() and TIME_FORMAT(). There is some overlap between the two and when you would use one or the other.
 
DATE_FORMAT() can be used to format both the date and time if a value contains both (e.g., YYYY-MM-DD HH:MM:SS). Comparatively, TIME_FORMAT() can format only the time value and must be used if only the time value is being stored (e.g. HH:MM:SS). The syntax is
 
 SELECT DATE_FORMAT(date_column,  tablename 

 
The formatting relies upon combinations of key codes and the percent sign to indicate what values you want returned. Table 4.11 lists the available date- and time-formatting parameters. You can use these in any combination, along with textual additions, such as punctuation, to return a date and time in a more presentable form.
 
Table 4.11. Use these parameters with the DATE_FORMAT() and TIME_FORMAT() functions.
DATE_FORMAT() and TIME_FORMAT() Parameters
TERM
USAGE
EXAMPLE
%e
Day of the month
1-31
%d
Day of the month, two digit
01-31
%D
Day with suffix
1st-31st
%W
Weekday name
Sunday-Saturday
%a
Abbreviated weekday name
Sun-Sat
%c
Month number
1-12
%m
Month number, two digit
01-12
%M
Month name
January-December
%b
Month name, abbreviated
Jan-Dec
%Y
Year
2002
%y
Year
02
%l
Hour
1-12 (lowercase L)
%h
Hour, two digit
01-12
%k
Hour, 24-hour clock
0-23
%H
Hour, 24-hour clock, two digit
00-23
%i
Minutes
00-59
%S
Seconds
00-59
%r
Time
8:17:02 PM
%T
Time, 24-hour clock
20:17:02
%p
AM or PM
AM or PM

 
Assuming that a column called the_date has the date and time of 2005-04-30 23:07:45 stored in it, common formatting tasks and results would be
 
Time (11:07:45 PM)
 TIME_FORMAT(the_date, '%r') 

Time without seconds (11:07 PM)
 TIME_FORMAT(the_date, '%l:%i %p') 

Date (April 30th, 2005)
 DATE_FORMAT(the_date, '%M %D, %Y') 

 
To format the date and time
 
1. 
Return the current date and time as Month DD, YYYY - HH:MM (Figure 4.41).

 
 SELECT DATE_FORMAT(NOW(),'%M %e,  %Y - %l:%i); 

 
Figure 4.41. The current date and time, formatted.
 

 Using the NOW() function, which returns the current date and time, I can practice my formatting to see what results are returned.

2. 
Display the current time, using 24-hour notation (Figure 4.42).

 
 SELECT TIME_FORMAT(CURTIME(),'%T'); 

 
Figure 4.42. The current time, in a 24-hour format.
 

3. 
Select every email address and date registered, ordered by date registered, formatting the date as Weekday (abbreviated) Month (abbreviated) Day Year (Figure 4.43).

 
 SELECT email, DATE_FORMAT  (registration_date, '%a %b %e %Y)  AS Date FROM users ORDER BY  registration_date DESC; 

 
Figure 4.43. The DATE_FORMAT() function is used to pre-format the registration date when selecting records from the users table.
 

 This is just one more example of how you can use these formatting functions to alter the output of a SQL query.

 
 Tips
 
In your Web applications, use SQL and functions to format any dates arising from the database.
The only way to access the date or time on the client (the user's machine) is to use JavaScript. It cannot be done with PHP or MySQL.