You have invalid SQL code, which happens to be accepted by MySQL for no good reason. The SQL standard states something along the lines of
When a group by clause is present, every selected field must either be part of the group by clause or be part of an aggregate function.
Example of aggregate functions are sum() and count().
Let's consider some simple example data
table: horse
id name age
1 bob 6
2 joe 6
If we execute the invalid SQL query (which should not be allowed to execute, but for some reason it does with MySQL)
SELECT name, age
FROM horse
GROUP BY age
What would you expect to show up in the result?
-- possibility 1
1, bob, 6
-- poss. 2
1, joe, 6
-- poss 3
2, bob, 6
-- poss 4
2, joe, 6
The query would be correct, and sensical, if every non-grouped by field is part of an aggregate function
SELECT count(*) AS horse_count_by_age, age
FROM horse
GROUP BY age
-- retrieves: 2, 6
SELECT GROUP CONCAT(name ORDER BY name DESC SEPARATOR ', '), age
FROM horse
GROUP BY age
-- retrieves: 'joe, bob', 6
Since you are grouping by ID, which I assume is unqiue, this problem will not arise in this particular case, but it is invalid SQL nonetheless, and besides, grouping on unique data is pointless.
Your use of UNION in the nested query means that you retrieve all the data in the table and (possibly) add another bunch of rows form the same table once again.
First you select data from items meeting certain criteria such as Type='work', thus retrieving a subset of the data in items
Then you select data from items without a where clause, thus selecting the entire items set.
And finally you create a union of these two sets, thus creating a set containing redundant rows if any row meet the criteria in the query to the left of UNION.
Then, the outer query uses this newly created "redundant-set" to select all items for which type = horse. In other words, your query could be rewritten as
SELECT some, fields
FROM items
WHERE type='horse'
ORDER BY some, fields
Glancing at your php code, which is way too hard to read due to switching in and out of php parsing mode and lacking indentation, you seem to be doing some seriously messed up things.
1. You have a do-while loop, which means $row_work is undefined during the first iteration and should provide you with an error message each time it's used. Switch to a while loop.
- You are adding classifications to $resultwk on each iteration, and after each new element is added, you sort the array. Why? You don't seem to need this data at all.
While sorting can be achieved in O(log n), you are actually at best sorting in
for(k:= 1..n) sum(k log(k))
- On each iteration retrieving the database row you are also looping over everything so far found in $resultwk. Why?
If I understand your intentions correctly, you should stick to the following structure
$first = true;
$previous = null;
while ($row = mysql_fetch_assoc($result))
{
if (!$first && $previous != $row['classification'])
{
# end previous block of data, i.e. some kind of end tag
}
if ($previous != $row['classification'])
{
# start new block of data, i.e. a header and some kind of start tag
}
# echo contents of the row
}
