Kalkran.COM

In this mini-tutorial I will try and show you the basics of MySQL, Integrating it with PHP is an entirely different story and I'll get to that in another tutorial.

How does it work

This will be the hardest part of the tutorial, but also the most basic part, that's why I put it in the beginning. If this is all clear to you, then you'll have no trouble understanding the rest of the tutorial.

MySQL is a language (Okay, seems I was wrong, thanks Ilias) in which you can interact with tables that are stored in databases. You can store all kinds of data in tables, from numbers to text and passwords to images. Tables have a simple layout:

	Column	Column	Column
Row 1	(data)	(data)	(data)
Row 2	(data)	(data)	(data)

Every time you want to insert new data into your tables, you create a new row. These rows have different fields you can (should) fill in, called columns. Different kinds of data should go in different tables, a table layout for a default page with users would be something like:

• news - stores the posts on the front page
• comments - stores comments on news
• members - stores user information (username, password, websites, etc.)

The main thing about this part is to remember that ROWS are inserted and the COLUMNS are defined when you create a table. When you request information from your table through the SELECT statement (we'll get to that later), it will return rows. Let's look at a dummy table

	id	title	message
Row 1	1	DUMMY POST	Hello World
Row 2	2	Welcome!	Thanks for visiting

That's how I lay out my news tables usually. An id which is UNIQUE to each post, and increases automatically on each new post (AUTO_INCREMENT). The title, for storing the title, obviously, and message for storing the bulk of the post.

Creating tables

Creating tables is done using the (guess it..) CREATE statement, the statement that would go with creating the above table would look like the following:

CREATE TABLE `database`.`dummy` (
`id` INT( 16 ) NOT NULL AUTO_INCREMENT ,
`title` VARCHAR( 255 ) NOT NULL ,
`message` TEXT NOT NULL ,
PRIMARY KEY ( `id` ) ,
UNIQUE (
`id`
)
) ENGINE = MYISAM COMMENT = 'dummy comment'

Let's rip that apart. First up is the actual CREATE statement. It specifies the actual name of the new table.

CREATE TABLE `database`.`dummy` (

In this case, the database name is called `database` and the table we'll be creating is called `dummy`. Now there's a list of fields (columns) we'll be needing:

`id` INT( 16 ) NOT NULL AUTO_INCREMENT ,
`title` VARCHAR( 255 ) NOT NULL ,
`message` TEXT NOT NULL ,

We are creating three columns, id, title and message. The word / abbreviation after the name specifies the type of the column, of which there is a tiny list below. Then the number between the bracket specifies a maximum length. The NOT NULL says that the fields are not supposed to be empty, and the AUTO_INCREMENT after the `id` makes sure that the `id` increases with one with every new row that's being inserted.

• INT - Stores an integer, has a maximum 'length' of 16 bytes. Allows you to store almost indefinately.
• VARCHAR - Stores a string, with a variable length up to 255 characters.
• CHAR - Also stores a string, but it always has to be the length you specify
• TEXT - For long pieces of text, alternatively use LONGTEXT. Does not require a length.

Those are all the types I ever use.

Deleting and clearing tables

To delete a table you only need a one-line statement:

DROP TABLE `tablename`

For emptying however you have two choices, the first one will just clear all the records, but the second one will also reset the AUTO_INCREMENT value:

DELETE * FROM `tablename`
 
TRUNCATE TABLE `tablename`

Comments

Comments are, literally, just comments. They are ignored by the query, and are only there to make code more readable. I'm introducing comments here, so I can explain the code in-line, allowing you to easily see what each piece of code does.

MySQL has three kinds of comment, two of which start at the point of the comment-indicator and last until the end of the line, and one which lasts from where you open it and lasts until where you close it.

There is the # hash-sign, it is the first kind of comment, which lasts until the end of the line. Just like the -- double-dash sign. Please, be aware that the double-dash needs a space after it to work.

DROP TABLE `table` # Delete the table
 
TRUNCATE TABLE `table` -- Clear the table

And then there is the multi-line comment. You can use this inside of your query, but it makes things unreadable:

DELETE * FROM  /* This is an in-line comment */ `tablename`

This post is starting to get longer than I wanted, so I'll cover the SELECT statement, and then you'll have to wait for the next tutorial. Sorry

SELECT

This statement is used to retrieve rows from the table. A generic SELECT statement will look like the following (building on our `dummy` table again):

SELECT * FROM `dummy`

The asterisk just means everything. So this query says: SELECT all columns FROM the `dummy`-table. If you're not using all of the columns, you should specify the ones you will be using, as follows:

SELECT id, title, message FROM `dummy`

Then, you can also only select the rows that match one of your needs (say, only the row with the title 'Welcome!'):

SELECT * FROM `dummy` WHERE title='Welcome!'

The WHERE is case-INsensitive. Meaning it ignores upper- and lowercase. If you're not sure about the exact title (WHERE title='Welcome' will not yield any results, since the title isn't exactly that), there is the option to use WHERE LIKE, in this statement you can use the % percent-sign as a wildcard:

SELECT * FROM `dummy` WHERE title LIKE '%welcome%'

Next time:

LIMIT, Manipulating table data and more.

Miscellaneous, Tutorials