Welcome to this first SQL tutorial of Combined Minds. In this tutorial we're going to talk about Structured Query Language, or simply said Databases!

First let's learn more about the basics of databases. What are databases exactly?

Well, databases are big virtual books that can store all kinds of information. Whether it's your phone number, your name or even whole textbooks! Sometimes a database can run up to gigabytes full of text and numbers.

In PHP you can access various types of databases. There is PostgreSQL which is very powerful on huge databases, there is SQLite which is good for small databases and mostly known is MySQL. Of course there are much more databases but these three are most common.

In this tutorial we're talking about MySQL since most of the webhosts you'll work with will have MySQL installed.

Connecting to a MySQL database via PHP

Connecting is very simple, but it can give beginners problems. They often forget to create a connection to the server before sending requests. Also people tend to make a connection multiple times per script session. Only once in the script you should create a connection, even if you include files afterwards they will be able to use the connection made earlier.

To make a connection you need four parameters, which are:

Hostname of the Server - Most of the time the server is on the same machine so you can use localhost
Username for SQL server
Password of user
Database name you want to work in

The code to connect in PHP:

php

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

<?php   // Open connection $sql = mysql_connect('hostname', 'username', 'password');   // Select database mysql_select_db('databasename', $sql);   // A cleaner way is the following:   // Open connection, and kill the script when no connection is possible // or die(mysql_error()); kills the script and gives a nice debug error $sql = mysql_connect('hostname', 'username', 'password') or die(mysql_error());   // Select database mysql_select_db('databasename', $sql); ?>

A good working example:

php
1 2 3 4 5 6 7 8	<?php   $sql = mysql_connect('localhost', 'jim', 'passeh') or die(mysql_error()); mysql_select_db('Combined_Minds', $sql);   ?>

Isn't that easy? :>

Creating a new database

Creating a database isn't difficult at all. The only thing you need to think of is the name. Of course your name has to be simple and logic, so don't call the database for your blog "database 1" but "domain_blog" or something. It may sound obvious but believe me, many people use stupid names!

Ok now to create a database we'll use the following SQL code:

php
1 2 3	CREATE DATABASE cm_blogsystem

Now I hear you thinking (seriously I do), where to place that code?

Well for that we need to make a SQL Query, or simply call it a SQL request. (well, I call it a request...)

We can do that with the mysql_query(); function in PHP. Again with this function, we'll add or die(mysql_error()); behind it, so when there is an SQL error we can easily find the problem.

php
1 2 3 4 5 6 7 8 9 10 11	<?php   // Include a PHP page with contains a connection to the SQL server include 'sqlConnection.php';   // Create database mysql_query("CREATE DATABASE cm_blogsystem") or die(mysql_error());   ?>

As you can see, I've included a file which i named sqlConnection.php. I've placed the following code in it:

php
1 2 3 4 5 6 7	<?php   $sql = mysql_connect('localhost', 'jim', 'passeh') or die(mysql_error());   ?>

I don't have a database yet, so omit that line while you don't have a database yet. After you've created the database you must add the line of code to select the database "cm_blogsystem".

Now we have our database, let's go on with the following part of this tutorial, creating tables.

Creating a table

Everybody has made tables once, whether it is a database in Microsoft's Access or in OpenOffice.org Spreadsheet. (keep the open source alive!) When creating these tables every column holds other data, col1 may hold a number indicating money, col2 may be some characters becoming a persons name and col3 may be some characters becoming the date when the money in col1 is used.

In (My)SQL you need to specify the datatype of each new column. In this tutorial I do assume that you know the difference between a string ("abd75_*") and a number ("1224")....... Ok for the beginners, a string can hold everything, while a number (called an Integer in the programming world!) can only hold numbers.

Here a little list of datatypes:

Characters
CHAR( ) Has a fixed length between 0 and 255 characters long.
VARCHAR( ) Has a variable length between 0 and 255 characters long.
TEXT Can hold text up to 65535 characters

Numbers (Integers)
TINYINT( ) Can hold a number between -127 and +127
MEDIUMINT( ) Can hold a number between -8388608 and +8388607
INT( ) Can hold a number between -2147483648 and +2147483647

These datatypes are the most common, there are many others but you won't deal with them too often. If you are interested in other types, just Google "mysql datatypes".

Now before creating your new table, you first have to check what kind of datatype you need to use.

- When you want to store a username, it won't be longer than 255 characters, so you can use a VARCHAR for that one.
- When you want to store his age, you can go for TINYINT because people older than 127 wont know what the hell a computer is.
- When you want to store the number of posts a user made on a forum, you can use a mediumint, because nobody will ever make over 8388607 posts. (I hope)

That's quite simple isn't it?

Now in MySQL, lets create the table we just thought of. I want to call my table "cm_users".

php
1 2 3 4 5	CREATE TABLE cm_users ( username varchar(30), age tinyint(2), posts mediumint(9) )

This is the most easy table you can think of. There are much extra options you can add to your freshly made table, but that would be too much just for the first introduction to MySQL.

You can see I added a number between the datatype of a value. I don't want my users to have a nickname higher than 30 characters, with the number i can limit it to 30. Also I don't want to use a font of 150px so I only want users to be max 99 by limiting the length to only two digits.

Now let's use this code in PHP. You can make the table via other methods like PHPmyAdmin or Navicat, but in this tutorial we'll do it the uneasy way for you to learn best!

php
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16	<?php   // Include a PHP page with contains a connection to the SQL server (and database!) include 'sqlConnection.php';   // Create a new table! mysql_query("CREATE TABLE cm_users ( username varchar(30), age tinyint(2), posts mediumint(9) ) ") or die(mysql_error());   ?>

And done! Your new table is made!

Inserting new data in our table!

Now lets move on the fun part, filling the table!

When inserting new information to the table, we must keep in mind that we used datatypes. When we'll try to add "eighteen" to the column "age" we would get an error. The column age is a TINYINT which only accepts a number between 0 and 99!

So lets say we want to add a person named "Bob" to the user table. Bob is an old fat programmer, one that only likes to communicate via the internet. Bob still lives with his mommy even though he's already 40 years old! (Okay sorry I got carried away)

So lets compare our information about Bob against the table.

Username (varchar max 30)
Bob - Passed

Age (tinyint max 99)
40 - Passed!

Posts (mediumint max 8388607)
5000 - Passed!

Ok so the data we want to insert seem to be in a good form (datatype). Now lets use that information to send to the MySQL server.

php
1 2 3 4 5 6 7 8 9 10	<?php   // Lets say we got the information via a POST form $username = 'Bob'; $age = 40; $posts = 5000;   mysql_query("INSERT INTO cm_users (username, age, posts) VALUES ('".$username."', ".$age.", ".$posts."") or die(mysql_error());   ?>

Ooh, that's a tough one. First you see we are filling in the variables, I assume you understand that (or we have a big problem). Next is the SQL Query, this one is quite difficult for beginners so we'll tare it apart to examine it closely.

First, I want you to know that what you send to server with mysql_query is not:

"INSERT INTO cm_users (username, age, posts) VALUES ('".$username."', ".$age.", ".$posts.")"

This is a string with PHP variables in between. What the server actually receives is:

"INSERT INTO cm_users (username, age, posts) VALUES ('Bob', 40, 5000)"

This line should be easier to understand. You can see the information we specified in the variables in this line.

Notice the quotes (') around the text Bob. This is because Bob is a string and not a number. A string should always have quotes around them. It's not necessary for numbers.

You might also think ". looks kind of weird, that's because we're switching from a string to a PHP variable. It does not make sense to just write variables through the text.

Now lets go back to the general look of this SQL line. Take a look at:

(username, age, posts) VALUES ('".$username."', ".$age.", ".$posts.")

You see I specified the columns I want to fill in before actually sending the information. It happens often that you only want to fill in a few of all columns. To keep you from mixing up the data, you first tell which columns to fill in.

Sometimes people write to the database without first setting the columns. That does work, SQL will just fill in the information to the table from left to right. But this is known as bad practice, sometimes the table will change over time, so the SQL query in your code can (and will) give you a lot of problems.

Some ending words

In this tutorial you were introduced into some MySQL. You know how to connect, create a database, a table and how to fill in the table.

On purpose I haven't added information on how to read, update and delete records in the database, that will come in a next tutorial. Now register to my website, tell me what you thought of the tutorial and practice what you've learned today!