exporting a mysql query in php version 4 to a csv

Question

Edit on my original post. I found the answer!!!! with help:)

I now have this working by using the below code with thanks for the advice on this in the comments:

<?php

$f = fopen('incident_csv\test.csv', 'w');

$query = "
select column1, column2, column3
from table
where columns = values
";

$var1 = mysql_query($query, $database connection variable);

/* From Monkey Zeus */

$csv_lines = array();

// Loop your records from the DB
while ($row = mysql_fetch_assoc($var1)){

$columns = array();

// Loop each column for the row
foreach($row as $k=>$v){
// Surround column item in double-quotes and escape double-quotes with double-double-quotes
$columns[] = '"'.str_replace('"', '""', $v).'"';
}

// Join on a comma
$csv_lines[] = implode(',', $columns);
}

// Create full CSV file by joining on a newline
$csv_file_string = implode("\n", $csv_lines);

/* From Monkey Zeus */  

fwrite($f, $csv_file_string);

?>

PHP 4 hasn't had a security update since Aug 2008. Run away. Run far, far away. — Quentin, Oct 20 '16 at 14:15
Which function is `fpost`? Did you write it? And you did not save the file resource returned from `fopen` anywhere. You won't be able to use it this way. — Vinicius Dias, Oct 20 '16 at 14:17
@Quentin It is very probable that OP is working within the confines of an older system which might have branched out to affect multiple aspects of the company infrastructure. As long as this is not being used to power a well-known website then PHP4 should be perfectly usable to achieve the stated goals. — MonkeyZeus, Oct 21 '16 at 13:44

Vinicius Dias · Answer 1 · 2016-10-21T18:05:50.573

0

You can simply wite an arrayToCsv function:

function arrayToCsv($array) {
    $string = array();

    foreach ($array as $field) {
        $string[] = implode(';', $field);
    }

    return implode("\n", $string);
}

$a = array(
    array('First Field of First Line', 'Second Field of First Line'),
    array('First Field of Second Line', 'Second Field of Second Line')
);
fwrite($f, arrayToCsv($a));

But, remember to have your $f setted from $f = fopen('file location', 'w');

Hope I was helpful.

edited Oct 21 '16 at 18:05

answered Oct 20 '16 at 14:17

Vinicius Dias

664
3
15

The C in CSV typically stands for **C**omma, not semi-colon. – MonkeyZeus Oct 20 '16 at 14:26
Excel opens it correctly only with semi-colon. ;-) But, it's easy to change if necessary. – Vinicius Dias Oct 20 '16 at 14:29
thanks for the advice on this but i struggled to get this method working, i will still try as its always good to know how to do things two ways :) – Sean Stevens Oct 21 '16 at 12:52
no error just couldn't populate the csv file even though the page finished. Im also quite new to php and struggled to change it meet my needs . – Sean Stevens Oct 21 '16 at 16:08
Can you post your full code? Did you change the part where you do `fopen` as I said? – Vinicius Dias Oct 21 '16 at 18:05

MonkeyZeus · Accepted Answer · 2016-10-21T13:46:45.120

You can do this:

$csv_lines = array();

// Loop your records from the DB
while ($row = mysql_fetch_assoc($var1)){

    $columns = array();

    // Loop each column for the row
    foreach($row as $k=>$v){
        // Surround column item in double-quotes and escape double-quotes with double-double-quotes
        $columns[] = '"'.str_replace('"', '""', $v).'"';
    }

    // Join on a comma
    $csv_lines[] = implode(',', $columns);

    // Write to the file right away. You are using PHP4 so I imagine system memory is not plentiful
    // If you use this then there is no need for the "$csv_lines[] =" from above
    // nor the $csv_file_string after the while loop
    // fwrite($f, implode(',', $columns)."\n");
}

// fclose($f); // If you choose to write to the file during the while loop then close the file handle when you are finished

// Create full CSV file by joining on a newline
$csv_file_string = implode("\n", $csv_lines);

Thanks @MonkeyZeus for this suggestion i managed to get this working by stitching this into my original query and changing the line "mysql_fetch_array" to "mysql_fetch_assoc" to remove duplicates in the CSV file. — Sean Stevens, Oct 21 '16 at 12:44
Nice! Glad I could help. However, please see my update using `fwrite($f, implode(',', $columns)."\n");` because I would imagine that your server with PHP4 does not have much memory so writing to the disk is probably desirable. — MonkeyZeus, Oct 21 '16 at 13:35

exporting a mysql query in php version 4 to a csv

2 Answers2