I am certifiably among the oldest codgers attending this forum. I suppose I was one of the first persons to program Oracle in a client/server environment using 4th Dimension as a front end.
About the time I got into RDBMSs, Oracle developed sequences.
INSERT INTO myTable SELECT mysequence.nextval FROM dual
INSERT INTO childTable SELECT mysequence.currval from dual
Previously they had used 'nextnum' tables which operated similarly.
I have built a similar method into my database. I keep separate unique id tables for most of my primary keys.
I'm just used to doing it, and it's easy enough to build and maintain.
function uniquekey($type) {
//usage: $myProductID=uniquekey('product');
//returns next ID in sequence from special table
//if no sequence table exists, builds the table
//uses a couple of handbuilt functions:
//getuser()provides unique userinfo stored in cookie
//(any uniqueinfo would do)
//dosql() connects to the database and returns the result of a query
$table = 'unique'.$type.'id';
$theuser = getuser();
$thequery .= " INSERT INTO $table
SET name = '$theuser'";
$result = dosql($thequery);
if ((!$result) && (mysql_errno() == 1146)) {
//error 1146 means "no table by that name",
//so we'll create the unique.$type.id table now:
$result = dosql("
CREATE TABLE $table (id int(11) NOT NULL auto_increment,
name varchar(70) default NULL,
PRIMARY KEY (id)) ");
$result = dosql($thequery);
dosql("INSERT into $table set id=1");
}
$uniqueid = mysql_insert_id() ;
$thequery="DELETE FROM $table where id=$uniqueid";
$result = dosql($thequery);
return $uniqueid;
}
Some other codger on another board created a similar method independently, because, as he said, this method was easily portable between different DB vendors.
$parentkey=uniquekey('parent');
$childkey=uniquekey('child');
$user=getuser();
mysql_query("INSERT INTO parent SET id=$parentkey");
mysql_query("INSERT INTO child SET id=$childkey, parentid=$parentkey, user=$user");
//etc.
The simplicity of reading the code above (since I'm constantly screwing up and needing to debug) beats the pants off INSERT INTO parent, $parentkey=mysql_last_insert_id(); etc.
for example, without realizing it, code like this could easily get broken:
mysql_query("INSERT INTO parent SET id=NULL");
$user=getuser();
$parentkey=mysql_last_insert_id();
mysql_query("INSERT INTO child SET id=NULL, parentid=$parentkey, user=$user");
This code worked fine UNTIL I changed getuser();
As part of getuser() I added code to insert a userrecord with a timestamp. So $parentkey=mysql_last_insert_id(); sets $parentkey to the last id of userrecord, not parentTable.
For awhile I actually had "currval(type)" and "nextval(type)" functions -- that's why the code above contains 'getuser()', which strictly speaking is no longer necessary -- but since most of my INSERT or UPDATE work was being done in a single script, I just compressed it to the function above: I get the unique key and use it for all subsequent SQL.
So that's how an old codger does it.
Perhaps my history will also explain why I differentiate very hard between doing work through SQL and doing work through PHP.
It is my experience that NEARLY ANYTHING that can be done with SQL is orders of magnitude faster than nearly anything that can be done by having PHP manipulate the results.
This is why, for example, I work date management through SQL whenever possible; and also string searches and manipulation, and value updates, etc. , etc.
It's very tough to learn several languages, and SQL isn't pretty, but understanding and knowledge of various languages increases the programmer's ability to pound the living shit out of pesky problems by choosing the right method for the job.
PS: the code above is not in anyway related to PHP's uniquid() function...however you could use that function to replace 'getuser()' in the above code.
