Introduction

At the beginning of these tutorials, I will provide the basic syntax needed to do what our objective is. It will be generic, but it will be a nice overview into what you need to follow when writing your own SQL. Data that you will need to alter or insert will be surrounded in < >. I’ll be following the format found on the MySQL website. Content in [ ] are usually optional – as in the PHP website regarding functions.

SQL

SQL syntax has a huge advantage as it is EASILY readable by the human eye. There are no hidden secrets. It’s pretty much writing in english what you want to do. MySQL has some reserved words, which I won’t list in a list, but if you see words in capital letters, then you should understand that this is a keyword in MySQL. For example, I will put two queries below that perform different functions. I’ll then identify the components afterwords.

SELECT * FROM `news`

SELECT `id`, `author` FROM `news` ORDER BY `id` DESC

Now I hope I didn’t confuse you. We’ll study these queries in later tutorials, but their main purpose is to pull data from a database. First we say to SELECT the data. The next thing we write is what we want to select. In the first one, I used an asterix (*) which means all. I want to select all data. The second query, I specified fields (columns) I wanted from the database. It is best to select columns if you are not using all the information. This will speed up your queries. My third step is to tell it FROM. So up to this point, I am saying SELECT [data] FROM…

For this example, I have a table called news. I want to SELECT [data] FROM [tabdle]. The first example will return data in the way it seems fit, usually the first in is the first out. The second one, I tell it how to ORDER BY. I am telling it to sort the data to my liking. DESC means descending.

Creating that table

I strongly advise people new to SQL, and creation of tables to use the GUI editors, like PHPMyAdmin. These editors make it a simple set of clicks to create the table. But before we actually build our table, we need to build a structure – on pen and paper. We have to know what we want to store in our database. So for the remainder of this tutorial, our focus will be on setting up a blog. Our blog will be very simple, and you can easily expand on it. It will be remaining simple for the simplicity of learning.

What do we want to be in our blog? We’ll need basics like the blog post, author, and the time it was posted. We could create a more detailed system to include categories, and such, but let’s keep it simple. We will also need another column that no one sees. We’ll call it “id”. This will help keep each row unique, and will be great for ordering content. Many tables had an id field, which upon a slightly more advanced approach, id’s can be used to join tables together. We’ll get to that later.

Now we know what we want to have in our database, we have to know how that data will look like. id will be a number (more specifically, an integer (INT)). Our author’s name will most likely be under 255 characters, so we can safely say that our author field will be a VARCHAR of 255. Our blog post can be really big, or it can be really small. We’re not sure until the author writes it. To play it safe, we’ll make this one a BLOB. Let’s say we want to capture the date in seconds via the PHP function time(). So we know that will be an interger (INT), but we will make it a VARCHAR as the time will become a very large INT.

If I lost you on the capital words, it’s okay. Columns inside tables have data types. These types are based on what you want stored. This is a critical part of designing a database. For example, VARCHAR can hold up to 255 characters. But, just because it can doesn’t mean we should allow it to. Let’s say we wanted only a “yes” or “no” answer. Our biggest answer is 3 characters, so we would create a VARCHAR of 3 characters. Placing limits on the columns will also prevent unwanted entries. You can read up more on datatypes on the MySQL website as it is really a whole lesson that can be devoted to just data types. The most common ones I would say are INT, VARCHAR, TEXT, and BLOB.

Let’s build that table now

In PHPMyAdmin, you’ll want to create a database. There is an input box to create a database. Type a name in there and hit enter. You’ll now be in your database. If you have one already set up, or want to switch to another database, use the drop down menu on the left side.

To create a table, we will follow the same format pretty much. There will be a input box for the name, which we can call blog, and then it will ask for how many columns. For our example, we want 4. It will also ask you what kind of database we want – select InnoDB. Hit enter. We will now be brought to a screen where we can insert a lot of information. Under “Field”, type our four column names: id, blog, author, dateposted.

Also, change the types to what we chose. INT, BLOB, VARCHAR, VARCHAR respectively. INT and VARCHAR need a length specified. Let’s put 11, ,255,12 respectively. Remember, BLOB does not get a value. Under “Extra”, select “auto_increment” for the id field ONLY!

Click Save, and you should see a success message with the SQL that it ran to create the table. As I said, it is pretty straight forward, and can be read without deciphering anything. Let’s take a look at our SQL.