Ready to watch this entire course?
Become a member and get unlimited access to the entire skills library of over 4,971 courses, including more Business and personalized recommendations.Start Your Free Trial Now
- View Offline
This course was updated on 6/12/2012.
- Understanding dynamic versus static content
- Adding PHP commands to web pages
- Setting and outputting variables
- Using server-side includes
- Creating PHP custom classes
- Adding the Zend Framework to a PHP installation
- Creating a MySQL database
- Adding data in phpMyAdmin
- Building recordsets
- Formatting dynamic data
- Building data entry forms
- Authenticating users
- Deploying a dynamic site
Skill Level Intermediate
Throughout the remainder of this video series, I'm going to be using a database that's built and stored in the MySQL database engine. I'll show you how to define a database from scratch first in phpMyAdmin, a web-based application that's included with both WAMP server on Windows and MAMP on Mac. Then in another video, I'll show you how to import an existing database from a script. To start up phpMyAdmin on Windows, go to the system tray icon, and then go to phpMyAdmin.
If you're working on MAMP with Mac, go to the MAMP homepage, and then click on phpMyAdmin. On the phpMyAdmin homepage, click into Create new database, and then type in the name of the database you want to create. Your database name should not have any spaces in it. If you want to represent a space, use an underscore character, type the name of the database, and click Create. You're taken to a screen to create tables. You can have as many tables as you want in your database.
Each table can have as many fields or columns as you need. In this initial database, I'm only going to have one table. It'll be named people. Set the initial number of fields. If you don't get this exactly right, it's no problem. You'll be able to add fields later on. I'll set my initial number of fields to 3, and click Go. On this screen, you're asked to define the fields or columns in your database. The first field should always be a primary key.
I typically set the name of this column with the name of the entity that's being stored in the table, such as person for the people table, and then I append Id to the end of it. So the name of my primary key column will be personId. In MySQL databases, it's very common to set the primary key column as an integer, and then to add the AUTO INCREMENT property. That means that each time a new row or record is added to the table, the next available numeric value will be assigned to the table automatically.
I first have to say that this is a primary key. You do that by setting the Index property to PRIMARY. Then I'll check the AUTO INCREMENT property. The other two columns will be named firstName and lastName. They'll both be set to variable character, or VARCHAR columns. VARCHAR columns can take text values. I'll set them each to VARCHAR. Then I'll set the available length for the columns to 50 characters each.
That means that that's the longest available value that you can put into the field. Finally, I'll set the Storage Engine for this table. The Storage Engine indicates how the table will be physically stored on disk, and also what its capabilities will be. The default is a storage engine named MyISAM. MyISAM is an incredibly fast storage engine. It's the default. When you set a table up as MyISAM, you don't get transactional capability - I'll give you an explanation of what that means in a moment - but you do get very fast storage and retrieval.
If you need transactional capability, meaning the ability to commit and roll back transactions, you can instead select the InnoDB engine. As with other settings, if you don't set this exactly the way you want it initially, you can change it later. I'll accept the default of MyIASM. Then, down at the bottom of the screen, I can click Save, or if I click Go, I can add additional fields or columns. I'll click Save, and that creates my table. On this screen, you're shown the actual SQL code that was used to create the table.
Also in the table structure, you can see what the columns are, their data types, and their properties. If you need to edit any of these columns, you can click on the Edit icon, represented by a pencil, and you'll be taken to this data entry form. If you click Save, you'll see the resulting SQL statement that was used to make the change. If you need to add fields, you can use the form down in the middle. You can add fields to the end of the table, or after any of the existing fields. If you need to completely drop the table, or delete it, you can click the Drop icon.
Be careful with this. If you drop a table or a database, there is no backup. You'll need to create it from scratch later. Once you've created the table structure, you're ready to add data to the table. I'll show you how to do that in a separate video.