Quantified Results Based on A few Variables Help!

[imperium2335]

Hello,

 

I am trying to get a page that shows a list of parts, with their quantities, condition and type.

 

So parts that are identical are counted as 2, 3 etc if they are the same part, condition and type.

 

But if one or more parts of the same type has any one of those variables different to the rest, it/they need to come out on a new line with the correct quantity against them.

 

I have tried using arrays and I can get the quantities working against each part but don't know how to adapt it to achieve what I want.

 

Here is what I have so far:

// GET ALL THE PARTS FROM THIS **ONE SUPPLIER** AND FIND OUT THE QUANTITIES...

include("../dbconnectlocal.php") ;
$result = mysql_query("SELECT supplier, partNumber, newUsed, jobType, directIndirect, currency, vat, total FROM pourbaskets
				  WHERE enquiryRef = '$enqId' AND supplier = '$sSup' ORDER BY
				  partNumber DESC")or die(mysql_error()) ;
$quantity = 0 ;
$currentCount = 1 ;

$partQuantityArray = array() ; // The box for our parts against their quantities.
$partsBag = array() ; // For finding out how many unique parts there are later.
$partCount = mysql_num_rows($result) ; // How many parts in total.
while($row = mysql_fetch_assoc($result)) { // While we are going through each part found...

$part = $row['partNumber'] ;
$cond = $row['newUsed'] ;
$jtyp = $row['jobType'] ;
$curr = $row['currency'] ;
$vat  = $row['vat'] ;
$tota = $row['total'] ;

array_push($partsBag, $part) ;

if($partCount == 1) { // Just one part...
	$quantity = 1 ;
	array_push($partQuantityArray, $part, $quantity) ;
}

if(!$prevItem && $partCount != 1 { // Is the first item and not the only one...
	$prevItem = $part ; // Set it to be the previous...
	$quantity++ ; // Increase quantity (to 1 now)...
}
elseif($currentCount == $partCount && $partCount != 1) { // If it's the last one in the list and not the only one...
	if($prevItem != $part) {
		array_push($partQuantityArray, $prevItem, $quantity) ;
		$quantity = 1 ;
		array_push($partQuantityArray, $part, $quantity) ;
		//$prevItem = $part ;
	}
	elseif($prevItem == $part) { // The final part is the same as the last one...
		$quantity++ ;
		array_push($partQuantityArray, $part, $quantity) ;
	}
	else {
	array_push($partQuantityArray, $part, $quantity) ;
	}

}
elseif($prevItem == $part) { // If the current item is the same as the last item...
	$quantity++ ;	// Increase quantity...
}
elseif($partCount != 1) { // If it's a new item and not the only one, store the quantity of the last item, and start at 1...

array_push($partQuantityArray, $prevItem, $quantity) ;
	$quantity = 1 ;
	$prevItem = $part ;
}
$currentCount++ ;
}
print_r($partQuantityArray) ;exit();

//////////////////// END OF QUANTITY FINDER //////////////////////////////

I have attached what I would like it to come out as.

 

[attachment deleted by admin]

[PFMaBiSmAd]

You are making this about 18 times too hard (you have 18 times more code than you need.) Assuming you want to do this first by part number, then type, then condition, the following query should (untested) group and count the information the way you want -

 

  SELECT supplier, partNumber, newUsed, jobType, directIndirect, currency, vat, total, count(*) as QTY FROM pourbaskets
  WHERE enquiryRef = '$enqId' AND supplier = '$sSup' 
  GROUP BY partNumber, jobType, newUsed
  ORDER BY partNumber DESC

[imperium2335]

Thanks very much! Works a treat.

 

Don't suppose you could walk me through this as I'm average with mysql?

[PFMaBiSmAd]

count(*) as QTY - counts the number of rows in each group and makes that count available using the alias QTY

 

GROUP BY partNumber, jobType, newUsed - consolidates the rows having the same partNumber, jobType, and newUsed value into groups, first by partNumber, then by jobType within each part number, then by newUsed within each job type.

[imperium2335]

Thanks, I will play with it more later!