(To make the most of this tutorial, I suggest creating the table “employees” from the previous tutorial, by running the SQL code from within PHPMyAdmin or equivalent database management tool)
Now that we’ve learned how to retrieve data from a database table and print it out to screen, let’s move onto the next logical step - writing our own data into the table, for future queries to access.
This process is fairly simple, and requires very little in the way of additional code. In fact, in some ways, it’s simpler than a SELECT statement, since we don’t need to do very much with the returned value. An INSERT or UPDATE statement will only return TRUE on success, or FALSE on failure.
One thing to take into account when running UPDATE or INSERT queries though, is security, since we are allowing new data into our table, and we need to ensure that the data we let in has been processed and cleaned up sufficiently as to not cause any formatting or display errors when we try and view it later. This concern will be outlined further on in this section, but for now, lets just look at updating our table with the UPDATE keyword
Consider this scenario. One of our employees, let’s say Jim, is recieving a promotion. He’s going to become the new Manager of the Sales department. In order to record this in our table, we’re going to perform a quick operation using the UPDATE keyword to keep our records fresh. Jim is going to receive an extra 3,000 a year for this promotion, so we’ll need to record that too
mysql_query("UPDATE employees SET position = 'Manager', salary = 32500 WHERE name = 'Jim'");
Let’s take a closer look at this statement to see what’s going on here
- UPDATE - This simply states that we’re going to be updating a table, rather than using SELECT to retrieve information from it
- employees - This is the table name we’re going to use for our operation. You may well notice that all MySQL queries follow roughly the same layout. This is true, and generally speaking, queries can be broken down into several distinct components. We’ll look at that a little closer later on.
- SET position = ‘Manager’, salary = 32500 - This states what changes we want to make to the table. Here, we’re setting the “position” column to “Manager” and the “salary” column to “32500″. It’s important to note that each individual assignment here is seperated by a comma. Again, strings must be encased within quotation marks, but this doesn’t matter for numbers.
- WHERE name = “Jim” - This is very important. Without this condition here, the UPDATE command would be executed on every single result in the table! This would be potentially disasterous, so make sure when you’re building your MySQL queries that you pay extra careful attention to the conditions you specify
Now, at the same time, the current Manager, Bob, is getting a promotion too, to Supervisor level. Let’s run the appropriate operation on that too
mysql_query("UPDATE employees SET position = 'Supervisor', salary = 36000 WHERE name = 'Bob'");
This will result in a change to the existing table showing the following changes
| Employee Name | Salary | Department | Job Title |
| Jim | 32,500 | Sales | Staff |
| Bob | 36,000 | Sales | Supervisor |
Generally, the UPDATE keyword can be used to change any piece of information in the table. You might encounter errors though, if you try and change data to values that exist elsewhere in the table, but that only occurs in certain situations, and we’ll look at that in a little more detail further down the page here.
Looking at our records, we see now that we have no regular employees in the Sales department! That’s not good, so we’ll look at adding some new data into our table now, via the INSERT command. But first, a quick mention of the DELETE keyword
DELETE isn’t a terribly complex operation, and merits only a short paragraph of description here. The main thing I should say is that, as with the UPDATE keyword you must pay extra attention to the conditions used in your MySQL query. For example
mysql_query("DELETE FROM employees WHERE name = 'Jim'"); mysql_query("DELETE FROM employees");
The second query will delete everything!. So you should see why you need to be careful here. Now, onto INSERT, where we’ll learn about adding new data into our table.
We use the INSERT keyword when we wish to add in a new record to a table. The UPDATE keyword, on the other hand, is used to update an existing record. Here’s an example of INSERT in action
$result = mysql_query("INSERT INTO employees (name, salary, department, position) VALUES ('Keith', 28500, 'Sales', 'Staff')");
Here, we’re adding a new entry into the table “employees” for an employee named “Keith”, who earns 28500 and is a regular employee in the Sales department. Let’s look a little closer at the syntax of this statement
- INSERT - Here we’re saying that we wish to insert a new record into the database. This record will be placed after the most recent record, at the end of the table
- INTO employees - Here we state that we wish to insert data into the table “employees”
- (name, salary, department, position) - This is a list of all the column names we want to define values for. Note that it’s not necessary to define all the columns here but any columns you miss out without default values will result in a MySQL error. Default Values are the values that a colum will be set to if no other is specified. So, for example, if we instead use (name, salary, department) and have our position column set to have a default value of “Staff”, then the new entry will have a value for “position” of “Staff”, unless we specify otherwise.
- VALUES (’Keith’, 28500, ‘Sales’, ‘Staff’) - Here, we specify the actual values we wish to place in the columns. There are two important things to note here - The values must match up exactly with the column names, and there must be exactly the same number of values as there are column names defined. Also, note how the second value (28500) isn’t within quotation marks. Numbers can be written with or without quotation marks, but strings must always have quotation marks
Running this query will not output anything to the screen, but it will result in a new line being entered into the database. If we try and run it again though, we’ll encounter an error
#1062 - Duplicate entry 'Keith' for key 1
(If you’re not seeing this message with PHP, try running the MySQL query directly within PHPMyAdmin or equivalent management tool.)
This is because in our definition for the table (defined at the start of this tutorial in the MySQL code) we have declared that the employees table should have a Primary Index on the “name” column. Simply put, this means that the “name” column is optimised in such a way that it can be searched very quickly, but this means that we cannot have duplicate values for entries in the “name” column. So, we’d run into problems if we tried to have multiple employees in our company called Keith.
But, here’s where another feature of PHP comes in handy - the use of autoincrement and unique row IDs to automatically number the rows in a database so that every entry has a unique identifier. Read on to the next section to find out more
September 28th, 2008 at 8:34 am
excellent !!
thanx for ur help. i got the exact answer to my query.
October 8th, 2008 at 10:17 pm
Just seen small problem with the example.
$result = mysql_query(”INSERT INTO employees (name, salary, department, position)
VALUES (’Keith’, 28500, ‘Sales’, ‘Staff’)”);
return error #1064 when used directly in phpmyadmin;
INSERT INTO employees (name, salary, department, position)
VALUES (’Keith’, 28500, ‘Sales’, ‘Staff’)
Gives the expected response.
Still a great tuto tho.
Colin
Col