The following script produces the txt file with the required data, but it produces the data twice in the text file.
mysql_connect($hostname_MySQLCon, $username_MySQLCon, "") or die(mysql_error());
mysql_select_db($database_MySQLCon) or die(mysql_error());
$data = mysql_query("
SELECT sales_flat_order.increment_id, sales_flat_order.gift_message_id, sales_flat_order.status, gift_message.gift_message_id, gift_message.message
FROM sales_flat_order
JOIN gift_message ON sales_flat_order.gift_message_id = gift_message.gift_message_id
WHERE sales_flat_order.gift_message_id IS NOT NULL
GROUP BY sales_flat_order.increment_id
/* AND sales_flat_order.status = 'pending' */;")
or die(mysql_error());
while($result = mysql_fetch_array( $data ))
{
$dataRow[] = implode("|", $result);
}
$theFile = 'orders-meta.txt';
if (!$handle = fopen($theFile, 'a')) {
exit;}
if (fwrite($handle, implode("
", $dataRow)) === FALSE) {
echo "Cannot write to file ($theFile)";
exit;}
echo "Success, the file was written.";
fclose($handle);
Example output in the txt file:
100000001|100000001|1121|1121|pending|pending|1121|gift message|gift message
100000002|100000002|1123|1123|pending|pending|1123|Gift message|Gift message
Why would it be producing each value twice? And how can I change it so that output would be:
100000001|1121|pending|1121|gift message
100000002|1123|pending|1123|Gift message
Really appreciate any help.
Thanks
From the docs:
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).
mysql_fetch_array fetches each result twice, once as an assoc array, once as a numerically indexed array by default. That's why you get duplicate rows.
Having said that, please don't use mysql_* as it's in the deprecation process, read the read box on the doc pages, and click on the links to PDO or mysqli_*, or the links here and here
Read the manual:
mysql_fetch_array ( resource $result [, int $result_type = MYSQL_BOTH ] )
Then try:
while($result = mysql_fetch_array( $data, MYSQL_NUM ))
By default, mysql_fetch_array returns a mixed array with both numeric and associative keys - so each value is duplicated. Change your fetch line to:
mysql_fetch_array($data, MYSQL_ASSOC)
Because you're using mysql_fetch_array() instead of mysql_fetch_row().
The default $result_type for mysql_fetch_array is MYSQL_BOTH:
array mysql_fetch_array ( resource $result [, int $result_type = MYSQL_BOTH ] )
which will produce array like this:
array(
0 => 100000001
'increment_id' => 100000001,
1 => 1121,
'gift_message_id' => 1121,
2 => 'pending',
'status' => 'pending',
3 => 1121,
4 => 'gift message',
'message' => 'gift message'
)
And why are you storing data in so ineffective way? Why not to do:
$theFile = 'orders-meta.txt';
if (!$handle = fopen($theFile, 'a')) {
exit;
}
while($result = mysql_fetch_array( $data )) {
fwrite($handle, implode("|", $result));
fwrite($handle, "
");
}
fclose($handle);