PHP —

Inserting, Updating, and Deleting Data

In this section, we complete our discussion of the basics of modifying data by individually considering inserting, updating, and deleting data. We illustrate the principles of each technique in PHP through introductory case study examples; complete examples are presented in Chapter 10 to Chapter 13. Let's begin by looking at two useful PHP functions, both of which have already been used in Example 6-1 and Example 6-2.

PHP DML functions for database modifications

The following two functions are used with the MySQL functions described in Chapter 4. The first, mysql_affected_rows( ), is used to insert, delete, and update data. The second, mysql_insert_id( ), is used only for insert operations.

int mysql_affected_rows([resource connection])

Reports the number of rows affected by the last UPDATE, DELETE, or INSERT SQL statement. The function takes as an optional parameter a DBMS connection resource handle. If no parameter is passed, the most recently opened connection is assumed. The function should not be used with SELECT statements; mysql_num_rows( ) should be used instead.

For example, if a customer is deleted with the SQL statement:

DELETE FROM customer WHERE CUST_ID=1

then mysql_affected_rows( ) returns a value of 1 if that customer has been successfully deleted. If the query:

INSERT INTO customer SET cust_id = 700

is executed successfully, the function also returns 1.

However, the function may report that zero rows were affected, even if a statement works successfully, because it is possible that an operation may not modify the database. For example, the statement:

UPDATE customer SET zipcode='3053' WHERE city = 'Carlton'

always executes but mysql_affected_rows( ) returns 0 if there are no customers who live in Carlton. Similarly, if a customer row has already been deleted, the function returns 0.

int mysql_insert_id([resource connection])

Returns the AUTO_INCREMENT identifier value associated with the most recently executed SQL INSERT statement. The function is used, for example, to find the cust_id of a new customer when relying on AUTO_INCREMENT to allocate the next available cust_id primary key value after an INSERT INTO customer operation.

The last connection opened is assumed if the connection resource handle parameter is omitted.

This function should be called immediately after the insertion of a row and the result saved in a variable, since the function works for a connection and not on a per-query basis. Subsequent insertions through the same connection make it impossible to retrieve previous key values using this function.