Export MySQL to CSV with PHP
92   1  

Export MySQL to CSV with PHP

  15 Nov 2017
  Coding Category
Export MySQL to CSV with PHP

Sometimes we (or users) need to download a MySQL table as a CSV to import in datasheet softwares like Excel, SPSS, Splus, SAS and more. We can do this in many ways (for example in PHPMyAdmin). I like send parameters in URL and get them with PHP. So I can choose table, rows and more with parameters. In this code we use only one parameters to choose table.


1. make a php file like exporttocsv.php with this codes:


<?php

require("config.php"); //general file to connect your DB and more

if(isset($_GET['id'])) {

    $tableid = !empty($_GET['id']) ? mysqli_real_escape_string($db, $_GET['id']) : '';

    $select = "SELECT * FROM " . $tableid;

    $export = mysqli_query($db, $select);

    $fields = mysqli_num_fields($export);

    while ($property = mysqli_fetch_field($export)) {

        $header .= $property->name . ",";

    }

    while($row = mysqli_fetch_row($export)) {

        $line = '';

        foreach($row as $value) {                                            

            if ((!isset($value)) || ($value == "")) {

                $value = "empty";

            } else {

                $value = str_replace(',' , ' ' , $value);

                $value = $value . ",";

            }

            $line .= $value;

        }

        $data .= trim($line) . "\n";

    }

    $data = str_replace("\r", "" , $data);

    if ($data == "") {

        $data = "\n(0) Records Found!\n";                        

    }

    header("Content-type: application/octet-stream");

    header("Content-Disposition: attachment; filename=mycsvtable.csv");

    print "$header\n$data";

} else {

    exit(json_encode(['error' => ' custom error message ']));

}

?>


2. Now we can use this url to export mytable as a CSV file and downlod it:


http://www.yourdomain.com/exporttocsv.php?id=mytable

Author: Ismael Azaran

Do you like it? Share it with your friends.

Categories

Tags