Code to create CSV file is bring in extra columns

PHP
1

Hi

I have the following code which takes data from a mySQL database and builds a simple CSV file from it.


$conn = new mysqli('localhost', '******', '******', '******');
 if (mysqli_connect_errno()) {
 printf("Connect failed: %s\
", mysqli_connect_error());
 	exit();
 	}
$curDate = date('Ymd H:i:s');

$filename = 'fairSquared-' . $curDate . '.csv';
$fp = fopen($filename, "w");

$query = "SELECT * from TABLE";


// fetch a row and write the column names out to the file
$result = $conn->query($query);
$row = $result->fetch_array();
echo print_r($row);
$line = "";
$comma = "";
foreach($row as $name => $value) {
    $line .= $comma . '"' . str_replace('"', '""', $name) . '"';
    $comma = ",";
}
$line .= "\
";
fputs($fp, $line);

// remove the result pointer back to the start
$result->data_seek(0);

// and loop through the actual data
while($row = $result->fetch_array()) {
   
    $line = "";
    $comma = "";
    foreach($row as $value) {
        $line .= $comma . '"' . str_replace('"', '""', $value) . '"';
        $comma = ",";
    }
    $line .= "\
";
    fputs($fp, $line);
   
}

fclose($fp);

The table is a simple affair with 4 columns named ‘fee’ ‘fi’ ‘fo’ ‘fum’

The problem is that the CSV file has the following columns in it:

0 | fee |1 | fi | 2 | fo | 3 | fum |

The values in 0 & fee, 1 & fi, 2 & fo and 3 & fum all match - so it’s like the php above is duplicating each row and naming it by a number.

I’m sure it’s a simple school boy error I’m making, can anyone advise please?

Thanks

CC

2

By default, mysqli_result::fetch_array returns the row data in both a numeric, and associative indexed array.

Take a look at the $resultype parameter for the method. :wink:

This optional parameter is a constant indicating what type of array should be produced from the current row data. The possible values for this parameter are the constants MYSQLI_ASSOC, MYSQLI_NUM, or MYSQLI_BOTH.

By using the MYSQLI_ASSOC constant this function will behave identically to the mysqli_fetch_assoc(), while MYSQLI_NUM will behave identically to the mysqli_fetch_row() function. The final option MYSQLI_BOTH will create a single array with the attributes of both.