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
Reports the number of rows affected by the last
INSERTSQL 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
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
mysql_affected_rows( )returns a value of
1if that customer has been successfully deleted. If the query:
INSERT INTO customer SET cust_id = 700
is executed successfully, the function also returns
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
AUTO_INCREMENTidentifier value associated with the most recently executed SQL
INSERTstatement. The function is used, for example, to find the
cust_idof a new customer when relying on
AUTO_INCREMENTto allocate the next available
cust_idprimary key value after an
INSERT INTO customeroperation.
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.