I am not sure this is possible with just a query.. (maybe needs some PHP parsing? after the data is returned?)
I have a MySQL table set up.
Columns are as follows:
id
topic
title
vimeo_id
video_length
speaker_first
speaker_last
speaker_designation
description
with records like:
1 topic1 title-asda 1719887 speakerFirst-1 speakerLast-1 desc.xxx
2 topic2 title-asdf 1719554 speakerFirst-2 speakerLast-2 desc.xxx
3 topic1 title-ghty 1719445 speakerFirst-3 speakerLast-3 desc.xxx
4 topic3 title-bnh6 1719676 speakerFirst-4 speakerLast-4 desc.xxx
5 topic1 title-gffd 1711121 speakerFirst-5 speakerLast-5 desc.xxx
6 topic4 title-fgdf 1896621 speakerFirst-6 speakerLast-6 desc.xxx
7 topic2 title-tyuu 1005674 speakerFirst-7 speakerLast-7 desc.xxx
I am looking to get an output (if possible) like so:
(i.e: unique TOPIC (doesn't need to be listed each time).. and the sub-records ordered by speaker last)
If I need to parse this on the php side of things.. (i.e: checking if currentTopic == prevTopic) before outputting it to the screen.. I can do so.. just not sure how to query things to be grouped by topic,...then by speaker_last)
I have tried this:
SELECT * FROM (
SELECT topic, speaker_first, speaker_last, title
FROM vimeo_videos
ORDER BY speaker_last DESC
) AS tmp_table GROUP BY topic
but it seems to only grab '1' entry for each topic.. not ALL entries under each topic.
this seems to get me the whole list:
SELECT * FROM vimeo_videos order by topic, speaker_last;
which I guess I could just loop through use PHP and check for a 'new topic to create my 'groupings'?
(just wanted to make sure there isn't a better/more efficient approach)
Method 1:
What you can do is first do a :
SELECT topic FROM mytable GROUP BY topic
Then you'll get all the different topics, and for each you can do :
SELECT * FROM mytable WHERE topic = :mytopic
Method 2:
Sort that in php using the usort function with a custom callback.
As Nathan said, you can use GROUP BY to get just the unique topics, and then make another query that goes through each topic.
Here's an SQL Fiddle example of GROUP BY: http://sqlfiddle.com/#!9/193cd2/1
If you want to reduce the amount of queries, you should do what you said and check for the topic in your foreach statement and ORDER BY topic