I need to pick your brains a little if I may. What I thought was a simple matter is proving a bit difficult for me.
Let me explain what the site is about and what I'm trying to do.
It's a teaching site (for a client) that sells online courses. The owner writes courses but there are also other course writers (teachers). The courses are for sale and I've used eCart 5 to construct this.
The "students" are allowed to buy more than one course at once (sometimes they are buyiig as many as 12) and each course has up to three teachers. The Teachers are just listed in the "users" table under the "UserGroups" field. This is held as an integer with a relationship to the UserGroups table.
We only know for sure when a course is sold when the "confirm payment" button is pressed on the "pp_confirm.php" page. At the moment that triggers an email (via UE) to a static email address (the owner of the site). That email lists ALL the courses sold.
What I'm trying to do is also either send that email or a new email to the teacher(s) of a course.
Supposing we have three teachers and three courses. On one PayPal payment transaction "Student A" buys three courses.
Course 1's teacher is Fred
Course 2's teacher is Fred and Ginger
Course 3's teacher is George
The course table has three fields "courseTeacher1", "courseTeacher2" and "courseTeacher3" These are populated from a list drawn from the users table filtered buy UserGroup (either 1-Administrator or 2-Teacher)..
So an email to is sent to Fred saying that his course 1 and course 2 has been sold to Student A
An email is sent to Ginger saying that her course 2 has been sold to Student A
And finally an email is sent to George saying that his course 3 has been sold to Student A (what the teachers then do is compile a monthly invoice based on this info and send to site owner for a percentage of the course fee).
On another occasion it might be only one course sold with one teacher or 12 courses sold with six teachers spead amongst them etc.
With your help (see the original query I made at the start of this thread) I can get the course ID('s) from the database but when I try to match the teachers to the courses and get an email to send with the info I'm lost.
Can you point me in the right direction on what is to me a very complicated issue. Perhaps I'm just going about it the wrong way.