Multiple Arrays in php for csv exporting

Question

I am trying to output multiple arrays into a csv file with pre-set headers. so far i can only manage to output 2 of the exact same results into the csv (sql result 0). I believe i have my "for" statements in a muddle somehow and believe this is probably very easy to fix but for the life of me cannot see it. Does anyone have any ideas at all, or any better way to do this csv output?

// output headers so that the file is downloaded rather than displayed
header('Content-type: text/csv');
header('Content-Disposition: attachment; filename="SageImport.csv"');

// do not cache the file
header('Pragma: no-cache');
header('Expires: 0');

// create a file pointer connected to the output stream
$file = fopen('php://output', 'w');

// send the column headers
fputcsv($file, array('Reference', 'Company Name', 'Currency', 'Credit Limit', 'Main Address Type', 'Main Address Line 1', 'Main Address Line 2', 'Main Address Town', 'Main Address County', 'Main Address Post Code', 'Main Address Country', 'Main Contact Name', 'Main Contact Phone', 'Main Contact Type', 'Main Contact Mobile', 'Main Contact Email', 'Main Contact Fax', 'Address 2 Type', 'Address 2 Line 1', 'Address 2 Line 2', 'Address 2 Town', 'Address 2 County', 'Address 2 Post Code', 'Address 2 Country', 'VAT Number', 'Ledger Account', 'Payment Terms', 'Notes', 'Bank Account Name', 'Bank Account Sort Code', 'Bank Account Number', 'Bank Account IBAN', 'Bank Account BIC'
));

$query = "SELECT * FROM clients WHERE sageexport='0'";
$result = mysql_query($query) or die("Error: ".mysql_error());
while ($row = mysql_fetch_array($result, MYSQL_NUM)) {$info[] = $row;}
for ($i = 0; $i < count($info); $i++) {
    if ($info[$i][10] = "yes") {$agreed = 'Agreement';}     
$data = array($info[$i][1], $info[$i][2], 'GBP', '', '', $info[$i][3], '', $info[$i][4], $info[$i][5], $info[$i][6], 'GB', '', $info[$i][8], '', '', $info[$i][7], '', '', '', '', '', '', '', '', '', '10001', '10', $agreed, '', '', '', '', '');
}
$agreed = "";


// output each row of the data
for ($i = 0; $i < count($info); $i++) {
foreach (array($data) as $row2){fputcsv($file, $row2);}
}
exit();

elixenide · Accepted Answer · 2018-04-22T19:33:28.240

You are getting the same row twice because you overwrite $data each time through your first loop with this line:

$data = array($info[$i][1], $info[$i][2], 'GBP', '', '', $info[$i][3], '', $info[$i][4], $info[$i][5], $info[$i][6], 'GB', '', $info[$i][8], '', '', $info[$i][7], '', '', '', '', '', '', '', '', '', '10001', '10', $agreed, '', '', '', '', '');

Either write the data to the buffer in that loop (the simplest option) or append each row to the array (which is what you're actually trying to do). To append it, you want something like this:

$data[] = array($info[$i][1], $info[$i][2], 'GBP', '', '', $info[$i][3], '', $info[$i][4], $info[$i][5], $info[$i][6], 'GB', '', $info[$i][8], '', '', $info[$i][7], '', '', '', '', '', '', '', '', '', '10001', '10', $agreed, '', '', '', '', '');
//   ^ this is the change that makes it work

A Bit More Explanation

$data is an array, but that doesn't make it magical. If you assign something to it, like $data = array(...);, that replaces whatever was there. You can't get that information back.

This means that the end of your code makes no sense, specifically this piece (which I have formatted to make it readable):

// output each row of the data
for ($i = 0; $i < count($info); $i++) {
    foreach (array($data) as $row2){
        fputcsv($file, $row2);
    }
}

You're trying to loop twice, once based on the number of rows in your MySQL result and once (the inner loop) based on what's in $data. But, since you only ever stored one row in $data, the inner loop won't work. And you really only need the inner loop (the foreach) anyway; the outer loop doesn't add anything if you actually save all of the rows into $data.

A Few Other Points

You have a number of other, major problems in this code.

Please don't use mysql_*; the mysql_* functions are outdated, deprecated, and insecure - they have been removed entirely from modern versions of PHP (version 7.0 and higher). Use MySQLi or PDO instead.
Never, ever use for(...; $variable < count(something); ...).* That requires count() to run every time your loop executes. This is murderous to performance. See phpbench.com for examples of how bad this kind of thing can be.
As Syscall pointed out, you are doing a comparison with = in if ($info[$i][10] = "yes"). This should be == or even ===.
The array() wrapper in foreach (array($data) as $row2)... is unnecessary and actually causing problems. (Also pointed out by Syscall.) Take it out.
Please format your code. It's extremely difficult to read it the way you have it written, which is going to make maintaining it very unpleasant.

^* There's one exception here, if something can change size within your loop. But in that case, there's probably a better design for whatever you're doing than a simple for loop. And you're not doing anything like that here, anyway.

@Syscall Sure thing. Yours is a good answer (I upvoted), and you caught a couple of things I initially overlooked. — elixenide, Apr 22 '18 at 19:41
Thanks guys, i have fixed it all up now. really appreciate the input, there's some solid advice in there! — Mike 'Ambroid' Foran, Apr 22 '18 at 21:01

score 2 · Answer 2 · answered Apr 22 '18 at 19:22

To compare, you have to use two equal sign ($info[$i][10] = "yes" should be $info[$i][10] == "yes").
You have to append the new array to $data using [].
In the foreach loop, you don't have to use array($data), but use directly $data, and remove the for loop:

while ($row = mysql_fetch_array($result, MYSQL_NUM)) {$info[] = $row;}
for ($i = 0; $i < count($info); $i++) {
    // compare using ==, not =
    if ($info[$i][10] == "yes") {$agreed = 'Agreement';}
    // append using [],
    $data[] = array($info[$i][1], $info[$i][2], 'GBP', '', '', $info[$i][3], '', $info[$i][4], $info[$i][5], $info[$i][6], 'GB', '', $info[$i][8], '', '', $info[$i][7], '', '', '', '', '', '', '', '', '', '10001', '10', $agreed, '', '', '', '', '');
}
// only one foreach, using $data, not array($data)
foreach ($data as $row2) { fputcsv($file, $row2); }

But, I think it could be much easy to create the CSV file in the while loop, instead of create 3 loops:

// use `$info` here
while ($info = mysql_fetch_array($result, MYSQL_NUM)) {
    // compare using ==, not =
    if ($info[$i][10] == "yes") {$agreed = 'Agreement';}
    // create the CSV line,
    $data = array($info[$i][1], $info[$i][2], 'GBP', '', '', $info[$i][3], '', $info[$i][4], $info[$i][5], $info[$i][6], 'GB', '', $info[$i][8], '', '', $info[$i][7], '', '', '', '', '', '', '', '', '', '10001', '10', $agreed, '', '', '', '', '');
    // write it,
    fputcsv($file, $data);
}

Multiple Arrays in php for csv exporting

2 Answers2

A Bit More Explanation

A Few Other Points