Your First MySQL Table
Posted The: 28/04/2009 At: 13:40
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 feild 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 primery 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/feilds, 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)
Comments: [0]
