Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    Kal
    Kal is offline
    Regular Coder
    Join Date
    Dec 2005
    Posts
    309
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Export MySQL to Excel modification

    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 Code:
    <?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";

    ?>

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    After the loop that builds the individual rows, add a query to get the sum(total_commission) and add it to your $data variable.

  • #3
    Kal
    Kal is offline
    Regular Coder
    Join Date
    Dec 2005
    Posts
    309
    Thanks
    0
    Thanked 0 Times in 0 Posts
    do you mean above this line of code?

    PHP Code:
    $data .= trim($line)."\n"
    how do you add a variable to an existing varaible?

  • #4
    Senior Coder Nightfire's Avatar
    Join Date
    Jun 2002
    Posts
    4,265
    Thanks
    6
    Thanked 48 Times in 48 Posts
    how do you add a variable to an existing varaible?
    PHP Code:
    $data .= trim($line)."\n".$anothervar.$andanother.$andanotherone


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •