By using this site you agree to the use of cookies by Brugbart and our partners.

Learn more

Your First MySQL Table

How to create mysql tables, and a short introduction to indexes and datatypes.

Edited: 2012-09-13 19:09

Now that you know how to connect to the database, you are ready to create your main relation (aka table). We are going to name this table GuestBook_Posts, and try to use it for a simple GuestBook.

Example Table

We need a field with autoincrement, so that whenever we insert something in the Table, MySQL automatically assigns it a unique id. We can use this when retrieving posts from the table.

The mysql table we are going to create, is shown below.

PostID (auto_increment) Text Time
1 This is some Example Text 1240926050
2 This is some Example Text 1240926050

As you can see, the table above has two Example posts. The PostID is the unique id of the post, we are going to make this a primary index, which will help to improve the speed of our queries.

The time column is just the date of postage, measured in seconds, i use the time function in php in these examples. The time can later be transformed into "readable" dates with the date function.

Creating the Table

Use the below mysql query to create the table.

CREATE TABLE GuestBook_Posts
   (
   PostID INT NOT NULL AUTO_INCREMENT,
   Text TEXT NOT NULL,
   Time varchar(128) NOT NULL,
   PRIMARY KEY(PostID),
   INDEX(Time)
   );

The above is the clean SQL, to use this in PHP, one would need something like.

<?php
  include 'includes/Connection.php';

mysql_query("CREATE TABLE GuestBook_Posts
   (
   PostID INT NOT NULL AUTO_INCREMENT,
   Text TEXT NOT NULL,
   Time varchar(128) NOT NULL,
   PRIMARY KEY(PostID),
   INDEX(Time)
   )") OR DIE(mysql_error());


   mysql_close($Connection);
?>

Note that the Connection i showed in the first Tutorial, was included. And finally remember to close the connection using mysql_close($Connection);

Explanation

Perhaps its still to early for you to understand any of this, so you can skip the Explanation if you want.

Data Types

As seen, chose a type of INT for the PostID column/fields, The keyword INT is a synonym for INTEGER. The INT data type has a minimum value of -9223372036854775808 and a maximum value of 9223372036854775807

What is important about the datatype, is that we need to make sure we got room enough for what we want. In this case INT will be more then enough. There are also datatypes dealing with larger numbers, but i wont mention those here.

The next column is the Text column, for this i chose the TEXT datatype. TEXT can handle just about (65,000 bytes) or a maximum of 65535 characters (2^16 - 1).

The final column is the Time column, i simply chose to use varchar with a maximum length of 128, which likely will be more then enough to cover my own lifetime in seconds. The maximum value of varchar is 255, or (256 - 1)