Most types of data have repeating data that defines some element of it. For example, consider a table that stores the round score for members in a golf club - each time they complete a round (play all 18 holes) their score gets entered into the table, along with their member ID, date of play, etc. The scores are of course individual to themselves, but the member IDs will recur.
Now you can, as you know, limit output of those results by member by using the WHERE clause, but what if you want all the scores printed out, but with the member ID bringing them together?
Here's our table created in MySQL, outputted using "SELECT * FROM golfscores;":
+----+----------+-------+-------------+
| ID | MemberID | Score | DateEntered |
+----+----------+-------+-------------+
| 1 | 1 | 70 | 1089448558 |
| 2 | 1 | 74 | 1089448562 |
| 3 | 43 | 69 | 1089448569 |
| 4 | 13 | 77 | 1089448576 |
| 5 | 1 | 72 | 1089448584 |
| 6 | 13 | 80 | 1089448590 |
| 7 | 1 | 69 | 1089448599 |
+----+----------+-------+-------------+
Now, here's the same table again, this time extracted using the GROUP BY clause with "SELECT * FROM golfscores GROUP BY MemberID;":
+----+----------+-------+-------------+
| ID | MemberID | Score | DateEntered |
+----+----------+-------+-------------+
| 1 | 1 | 70 | 1089448558 |
| 4 | 13 | 77 | 1089448576 |
| 3 | 43 | 69 | 1089448569 |
+----+----------+-------+-------------+
As you can see, each MemberID gets printed out just once, and the Score and DateEntered fields that are printed are the values from the first row with each MemberID in. So, although MemberID 1 has scores of 74, 72, and 69, it's their first score of 70 that's printed - not very helpful!
However, in the creation of that query, MySQL does indeed read all the rows for each MemberID and could do something else with these values. By default, though, it just prints the first value - to do anything more we need to get into the realm of MySQL functions!
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: MySQL functions >>
Previous chapter: Multiple WHERE conditions
Jump to:
Home: Table of Contents
Copyright ©2015 Paul Hudson. Follow me: @twostraws.