SQL export to XML. usting php script. (customer export)

Question

I have a problem whith the query result. When I run this, it query's it all in on batch. I need to make a new head from every customers.

I think the problem is from line 143 to 179.

if (!$_HTTP_POST_VARS["separator"])

$sep= str_replace('\t', "\011", $sep);
//$contents="customers_id".$sep."customers_lastname".$sep."customers_firstname".$sep."customers_email_address".$sep."customers_gender".$sep."customers_dob".$sep."entry_company".$sep."entry_street_address".$sep."entry_postcode".$sep."entry_city".$sep."entry_state".$sep."entry_suburb".$sep."countries_name".$sep."customers_telephone".$sep."customers_fax\n";
$customers_query_raw = "select c.customers_id,
                                  c.customers_lastname,
                                  c.customers_firstname,
                                  c.customers_email_address,
                                  c.customers_gender,
                                  c.customers_dob,
                                  c.customers_telephone,
                                  c.customers_fax,
                                  a.entry_company,
                                  a.entry_street_address,
                                  a.entry_postcode,
                                  a.entry_city,
                                  a.entry_state,
                                  a.entry_suburb,
                                  co.countries_name
                                   from " . TABLE_CUSTOMERS . " c left join " . TABLE_ADDRESS_BOOK . " a on c.customers_id = a.customers_id and c.customers_default_address_id = a.address_book_id
                                   left join " . TABLE_COUNTRIES . " co on co.countries_id = a.entry_country_id
                                   WHERE c.customers_id >= 4150 and c.customers_id <= 4200";
    $customers_query = tep_db_query($customers_query_raw) or die(mysql_error());
    while ($row = tep_db_fetch_array($customers_query)) {

                $customers_id.=$row['customers_id'];
                                $customers_lastname.=$row['customers_lastname'];                    
                                $customers_firstname.=$row['customers_firstname"'];
                                $customers_email_address.=$row['customers_email_address'];
                                $customers_gender.=$row['customers_gender'];
                                $customers_dob.=$row['customers_dob'];
                                $entry_company.=$row['entry_company'];
                                $entry_street_address.=$row['entry_street_address'];
                                $entry_postcode.=$row['entry_postcode'];
                                $entry_city.=$row['entry_city'];
                                $entry_state.=$row['entry_state'];
                                $entry_suburb.=$row['entry_suburb'];
                                $countries_name.=$row['countries_name'];
                                $customers_telephone.=$row['customers_telephone'];
                                $customers_fax.=$row['customers_fax'];                            
            }

    /*print('<?xml version="1.0" encoding="ISO-8859-1" ?>');*/
    header("Content-type: text/html; charset=ISO-8859-1");
    header("Content-disposition: attachment; filename=ordre_export_" . date("Ymd") . ".xml");
    header("Content-type: application/octet-stream; charset=iso-8859-1");


        echo '<?xml version="1.0" encoding="iso-8859-1"?>
        <!--  -->
        <Root xmlns:dt="urn:schemas-microsoft-com:datatypes">
        ';  

        echo "
        <head>
            <Kundenr>$customers_id</Kundenr>
            <Company>$entry_company</Company>
            <Kundenavn>$customers_lastname  </Kundenavn>
            <Adresse>$entry_street_address</Adresse>
            <Adresse2> </Adresse2>
            <Postnr>$entry_postcode</Postnr>
            <Poststed>$entry_city</Poststed>
            <Landkode> </Landkode>
            <Land>$countries_name</Land>
            <customers_telephone>$customers_telephone</customers_telephone>
            <fax>$customers_fax</fax>
            <mail>$customers_email_address</mail>
            <dob>$customers_dob</dob>
            <Fritekst></Fritekst>
            <Kommentarer></Kommentarer>
        </head>
        ";
                                $j++;

    echo "
    </Root>";
    die();
}
require(DIR_WS_INCLUDES . 'application_bottom.php');
?>

A sample of how it come out. My customer id is 4 digits long. So it should brake up after 4150 and start a new XML block.

<?xml version="1.0" encoding="iso-8859-1"?>
        <!--  -->
        <Root xmlns:dt="urn:schemas-microsoft-com:datatypes">

        <head>
            <Kundenr>41504151415241534154415541564157415841594160416141670417</Kundenr>

Please read the help-section of this site, to understand why this question can do with a bit of editing (like posting only the relevant pieces of code). — Elias Van Ootegem
– Elias Van Ootegem, Commented Jul 5, 2013 at 9:03
Did no one tell you yet that having huge scripts mixing html, xml, css, javascript and sql is bad practise? That's a maintenance and readability nightmare. Separate that code. And if you need to create XML why not use DOM? — Gordon
– Gordon, Commented Jul 5, 2013 at 9:07
I did have a look at the help section. i can under stand the relevant og posting only the code of issue. — CodeMan
– CodeMan, Commented Jul 6, 2013 at 9:18

Expedito · Accepted Answer · 2013-07-05 09:16:50Z

1

You are using the concatenate operator, so it's making a long string of all your query results:

$customers_id.=$row['customers_id'];

You might want to put that data into an array instead:

$customer['id'][$key] = $customers_id.=$row['customers_id'];

answered Jul 5, 2013 at 9:16

Expedito

7,8095 gold badges32 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

ElmoVanKielmo Over a year ago

Good one - but if it's only for XML output and not being used further, it's simpler to just output XML in the first loop as in my answer stackoverflow.com/questions/17485001/…

ElmoVanKielmo Over a year ago

die() after closing </Root> tag diminishes doubts ;) But +1 for your answer.

CodeMan Over a year ago

Thats correct this is only for xml out put. and not further use. But it nice to know for later.

ElmoVanKielmo · Accepted Answer · 2013-07-05 09:20:24Z

0

Your mistake was in concatenating results from all rows. Loop

while ($row = tep_db_fetch_array($customers_query))

should be used to output XML entries like

echo "
    <head>
        <Kundenr>$row[customers_id]</Kundenr>
        ...
    </head>
";

Please change your header also to match actual content type (this is not the cause of your problem but important):

header("Content-type: text/xml; charset=ISO-8859-1");

answered Jul 5, 2013 at 9:20

ElmoVanKielmo

11.4k2 gold badges35 silver badges51 bronze badges

1 Comment

ElmoVanKielmo Over a year ago

@CodeMan, please upvote then and/or accept as correct answer. Good luck!

Collectives™ on Stack Overflow

SQL export to XML. usting php script. (customer export)

2 Answers 2

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related