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.
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
Once
$resultis set from themysqli_query()method, you can usemysqli_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 asmax_length,length, andtable.Linked Documentationshows an example of using this metadata. p>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 innamecode> Properties of metadata array elements.This works even if there are no rows in the result set.
Since you are using
mysqli_fetch_assoc, the names of the columns are keys to the$rowarray 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); // .. }