I've trauled through this site and found tonnes of threads about this same thing but none really help a complete novice like me as I dont understand the answers properly.
I have selected data from a database and displayed in a table on a webpage. I now need a link below this table which will export this data to a CSV file.
one of the answers I have found on this site is: Create a CSV File for a user in PHP
However the answer doesnt really make sense to me and I dont know what to do with the code.
Here is the code from that answer:
header("Content-type: text/csv");
header("Content-Disposition: attachment; filename=file.csv");
header("Pragma: no-cache");
header("Expires: 0");
$array = array(
array("data11", "data12", "data13"),
array("data21", "data22", "data23"),
array("data31", "data32", "data23"));
outputCSV($array);
function outputCSV($data) {
$outstream = fopen("php://output", "w");
function __outputCSV(&$vals, $key, $filehandler) {
fputcsv($filehandler, $vals); // add parameters if you want
}
array_walk($data, "__outputCSV", $outstream);
fclose($outstream);
}
I have so many questions like do I need to create an excel file first? if so does it need to be blank?
what is this?:
$array = array(
array("data11", "data12", "data13"),
array("data21", "data22", "data23"),
array("data31", "data32", "data23"));
is this supposed to be my data? how do I change that to suit the data I have?
Why is the call to the function before the function is defined?
what are these variables in the function ($vals, $key, $filehandler) and where are they created?
How do I use this code because so many people seem to think its perfect.
I really need help from start to finish with this as I am a complete PHP novice
Ive worked on one of the answers below and now I have this code
$i=0;
$csv="";
for ($a=0; $a<=$count; $a++) {
$i++;
$csv.=preg_replace("/
/",'',preg_replace("/,/",';',$serveys[$a]['FeedbackName'])).",".
preg_replace("/
/",'',preg_replace("/,/",';',$serveys[$a]['BranchName']));
$csv.="
";
}
if ($i>0) {
header("Pragma: public");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Cache-Control: private",false);
header("Content-Type: application/octet-stream");
header("Content-Disposition: attachment; filename=\"table.csv\";" );
header("Content-Transfer-Encoding: binary");
echo $csv;
} else {
return "Nothing to download!";
}
when I run the page though there is no change, the data is still displayed on the page as it should be. but no csv file is created and no errors occurr
The code created a new file object, which outputs to standard output, which then is offered to the user as download because of the headers sent before.
That IS your data; an array of associative arrays. How you fill it is up to you.
The call to the function is before it is defined because PHP first parses the file at top level completely, so the function actually will be defined when it is called. This does not apply to functions inside if statements.
The values to the function are provided by the standard function fputcsv.
You use the code almost exactly like presented.
3rd line - query to your database (this depend how do you connect to database, I'm using class)
4th line - fetch data (this depend how do you connect to database)
But you can use your connecting, just fetch you data, and when you do this, do not display data from database, add it to variable $csv.='some string'. When you fetch all data, then display headers (it will create csv file), and then display all your data.
"," - new cell" " - new line
thats why I used preg_replace, because if your cell in database is ,, this comma create new cell, and you dont want that, so you can replace it by ;.
You dont have to create csv file, name of your file is in this line header("Content-Disposition: attachment; filename=\"table.csv\";" ); - table.csv
$i=0;
$csv="";
$res=$db->query('SELECT * FROM `table_name`');
while ($row=$res->fetch()) {
$i++;
$csv.=preg_replace("/
/",'',preg_replace("/,/",';',$row['col1'])).",".
preg_replace("/
/",'',preg_replace("/,/",';',$row['col2'])).",".
preg_replace("/
/",'',preg_replace("/,/",';',$row['col3'])).",".
preg_replace("/
/",'',preg_replace("/,/",';',$row['col4'])).",".
preg_replace("/
/",'',preg_replace("/,/",';',$row['col5']));
$csv.="
";
}
if ($i>0) {
header("Pragma: public");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Cache-Control: private",false);
header("Content-Type: application/octet-stream");
header("Content-Disposition: attachment; filename=\"table.csv\";" );
header("Content-Transfer-Encoding: binary");
echo $csv;
} else {
return "Nothing to download!";
}
The part you highlighted is a two-dimensional array of values, much like a spreadsheet (or a CSV file in this case). The outermost array holds together arrays that you can imagine as rows. Every value in that array is written to a column. So from this:
$array = array(
array("data11", "data12", "data13"),
array("data21", "data22", "data23"),
array("data31", "data32", "data23"));
, you would get a table like this:
----------------------------
| data11 | data12 | data13 |
----------------------------
| data21 | data22 | data23 |
----------------------------
| data31 | data32 | data33 |
----------------------------
How you're changing this data depends on how you got the data from the database that you mention, but the basic principle is the same: put all the data in an array, have one array for each row, and have the values in that row inside the row array.
The variables are the parameters passed to the callback function you give to the array_walk function. array_walk takes an array, iterates over every item in it, and applies a function on the item, in this case, it walks over the array of rows, and writes every row array as CSV to the opened file. So $vals will always contain the row array, $key is ignored here but it would contain the index or key of the array item accessed, and $filehandler is the $outstream variable passed in, which is the open file handler used to write the values to a file.
Lastly, in PHP you can define functions later than they are used, as long as they are in the same scope.
The header function is used to send aditional information about the response to the user agent. Those adtional information is passed as HTTP header-fields. See header.
header("Content-type: text/csv");: This code will tell the user agent, or browser, that the content that it will receive is CSV file. See Content-type.
header("Content-Disposition: attachment; filename=file.csv");: This code is telling the user agent, or browser, to receive this content as attachment, and that the this attachment had the name file.csv. The browser might save this content in the Downloads folder or ask to the user for a local to save that file, depending of the browser settings. See Content-Disposition
header("Pragma: no-cache");: Accordingly with the HTTP/1.1. specification, this directive has the same meaning of Cache-Control: no-cache. The Pragma: no-cache directive is used of for backward compatibility with HTTP/1.0. See Pragma
header("Expires: 0"); This code prevents the user agent of caching the generated content. So, the browser will made a request to the server every time the user asks for it's contents. See Expires.
$array = array(
array("data11", "data12", "data13"),
array("data21", "data22", "data23"),
array("data31", "data32", "data23"));
This code create a 3x3 bidimensional array, i.e. 3 rows and 3 columns.
outputCSV($array);: This code will call the outputCSV function, passing the bidimensional array as a paramater
$outstream = fopen("php://output", "w");: The fopen function is used to open an file for reading or writing. This funcion returns a handler of that file, so you can execute i/o operations in that file. See fopen
When you use "php://output" you're telling PHP that any write operation on the file handler will be done in the standart output. So the command fwrite($fhandler, "Hello World!") have the same result of echo "Hello World!". See php://
The "w" means that the file is being open for write operations only .
array_walk($data, "__outputCSV", $outstream) is used to execute a function for every element of the array passed as the first parameter. In that way, it will execute the __outputCSV function, that executes the fputcsv. See array_walk
The fputcsv writes the content of an array in a file. Each element of the array will be separated by delimiter. The defaul delimiter is , (comma). See fputcsv
As you have a 3x3 array, the array_walk will execute the fputcsv function 3 times. The first time for array("data11", "data12", "data13"), the second time for array("data21", "data22", "data23"), and the last time for array("data31", "data32", "data23").
In it's turn, the fputcsv will output the following:
"data11","data12","data13"
"data21", "data22", "data23"
"data31", "data32", "data23"
Finally, fclose is used. This will output the buffered data which was previously sent with fputcsv and will close the file. See fclose