-1

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("/\n/",'',preg_replace("/,/",';',$serveys[$a]['FeedbackName'])).",".
                                  preg_replace("/\n/",'',preg_replace("/,/",';',$serveys[$a]['BranchName']));
                            $csv.="\n";
                        }

                        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

Community
  • 1
  • 1
user2381872
  • 9
  • 6

4 Answers4

1

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.

Adder
  • 5,525
  • 1
  • 18
  • 47
0

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
"\n" - 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("/\n/",'',preg_replace("/,/",';',$row['col1'])).",".
                preg_replace("/\n/",'',preg_replace("/,/",';',$row['col2'])).",".
                preg_replace("/\n/",'',preg_replace("/,/",';',$row['col3'])).",".
                preg_replace("/\n/",'',preg_replace("/,/",';',$row['col4'])).",".
                preg_replace("/\n/",'',preg_replace("/,/",';',$row['col5']));
    $csv.="\n";
}

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!";
}
miszczu
  • 1,129
  • 4
  • 19
  • 36
  • im going to need some more explanation as I dont fully understand the code – user2381872 May 14 '13 at 13:50
  • Using str_replace would be faster and alittle easier to understand. There is documenation at http://www.php.net which can be searched and read. – Adder May 14 '13 at 13:53
  • do i need to create a blank excel file first? – user2381872 May 14 '13 at 13:56
  • No you don't. Do you want to create the file on the file system or do you want the user or you to download it? – Adder May 14 '13 at 13:59
  • No you don't need a blanke Excel file first; and a CSV file is not an Excel file, although Excel can read CSV files in addition to Excel files – Mark Baker May 14 '13 at 13:59
  • Although the name csv suggests a comma as a separator, I find semicolon works better. What do you think? – Adder May 14 '13 at 14:03
  • @Adder I need the user to download it. As well as it being Emailed to people. The answers people are giving me are not dumbed down enough :( – user2381872 May 14 '13 at 14:03
  • you didnt say anything about sending out via email in your question, here is [info](http://webcheatsheet.com/PHP/send_email_text_html_attachment.php) or create new question – miszczu May 14 '13 at 14:08
  • I assume this is a seperate issue...right now I just want to create a CSV file...and I need help but uncomplicated help – user2381872 May 14 '13 at 14:09
  • What is in the csv file? Do you know how to get at the data? Maybe a separate question would be in place, all the answers including the one you presented are good. – Adder May 14 '13 at 14:13
  • im sure the answers are fine. but i dont understand them. like i said, ive took data from a database and displayed it on a webpage. I need to export this same data into a CSV – user2381872 May 14 '13 at 14:15
  • Instead of displaying the data, put it into an array like `$array= array(); while($row=..get data..) $array[]= $row;`. Then use the code you found. – Adder May 14 '13 at 14:17
  • and where is the file created? I cant see one anywhere – user2381872 May 14 '13 at 14:23
  • It is sent to HTTP output with the name file.csv provided as a default name. When the script is run by typing its name in the location bar, the user is asked to download the file to his hard drive. You may provide a different file name in the line with the fopen: `fopen("file.csv", "w");` – Adder May 14 '13 at 14:28
  • could you please tell me where to view this file if the URL with all this code on is www.123.com/stats/customer_report.php. Would it be www.123.com/stats/file.csv?? because ive tried that and it didnt work – user2381872 May 14 '13 at 14:31
  • No you would get it right from e.g. `www.123.com/stats/customer_report_download.php` – Adder May 14 '13 at 14:32
  • omfg, [click here](http://php.net/manual/en/function.fputcsv.php) and see example 1 - this is how to save file on your server. Use google sometimes, I googled 'save csv file php' and firs result was correct. Next google 'download file php' (you know location of this file), and next 'send file in attachement php', I'm sure if you follow those steps you will create complete code you want to... – miszczu May 14 '13 at 14:37
  • i did bloody google it and searched this forum also but ive already told you im a novice and you probably have a better grasp and understanding of all this. the crap your showing me doesnt really help me to understand it otherwise Id use it since I found all that before I came here. @Adder, what do I put in that customer_report_download.php file? – user2381872 May 14 '13 at 14:43
  • User, you put `` into the php file (for a first test). – Adder May 14 '13 at 14:49
  • The thing is, you even didnt explain what you want to do. Did you wrote in your question that you want to save thas file on your server? No, Did you wrote that you want to send it via email in attachements? No. Because if you would, you could get complete code from me, or someone else. Thats why I wrote OMFG. And belive me, if you google 'save csv file php', 'download file php', 'send file in attachement php', you can create it all by your self. – miszczu May 14 '13 at 14:52
  • At the moment this code is in the customer_report.php file. at the bottom underneath the already displayed table. and @miszczu yes I want to save it to my server...ill tackle the emailing later – user2381872 May 14 '13 at 14:53
  • Can you update your question and write your database connection (use `xxx` in username, passwrd, db_name, domain etc.) and how do you fetch your data from database? I will update mine late evening today if you like. – miszczu May 14 '13 at 14:59
  • Accept an answer of the ones provided here, work on it, and come back - maybe then you will have enought points to `automatically move this discussion to chat?` – Adder May 14 '13 at 14:59
0

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.

Oliver Nagy
  • 316
  • 1
  • 10
  • do i need to create a blank excel file first? – user2381872 May 14 '13 at 13:56
  • No, what you're creating with this script is a CSV (Comma-Separated Values) file. It represents a table by using line-breaks as row separators and commas or semicolons for separating cells in a row. A row in this case looks like this: data11;data12;data13 Excel can load these files because they represent a table, and can convert them to excel files when saving. – Oliver Nagy May 14 '13 at 14:05
0

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

hdvianna
  • 413
  • 5
  • 13