Welcome again. In this article we?re going to dive into the wonderfull world of mySQL. I?m going to guide you step by step from creating a new database to as far as populating it with tables. To follow this article you should have PHP and mySQL already installed and properly working on your box. If you don?t have and you?re running a windows system then try www.foxserv.net and follow the instructions there. Ok, now you?re ready and set to go. Let?s take a look at mySQL at first before doing anything.
?MySQL is the world's most popular Open Source Database, designed for speed, power and precision in mission critical, heavy load use. ? says on www.mysql.com which is the official website of the Open Source database mySQL. Yes you heard right. It?s open source which means that you can use it for free! And it?s very good pal of PHP also. I would say that if you?re developing a web based system use PHP and mySQL. They are unbeatable together. The performance of mySQL beats any other database in common web use. Now you may wonder that can a free system be that powerfull? Well mySQL doesn?t have the advanced features which commercial products have but for web use?it?s nro. 1.
NOTE: this is just my opinion so don?t just take my word from it.
I suggest you take a closer look at mySQL at www.mysql.com and learn all the nice features it has. I urge you to download the pdf version of the manual too. It will come very handy when you start using it by yourself. You can get the manual from http://www.mysql.com/Downloads/Manual/manual.pdf so guide your browser there and download it. Let?s take a closer look at the manual now.
The mySQL Reference Manual is a huge ?book?. 757 pages of information about mySQL database system. You can take a quick look at it and check few sections now. Just to familiarize yourself to it. The most important thing is that you have a slight knowledge where everything can be found so when you need something you don?t have to read all the manual from the beginning. Most important part of this manual is the MySQL Language Reference. You can find all the SQL references there so keep that in mind when you face problems. But I think we have talked enough from the manual already. Let?s see next how me manipulate the database.
creating database
MySQL package doesn?t offer anything else than just command line interface to manipulate the database. And for most users that?s just not enough or it is too difficult to use. That?s why there are many great software?s to help you. I?m going to introduce you to couple of them. One is mySQL front which is windows based software. Also free like the mySQL itself. If I?m not totally mistaken the development of mySQL front has been discontinued but the latest version is still very good and very usefull. You can check the search engines to find it. The one I?m going to use in this article and suggest to all of you is phpMyAdmin. You can find it at http://www.phpmyadmin.net/ . phpMyAdmin is PHP based management system to mySQL databases. Also 100% free. It is being used via web browser so it?s very usefull in hosts too when you don?t have access to install any software to the host computer. I have always used this as my primary managament system and I?m very pleased with it. Download it right away and follow the instructions how to install it. I?m using version 2.3.0 at the moment and you might have a newer version but it?s ok. The basic functions are the same anyway. I want to make sure that I?m not going to give you a ground up tutorial here how to use phpMyAdmin. We?re just using it here to manage the mySQL database and to create a database and fill it with tables and data. So the more detailed look into the phpMyAdmin will be in another article. So now you have phpMyAdmin installed on your machine and you?re ready to continue.
So the first thing to get started is to create a new database. Your system can hold any number of databases so you don?t have to worry about that. I?m using a real world example here. So you can name things differently than I. Ok, so write the name you want a new database to be to the ?Create New Database? text box and press ?Create?. I used the name herrasmies (It?s Finnish). I have a custom that I write every database and table names at lower case and I suggest you do the same but it?s your choice. Now you have just created a new database! Congratulations. Now you have screen front of you which shows information of your new database. The Database doesn?t contain any data yet so you can?t explore it or export it either. But you can check the structure (which it doesn?t have yet) or execute SQL in it. This is very good feature in phpMyAdmin since every command you execute will be shown in plain SQL too so you can learn the same time and finally you can use the SQL text area for more advanced queries or some other SQL. So, what will we do next? We have to create tables inside the database. Now, this is the time where you have to dig up the plans of the system you are doing and to see what the database needs to contain. (you have them, right?)
creating table
It?s time to create some tables inside the database. You can create tables from the phpMyAdmin by just typing the name of the table to text box and putting a number of rows it will have. The row number is not to final because you can alter the rows after the creation too. Ok, I?m going to create a table called uutiset which means news. This is the table where all the news will go. It?s much similar to those in newspost scripts you?ve seen in many websites. Now, since I?m going to do very simple newstable to this database. And all I need to have there is the id number which is unique to every post in here. It?s always good to have a column like this in every table if you don?t have any other sure way to identify every entry. So I use an id. Id is a number which increases by one when a new post is posted. So I?ll put auto_increase to it so I don?t have to remember all the numbers by myself. Now we?ve dealt with identification and it?s time to start doing the rest of the columns. So what do we need to have here. I have a plan that several persons could post news so I need to have an author column as well. So I?ll put and kirjoittaja column which means author in English. Then I need a date and time as well as the news itself. So how do I make sure every post has the date and time fields in it. MySQL has a great field type called DATETIME which I use here too. It includes the date and the time of certain moment so that will do just fine. If you want to use only a date for the site then you choose only DATE as a field type. And finally we put a text field which includes the news itself. Now let?s take a look at these fields a bit closer.
a closer look
We have an id colum which has a type: smallint. What does that mean? First guide your browser to http://www.mysql.com/doc/en/Column_types.html or find column types from the pdf version of mySQL manual. Ok, so the smallint says the following:
A small integer. The signed range is -32768 to 32767. The unsigned range is 0 to 65535
So in this case we make sure that we use unsigned values because we don?t need negative values for the id number. Why use this small? Well, I think 65535 posts is quite enough in this case but if you need more you can use mediumint, int or even a bigint to be able to have more entries. Make sure you choose from the extra field the auto_increment! This makes it automatic so you don?t have to worry about it.
Next we have DATETIME type of field. This was the date and the time of when the post was posted. The manual says: A date and time combination. So this is perfect for our use. Remember: when you get the value to webpage with PHP you can get it in any way you want so don?t worry about the format of the field.
Then we had the author column where we used the TINYTEXT type. It allows us to have max 255 chars long text in it which is far more than we need because I don?t think anyone?s name is more than 255 chars long, don?t you think?
And final comes the text itself where we used MEDIUMTEXT type. It allows us to have 16777215 chars long text and in this case it?s just good for us. If you need more space then choose INT or BIGINT for larger space.
Now we have the columns almost ready. We just need to define the primary key. Primary key is a column which identifies the entry and it must be unique. So this is why we used the id column. Every table must have Primary key so make sure you have one. We are not going to go into indexes just yet because they are more advanced stuff but this will get you started and if you don?t have a huge database you don?t even need indexes since they only make your searches faster. Learn the column types well because they are important. So remember to read the manual like I said before.
