MySQL has a large collection of functions you can use to make your SQL queries more powerful. While some of these can be done in PHP code once you have extracted the results of a MySQL query, doing them inside MySQL is often much faster, and also allows you to use the calculated value as part of a WHERE clause. These functions are MIN(), MAX(), COUNT(), SUM(), AVG(), UNIX_TIMESTAMP(), NOW(), and CONCAT(). These can be split into two distinct groups: query modifiers and field modifiers.
These functions change the number of rows returned by your query, and therefore cannot usually be mixed with a normal query. If you find MySQL outputting the following error message, you know you have tried to mix these functions in where they should not be:
ERROR 1140: Mixing of GROUP columns (MIN(), MAX(), COUNT()...) with no GROUP columns is illegal if there is no GROUP BY clause.
MIN() and MAX() return the minimum and maximum values of a field you specify respectively. For example, to find the oldest person in usertable, you would use SELECT MAX(age) FROM usertable;. If the field chosen is a character field, MIN() and MAX() work alphabetically, so MIN() would return "Alex" before "Becky".
COUNT() returns the number of rows which match your query. For example, "SELECT COUNT(*) FROM usertable WHERE Age > 22;" would return the number of people in your table who are aged 23 or above.
SUM() adds up all the values in the attributes you select. Viz: SELECT SUM(Age) FROM usertable would return the ages of all the people in your table, added up.
AVG() adds up all the values in the attributes you select and divides that result by the number of values, giving the mean average. Viz: SELECT SUM(Avg) FROM usertable would return the average age of all members.
Going back to our golfscores example, we can use these functions to make better use of the GROUP BY clause. If you recall, MySQL was using the first value for the Score field for each MemberID rather than something helpful. Now that we've looked at MySQL functions, we can see how to get more useful values out of MySQL.
The most obvious example is, "what is the average score of this member?" To get that we need to use GROUP BY with the AVG() function, like this:
mysql> SELECT ID, MemberID, AVG(Score), DateEntered from golfscores GROUP BY MemberID;
+----+----------+------------+-------------+
| ID | MemberID | AVG(Score) | DateEntered |
+----+----------+------------+-------------+
| 1 | 1 | 71.2500 | 1089448558 |
| 4 | 13 | 78.5000 | 1089448576 |
| 3 | 43 | 69.0000 | 1089448569 |
+----+----------+------------+-------------+
Similarly, you can find the best score a player has returned (remember, in golf a lower score is better!):
mysql> SELECT ID, MemberID, MIN(Score), DateEntered from golfscores GROUP BY MemberID;
+----+----------+------------+-------------+
| ID | MemberID | MIN(Score) | DateEntered |
+----+----------+------------+-------------+
| 1 | 1 | 69 | 1089448558 |
| 4 | 13 | 77 | 1089448576 |
| 3 | 43 | 69 | 1089448569 |
+----+----------+------------+-------------+
Of course, many of these functions work fine without the GROUP BY clause. For example, to get the overall average score of all members, use this query:
mysql> SELECT AVG(Score) from golfscores;
+------------+
| AVG(Score) |
+------------+
| 73.0000 |
+------------+
These functions affect one field only, changing the value it returns with the rest of the row. These functions can be used freely in all SELECT queries.
UNIX_TIMESTAMP() takes a MySQL time as a parameter, and returns the corresponding Unix timestamp. As PHP uses Unix timestamps, this is a very helpful function, especially when combined with NOW().
NOW() returns the current MySQL time. You'll need to combine it with UNIX_TIMESTAMP() to get a helpful number. If we had a table which stored a last login time for each person, we could use NOW() and UNIX_TIMESTAMP() combined like this:
SELECT * FROM Users WHERE LastLogonTime > UNIX_TIMESTAMP(NOW()) - 86400;
That query would return all users who have logged on in the last 24 hours. 86400 is the number of seconds in a day, so it searches for people who's last logon time is greater than (more recent than) the current time - 86400.
Finally, CONCAT() can take any number of parameters, and it returns them combined. So, to combine first name and second name with a space in the middle, you would use this query:
SELECT CONCAT(FirstName, ' ', LastName) FROM usertable;
Want to learn PHP 7?
Hacking with PHP has been fully updated for PHP 7, and is now available as a downloadable PDF. Get over 1200 pages of hands-on PHP learning today!
If this was helpful, please take a moment to tell others about Hacking with PHP by tweeting about it!
Next chapter: Managing indexes >>
Previous chapter: Grouping rows together with GROUP BY
Jump to:
Home: Table of Contents
Copyright ©2015 Paul Hudson. Follow me: @twostraws.