Web Database Applications with PHP and MySQL, 2nd Edition

Inserting, Updating, and Deleting Data

There are four major statements for working with data in SQL: SELECT, INSERT, DELETE, and UPDATE. We describe the latter three statements in this section. SELECT is covered it in its own section later in this chapter.

Inserting Data

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

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

INSERT INTO customer VALUES (1,'Williams','Lucy','E',3,
'272 Station St','Carlton North','VIC','3054',12,'(613)83008460',
'2002-07-02');

The statement creates a new row in the customer table, then the first value 1 is inserted into the first attribute, cust_id. The second value 'Williams' is inserted into the second attribute surname, 'Lucy' into firstname, and so on.

The number of values inserted is the same as the number of attributes in the table (and an error is generated if the number of values doesn't match the number of attributes). If you don't want to supply data for an attribute, you can include NULL instead of a value (as long as the attribute isn't defined as NOT NULL and NULL is valid for that data type). For example, to create a partial customer row, you could use:

INSERT INTO customer VALUES (1,'Williams','Lucy',NULL,3,
NULL,NULL,NULL,NULL,12,NULL,NULL);

To create an INSERT statement using ...