Get column names and data for CSV export (MYSQL) using PHP

Question

I need a way to export a MYSQL database to CSV via PHP, but I also need to select the column names. So far I have the following, which does everything I need except getting 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'm not sure how I'm going to achieve this. I need to get not only the column names, but also the column names and the data contained in each column. I didn't find any relevant information in the other questions suggested.

Answer 1

Once $result is set from the mysqli_query() method, you can use mysqli_fetch_fields() to return the columns in the result set description array.

Each element of this array is an object with multiple properties. One of the properties is name - you can use this as the title of your csv file. You also get properties such as max_length, length, and table. Linked Documentation shows an example of using this metadata.

This metadata is especially useful if your query is more complex than SELECT * FROM table: if you assign aliases to columns in the query, they will appear in name code> Properties of metadata array elements.

This works even if there are no rows in the result set.

Answer 2

Since you are using mysqli_fetch_assoc, the names of the columns are keys to the $row array in each iteration. You can put this into a file on 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);
    // ..
}