Posted on: 4:40 pm, 19-03-2019

About MySQL

This entry is part 3 of 3 in the series Build your own CMS

So this is an important part. First of all, for me to explain how MYSQL works we need to make a database. Then I will show you how to manually fill the database with data so you can get the hang of how this works, and after that I will show you how to do this with MySQL queries. These queries are necessary because when we use PHP to interact with the MySQL database we have to use queries. How this works will become clear shortly.

We’ll begin with creating a database. To do this you need to log in to your Web Hosting Control Panel, in my case: DirectAdmin. Every web hosting control panel differs, so you might want to google something similar to ‘DirectAdmin how to create database’, but in DirectAdmin it looks something like this:

Create a database

Once you did this you need to login to the phpmyadmin part of your web hosting control panel. In DirectAdmin you can just click phpMyAdmin below ‘Create new Database’. When being redirected from here, you should automatically be logged in. If not, you can either use the login credentials you got from your webhosting provider, or use the login credentials you just created along with your new database. The login credentials that you got from your webhosting provider will give you access to all the databases you create while the latter wil just give you access to the corresponding database. Anyway, when you are logged in you should see something like this, with on the left side below ‘information_schema’ the name of your database:

PHPMyAdmin (SQL)

Now that we have created an empty database, it is time to fill it with tables containing data. Let’s click on our database and start working. To make things more convenient I’ll use the textbook example of a table containing students and some information about those students. So let’s call the table ‘students’, make the number of columns 5 and press start. Next, fill it in like this:

Creating a table

What is important to notice is that every column has a name and a type. The name will make it possible to refer to fields in this column. The type makes sure all data entered satisfies the chosen data-type. For instance, if you choose the type of a column to be INT, it will make sure only integers can be inserted in these fields. Also notice the checked A_I box, which (in this context) stands for Auto Increment. This means that every time you make an entry for this table, you won’t have to fill in a value for ID because it will be automatically incremented by 1 from the previous entry. Almost always it is important to make a column like this, so every input has a unique ID. When you’re done you can press Save and an empty table will be created.

Now to fill in some values. Click your new table in the menu to the left and press Insert from the top menu, fill in some values and press Start. Remember to leave the ID field empty because this will be automatically filled in. When start is pressed you will be automatically redirected to the SQL part from the top menu. What you see here is the query for the data you just filled in, but we will get to this in a second. Repeat this step a few times with random values and return to Browse from the top menu. You should be seeing something like this:

Table

These are your entries for now. This is pretty much it: a table in a database. A database can contain multiple tables. Anywho, on to the SQL queries. Apart from the manual insertion we just did, there is another way to insert data. Namely by using a language created specifically for this purpose. There are a lot of commands you can use but we are mainly going to use four of them: INSERT, SELECT, DELETE and UPDATE. I will just be explaining the very basics of these commands, nothing fancy. If you want some advanced SQL queries you can look up some tutorial on youtube but this is highly unnecessary for what we’re trying to achieve.

To start off click on SQL from the top menu. Remove all text in the text area and type the following: INSERT INTO students VALUES (”,’James’, 10, ‘male’, ‘math’) and press Start. This query inserts a new row into the table. Notice that students is the name of our table, and that the values must be in the right order. Also, empty fields like ID must still accounted for by inserting an empty string. If there is a column in your table that you want to leave empty, you also need to insert an empty string when adding a new row. This is because your database expects 5 value entries when there are 5 columns. If there are less (or more) than 5 input values, the query will not execute. Also notice that the integer doesn’t necessarily have to be in between quotation marks, for it is not a string.

Now let’s look at the SELECT command. If INSERT inserts data in the table, SELECT retrieves data. Type the following in the text area: SELECT name FROM students WHERE sex = ‘female’ and press Start. You might already have guessed that this query returns all names from the students table where sex is set to female. Notice that female is in between quotation marks because it is a string, and the name of the columns and table don’t need to be in between quotation marks. But maybe instead of retrieving the names (or age, for that matter) of all females, you want to retrieve all information of all students that are female. A query for this would be: SELECT ID, name, age, sex, subject FROM students WHERE sex = ‘female’. But this one can be shortened to: SELECT * FROM students WHERE sex = ‘female’, where the asterix (*) simply means ‘all’.

The DELETE and UPDATE queries respectively delete and alter rows. A query for DELETE would look like this: DELETE FROM students WHERE age = 12. This will delete all rows that have age set to 12. Mostly you will only delete rows based on ID because this makes sure you only delete one single row. A query for UPDATE would look like this: UPDATE students SET sex=’male’, age=10 WHERE ID=4. After the UPDATE command you tell the database in what table the change should take place: students. Then you tell the database which columns need to be set. In this case: sex and age. Then you tell it in what row the changes should take place. You can update multiple rows at once. Say you want to change the subject ‘math’ to ‘calculus’, you can just type: UPDATE students SET subject = ‘calculus’ WHERE subject = ‘math’.

These are some very basic queries, but for this tutorial it literally won’t get any harder than this. So if you understand the syntax of these examples you are golden.



« |

Leave a Reply

Your email address will not be published. Required fields are marked *