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.
We saw earlier in this chapter, that it takes only a few lines of code to export a data base result to a text file, or to a CSV file. Because so little code is involved, it might seem overkill to use custom classes instead. But even if you Copy and Paste all of this code into another file, you'll probably want to make some changes to it. For example, this code on line 12. It capitalizes the first letter of each column name in the database result. What if you want it to be all uppercase, or what if you don't want to include it at all? You might also want the idea of each database row to create links in your webpage.
But you don't want that information to be included in the download file. That's where custom classes come in handy. The class definitions are in the SRC folder, that I've created at the top level of my testing site. You can find a copy of this folder in the exercise files for this video. Let's jut open it. It contains another folder called foundationphp. That's my domain name. So I've used that as the name space for the classes I have defined, and inside this folder is the auto loader that was described in chapter one.
PSR 4 auto loader.php. There's also a folder called Exporter. Let's just expand that. This contains six files with class definitions, that'll be used in different parts of this course. The one we're interested in at the moment is this first one, base.php. This is used as the basis for three other classes - CSV, text, and XML. Let's just open the base class to see how it looks.
All the class definitions are fully commented, so you can read the comments to see how they work. And as it explains on line 7 here, this accepts both MySQL Improved and PDO database results. But it does require PHP version 5.3 or later. Let's just scroll down to have a look at the constructor. Here it is on line 52. The constructor takes three arguments. The first one is a database result, and that is a required argument.
The two other arguments are optional. The second argument is a file name, and the third argument is an array of options. The code on line 57 passes the database result to the first protected method. Set result type. This checks whether the database result is a MySQLI result, or a PDO statement. A constructor checks two options, suppress and local. The suppress option, allows you to remove columns that you don't want included in the download file, and the local option makes the data export more flexible by creating a file on the local server, rather than downloading it.
By default, local is set to false, so files are downloaded unless you set local to true. If we move down a little bit further, we can see two more protective methods; buildSuppressedArray, and removeSuppressedColumns. These methods remove columns from the export file, if the suppress option is used. And further down, is the getRow method. This gets a database row, depending on whether you're connecting to the database using MySQLI or PDO.
And then, the last protected method is output headers. This generates the headers for the download file. And the code on line 141 adjusts the content type, according to which type of file you're downloading. And on line 142, it also adds the file name. So this base class is completely neutral. It will work with either MySQLI or PDO, and it can be used for any file type or file name. You can see from the line number at the bottom 147, the base class is considerable longer than the procedural code that we've been using so far, and it doesn't even generate the download file.
But it does have several advantages. It's not tied to a particular project, so it's more flexible. What's more, you never need to touch this code. The auto loader will load it automatically whenever it's needed, and the code you end up writing in your webpage will be a lot shorter and easier to understand.
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.