Web Design/Dynamic Websites/Creating Database Tables

Web Design → Creating Database Tables

This page is part of the Web Design project.

Before we can start using databases to create dynamic sites, we first need to create a database table! Since most cheap hosting sites support MySQL as their database server, we're going to use a special program that's been written by someone else to help us setup and administer our MySQL databases... it's called PhpMyAdmin.

From the PhpMyAdmin homepage:

phpMyAdmin is a tool written in PHP intended to handle the administration of MySQL over the Web. 
Currently it can create and drop databases, create/drop/alter tables, delete/edit/add fields, 
execute any SQL statement, manage keys on fields, manage privileges,export data into various 
formats and is available in 50 languages.

To take part in this activity, you'll need to know:

the URL of your database host, as well as your database username and password. If you are unsure about these, ask your hosting company or your facilitator.
Alternatively, you can install XAMPP Lite or WampServer on your local computer as it has PhpMyAdmin already installed for you.

Getting aquainted with PhpMyAdmin

To start getting aquainted, there's a nice and simple PhpMyAdmin Tutorial that will get you started creating your first example table using PhpMyAdmin, and inserting data into it. Try it out and see how you find the experience!

After finishing the tutorial, you might like to reflect on your blog or elsewhere:

Describe why database tables usually have one field set as the primary key?
How is the Auto Increment option useful?,
When adding a field to a database, what other information do we need to specify?

Creating your own table

Once you've finished with the example, we're ready to create our own database table! Referring back to an HTML form that you have created in the past (one that collects information from users, say for purchasing a product):

write down the fields that will require to store this information in a database.
Using PhpMyAdmin, create a new table in your database with a meaningful title (relevant to the data you're collecting, such as orders or enquiries etc)
Specify the fields that you require to store your data (making sure that you choose the correct type, and that you've created a primary key etc).
Insert 4 or 5 lines of sample data to your new table, each time noting the actual SQL code that is being used to add the information into your database.

More on Database Types

To re-cap some and pull together some of the things you've learned so far, read through Using MySQL down to (but not including unless you're keen) the Basic SQL Statement.

For the moment, it's important that you:

Feel comfortable creating a new table in a database using PhpMyAdmin,
Correctly select the type for each field in your table using only the simple types (e.g. int, text, varchar, datetime, but you should be aware that there's lots of other types),
Create a primary key for each table and use the auto-increment option correctly,
Have a vague idea of what an SQL statement looks like (more on this later!)

Phew! Enough for one day?! Next, we'll be finding out how we can connect to our database from PHP!