Use PHP to get Column Names and Data for CSV export (MYSQL)

Question

I am in need of a way to export my MYSQL Database to CSV via PHP, but I need to select the column names as well. So Far I have the following which does everything I need except get the column names.

echo "Export Starting \n";
$SQL = ("SELECT *
FROM INF_TimeEntries
WHERE Exported IS NULL");
$result = mysqli_query($db_conn, $SQL) or die("Selection Error " . mysqli_error($db_conn));
echo "Export Data Selected \n";
$fp = fopen('../updateDatabase/timesheetExport/TimeEntries.csv', 'w');
echo "Starting Write to CSV \n";
while($row = mysqli_fetch_assoc($result)){
    fputcsv($fp, $row);
    $RowID = $row['ID'];
    $exportTime = date("Y-m-d H:i:s");
    $sql = ("UPDATE INF_TimeEntries
                    SET Exported = '$exportTime'
                    WHERE ID = '$RowID'");
    if ($mysqli_app->query($sql) === TRUE) {
    }
    else {
        echo date("Y-m-d H:i:s")."\n";
        echo "An Error Occured please contact the administrator ". $mysqli_app->error."\n";
    }
}
echo "Export Completed \n";
fclose($fp);
mysqli_close($mysqli_app);
mysqli_close($db_conn);

I am not sure how I would go about Achieving this. I do not simply need to get column names but Column names and the data contained in each of these columns. I have not found any information on this in the other question suggested.

Answer 1

Since you're using mysqli_fetch_assoc, the name of the columns are the keys of the $row array in each iteration. You can put that in the file in the first iteration:

echo "Starting Write to CSV \n";
$first = true;
while($row = mysqli_fetch_assoc($result)){
    if ($first) {
        fputcsv($fp, array_keys($row));
        $first = false;
    }
    fputcsv($fp, $row);
    // ..
}

Answer 2

Once you have your $result set from your mysqli_query() method, you can use mysqli_fetch_fields() to return an array of descriptions of the columns in the result set.

Each element of that array is a an object with several properties. One property is name -- which you can use as a header for your csv file. You also get properties like max_length, length, and table. The linked documentation shows an example of using this metadata.

This metadata is especially useful if you have a query more complex than SELECT * FROM table: if you assign aliases to the columns in your query, they show up in the name properties of the metadata array elements.

This works even if the result set has no rows in it.

Answer 3

Sounds pretty simple, as long as everything else is already working for you. You can create an array with the column names, and fputcsv($fp, $array_of_column_names) right before your while loop.