Lewis M Hackfath Lewis M Hackfath - 7 months ago 54
SQL Question

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

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

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);
    // ..
}
Comments