Creating Your first MySQL Table
MySQL Tables are like basic html tables that web developers use to display data with rows and columns. Or you can think of MySQL Tables like the data tables you had to create in science class before graphing your data.
Why do we use MySQL Tables?
We use mysql tables because that’s where all our data is stored. Many mysql database’s contain more then one mysql table that connects to the next to make things organized and easy to manage. We also need mysql tables because its a requirement in mysql or you wouldn’t be using mysql in the first place. The concept of mysql is a database storage facility. It’s not like any text file or any file where you store random data. MySQL is for organization.
Your First Database Table
In this example we will create our first mysql table of users and their age. Lets start out with a basic table layout.
Above is a basic table that we will create using mysql. Each mysql table and any table have two things alike the table rows and the table columns. A column in the above picture are “ID”,”1″, and “2″. They are vertical of each other. A row in the above picture is “1″,”Lamonte” and “17″. They are horizontal to each other. You should have learned this in your elementary years.
First we want to create a mysql tabling using the “CREATE TABLE tablename” syntax mysql provides us with. We know we need an “id”, “username” and “age” field. As you can see each user doesn’t have the same id? This is because if say we wanted to update something in the user row we want something unique that we can identify the user by for example, you could think of this as a social security number in the US. We want to always start at one though for each website we create to make things easier in ordering and organizing. You can also tell that the “Username” are words so we want this to be a string and the age is numbers so we always want this to be an integer.
Now lets create our basic mysql table:
CREATE TABLE `users` (
`id` INT(255) NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id),
`username` varchar(40),
`age` INT(3),
);
Explanation:
Now that we created out mysql table you are wondering what the heck have you created? Well its simple, but I’ll take it step by step explaining what we did.
1. CREATE TABLE `tablename`( when we create a mysql table we need to use specific syntax. CREATE TABLE is pretty much self explanatory its how you start off your code to create your table. Then its followed by the actual table name we created and then followed by a parenthesis to specify what block we are in.
2. Second line is our unique id(Identification number) we put `id` between back ticks because mysql has certain keywords you can and can’t use and to be safe you should always put back ticks around your column fields(names). Next you see INT(255) this is the max width that can be stored in the column. Sort of like a string’s max length. I will be making Tip about Integers and other number types for sql. After that you’ll see NOT NULL, this means this column will always have a value. NOT and NULL are two mysql keywords. NULL means empty and NOT NULL means the opposite. Last but not least you’ll see AUTO_INCREMENT. This means each id will be unique so it will add +1 to the id based on the last row inserted into the mysql table.
3. The third line is PRIMARY KEY (`id`). This means the id column will be unique and will error out if any id is the same. Therefore if any id was the same we would have duplicate users which we don’t want and editing profiles could be a problem.
4. The fourth line is the username column. Like we said before the username is a string but now we use VARCHAR instead of INT. After VARCHAR you set parenthesis with 40 again this is the maximum length of that column. Anything added more then that length will be chopped off and we don’t want that.
5. The last line is pretty self explanatory if you’ve understood the read of the explanation. Try guessing how it works :).
Introducing “mysql_query” PHP function
PHP allows you to execute mysql queries(mysql code) with ease. Using it’s basic function mysql_query which takes one parameter we can execute our code and our newly create mysql table will be create. Remember to always connect to your database before executing mysql queries. If you don’t know how to please read Part 2 of Mysql Starter Tut.
Example:
<?php
$link = mysql_connect(”localhost”,”username”,”password”) or die(”Couldn’t connect to db”);
mysql_select_db(”database”,$link) or die(”Couldn’t get active database”);
$query = “CREATE TABLE `users` (
`id` INT(255) NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id),
`username` varchar(40),
`age` INT(3),
);
“;
$query = mysql_query($query) or die(”There was an mysql error”.mysql_error());
?>
Above you’ve created your first mysql table. Like in part 2 we introduced the die function we do the same thing to make sure there aren’t any errors. I’ve also in the above code introduced the mysql_error function that php provides. This helps us tell what our mysql error was to simplify what the problem that occured.
Continue Reading
Stick around for Mysql Starter Tut - Part 4 - Where you learn how to insert mysql rows into the mysql table you created in part 3.