[nmcentire]

I'm trying to pull data from a database, and export it to a CSV file. However, just about ALL of the columns are being duplicated within the array. Yes, this is a mod to Zen Cart... but this is done from scratch. The result of this is a CSV file that looks similar to this:

orders_id, orders_products_id, products_id, products_name, products_model, products_price, products_tax
2 2 2 2 134 134 "Item Name"


And maybe it's not duplicating stuff - it's just getting everything screwed up somewhere between "Iterate through and get every column's name" and "Post this record into the CSV". But I don't see where I'm doing any actions to the result that would result in the data being moved around. Any advice would be greatly appreciated.

<?php

// SQL statement to select all the necessary information, this is the stuff that nightmares are made of...
$sql = "SELECT zen_orders_products.orders_id, zen_orders_products.orders_products_id, zen_orders_products.products_id, zen_orders_products.products_name,
zen_orders_products.products_model, zen_orders_products.products_price, zen_orders_products.products_tax,
zen_orders_products.products_quantity, zen_orders_products.final_price,
zen_orders_products.onetime_charges,
zen_orders_products.products_priced_by_attribute, zen_orders_products.product_is_free, zen_orders_products.products_discount_type,
zen_orders_products.products_discount_type_from, zen_orders.orders_id, zen_orders.customers_id, zen_orders.customers_name, zen_orders.customers_company,
zen_orders.customers_street_address, zen_orders.customers_suburb, zen_orders.customers_city,
zen_orders.customers_postcode, zen_orders.customers_state, zen_orders.customers_country,
zen_orders.customers_telephone, zen_orders.customers_email_address, zen_orders.customers_address_format_id,
zen_orders.delivery_name, zen_orders.delivery_company, zen_orders.delivery_street_address, zen_orders.delivery_suburb,
zen_orders.delivery_city, zen_orders.delivery_postcode, zen_orders.delivery_state, zen_orders.delivery_country,
zen_orders.delivery_address_format_id, zen_orders.billing_name, zen_orders.billing_company,
zen_orders.billing_street_address, zen_orders.billing_suburb, zen_orders.billing_city, zen_orders.billing_postcode,
zen_orders.billing_state, zen_orders.billing_country, zen_orders.billing_address_format_id,
zen_orders.payment_method, zen_orders.payment_module_code, zen_orders.shipping_method, zen_orders.shipping_module_code,
zen_orders.coupon_code, zen_orders.cc_type, zen_orders.cc_owner, zen_orders.cc_number, zen_orders.cc_expires, zen_orders.currency, zen_orders.currency_value,
zen_orders.date_purchased, zen_orders.orders_status, zen_orders.last_modified, zen_orders.order_total, zen_orders.order_tax, zen_orders.ip_address, zen_customers.storeNumber
FROM zen_orders_products LEFT JOIN zen_orders
ON zen_orders_products.orders_id = zen_orders.orders_id
JOIN
zen_customers
ON zen_orders.customers_id=zen_customers.customers_id;";
 
/** Database Stuff **************************************************************************
 ********************************************************************************************/
$db = mysql_connect('myhost', 'myname', 'mypass') or die("Can't connect");  // Database connection
mysql_select_db('mydatabase');																														// Database selection

/** Variables *******************************************************************************
 ********************************************************************************************/
$result = mysql_query($sql);																																// Assign the result of the monstrous SELECT statement to $result
$num_fields = mysql_num_fields($result);																										// Used to put column headings in the CSV file
$fp = fopen('myfile.csv', 'w');																											// Opening the file to write



/** Getting the array of column headings to put in the CSV file by iterating through the  ***
*** fields, returning the name, and assigning it to the current ($i) element of the array ***
*********************************************************************************************/
for($i = 0; $i < $num_fields; $i++) {
  $str[] = mysql_field_name($result, $i);

}

/** Putting the $str which now contains the column headings on the first line of the CSV ****/
fputcsv($fp, $str);


/** Iterating through the $result of the SQL statement, and assigning each result to an 
element in the $list array *****************************************************************/
while($row = mysql_fetch_array($result)) {
$list[] = $row;
}

/** Iterating through the $list, which we have nicknamed to $fields, and putting each element
*** into the CSV file.  This could very likely be done above I think ***********************/
foreach($list as $fields) {
  fputcsv($fp, $fields);
}

/** Close the file - even though it will close automagically, good manners are good ********/
fclose($fp);
?>

[Dormilich]

that is because of the default fetch mode MYSQL_BOTH.

The PHP Manual said:

array mysql_fetch_array ( resource $result [, int $result_type = MYSQL_BOTH ] )

Returns an array of strings that corresponds to the fetched row, or FALSE if there are no more rows. The type of returned array depends on how result_type is defined. By using MYSQL_BOTH (default), you'll get an array with both associative and number indices. Using MYSQL_ASSOC, you only get associative indices (as mysql_fetch_assoc() works), using MYSQL_NUM, you only get number indices (as mysql_fetch_row() works).

[JBrace1990]

Dormilich, on 07 October 2011 - 06:14 PM, said:

that is because of the default fetch mode MYSQL_BOTH.

The PHP Manual said:

array mysql_fetch_array ( resource $result [, int $result_type = MYSQL_BOTH ] )

Returns an array of strings that corresponds to the fetched row, or FALSE if there are no more rows. The type of returned array depends on how result_type is defined. By using MYSQL_BOTH (default), you'll get an array with both associative and number indices. Using MYSQL_ASSOC, you only get associative indices (as mysql_fetch_assoc() works), using MYSQL_NUM, you only get number indices (as mysql_fetch_row() works).

Like he said.

A fix is either use a for to loop through it and get the numerical values, or set MYSQL_BOTH to 0.

[Dormilich]

JBrace1990, on 09 October 2011 - 05:27 AM, said:

or set MYSQL_BOTH to 0.

MYSQL_BOTH is a constant, you can’t set it. though you can use one of the other mentioned constants.

[nmcentire]

Sorry for the delay in getting back to this, got swamped with schoolwork (yay for essay classes )

So...if I change the mysql_fetch_array() to mysql_fetch_assoc() or mysql_fetch_num() it will only pull the information once, instead of basically doing a double pull?

[Dormilich]

I wouldn’t call it "double pull" but yes.

or in other words, I’m glad I’m not using the outdated mysql_* functions any more.

[nmcentire]

Thanks for your help. I'm completely new at PHP, and crash coursing my way into it. I just have a couple of semesters of C++ to back me up, with just a minor touch on PHP and Java, so I'm winging it on my own trying to figure this stuff out - maybe eCommerce isn't the best medium to tinker with in learning PHP, but it does have a wide range of things I can get myself into unknown territory easily with.

[Dormilich]

nmcentire, on 10 October 2011 - 09:00 PM, said:

maybe eCommerce isn't the best medium to tinker with in learning PHP, but it does have a wide range of things I can get myself into unknown territory easily with.

I’d rather say there is a narrow path that is not. for starters I’d rather recommend something like a Blog, since if you want to do more than backend processing, you’ll immediately find yourself in a couple of languages that you’d need to master at the same time (HTML, CSS, Javascript at least) as well as technical stuff like security and client-server communication. "unfortunately", web development requires all of them.