...

View Full Version : Export MySQL to Excel modification



Kal
04-02-2007, 10:46 AM
Hi guys,

i am basically creating a tool which calculates the commission a sales person will get and display a break down off the calculations for each deal.

the tool allows you to create a report of all sales persons or individual sales persons.

i have found a script which exports mysql data to an excel spreadsheet, i have slightly made some modifications too. I have this all working perfectly.

each deal displayed goes on a new row and each deal has a total_commission.

however i need to modify the code so that i can get a grand total of total_commission for each sales person. i need to get this information on the same worksheet or on a new worksheet in the same workbook.

hope this makes sense.

thanks in advance



<?php

$sales_code=$_POST['sales_code'];
$prev_week_number=$_POST['prev_week_number'];

require('database.php');

if(isset($_POST['all_sales_codes']))

{
$result = mysql_query("select tc.sales_code, c.customer_id, c.deal_date, tc.customer_id, tc.standard_commission, tc.bonus, tc.petrol, tc.total_commission from orbmobi_orbcommission.customers c left join test_test.commission_transactions tc on c.customer_id=tc.customer_id where week_number='$prev_week_number' order by tc.sales_code, tc.customer_id ASC");
}

else

{
$result = mysql_query("select tc.sales_code, c.customer_id, c.deal_date, tc.customer_id, tc.standard_commission, tc.bonus, tc.petrol, tc.total_commission from orbmobi_orbcommission.customers c left join test_test.commission_transactions tc on c.customer_id=tc.customer_id where sales_code='$sales_code' and week_number='$prev_week_number' order by tc.customer_id ASC");
}

$count = mysql_num_fields($result);

for ($i = 0; $i < $count; $i++)

{
$header .= mysql_field_name($result, $i)."\t";
}

while($row = mysql_fetch_row($result))

{

$line = '';

foreach($row as $value)

{

if(!isset($value) || $value == "")

{
$value = "\t";
}

else

{
# important to escape any quotes to preserve them in the data.
$value = str_replace('"', '""', $value);
# needed to encapsulate data in quotes because some data might be multi line.
# the good news is that numbers remain numbers in Excel even though quoted.
$value = '"' . $value . '"' . "\t";
}

$line .= $value;

}

$data .= trim($line)."\n";

}

# this line is needed because returns embedded in the data have "\r"
# and this looks like a "box character" in Excel

$data = str_replace("\r", "", $data);

# Nice to let someone know that the search came up empty.
# Otherwise only the column name headers will be output to Excel.

if ($data == "")
{
$data = "\nno matching records found\n";
}

# This line will stream the file to the user rather than spray it across the screen
//header("Content-Type: application/vnd.ms-excel; name='excel'");

header("Content-type: application/octet-stream");

header("Content-Disposition: attachment; filename=sales_commission_report_$prev_week_number.xls");
header("Pragma: no-cache");
header("Expires: 0");

echo $header."\n".$data;

//print "done";

?>

Fumigator
04-02-2007, 03:50 PM
After the loop that builds the individual rows, add a query to get the sum(total_commission) and add it to your $data variable.

Kal
04-03-2007, 08:38 AM
do you mean above this line of code?


$data .= trim($line)."\n";

how do you add a variable to an existing varaible?

Nightfire
04-03-2007, 10:25 AM
how do you add a variable to an existing varaible?




$data .= trim($line)."\n".$anothervar.$andanother.$andanotherone;



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum