August 22, 2013
So we got our Database (inventory) all set up. Awesome, now let's get down to some business with that son of a gun. Well in order to do any sort of constructive thing with these databases we're going to need Tables. Before I described tables as the spreadsheet in a excel program. This is because tables in MySQL work much like a spreadsheet, it has loads of rows and columns which we will define for our own purposes. Depending on how we name our columns and specify what type of data we want in those columns we can do all sorts of crazy biz.
At this point I would really hope you have phpmyadmin installed, otherwise we're going to have to do it the following way, which is probably going to be a huge headache for the both of us, but we'll get through. We will persevere! I'm just going to do this the hard way because creating tables in phpmyadmin (the preferred way to do this) is easy and doing stuff the easy way is for chumps. (Plus I'm sure you could just Google how to do it, or just click a few things and get it set up). I'll set up a phpmyadmin tutorial sometime after I make this post.
In our tables we can use a number of different types of data depending what we want in our table. Truth be told, what you put in your table doesn't have to just be numbers like in an excel sheet. You can put all sorts of information, even pictures and video if that's what you want. I can't imagine that database being fast or easy to use however, but that's just me, for some folks out there it might be easy. For our purposes here in the tutorial I'll explain just a few basic data types for you. You can alwasy look these up in more detail on the MySQL site.
Alright, we're going to have to sort out what exactly you want in this table before we create it. We need an unique ID number for each item of inventory, we need a name or description of the item, the price, and how much we have in stock or the quantity. In order to keep things from getting overly confusing I'm going to name my table stock and just have the number of that item listed as quantity.
Let's get it crackin'
CREATE TABLE tablename( column_1_name data_type attributes, column_2_name data_type attributes );
using the template above we can create the following declaration for our table.
CREATE TABLE stock( ID int NOT NULL AUTO_INCREMENT, Name varchar(255) NOT NULL, Price currency, Quantity int, PRIMARY KEY (ID) );
[Woah, woah, woah, what the fucking shit-pickles is happening here! You didn't explain half of that mumbo jumbo up there!]
Easy my young grasshopper, I will teach you right now. Let's break this down like a fraction.
So we need a unique ID for each fruit, so let's make it an integer, and one that will automatically increase with each entry that is made into the table. By putting the attribute "AUTO_INCREMENT" with our ID declaration we are able to make sure that the value of the ID will increment (add 1 to itself) with every new entry. This can be brutal because if you decide to delete a row from the table, the ID will continue to increment as if it were there. Say you have rows [1, 2, 3]. If you delete 3 and then add an entry, the next ID will be 4. Your set is now [1,2,4]. Say you delete the whole table, your set is now: , now if you add an entry your next ID will be 5, giving you a set . I'm displaying this horizontally, however rows in the tables will be vertical.
column | column | column row | | row | |
get the idea? [if not, i'll explain it better later when I go back and edit this post.]
Now you're probably shitting your pants wondering what the hell NOT NULL is because I didn't even mention it and yet I used it in my table declaration. The answer is simple, it makes it so that when a entry is made the value of ID and Name can't be empty, there has to be something there. Name won't check to see if it is unique, that is up to the user to do, and if there are duplicates they will have to go through and update the data, we'll get into that later. For now, NOT NULL, not empty, its just a way to make sure that certain values always show up so that they don't break stuff. Precautions if you will, covering your own ass.
This handy little thing determines what is the primary way of finding things, I like to make the ID the key because they are unique for every single row, they are never the same, this is important and should be a requirement for keys, they never, ever have duplicates. This is why we don't make Name the key, because we could have potential duplicates or names that are too similar and hard to remember, etc, etc. It would just be a bad day for everyone.
You could just put PRIMARY KEY as an attribute in your column declaration, but you could also do it like above, both get the job done.
Varchar can only hold a maximum of 255 characters, you can limit that to 5, 10, or 27, whatever you heart desires. For our purposes we use the maximum value since we don't know what the longest name will be. Why not just use the text data type then? That could lead to people possibly filling our database with a mess load of data, and we want to avoid that, plus using varchar will be a lot more lightweight in the end. Not as light as char, but the limit on char is 30 and more often than not we want more than 30 characters for our names.
Boom, you just created your first table, awesome, high five.