PHP —

Inserting, Updating, and Deleting Data

The Data Manipulation Language (DML) encompasses all SQL statements used for manipulating data. There are four statements that form the DML statement set: SELECT, INSERT, DELETE, and UPDATE. We describe the last three statements in this section. While SELECT is also part of DML, we cover it in its own section, Section 3.6. Longer worked examples using all the statements can be found in the section Section 3.8.

Inserting Data

Having created a database and the accompanying tables and indexes, the next step is to insert data. Inserting a row of data into a table can follow two different approaches. We illustrate both approaches by inserting the same data for a new customer, Dimitria Marzalla.

Consider an example of the first approach using the customer table:

INSERT INTO customer
  VALUES (NULL,'Marzalla','Dimitria', 'F','Mrs',
          '171 Titshall Cl','','','St Albans','WA',
          '7608','Australia','(618)63576028','',
          'dimitria@lucaston.com','1969-11-08',35000);

In this approach a new row is created in the customer table, then the first value listed-in this case, a NULL-is inserted into the first attribute of customer. The first attribute of customer is cust_id and-because cust_id has the auto_increment modifier and this is the first row-a 1 is inserted as the cust_id. The value "Marzalla" is then inserted into the second attribute surname, "Dimitria" into firstname, and so on. The number of values inserted must be the same as the number of attributes in the table. To create an INSERT statement in this format, you need to understand the ordering of attributes in the table.

The number inserted by an auto_increment modifier can be checked with the MySQL-specific function last_insert_id( ). In this example, you can check which cust_id was created with the statement:

SELECT last_insert_id(  );

This statement reports:

+------------------+
| last_insert_id(  ) |
+------------------+
|                1 |
+------------------+
1 row in set (0.00 sec)

You can see that the new row has cust_id=1. To check a value, the function should be called immediately after inserting a new row.

When inserting data, nonnumeric attributes must be enclosed in either single or double quotes. If a string contains single quotation marks, the string is enclosed in double quotation marks. For example, consider the string "Steve O'Dwyer". Likewise, strings containing double quotation marks can be enclosed in single quotation marks. An alternative approach is to escape the quotation character by using a backslash character; for example, consider the string 'Steve O\'Dwyer'. Numeric attributes aren't enclosed in quotes.

The same insertion can also be performed using a second approach. Consider this example:

INSERT INTO customer
  SET surname = 'Marzalla',
      firstname = 'Dimitria',
      initial='F',
      title='Mrs',
      addressline1='171 Titshall Cl',
      city='St Albans',
      state='WA',
      zipcode='7608',
      country='Australia',
      phone='(618)63576028',
      email='dimitria@lucaston.com',
      birthdate='1969-11-08',
      salary=35000;

In this approach, the attribute name is listed, followed by an assignment operator, "=", and then the value to be assigned. This approach doesn't require the same number of values as attributes, and it also allows arbitrary ordering of the attributes. cust_id isn't inserted, and it defaults to the next available cust_id value because of the combination of the auto_increment and DEFAULT modifiers.

The first approach can actually be varied to function in a similar way to the second by including parenthesized attribute names before the VALUES keyword. For example, you can create an incomplete customer row with:

INSERT INTO customer (surname,city) VALUES ('Smith','Sale');

Other approaches to loading data using a similar syntax are also possible. A popular variation is to insert data into a table from another table using a query, and it's discussed briefly in Section 3.8.3.