HooperG;10940101 wrote:If I have to count rows, before or after the insert, do I have to worry if the db gets busy and possibly getting the wrong count ?
Counting rows may or may not tell you anything relevant.
DROP TABLE t;
CREATE TABLE t (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY);
INSERT INTO t VALUES(null);
INSERT INTO t VALUES(null);
DELETE FROM t WHERE id = 1;
SELECT count(*) FROM t; -- row count = 1, highest ID = 2.
DROP TABLE t;
CREATE TABLE t (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY);
INSERT INTO t VALUES(10);
SELECT count(*) FROM t; -- count = 1, highest ID = 10.
And beyond that, to first insert and then get the count at the time of completion of your insert, you'd need to wrap your code in a transaction to avoid counting rows from other people's inserts.
Furthermore, do note that the function returns the FIRST id, not the last, of the latest question.
INSERT INTO t VALUES (11),(null),(null);
mysql_insert_id() returns 12 (first auto generated value, 11 was not...)
For the update you either allready have the id (WHERE id = ...), or some other where clause that will return one or more rows (WHERE field = val AND field2 = val2...) of no where clause which means an update to all rows.
Either way, selecting the same rows whenever you need to is easy. All you need is the same where clause (or no where clause).
On the other hand, for an INSERT ... ON DUPLICATE KEY UPDATE, mysq_insert_id will return the LAST value.
There are some other things to consider as well, and also if you are using an older db than 5.1.something_or_other, the behaviour is not the same as for more recent versions. Refer to the reference manual for all the info.
