Join David Powers for an in-depth discussion in this video Loading the test data into a database, part of PHP: Exporting Data to Files.
Before we can start exporting datas of various file formats, we need some data to work with. Although php is frequently used with a MySQL database, the export code that we will be working with in course, is not specific to MySQL. So the exercise files for this video, provide the test data in two formats. I've got the exercise files on my desktop. Let's just open that folder. In the chapter one folder. Add 01_01. And, you can see that there are two files in there.
The one on the right, phpexport.sql, has all the data ready for importing into a MySQL database. The one on the left, phpexport.db, is an SQLite database file. The simplest, option is the use the SQLite file. You don't even need SQLite installed on your server. All you need to do, is to copy this file, phpexport.db. To a convenient location on your computer, and that's it.
But hang around for a moment, I'll describe the data we'll be working with, after showing how to import the data into MySQL. Now, I'm going to be using phpMyAdmin, but you can use Navicat or any other method you prefer. I'm going to create a new database, called phpexport. You can use an existing database if you prefer. The names of the tables we'll be importing, are arrangements, cars, and makes. So let's create that new database, call it phpexport.
And then just click Create. The database has been created. We now need to create, a user account for it. So, click the Users tab, and then Add user. And the user name will be Exporter. We want to limit it to local host. I'm going to use a very simple password, lynda. And scroll down to the bottom, and click Go at the bottom right. That's added the new user. We now need to edit the privileges. So, click Edit Privileges for Exporter.
Then scroll down, we want database specific privileges, open that field there phpexport. And we only need the, select privilege so select that, and then click Go. We've now created the phpexport database and user account. Let's go to the phpexport database, and then click the Import tab. And we need to go to the exercise file, so File to Import, choose File, and I'm already in the exercise files, chapter one, zero one, underscore, zero one, and select phpexport.sql.
With that selected, scroll down to the bottom, and click Go. It says the import has been successful. And we can see now, in the left hand column, the phpexport database has got three tables, arrangements, cars, and makes. Let's look at cars first. This contains details of used cars for sale. And the make ID that matches with the make's table. So the two tables will be linked together. And the point about the cars table, is that it doesn't contain any images.
It contains text only. The arrangements table, this contains details of flower arrangements, and there's a column there for images, so we'll be using images later on in the course, to actually include them in our export files. And the data in the SQLite database, is structured exactly the same as this. So, now that we've imported the data, we're ready to query the database and display it in a webpage.
- 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