MySQL and CRUD
Before we discuss CRUD, let’s quickly review what we have covered so far. At this point you will have a good understanding of what MySQL is and what we use it for. You will also have a good understanding of what a database is, what a table is and how they are structured. In the last lesson you created a database called learningglue and created a table within it, called sections.
In this lesson you will learn how to Create, Read, Update and Delete records from a table, otherwise known as CRUD.
Inserting a Record:
To insert a record we use the INSERT command. The syntax is:
INSERT INTO table_name (column1, column2, …) VALUES (value1, value2, …);
Let’s insert a new record into our sections table created earlier. This table will contain the menu items, or sections, for our web site. Let’s start by inserting a new record for the courses section:
INSERT INTO sections (menu_title, position, visible) VALUES (‘Courses’, 1, 1);
Note that the value Courses is in single quotes whereas the position and visible values are not. The value Courses is a string and all string values must be contained within single quotes. The values position and visible are integers and do not require this special treatment.
Did you notice that we did not include the id field? Cast your mind back to when you created the subjects table, remember that the id column had an AUTO_INCREMENT property? This means that we do not have to include this when inserting records, in fact it is better that we do not include it as MySQL will automatically insert a new and unique number into that field. It is important that this field contains unique values as it will be required to uniquely identify each record in the table.
This INSERT query has inserted one record into your sections table. Later, when you create a web site that will interact with your database, this record will create a new menu item called Courses that will be in the first position (position = 1) and will be visible (visible = 1) o our users.
Viewing Contents of a Table
Now that you have a record to see, let’s look into the table and see what’s there. To do that we use the SELECT query and the syntax to select specific columns looks like this:
SELECT column1, column2, …
FROM table_name;
To select everything in a table, simply replace the columns with a single asterisk (*), like this:
SELECT * FROM table_name;
Let’s view all contents of our sections table by typing the following:
SELECT * FROM sections;
The result will be all records that currently exist in that table and you should see the following in your console:
mysql> SELECT * FROM sections;
+—-+————+———-+———+
| id | menu_title | position | visible |
+—-+————+———-+———+
| 1 | Courses | 1 | 1 |
+—-+————+———-+———+
1 row in set (0.11 sec)
Now that you have inserted a record into a database table and read that record back, let’s look at the remains parts of CRUD: update and delete.
Updating a Record:
To update an existing record you use the UPDATE query. Let’s update that first record to read Topics rather than Courses. To do this you first need to know how to uniquely identify this single record or else we run the risk of overwriting every record in the table. Remember that id field that we have set to auto_increment? Well, each record within our table will have a unique id value and all we need to know is which id belongs to the record we want to update.
We can use the SELECT * FROM sections query to get this, and since you have only inserted one record your id should be 1, however it is possible that yours may be another number but that’s OK, just make sure that you have the right id for your table.
We need this id number for a new clause called WHERE. We will use the WHERE clause to limit the records that MySQL will return to those that satisfy
The syntax to update a record is:
UPDATE table_name
SET column1 = value1, column2 = value2, …
WHERE condition;
Since you only want to update one field you will only have one column = value pair:
mysql> UPDATE sections SET menu_title = ‘Topics’ WHERE id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
If Query OK was returned then you have successfully updated the record with an id of 1 with a new menu_title of Topics, replacing the old menu_title of Courses.
Let’s check with a SELECT query:
mysql> SELECT * FROM sections;
+—-+————+———-+———+
| id | menu_title | position | visible |
+—-+————+———-+———+
| 1 | Topics | 1 | 1 |
+—-+————+———-+———+
1 row in set (0.00 sec)
There’s the proof we were looking for, the menu_title is now Topics. Now that we have mastered the first 3 CRUD’s – Create, Read and Update – let’s look at the final CRUD – Delete.
Deleting a Record
To remove a record from a table we use the DELETE command. It is vitally important that you uniquely identify the record we want to delete otherwise you run the risk of deleting everything in the table. The general syntax is:
DELETE FROM table_name
WHERE condition;
Let’s delete the record we just created, and to uniquely identify it we can again use the id field. You can use the SELECT command to retrieve the id if you are in anyway unsure, but since we have just been working on that particular record with the UPDATE query we should have the id fresh in our heads.
Input the following code to delete the record – my record id is 1 but yours may be different:
mysql> DELETE FROM sections WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
If your console returned a Query OK also, then you successfully deleted a record and you have now completed every CRUD operation – Create, Read, Update and Delete.
Your sections table is now empty. Before moving onto the next lesson, create the following visible records in the sections table in this order (position):
- Courses
- Services
- About
In the next lesson we will look at the relational tables.