Bit of a puzzle displaying array data

[c_shelswell]

I've been scratching my head on this for a while and i'm hoping its a can't see the wood for the trees question! 

 

I've got an array which for example contains 3 purchase entries for username 'fred' and then 2 entries for 'brian' I need to display a total line after each user and also only display their username once. I can't group the mysql output as that'll only give me one line.

 

Has anyone come across this before and have any advice?

 

Cheers

 

[Ken2k7]

Can you post the SQL or the code you have?

[c_shelswell]

certainly can.

 

This is the sql:

 

SELECT * FROM cart left join users on cart.crt_user_id=users.users_id WHERE crt_insert_date between '2009-05-01 00:00:00' and '2009-05-02 00:00:00'

 

I've just been using a foreach on the returned data going through a line at a time.  Which is my problem as I need all the lines but i need certain data grouped such as the username and the total line per user. Here's my php anyway:

 

<?php

if ($rtn)
		{
			foreach($rtn as $k => $v)
			{
				?>
					<tr>
						<td class='tableLine'><?php echo $v->crt_garage_name; ?></td>
						<td class='tableLine'><?php echo $v->users_username; ?></td>
						<td class='tableLine'><?php 

						if ($v->crt_insert_date != NULL)
						{
							echo date('d-m-Y', strtotime($v->crt_insert_date  )); 
						}

						?></td>
						<td class='tableLine'><?php echo $v->crt_id; ?></td>
							<td class='tableLine'><?php $grandTotalFund += $v->crt_fund_top_up; echo number_format($v->crt_fund_top_up,2); ?></td>
						<td class='tableLine'><?php $grandTotalAdmin += $v->crt_administrative_fee; echo number_format($v->crt_administrative_fee,2); ?></td>
						<td class='tableLine'><?php $grandTotalVat += $v->crt_vat_amount * $rtn[0]->crt_administrative_fee; echo number_format($v->crt_vat_amount * $rtn[0]->crt_administrative_fee,2); ?></td>
						<td class='tableLine'><?php $grandTotalInv += $v->crt_net_price; echo number_format($v->crt_net_price,2); ?></td>
						<td class='tableLine'><a href='./fund_day_book_view.php?crt_id=<?php echo $v->crt_id; ?>'>view</a></td>
					</tr>

				<?php
			}
?>

 

cheers

[Ken2k7]

Can you post the table structure for cart and users?

[Daniel0]

Sort of like this? Adjust column names. You can probably sort using MySQL as well.

 

function sortByCustomer(array $a, array $b)
{
if ($a['customer_id'] == $b['customer_id']) {
	return 0;
}
return ($a['customer_id'] < $b['customer_id']) ? -1 : 1;
}

$data = array(
array(
	'item_name' => 'Foo bar test',
	'price' => 48,
	'qty' => 2,
	'customer_name' => 'John Doe',
	'customer_id' => 5,
),
array(
	'item_name' => 'Foo bar test',
	'price' => 48,
	'qty' => 3,
	'customer_name' => 'Daniel Egeberg',
	'customer_id' => 2,
),
array(
	'item_name' => 'Hello world',
	'price' => 52,
	'qty' => 1,
	'customer_name' => 'Daniel Egeberg',
	'customer_id' => 2,
),
array(
	'item_name' => 'Test Item',
	'price' => 15,
	'qty' => 4,
	'customer_name' => 'John Doe',
	'customer_id' => 5,
),
);

$totals = array();

usort($data, 'sortByCustomer');

$currentName = $data[0]['customer_name'];
$newCustomer = true;

echo "Item name:\tPrice:\tQty:\tTotal:\n";

foreach ($data as $i => $purchase) {
if ($newCustomer) {
	echo "\n" . $purchase['customer_name'] . ":\n";
	$newCustomer = false;
}

$total = $purchase['qty'] * $purchase['price'];
printf("%s\t\$%.2f\t%d\t\$%.2f\n", $purchase['item_name'], $purchase['price'], $purchase['qty'], $total);
$totals[$purchase['customer_id']] = isset($totals[$purchase['customer_id']]) ? $totals[$purchase['customer_id']] + $total : $total;

if (!isset($data[$i+1]) || $data[$i+1]['customer_id'] != $purchase['customer_id']) {
	printf("Total: \$%.2f\n", $totals[$purchase['customer_id']]);
	$newCustomer = true;
}
}

printf("\n\nTotal (all customers): \$%.2f", array_sum($totals));

[c_shelswell]

Cheers Daniel0  I'll give that a stab and see if I can get it to work. I've put in my table structures too. Thanks!

 

 

CREATE TABLE IF NOT EXISTS `cart` (
  `crt_id` int(11) NOT NULL auto_increment,
  `crt_order_id` int(11) default NULL,
  `crt_user_id` int(11) default NULL,
  `crt_customer_type` varchar(55) default NULL,
  `crt_discount_amount` double default '0',
  `crt_discount_rate` double default '0',
  `crt_affiliate_commission` double default '0',
  `crt_net_price` double default NULL,
  `crt_price_ex_ipt` double default NULL,
  `crt_vehicle_type` varchar(255) default NULL,
  `crt_make` varchar(255) default NULL,
  `crt_model` varchar(255) default NULL,
  `crt_registration_number` varchar(55) default NULL,
  `crt_registered_date` datetime default NULL,
  `crt_engine_capacity` varchar(55) default NULL,
  `crt_fuel_type` varchar(55) default NULL,
  `crt_drive_type` varchar(55) default NULL,
  `crt_transmission` varchar(255) default NULL,
  `crt_previous_keepers` int(11) default NULL,
  `crt_import_marker` varchar(55) default NULL,
  `crt_age` varchar(255) default NULL,
  `crt_mileage` int(255) default NULL,
  `crt_warranty_type` varchar(255) default NULL,
  `crt_warranty_period` int(11) default NULL,
  `crt_cover_limit` int(11) default NULL,
  `crt_base_price` float default NULL,
  `crt_ipt_tax` double default '0',
  `crt_warranty_number` varchar(20) default NULL,
  `crt_warranty_start_date` datetime default NULL,
  `crt_warranty_end_date` datetime default NULL,
  `crt_status` varchar(50) default NULL,
  `crt_renewal_status` int(11) default '0',
  `crt_garage_name` varchar(255) default NULL,
  `crt_garage_address` varchar(255) default NULL,
  `crt_garage_postcode` varchar(55) default NULL,
  `crt_garage_phone` varchar(55) default NULL,
  `crt_garage_email` varchar(255) default NULL,
  `crt_owner_title` varchar(4) NOT NULL,
  `crt_owner_initial` varchar(255) default NULL,
  `crt_owner_surname` varchar(255) NOT NULL,
  `crt_owner_address` varchar(255) default NULL,
  `crt_owner_post_code` varchar(255) default NULL,
  `crt_owner_delivery_address` varchar(255) default NULL,
  `crt_payment_date` datetime default NULL,
  `crt_commision_status` varchar(50) default NULL,
  `crt_payment_type` varchar(50) default NULL,
  `crt_owner_phone` varchar(55) default NULL,
  `crt_owner_email` varchar(55) default NULL,
  `crt_update_date` datetime default NULL,
  `crt_insert_date` datetime default NULL,
  `crt_voucher` varchar(10) default NULL,
  `crt_vehicle_price` int(11) default '0',
  `crt_book_sent` datetime default NULL,
  `crt_voucher_rate` double default '0',
  `crt_payment_requested` int(11) default NULL,
  `crt_profit_price` double default '0',
  `crt_vin_number` varchar(55) default NULL,
  `crt_manufactured_date` datetime default NULL,
  `crt_email_sent` datetime default NULL,
  `crt_sms_sent` datetime default NULL,
  `crt_quote_number` varchar(255) default NULL,
  `crt_telesales_email_content` text,
  `crt_allocated_id` int(55) default '0',
  `crt_administrative_fee` float default '0',
  `crt_vat_amount` float default '0',
  `crt_last_action_date` datetime default NULL,
  `crt_mail_status` int(4) default '0',
  `crt_fund_top_up` float default '0',
  `crt_modified_date` datetime default NULL,
  `crt_representative_email` varchar(255) default NULL,
  `crt_mode_of_sale` varchar(255) default NULL,
  PRIMARY KEY  (`crt_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

 

and users:

CREATE TABLE IF NOT EXISTS `users` (
  `users_id` int(11) NOT NULL auto_increment,
  `users_refered_by` int(11) default '0',
  `users_username` varchar(50) default NULL,
  `users_password` varchar(50) default NULL,
  `users_admin_password` varchar(50) default NULL,
  `users_customer_type` varchar(50) default NULL,
  `users_business_name` varchar(50) default NULL,
  `users_business_type` varchar(50) default NULL,
  `users_website` varchar(255) default NULL,
  `users_title` varchar(4) NOT NULL,
  `users_contact_name` varchar(100) default NULL,
  `users_address_1` varchar(100) default NULL,
  `users_address_2` varchar(100) default NULL,
  `users_town` varchar(100) default NULL,
  `users_county` varchar(100) default NULL,
  `users_postcode` varchar(12) default NULL,
  `users_email` varchar(100) default NULL,
  `users_phone` varchar(20) default NULL,
  `users_fax` varchar(20) default NULL,
  `users_mobile_number` varchar(20) default NULL,
  `users_status` varchar(20) default NULL,
  `users_unique_id` varchar(50) default NULL,
  `users_commision_payed_to` varchar(20) default NULL,
  `users_cheque_name` varchar(100) default NULL,
  `users_paypal_account` varchar(255) default NULL,
  `users_discount_percentage` double default NULL,
  `users_affiliate_commission` double default '0',
  `users_registered_date` datetime default NULL,
  `users_capture_details` int(11) default NULL,
  `users_cookie_length` varchar(11) default NULL,
  `users_affiliateplus_details` varchar(55) default NULL,
  `users_affiliateplus_units` double default NULL,
  `users_affiliateplus_percentage` double default NULL,
  `users_contract_start_date` datetime default NULL,
  `users_superaffiliate` varchar(11) default NULL,
  `users_cover_limit` int(11) default '0',
  `users_cover_period` int(11) default NULL,
  `users_administrative_fee` float default '0',
  `users_fund_amount` float default '0',
  `users_fund_mail_status` int(4) default '0',
  `users_representative_email` varchar(255) default NULL,
  `users_delete_status` int(4) default '0',
  `users_userlevel` tinyint(1) NOT NULL,
  `users_admin_userlevel` tinyint(1) NOT NULL,
  PRIMARY KEY  (`users_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=344 ;