So far you seem to have done really well, the loop through a loop is OK though others may have different ideas. Can I confirm what happens about dates? You seem to be listing every class on a Tuesday with start and end times so this Tuesday and next Tuesday would be shown - is this right?
Is the wp-prefix in your config file 'wp_' or 'yoga'? The wp_prefix merely sticks the wp-config setting in front of the table names, so I am not sure if wp->prefix.bp_class_schedule is actually wp_bp_class_schedule or yogabp_class_schedule, as the sql suggests, within the databse.
You should still be able to run, or copy, the original SQL, it might just need padding with the wp-query function. By the way you can use an alias on table names, so
yogabp_class_schedule couild be aliased to, for example, ycs by changing line 21 on http://wordpress.pastebin.com/76cRLKga to
then line 6. ".$wpdb->prefix."bp_class_schedule.class_id,
can become ycs.classid as you have aliased the full table name including the wp-prefix. This will compact the code to something like (not tested!):-
$results = $wpdb->get_results("SELECT ycs.class_id, ycs.group_id, ycs.title, ycs.
day, ycs.start_time, ycs.end_time, ycs.member_list, ycs.class_type, ycs.class_description, ycs.teacher, ycs.teacher_link, u.user_url, u.ID, bxd.value
FROM yogabp_class_schedule ycs, users u, bp_xprofile_data bxd
Same alias can be used with the joins statements.
This might give you a more compact sql statement.
It might be possible to do a SELECT within a SELECT - that is run a SELECT to get the day code then run a select on that result to get the information, but it is still two parses of the data per record.
Can you show me what your current output is and an example of what you want? If you want to send me data direct I will let you have my email.
If you have access to the DB could you let me have a copy of the table design? Some data inside would be useful, but *not* private or confidential information, just some test data.
I always find the last 10% of any problem is the hardest to solve, but hang on in there, it will sort out!