getting rid of repeated customer id's in mysql query

Question

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

Answer 1

If I'm joining together related tables for one row (which is definitely a best practice as opposed to nested queries and should be done the majority of the time when you can), I tend to do the formatting into neat tables through code.

(pseudocode provided as i don't remember PHP):

// query database
while !EOF {
  currentCustomerId = $database["CustomerId"]
  // do opening table row stuff; customer name, etc.
  while !EOF && currentCustomerId == $database["CustomerId"] {
     // do the relational columns from the join
     // move to next record
  }
  // do closing table row stuff
}

The outer loop iterates over each customer, and the inner loop iterates through the relational data for that customer.

Answer 2

Can you achieve that with SQL? Maybe, but I doubt it'd look nice.

Here is the easy PHP solution.

$mornings_by_customer = array();
foreach ($result as $r) {
    $mornings_by_customer[$r['customerid']][] = $r['morning'];
}

An example of your result data structure and an example of what you'd rather have - in PHP's array notation - would allow me to give you a more exact answer. This, however, should give you the general idea.

Answer 3

Based also on this near identical problem I'm trying to help you solve, I know you're uncomfortable with arrays. But you're going to have to learn them if you're going to be coding PHP, especially if you need to deal with multidimensional ones as you seem to want to do here.

$sql = "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 ";

$results = mysql_fetch_result($sql);
$customer_array = array()

// Load up an array with each customer id as the key and array full of mornings as the value
while($row = mysql_fetch_array($results)) {
 array_push($customer_array[$row['customerid']], $row['morning']);
}
// For each customer ID, get the array of mornings
foreach ($customer_array as $customerID=>$morningArray) {
 echo $customerID;
 // For each morning array item, echo it out
 forreach ($morningArray as $key=>$value) {
   echo " $value";
 }
}

Answer 4

I finally worked out what I was missing.

In order to address the element of the array I needed to use, I needed to use a double bracket ie $customer_array[0][lname], $customer_array[1][lname]. I realise this is probably obvious to most but it was completely eluding me. The key to my understanding this was
print_r(customer_array) which I'd seen a lot but never got working properly.

Then it was just a case of pulling out all the database rows with:

$customer_array =array();
while($row1=mysql_fetch_assoc($extract1)){
$customer_array[] = $row1; 
}

and then to loop through as I have a fixed number of records:

 for ($x=0;$x<=900;)
{ 
echo $customer_array[$x][fname];
echo$customer_array[$x][lname];
for($y=0;$y<=30;$y++)
{
echo $customer_array[$x][morning];
echo $customer_array[$x][afternoon];
        $x++;
    }
     }

Hope this helps someone else.