Join David Powers for an in-depth discussion in this video Displaying the data in a webpage, part of PHP: Exporting Data to Files.
There are two basic pages that we'll be using to export data, cars.php and flowers.php. You can find copies of these files in the exercise files for this video. You also need to copy the images folder and the styles folder from the exercise files to your testing site. MySQL Improved and PDO use methods with slightly different names to fetch each row of a database result. So we need to create a custom function to make the code database neutral.
You can see that custom function in action here on line 17 of flowers.php. It's get row. It takes a single argument, result, that's our database result and it returns the current row. We can define this at the same time as including the relevant database query into each file. I'll start with cars and MySQLi, and then show you the code for PDO with the flowers page. So above the doc type, insert PHP block.
And inside that we include the file that we want, so it's require once. It's in the includes folder, and it's cars_mysqli.php that we want. So let's open cars_mysqli.php, and we can define the function at the bottom of the file on line 16, so it's function, get row, and it takes a single parameter, result, and in the body of the function, all that we need to do is to use the MySQLI method for fetching the current row and return it.
So it's return, and then we use the result object, and it's the fetch_assoc method that we use. So if we save that page and also save cars.php, and then go to a browser, you can test this, and it's locallost/phpexport and the file that we're looking for is cars.php.
And when we load that into the browser, we get a fictitious list of used cars for sale, which are being drawn from the cars and the makes table of our database. And if we scroll down to the bottom of the page. We'll see that there is a download results field there and download file button and what we'll be doing throughout the course is finding different ways of wiring up this download button, so that it exports and downloads to different formats.
So let's go back to our editing program, and cars_mysqli.php, just copy that function there, and if you're using MySQLI, you'll need to copy that into flowers_mysqli.php. Let's just copy that there and save that file. And then, we'll do the same with flowers.php. This time, I'm going to use the PDO version. So, we'll see how that's done. So, php code block.
Then, we require once. It's the includes folder. And the file that we want is flowers_pdo.php. So we'll save that file and we'll open flowers_pdo.php and at the bottom here we can just paste in that function, and for PDO, instead of fetch_assoc, it's simply called fetch. So if we save that and we go to the browser and change that from cars.php to flowers.php, we get a list of flower arrangements.
Again, they're fictitious flower arrangements. Drawn from the database, and they've got images, and at the bottom we've again got the field and the button for downloading, so let's go back to the editing program. We need to copy that and put that in cars_pdo.php, paste it in there, and save. Now the exercise files contain copies of all of these files. It's up to you which you want to use, whether you use PDO, or mySQLI.
Of course, in a real world application, the database results would normally be drawn dynamically from a search query. But to keep things simple, we're using hard coded queries. Which will allow us to focus on exporting the data to different file formats. And there's just one more issue to sort out and we'll be ready to start.
- Connecting to the database with PDO or MySQL Improved
- Outputting data into a simple text or CSV file
- Generating a spreadsheet
- Creating columns and headers
- Using a class to generate XML
- Creating a page template