Exporting Order Details in Excel Format

[Guest]

Hello Everyone,

 

I would like to export the order details in excel format for printing the monthly report. I've wrote the code below but found some problems during editing.

 

$select = "SELECT oi.inv, oi.date_added, o.customers_name, o.payment_method, ot.value from orders_invoices oi LEFT JOIN orders o ON (oi.orders_id = o.orders_id) LEFT JOIN orders_total ot ON (ot.orders_id = oi.orders_id) WHERE ot.class = 'ot_subtotal'";

             

$export = mysql_query($select);

$fields = mysql_num_fields($export);

for ($i = 0; $i < $fields; $i++) {

    $header .= mysql_field_name($export, $i) . "\t";

}

while($row = mysql_fetch_row($export)) {

    $line = '';

    foreach($row as $value) {                                           

        if ((!isset($value)) OR ($value == "")) {

            $value = "\t";

        } else {

            $value = str_replace('"', '""', $value);

            $value = '"' . $value . '"' . "\t";

        }

        $line .= $value;

    }

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

}

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

if ($data == "") {

    $data = "\n(0) Records Found!\n";                       

}

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

header("Content-type: html/text; charset=iso-8859-1");

header("Content-Disposition: attachment; filename=extraction.xls");

header("Pragma: no-cache");

header("Expires: 0");

print "$header\n$data";

 

The problem of mine is the select statement. Those information in table orders and orders_invoices are okay but not the orders_total.

 

I want to export the file in the follwing format

 

INV | OID | Customers name | Payment Method | Subtotal | Shipping | Total |

 

I can export the first four rows but have no idea how I can extract the subtotal, shipping and total column since the are not the field name but only specific by the class.

 

I dont know how to combine the selection with my previous one. I think I need to combine the following thing.

 

value1 = ot.value where ot.class = ot_subtotal

value2 =ot.value where ot.class = ot_shipping

value3 = ot.value where ot.class = ot_total

 

I am really an idiot in PHP coding and hope someone can help.

 

Thank You

[Guest]

Try this:

$select = "SELECT oi.inv, oi.date_added, o.customers_name, o.payment_method, ot.value from orders_invoices oi LEFT JOIN orders o ON (oi.orders_id = o.orders_id) LEFT JOIN orders_total ot ON (ot.orders_id = oi.orders_id AND ot.class = 'ot_subtotal')";

[Guest]

I am sorry but still only 1 column of "value" comes out.

But anyway thank you very much for your help. You are really kind to help me try!

[Guest]

Well, that is the way the original query was structured...to only pull the subtotal. If you want the others you'll have to use a separate query.

[Guest]

Hello, I am really sorry as I really know nothing of mysql.

 

Do you mind tell me how to do it in separate query? It is rather complicated or I need to totally change the whole code? I really think I might nearly get what I want and dont really want to give up.

 

Any help would be appreciate!