Insert or update data with SQL

Structured Query Language is the most widely used language for interacting with databases. Today we will learn how we can use SQL to insert or update data into MySql database.

For the purpose of our lesson we will use phpMyAdmin tool.

Basic usage of this tool is described in lesson where you will learn how to create a new database and tables.

Create MySql database with phpMyAdmin

We will use table from the previous step, described in lesson Create MySql database with phpMyAdmin.

Table contains the following fields:
id  (INT)
number  (INT)  
text  (TEXT)
date  (DATE)

New data is added to the database with SQL command INSERT.

You can see the syntax below:
INSERT into tabele_name VALUES('value1', 'value2', ...)

To insert data into our table we can use the following SQL statement:
INSERT into my_table VALUES(0,444,'my text','2013-05-10');

We have used value 0 for the first field named Id. Because this field was defined as Autoincrement, the actual value in database will be one higher then the current maximum value of the existing records.

There is also an extended syntax where we also use field names and uses the following form:
INSERT into tabele_name(field1, field2, ...) VALUES('value1', 'value22', ...)

In our case the SQL sentence would then be:
INSERT into my_table(id, number, text, date) VALUES(0,444,'my text','2013-05-10');

I would strongly recommend that you use this syntax, because in case of changes in the structure of database SQL sentence will still work as intended, which cannot be said for the first example.

For updating data we will use command UPDATE.

Basic syntax is the following:
UPDATE tabele_name SET field1='value1', field2='value2', ... WHERE condition

Because we usually want to change a specific record we need to add WHERE clause, where we define which record we want to change.

In our case the SQL command for updating would be:
UPDATE my_table set text='new text' WHERE id=4;

As you can see it is not necessary to use all field names.

We have now learned basic usage of INSERT and UPDATE SQL statements.

There also exists more complicated examples, but for developing most of the PHP web applications usage of these two SQL commands should be enough.

In case of additional questions you can of course ask on forum and you will surely get an answer.