Create-Export csv file from mysql using php

Often you or your client has requirement to generate/create/export table records of database in excel sheet. 
Now it is possible to generate excel sheet of mysql database table using php. 


We will see that how easy to generate reports using php and mysql of table.

---

Code: 
<?php
    //Connection to database
    mysql_connect("localhost","root","");
    mysql_select_db("yourDatabase");

    $result = mysql_query("SELECT * FROM `table`");
    if (!$result) die('Couldn\'t fetch records');
    $num_fields = mysql_num_fields($result);

    $headers = array();

  // Creating headers for output files
    for ($i = 0; $i < $num_fields; $i++)
    {
        $headers[] = mysql_field_name($result , $i);
    }

    $fp = fopen('php://output', 'w');
    if ($fp && $result)
    {
    // name of file with date
        $filename = "jobPostingReport-".date('Y-m-d').".csv";
       
            // Setting header types for csv file.
            header('Content-Type: text/csv');
            header('Content-Disposition: attachment; filename='.$filename);
            header('Pragma: no-cache');
            header('Expires: 0');
            fputcsv($fp, $headers);

            while ($row = mysql_fetch_row($result))
            {
                 fputcsv($fp, array_values($row));
            }
            die;
     }
?>

---

Explanation Of Code:

First two lines to create connection to the database. Then create your desired query with field which are required to generate csv files.
if(!$result) to check whether your query is correct or not, if not then it will execute next lines means here "die".
Next set the headers/titles for CSV files from query resource. mysql_field_name() has two arguments first one is resource and the second one is offset, offset starts with 0.
 Now open the file writing wrapper with writable mode using fopen('php://output', 'w'). php://output is a write-only stream that allows you to write to the output buffer mechanism in the same way as print and echo.
for more info about wrappers

Now, use the above headers to export/download csv files from MySQL database and php.

fputcsv() command will help you to place data into csv sheet. At first write the headers/titles of csv files means field name to the csv file with help of file pointer which we have created earlier.

Now, Finally using fetch_array() fetch all rows and write them all into csv file using file pointer.