I originally started by selecting customers from a group of customers and then for each customer querying the records for the past few days and presenting them in a table row.
All working fine but I think I might have got too ambitious as I tried to pull in all the records at once having heard that mutiple queries are a big no no.
here is the mysqlquery i came up with to pull in all the records at once
SELECT morning, afternoon, date, date2, fname, lname, customers.customerid
FROM customers
LEFT OUTER JOIN attend ON ( customers.customerid = attend.customerid )
RIGHT OUTER JOIN noattend ON ( noattend.date2 = attend.date )
WHERE noattend.date2
BETWEEN '$date2'
AND '$date3'
AND DayOfWeek( date2 ) %7 >1
AND group ={$_GET['group']}
ORDER BY lname ASC , fname ASC , date2 DESC
tables are customer-customerid,fname,lname
attend-customerid,morning,afternoon,date
noattend-date2 (a table of all the days to fill in the blanks)
Now the problem I have is how to start a new row in the table when the customer id changes
My query above pulls in
customer 1 morning 2
customer 1 morning 1
customer 2 morning 2
customer 2 morning 1
whereas I'm trying to get
customer1 morning2 morning1
customer2 morning2 morning1
I dont know whether this is possible in the sql or more likely in the php
