Poor Mans Autonumber

Anon

Here is my version of the "Poor Mans" autonumber in a database. This is necessary because you can't EDIT an autonumbered row in a MySQL table without changing that number.

The problem with this, I'm told, is that it isn't multi-user safe (which I can see, although I haven't had any problems yet).

In any case, I'm curious. How do I make the following function "multi-user safe"?

	function getid() {

		// Declare globals
		global $host, $user, $pass;

		// Connect to MySQL database
		$db = mysql_connect ($host, $user, $pass);

		// Run insert query
		$query = "SELECT id, value FROM counters WHERE id = 'id' LIMIT 1";
		$result = mysql_db_query ('tickets', $query, $db);
		$row = mysql_fetch_row($result);

		// Add one to the counter
		$counter = $row[1];
		$counter = $counter + 1;

		// Run delete query
		$query = "DELETE FROM counters WHERE id = 'id'";
		mysql_db_query ('tickets', $query, $db);

		// Run insert query
		$query = "INSERT INTO counters (id, value) VALUES ('id', $counter)";
		mysql_db_query ('tickets', $query, $db);

		// Return results
		return $counter;

	};

Anon

Why don't use phplib and its $db->nextid() function. Way easier than what you have tried to do.

Anon

This is necessary because you can't EDIT an autonumbered row in a MySQL
table without changing that number.

Sure you can. Are you perhaps confusing AUTO_INCREMENT with TIMESTAMP? The latter gets updated with every modification to the row (as long as you don't supply an explicit value) but AUTO_INCREMENT happens only on inserts.

In any case, I'm curious. How do I make the following function "multi-user
safe"?

It's not that easy in MySQL. You basically have to lock the whole table:

  LOCK TABLES counters WRITE
  update counters set value = value + 1 where id = 'id'
  select value from counters where id = 'id'
  // then retrieve the value from the query into your php variable
  UNLOCK tables

I.e. you first set the column to a new value, then retrieve it before you let anyone else have at it. But again, for your application none of this should be necessary.