To export orders with shipping address in magento

Put this script to your magento root and run as: www.youdomain.com/scriptfilename.php


<?
    //
    // Magento CSV Report Script -  Unprocessed Sales Orders
    //
   
    // DATABASE CONNECTION
   
   
    $hostname_MySQLCon="host of database";
    $database_MySQLCon = "database name";
    $username_MySQLCon = "database username";
    $password_MySQLCon = "database password";

    $MySQLCon = mysql_pconnect($hostname_MySQLCon, $username_MySQLCon, $password_MySQLCon);
    $MySQLDb = mysql_select_db($database_MySQLCon, $MySQLCon);
   
    // FUNCTION AREA
   
    function get_DB_Row($query){
        $result = @mysql_query($query);
        $numresults = @mysql_num_rows($result);
       
        for ($i = 0; $i < $numresults; $i++) 
        {
            $row = @mysql_fetch_array($result);
        }
       
        return $row;
    }
   
    // Using this temporary function until I find correct way to pull country names from the database according to two digit code
    // Put in your own until a database polled solution is uncovered
   
   
   
    // INITILISATION AREA
   
    $sitename = "mysite";                  // update this to customise your filename
    $datestring = date('j-m-y');
    $filename = $sitename."-".$datestring;
    $output = "";
   
    $fieldlist = array("orderid","firstname","lastname","address","city","region","country",
    "postcode","telephone","email","itemcode","itemname","quantity","shipping_instructions");
   
    $checklist = array("country", "quantity");
   
    // FIELDLIST INSTRUCTIONS 
    // The order of the CSV fields are set here as well as the field titles
    // The field titles must correspond to a number of SQL Variables being set with the AS operator below
    // If you change the default titles be sure to also update the SQL command accordingly
    // Please note that 'country' and 'quantity' values must be processed but should still be put in your prefered sequence
   
    $numfields = sizeof($fieldlist);
   
    // *********************   NOW START BUILDING THE CSV
   
    // Create the column headers
   
    for($k =0; $k < $numfields;  $k++) { 
        $output .= $fieldlist[$k];
        if ($k < ($numfields-1)) $output .= ", ";
    }
    $output .= "\n";
   
   

    $orderquery = "SELECT * FROM sales_order";      // Query to find unprocessed orders
    $orderresult = mysql_query($orderquery);
    $numorders = @mysql_num_rows($orderresult);
   
    for($i =0; $i < $numorders; $i++) {
        $order = @mysql_fetch_array($orderresult);    // Place each order into an array for proccessing
        $orderid = $order['entity_id'];               // Grab the orderid for use in the main SQL command
       
        // The following SQL command will find all the individual items associated with this order
       
        $itemquery =     "SELECT sales_flat_order_item.order_id AS orderid,
                        sales_flat_order_item.product_type,
                        sales_flat_order_item.quote_item_id,
                        sales_flat_order_item.parent_item_id AS parentid,
                        sales_flat_order_item.sku AS itemcode,
                        sales_flat_order_item.name AS itemname,
                        sales_flat_order_item.qty_ordered AS qty_ordered,
                        sales_flat_order_item.qty_shipped AS qty_shipped,
                        sales_flat_quote_address.email AS email,
                        sales_flat_quote_address.prefix AS title,
                        sales_flat_quote_address.firstname AS firstname,
                        sales_flat_quote_address.lastname AS lastname,
                        sales_flat_quote_address.street AS address,
                        sales_flat_quote_address.city AS city,
                        sales_flat_quote_address.region AS region,
                        sales_flat_quote_address.country_id AS country,
                        sales_flat_quote_address.postcode AS postcode,
                        sales_flat_quote_address.telephone AS telephone,
                        sales_flat_quote_address.shipping_description AS shipping_instructions
                        FROM sales_flat_order_item 
                        INNER JOIN sales_flat_quote_item
                        ON sales_flat_order_item.quote_item_id = sales_flat_quote_item.item_id
                        INNER JOIN sales_flat_quote_address
                        ON sales_flat_quote_item.quote_id = sales_flat_quote_address.quote_id
                        WHERE sales_flat_order_item.order_id = $orderid
                        AND sales_flat_quote_address.address_type = 'shipping' 
                        AND sales_flat_order_item.product_type <> 'configurable'
                        AND sales_flat_order_item.qty_shipped < sales_flat_order_item.qty_ordered";
                       
        $itemresult = mysql_query($itemquery);        // Run the query
        $numitems = @mysql_num_rows($itemresult);   // Check the number of items in the order
       
        for($j =0; $j < $numitems; $j++) {
            $item = @mysql_fetch_array($itemresult); // Place the item in an array for processing
           
            // Output the order item values in the same sequence set in the fieldlist to match headers
           
            for($m =0; $m < sizeof($fieldlist); $m++) { 
               
                $fieldvalue = $fieldlist[$m];
               
                if(in_array($fieldvalue, $checklist)) {                    // check if on special processing list
               
                    if($fieldvalue == "country") $output .= getCountryName($item[$fieldvalue]);
                   
                    if($fieldvalue == "quantity") {
                        $parentid = $item['parentid'];
                        if($parentid == 'NULL') {                          // simple product sold on its own (no parent)
                            $qty_ordered = $item["qty_ordered"];
                            $qty_shipped = $item["qty_shipped"];
                        } else {                                         // simple product was part of bundle or configurable group
                                                                        // If so, use the parent quanities to calculate
                            $parentitem = get_DB_row("SELECT * FROM sales_flat_order_item WHERE item_id = $parentid");
                            $qty_ordered = $parentitem["qty_ordered"];
                            $qty_shipped = $parentitem["qty_shipped"];
                        }
                        $output .= ($qty_ordered - $qty_shipped);
                    }
                   
                } else {
                    $output .= $item[$fieldvalue];
                }
               
                if ($m < ($numfields-1)) $output .= ", ";
            }
           
            $output .= "\n";
        }
       
       
    }
   
    // Send the CSV file to the browser for download
   
    header("Content-type: text/x-csv");
    header("Content-Disposition: attachment; filename=$filename.csv");
    echo $output;
    exit;
   
?>