Query mysql and export data as CSV in PHP

Question

I have myssql db with different tables. The data between the tables is related, and I retrieve it and display it using the user ID. I used the link from PHP MYSQLi Displaying all tables in the database

But how can I export this information as a csv file? I tried instead of echo changing it to a line and printing the line in the csv file, but it does not work.

I also tried an example from: http://sudobash.net/php-export-mysql-query-to-csv-file/

But I see the data, but there is also garbage on top (for example, "<font size='1'><table class='xdebug-error xe-notice' dir='ltr' border='1' cellspacing='0' cellpadding='1'>"
etc.) inside the csv file.

Is there any other way to do this?

Answer 1

If you want to write each MySQL row to a CSV file, you can use the PHP5 built-in fputcsv function

 $result = mysqli_query($con, 'SELECT * FROM table'); $row = mysqli_fetch_array($result, MYSQLI_ASSOC); $fp = fopen('file.csv', 'w'); foreach ($row as $val) { fputcsv($fp, $val); } fclose($fp); 

Which should return a comma-separated line for each line written in file.csv :

 row1 val1, row1 val2 row2 val1, row2 val2 etc.. 

Also, be sure to check permissions for the directory in which you are writing.

Answer 2

This is a solution that includes headers or field names using the MySQLi fetch_fields function

 $query = "SELECT * FROM table"; $result = $db->query($query); if (!$result) die('Couldn\'t fetch records'); $headers = $result->fetch_fields(); foreach($headers as $header) { $head[] = $header->name; } $fp = fopen('php://output', 'w'); if ($fp && $result) { header('Content-Type: text/csv'); header('Content-Disposition: attachment; filename="export.csv"'); header('Pragma: no-cache'); header('Expires: 0'); fputcsv($fp, array_values($head)); while ($row = $result->fetch_array(MYSQLI_NUM)) { fputcsv($fp, array_values($row)); } die; } 

This is also a modification where a similar solution was proposed, but part of the headers did not work for me, so I changed the method for retrieving them. Credit is related to @Jrgns

Answer 3

You can try using MySql INTO OUTFILE :

 SELECT * INTO OUTFILE '/tmp/tablename.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM tablename WHERE userid = 1 

Answer 4

to create and load the csv file from mysqli query (using object oriented) I think this will help.

This is a class that connects to the database, and the functions will do what you want using mysqli and PHP. In this case, calling this class (require or include), just use the downloadCsv () function.

As an example, this will be the file "class.php":

 <?php class DB{ private $con; //this constructor connects with the database public function __construct(){ $this->con = new mysqli("Your_Host","Your_User","Your_Pass","Your_DatabaseName"); if($this->con->connect_errno > 0){ die('There was a problem [' . $con->connect_error . ']'); } } //create the function that will download a csv file from a mysqli query public function downloadCsv(){ $count = 0; $header = ""; $data = ""; //query $result = $this->con->query("SELECT * FROM Your_TableName"); //count fields $count = $result->field_count; //columns names $names = $result->fetch_fields(); //put column names into header foreach($names as $value) { $header .= $value->name.";"; } } //put rows from your query while($row = $result->fetch_row()) { $line = ''; foreach($row as $value) { if(!isset($value) || $value == "") { $value = ";"; //in this case, ";" separates columns } else { $value = str_replace('"', '""', $value); $value = '"' . $value . '"' . ";"; //if you change the separator before, change this ";" too } $line .= $value; } //end foreach $data .= trim($line)."\n"; } //end while //avoiding problems with data that includes "\r" $data = str_replace("\r", "", $data); //if empty query if ($data == "") { $data = "\nno matching records found\n"; } $count = $result->field_count; //Download csv file header("Content-type: application/octet-stream"); header("Content-Disposition: attachment; filename=FILENAME.csv"); header("Pragma: no-cache"); header("Expires: 0"); echo $header."\n".$data."\n"; } ?> 

After creating the "class.php" file in this example, use this function in the "download.php" file:

 <?php //call the "class.php" file require_once 'class.php'; //instantiate DB class $export = new DB(); //call function $export->downloadCsv(); ?> 

After downloading, open the file using MS Excel.

Hope this helps you, I think I wrote it well, I didn’t like the text box and code box.

Answer 5

Try it.

 function addRowToCsv(& $csvString, $cols) { $csvString .= implode(',', $cols) . PHP_EOL; //Edits must be 6 characters - all I added was a "." before the =. :-) } $csvString = ''; $first = true; while ($row = mysqli_fetch_assoc($query)) { if ($first === true) { $first = false; addRowToCsv($csvString, array_keys($row)); } addRowToCsv($csvString, $row); } header('Content-type: text/csv'); header('Content-disposition: attachment;filename=MyCsvFile.csv'); echo $csvString; 

Note that the first argument to addRowToCsv is passed by reference. This is not required, and you can easily use the return value, but this is exactly how I did it.

If you want to save the output to a file, and not serve it as a download, use the above, but replace

 header('Content-type: text/csv'); header('Content-disposition: attachment;filename=MyCsvFile.csv'); echo $csvString; 

WITH..

 file_put_contents('MyCsvFile.csv', $csvString);