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.

I read this article and found it did not really help me. Hence why I posted my own question — Lewis M Hackfath
– Lewis M Hackfath, Commented Apr 20, 2016 at 21:36
You seemingly want the column names (and as you commented on an answer given, not hardcoded), and that question answers how to get the column names of a table dynamically. So is your question not how to get column names now? — skrilled
– skrilled, Commented Apr 20, 2016 at 21:37

fejese · Accepted Answer · 2016-04-20 21:47:40Z

4

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

answered Apr 20, 2016 at 21:47

fejese

4,6284 gold badges32 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

O. Jones · Accepted Answer · 2016-04-20 22:36:15Z

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.

edited Apr 20, 2016 at 22:36

answered Apr 20, 2016 at 21:43

O. Jones

110k17 gold badges134 silver badges187 bronze badges

Comments

Don't Panic · Accepted Answer · 2016-04-20 21:33:46Z

1

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.

answered Apr 20, 2016 at 21:33

Don't Panic

41.9k11 gold badges70 silver badges87 bronze badges

4 Comments

Lewis M Hackfath Over a year ago

How would I get the array of the column names? I would prefer not to hard code it.

Don't Panic Over a year ago

Look at the database using an admin interface like phpmyadmin or mysql workbench.

Don't Panic Over a year ago

I replied to your comment before you edited it. If you don't want to hard code it, the linked possible duplicate does show how to do it. How to select the columns, that is; not necessarily how to append them to a CSV.

Don't Panic Over a year ago

On second thought, the other two answers offer better options than running another query to get the column names.

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related