Combining 2 sets of data

[gin]

I have 2 sets of data I pulled from MySQL. Below is a small sample of the data.

$set1[0][item_id] = 'admin';
$set1[0][price] = 500;
$set1[1][item_id] = 'courier';
$set1[1][price] = 200;

$set2[0][item_id] = 'courier';
$set2[0][price] = 300;
$set2[1][item_id] = 'flight';
$set2[1][price] = 100;

 

I want to add the two sets of data together such that the prices of identical items are added together, like so:

$set3[0][item_id] = 'admin';
$set3[0][price] = 500;
$set3[1][item_id] = 'courier';
$set3[1][price] = 500;      // new price
$set3[2][item_id] = 'flight';
$set3[2][price] = 100;

 

Any kind of array merging or appending will not work due to the varying line numbers. Right now I'm resorting to comparing the two arrays with for loops, as below. Sadly my code only allows me to add the prices, but not insert new lines of data.

$set3 = $set1;
foreach ($set3 as $i => $idata) {
   foreach ($set2 as $jdata) {
      if ($idata['item_id'] == $jdata['item_id'])
         $set3[$i]['price'] += $jdata['price'];
   }
}

 

Please advise. Any help much appreciated!

[freelance84]

oops... that saved by mistake

[freelance84]

Could you somehow rearrange the arrays. How are they being produced?

 

 

 

 

[freelance84]

This might work:

$set = array()	
//get all the first set of data into the row: eg: $set['admin'] = '500';

//then get the second lot of data, on each fetch_row loop run:
$valueFoundFlagger = 1;
foreach($set as $key => $value)
	{
		if($key == $newIDFromMysql)
			{
				$set[$key] = $value + $newPriceFromMyDql
				$valueFoundFlagger = 2;
				break;		
			}
	}
if($valueFoundFlagger != 2)//then the new key from the second set of data was never in the first and a new entry into the $set array must be made
	{
		$set[$newIDFromMysql] = $newPriceFromMyDql
	}

[gin]

Thanks for the suggestions, freelance84, but rearranging the data in that manner doesn't quite work. While I'm only showing item_id & price, there are also 3 other fields, like so:

$set1[0][item_id] = 'admin';
$set1[0][price] = 500;
$set1[0][item_name] = 'Administrative charges';
$set1[0][section] = 'Administration';
$set1[0][sec_id] = 1;

 

I supposed I could pull out the id & price and sort it like you suggest, but then I'd just have to match it back to the rest of the data. Seems a bit circular?

 

As it is, there'll be more loops to get section subtotals and to format for output, though really, I'll probably try cram everything into one convoluted loop anyhow. One thing at a time

 

As for the original data arrays, they're being pulled from 3 different MySQL tables. As in data from 3 tables to get 1 set. And although I say I want to "combine 2 sets of data", in reality it's "2 or more". If it's possible to do any of this in the SQL code, it's beyond my can-barely-get-joins abilities.

[Adam]

Why don't you use a JOIN in the query to return the data from each table as one set?

[gin]

Why don't you use a JOIN in the query to return the data from each table as one set?

It's not 3 tables, each with a different set. I'm already JOINing the 3 to get just 1 set. Each set is a different row. Unless I'm misunderstanding you?

[PFMaBiSmAd]

You would use GROUP BY item_id term in your query to consolidate rows with the same item_it into groups and use SUM(price) to sum the price within each group.

 

If you show your existing query, someone can probably help.

[xyph]

Doing this in the query is by far your best bet.

 

The PHP to do it is ugly.

$set1[0]['item_id'] = 'admin';
$set1[0]['price'] = 500;
$set1[1]['item_id'] = 'courier';
$set1[1]['price'] = 200;

$set2[0]['item_id'] = 'courier';
$set2[0]['price'] = 300;
$set2[1]['item_id'] = 'flight';
$set2[1]['price'] = 100;

foreach( $set1 as &$a )
foreach( $set2 as $key => &$b )
	if( $a['item_id'] == $b['item_id']) {
		$a['price'] += $b['price'];
		unset( $set2[$key] );
	}
unset( $a, $b );

$merge = array_merge( $set1, $set2 );

print_r( $merge );

[gin]

Hey guys, as per your advice, I'm looking into changing the MySQL query. If you have the time, take a look at the topic here:

http://www.phpfreaks.com/forums/index.php?topic=337473.0