Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
In the exercises in the remainder of this video series, I am going to use a database that I have defined in an SQL script file. If you don't have access to the exercise files, you can find a copy of this database script in the free exercise files available on the lynda.com web site with this video series. If you have access to the exercise files, you'll be able to import the database quickly and easily in phpMyAdmin. You'll find the SQL file in the 04_createdatabase folder, under Exercise Files.
It's named explorecalifornia.sql. You can open this up in any text editor. I am using TextPad on Windows. I am first going to walk through the database structure and show you how the database is defined. In the top of the SQL file, there are some comments that indicate the character set, connection, and names. Then the table structures and data are added. For each table there is a DROP TABLE command and then a CREATE TABLE command.
This allows you to run the script, and it will wipe out any existing versions of the database tables and then create the new ones. The first table is named admin. It has three columns, called adminId - the primary key - userName and password. userName and password are both variable character columns. The primary key is adminId, the engine for the table is MyISAM, and then the character set and AUTO_ INCREMENT properties were set. The AUTO_INCREMENT is always set to the next available numeric value for the table.
There is one Insert statement that adds a single row to the table. The primary key is set to a value of 1, and so the next available primary key would be 2. So that's the admin table. I'll be using that in exercises about authenticating users on databases. The next table is named explorers. Once again, it has a Primary Key column. This time it's named explorerId. Then it has a whole series of columns, such as firstName, lastName, and so on. Most of these are variable character or text columns.
There is one column though, named dob, for date of birth, that's set as a date column. There are three existing records in the explorers table with primary key values of 1, 2 and 3, and so the AUTO_INCREMENT is set to 4. The next table is named packages and refers to tour packages. It has a primary key named packageId and three variable character columns. And then there are 9 records names Backpack Cal, California Calm, and so on.
If you are setting up this database from scratch, you can add any values you want into these columns. The next column is named states, and it's a list of the states of the union. It has two columns, named stateId and stateName. The first column is set as the primary key, but in this case there is not an AUTO_INCREMENT column because it's a text-based column. With the states table, the first value in the each row is the two-character abbreviation for the state, and the second column is the name of the state.
There are 51 records in this table, including 50 for the states themselves and one record for the District of Columbia. I'll be using this table in an exercise about data entry forms, and I'll show you how to take the data from this table and display it in a pulldown list that lets the user select a state. I'll scroll down a bit to the next table, which is named tours. The tours table has a primary key of tourId and a packageId, which is a foreign key.
This column, packageId, lets you relate the table to the packages table, using a primary key-foreign key relationship. Most of the other columns in this table are variable character or text columns, but there is one column named price which is set with a data type of double. A double is a numeric value that allows fractional values. The double type is very commonly used in MySQL to represent currency values. There is another column that's not text-based. It's called length, and it's in integer value.
This is the number of days for each tour. And then there were 20 something records that are provided for that table. So that's a look at the database structure. I'll show you how to import it. I'll go to phpMyAdmin. Before I can run the script, I need to create a new database, so I'll click into the Create new database field and type in "explorecalifornia" and then click Create.
Once I've created the database, I can import the script. I'll click the Import button at the top. Then I'll Browse and select the SQL file from the 04_ createdatabase folder of the exercise files. After selecting the file, I'll click Go, in the lower-right corner. It takes only a second or two to execute the script and create the database structure and data. I'll click into the first table admin, and show you that there is one row, with a userName of explorerone and a password of password.
I'll click into packages and show you that there are 9 records, states, and show you that there are many records for the various states, and tours.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 98370 Viewers
61 Video lessons · 85664 Viewers
71 Video lessons · 69601 Viewers
56 Video lessons · 101924 Viewers
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.
Your file was successfully uploaded.