MySQL can be quite confusing when you first start learning it, but the truth is, once you get the basics down, it can be quite easy. MySQL is used in conjunction with PHP quite often, and they work flawlessly together, that is what PHP is meant for, database driven sites. So if you use PHP, then you should also be using MySQL. Learn from the beginning, on how to use MySQL on your sites, and start creating dynamic sites now!

At the end of this tutorial, I hope you will have the basic understandings of how to, create a table to store data in, how to insert data into your tables, how to delete data from your tables and how to update data in your tables. These are three very principal tasks when using MySQL, such as, adding a new user, deleting a user, and edit an existing user (in theory).

For a user to execute SQL queries, you need a means to do so. One of the most popular ways of executing SQL queries is through PhpMyAdmin. This is a very popular MySQL database management script that most web hosts offer, if you are using MySQL directly installed off of your own computer, you will most probably be using a MySQL command prompt, both work in the exact same way.

When using MySQL there are few things you should learn, in the way that data is organized.

First there is the database, this is what stores everything, in a database there are tables. Tables are used to organize the type of data stored, such as a table named users will most likely store information about users on a particular site. Tables can be named anything your heart desires. In a table, there are rows and columns. Below is an image demonstration of how this is all put together, hopefully it will explain this better to you -

Descript of database organization

Alright, so from my explanation and picture, you should hopefully already have a website (or have MySQL installed on your own computer)

. So the next step, is to create a database.

If you have your own web host, usually in your control panel for your site there will be a MySQL database area, where you can create new databases and users for those databases. This is a bit hard to help you with, seeing as how there are so many hosts offer different control panels, the most popular of them being cPanel though.

Once you get your database created (which is not very hard). Now we can get onto the actual MySQL work, so our first step will be to create a table.

Let's say we want to create a table that stores links in a database. This is a pretty simple example, we will need to store the URL of the link, the link name and lets say for each link we will add a title for the link. Also, we will want each link to have a unique ID, so in our scripts they are easier to reference.

PLAIN TEXT
MySQL:
  1. CREATE TABLE links (
  2. id INT(11) AUTO_INCREMENT NOT NULL,
  3. name VARCHAR(30) NOT NULL,
  4. url VARCHAR(70) NOT NULL,
  5. title VARCHAR(50) NOT NULL,
  6. PRIMARY KEY(id)
  7. );

This is a SQL query, this creates a table. As you can see MySQL is almost like reading english. It reads all pretty straight forward (like a caveman was reading it).

First off, I would just like to say you do NOT need to capitalize certain parts of your query like I have, I do this to make it easier to read.

So first you define that you are creating a table, this is pretty obvious and straight forward, you name it then open your statement with a (. Next is where we define our fields, each field has a name and a type. So as you can see with our name row, we define it with VARCHAR(30). VARCHAR means that you want this field to store short bits of text, the max length a VARCHAR field can store is 255 characters, but we set our max to 30 in my example. I doubt anyone will have a link name of more then 30 characters, try to keep your max to a minimum, when querying your table later on it will go faster.

I skipped the id field at first, because it needs a whole section to explain :). First off, since we want a unique id identifier for each link we insert, the name 'id' seems pretty fitting, now our field type is INT (integer), and we want it to have a max of 11 numbers in it. So as long as your id numbers are below 11 numbers long (ex : 12345678912) then we are good! Now, the AUTO_INCREMENT part, this says that for each new link inserted, the next id number will be inserted automatically, and it will be incremented from the last one inserted. This way, you don't have to insert the id number yourself, it does it automatically.

As you can see on the end of each field, we have set a NOT NULL, this means that when we insert a new link this field can NOT be left empty, so it always has to have a value entered when you insert a new link.

Ok, now that you have the basics of creating your table, we are going to want to insert some links into it!

PLAIN TEXT
MySQL:
  1. INSERT INTO links (name, url, title) VALUES ('TutorialCode', 'http://www.tutorialcode.com', 'Tons of great coding tutorials!');

This is pretty simple, first we say which table we would like to insert a new record (link in our case) into. Then in our first set of brackets, we define the fields that we are inserting data into, the order of these actually does matter, they must match up to the data in your values set of brackets. When defining your fields, there are NO single quotes ( ' ), each field is separated by a comma. Next you write VALUES, this means we are starting to insert some actual data. Each piece of data is encased in single quotes, (only if it is a string, if it is a number you do not put the single quotes), and each piece of data is seperated by a comma again. So in our example as you can see 'TutorialCode' matches up with the name field of our query, and so on and so forth.

If you noticed, in both our create table query and our insert query, we end it with a ; - this is to say that your query is finished, and not to expect more.

Now, let's say we made a mistake and want to change something in a link that is already added. We want to use an UPDATE query.

PLAIN TEXT
MySQL:
  1. UPDATE links SET name = 'Coding Tutorials' WHERE id = 1;

This is really easy, we want to update our links table, and set the name of our link to just Coding Tutorials, now we have to define which link we want to update, if we didn't it would set all our links names to Coding Tutorials (even though we do only have 1 still). So we use a WHERE clause, we want to only change our link name where the id of the link is equal to 1. Which is the first link we entered into our table.

Now, that you can put data into your database and edit it, what if you wanted to erase data? So from this example we will be erasing our link from our table, then finish by erasing our table from our database completely.

PLAIN TEXT
MySQL:
  1. DELETE FROM links WHERE id = 1;

Deleting something from our database is equally as easy, just use our WHERE clause once again, and delete our only link from the database, oh no!

PLAIN TEXT
MySQL:
  1. DROP TABLE links;

And this final query, will totally drop this table from your database.

MySQL is a bit confusing in the beginning for any user, but once you get the basics, it is very easy to understand.

If you have any questions just leave a comment.

Share and enjoy our tutorial : These icons link to social bookmarking sites where readers can share and discover new web pages.
  • Digg
  • StumbleUpon
  • del.icio.us
  • Fark
  • Furl
  • Slashdot
  • Netscape
  • BlinkList
  • Ma.gnolia
  • Reddit
  • Technorati