getting rid of repeated customer id's in mysql query

Posted by bsandrabr on Stack Overflow See other posts from Stack Overflow or by bsandrabr
Published on 2010-04-22T20:24:45Z Indexed on 2010/04/22 21:03 UTC
Read the original article Hit count: 185

Filed under:
|
|

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

© Stack Overflow or respective owner

Related posts about mysql-query

Related posts about php