Viewers: in countries Watching now:
Providing a file from a database in exactly the same format that's requested by the user is an extremely valuable technique. In this course, David Powers shows you how to export data from a database with PHP in a variety of formats, including rich text, CSV, Excel, Word, OpenOffice spreadsheets and documents, and even XML. He introduces tools like PHPExcel and PHPRtfLite that make the job of formatting the data (fonts, headers, columns, and all) easier to manage, and also shows how to embed nontext data like images in your exports.
Generating a plain text file from a database result requires only a dozen or so lines of code. You don't even need to create a copy of the file for the user to download. The PHP script streams it directly to the browser with the appropriate HTTP headers. In my editing program, I've opened cars_text.php. It's identical to the page we set up in chapter one, apart from the label on the submit button. The command on line two includes the database query.
I'm using the mySQLi version. Change it to the PDO version if you're using the SQLi database or any other database. The code we'll write in this page will be the same, whichever version you're using. If we scroll down to the bottom of the page, we can see that the name of the submit button is download, so we need to add a conditional statement to check for download in the post array when the submit button is clicked. So, let's go back above the doctype and add a new line after line two, then a conditional statement, if is set and what we're looking for is the post array and the download element.
Inside that conditional statement, we need to send a series of http headers so the browser knows to expect a file. So we use the header of function which expects a string and the first one will be content-type followed by a colon and the mine type which is text/play. Then another header, and this one will be content-disposition, followed by a colon, attachment, a semi colon and then, filename equals, and let's call this cars.txt.
We need another three headers to prevent browsers from caching the file and to save time I'm going to copy them from the text file which you can find in the exercise files for this video. The first one there is cache control, no cache, the second one pragma, no cache, it the same as cache control but pragma is used by older browsers and this last one here, header expires zero. That means the file expires immediately, so a fresh download will always be generated.
Next, we need to open a file for writing, and we can stream it directly to the browser using the f open function. F open returns a file handle, in other words, a reference to the file, that we need to pass to functions that actually write the output. So we'll create that handle, we'll call that output, and F open, and the first argument as as string, php://output and that will stream it directly to the browser rather than creating a local file.
And the second argument that we need is the mode. Which will be right. So in quotes, w. With the stream open, we can loop through the database results, exporting a row at a time. Because mySQLI and PDO use different methods to fetch a row, we're going to use the custom get row function that was defined in chapter one. So, we need a while loop here and we'll save the row as row equals get row, and we pass it result, which is our database result.
Row is an associative array that uses the column names as the key for each array element. We can use the column names as labels. So when we go through each row, we need to go through each array element, and getting both the key and the value for each one. So we need a for each loop here, so for each row as, then key, and a double arrow value. So, we use f write to go through each array element and f write expects that handle which is output.
And the second argument it expects is a string. What we're going to use is the key as the label. And to make it look better, I'm going to use UC first to make the first letter of each label uppercase. So, UC first, then pass it key, then we'll have the concatenation operator, a dot, we need the rest of the string, We make that a double quoted string. We go in with a colon, then a space, value, then backslash R, backslash N.
That will create a new line at the end of each array element. So we'll have the label, followed by its value, followed by a new line, and then when we get to the end of the row, we'll add another new line so each result is separate. So, f write again, output, and the string will be a double quoted string backslash F, backslash N. And then when we get to the end of our database result, we need to close the stream.
So at the end of that while loop, f close, pass it the handle which is output and then finally exit the script. So we can now save that page. And test this in a browser. Here we are in a browser, it's localhost, phpexport, cars_text.php. Load that page. There's the list of fictitious cars for sale. Scroll down to the bottom. There's our download button.
Click that. And it has downloaded cars.txt. And if I open that, there it is in notepad. And each element is on a single line. And each row has got a line between them. So there is the plain text output. It doesn't produce a very elegant result, but it does have the advantage of being quick and easy to implement.
There are currently no FAQs about Exporting Data to Files with PHP.
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.