Creating tables

The very first skills to learn are how to create tables in a database. Once you have your phpdb database created and you have typed "USE phpdb", you are all set to start entering queries to create tables. First, though, type "show tables;" - this is the command to make MySQL output a list of all tables in the currently selected database.

So, it is time to get stuck in with our first piece of SQL - how to create a table. Here is the SQL code:

CREATE TABLE usertable (ID INT, FirstName CHAR(255), LastName CHAR(255), Age INT);

The above SQL instructs your DBMS to create a table which you will reference as usertable, and then defines the four fields of data you want to store in that table. If you look back over the data types from earlier, you will recognise that the first field, "ID", is an integer, integer, the second and third fields both hold fixed-length character strings of 255 characters, and the last field is another integer.

Author's Note: Very often people format their SQL code so that each individual part of it is placed on a new line and indented to match the previous line. This can help readability - choose what works for you.

Once that query is executed, MySQL will attempt to create the usertable table for you, with those four fields. If it exists already, or if you somehow do not have permission to create such a table, it will report an error.

You should notice that MySQL outputs "Query OK" first, which tells us that the query executed just fine - as expected. However, if you try running the same query again, MySQL will flag an error up because usertable already exists and therefore cannot be created again.

Once a table is created, you can run "DESC usertable" to have MySQL print out a small report about the fields in the table. It will show the following information:

Field - the name of the field
Type - the data type
Null - does this field allow null information?
Key - is this field a key?
Default - what is the default value of this field?
Extra - stores comments about each field

You can also use CREATE TABLE foo LIKE bar, where "bar" is an existing table - it will create foo as a copy of bar's table structure.

Want to learn PHP 7?

Hacking with PHP has been fully updated for PHP 7, and is now available as a downloadable PDF. Get over 1200 pages of hands-on PHP learning today!

If this was helpful, please take a moment to tell others about Hacking with PHP by tweeting about it!

Next chapter: Making table changes >>

Previous chapter: Interacting with MySQL

Jump to:

Home: Table of Contents