PHP – MySqli – Export all Fields and Data of a Table

The following php example helps you to export all the fields and the data of a table into a .csv file.

Code Example:

<?php
    $servername = "localhost";  // server name
    $username = "root";         // username
    $password = "password";   // password

    $database = 'school';    // database
    $tables = array('students', 'classes', 'teachers'); // array of tables need to export

    $file_name = 'export_school.csv';   // file name
    $file_path = 'downloads/'.$file_name; // file path

    // Create connection
    $conn = new mysqli($servername, $username, $password);

    // Check connection
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    }

    // select database
    mysqli_select_db($conn, $database);

    $file = fopen($file_path, "w"); // open a file in write mode
    chmod($file_path, 0777);    // set the file permission

    // loop for tables
    foreach($tables as $table) {
        $table_column = array();
        $query_table_columns = "SHOW COLUMNS FROM $table";

        // fetch table field names
        if ($result_column = mysqli_query($conn, $query_table_columns)) {
            while ($column = $result_column->fetch_row()) {
                $table_column[] = $column[0];
            }
        }

        // Format array as CSV and write to file pointer
        fputcsv($file, $table_column, ",", '"');

        $query_table_columns_data = "SELECT * FROM $table";

        if ($result_column_data = mysqli_query($conn, $query_table_columns_data)) {

            // fetch table fields data
            while ($column_data = $result_column_data->fetch_row()) {
                $table_column_data = array();
                foreach($column_data as $data) {
                    $table_column_data[] = $data;
                }

                // Format array as CSV and write to file pointer
                fputcsv($file, $table_column_data, ",", '"');
            }
        }
    }

    // close file pointer
    fclose($file);

    // ask either save or open
    header("Pragma: public");
    header("Expires: 0");
    header("Content-Type: application/octet-stream");
    header("Content-Disposition: attachment; filename='{$file_name}';" );
    header("Content-Transfer-Encoding: binary");

    // open a saved file to read data
    $fhandle = fopen($file_path, 'r');
    fpassthru($fhandle);
    fclose($fhandle);
    $conn->close();
    die;
?>