Grouping results issue

[cheeseus]

Hello,

I am trying to resolve the following issue:

 

I get my results from a MySQL DB as follows:

			$query = mysql_query("SELECT clientname,units,unit, SUM(units) FROM ".PROJECT_TABLE." GROUP BY clientname,unit ");
			$row = array();
			while($row = mysql_fetch_array($query)) {
				$rows[] = array(
					'clientname'=> $row['clientname'],
					'unit'		=> $row['unit'],
					'units'		=> $row['SUM(units)'],
				);
			}

 

This works fine when for each client if I have only one type of unit for them, but if I have two or more different types of unit, the grouping leaves more to be desired. I print my results as follows:

$units_total = calculate("units");
foreach($units_total as $units_row) {
	$clientname	= $units_row['clientname'];
	$unit 		= $units_row['unit'];
	$units 		= $units_row['units'];
	$units_all 	+= $units;
	$units_list .= "<li>".$clientname." - ".$units." ".$unit."</li>";
}

And the above prints the same client twice if the unit type is different, e.g.

Client 1 - 4707 words

Client 2 - 590 words

Client 3 - 1.75 hours

Client 3 - 3571 words

Client 4 - 5.8 pages

Client 4 - 43726 words

 

What I'd like to achieve is get the client printed only once but have the different type of units listed after its name, e.g.

Client 1 - 4707 words

Client 2 - 590 words

Client 3 - 1.75 hours + 3571 words

Client 4 - 5.8 pages + 43726 words

 

However, I have little idea where to aim my efforts. Hope someone can help me.

[PFMaBiSmAd]

Just detect a change in the client name and do any special processing -

 

$last_client = ""; // initialize to a value that will never appear in the data
foreach($units_total as $units_row){
    $clientname   = $units_row['clientname'];
    if($last_client != $clientname){
        // do any special processing here when the client name changes
        // such as starting a new content section or initializing other variables
        // remember the new client name
        $last_client = $clientname;
    }
    // other processing in the loop ...
}

[jcbones]

Another take.

foreach($units_total as $units_row) {
      $clientname   = $units_row['clientname'];
      $unit       = $units_row['unit'];
      $units       = $units_row['units'];
      $units_all    += $units;
      //$units_list .= "<li>".$clientname." - ".$units." ".$unit."</li>";
     //Set the information to an array.
  $clients[$clientname][$unit] += $units;
   }
   
if(is_array($clients)) {
foreach($clients as $name => $v) {
                //Rip out the name of the client.
	$units_list .= '<li>' . $name . ' - ';
               //set an integer so we know how many tier2
              //values we have read.
	$i = 0;
	foreach($v as $unit => $units) {
                        //if this is the more than the first tier2 values, use a '+' between the units.
		$units_list .= (++$i > 1) ? ' + ' . $units . ' ' . $unit : $units . ' ' . $unit;
	}
                //close the line out.
	$units_list .= '</li>';
}
}

[cheeseus]

Well, obviously I'm not getting it. Must be something I'm missing.

I tried the first suggestion and wrote this:

			$query = mysql_query("SELECT clientname,units,unit, SUM(units) FROM ".PROJECT_TABLE." GROUP BY clientname,unit ");
			$rows = array();
			while($row = mysql_fetch_array($query)) {

				$last_client = "";
				$units_total = $row['SUM(units)'];
				foreach($units_total as $units_row) {
					$clientname = $units_row['clientname'];
					if($last_client != $clientname) {

						$rows[] = array(
							'clientname'=> $row['clientname'],
							'unit'		=> $row['unit'],
							'units'		=> $row['SUM(units)'],
						);
					}
				}
			}

 

But I only get "Invalid argument supplied for foreach()".

 

I tried copy-pasting the second suggestion (as it's more than I can grasp at the moment) but nothing changed.

[cheeseus]

Sorry, it's getting too late and my mind's not working.

 

The second solution worked "out of the box"

 

THANK YOU.

 

Will try to comprehend the way it works.