TonyMontana — Sun, 20 Jul 2003 18:49:05 +0000

Hi Mark,

I didn't get immediate results from that. For now, I'm doing a couple of individual selects and storing the 'userID' index for reference.

Thanks,

TM
aka Phil
electricmountain.com

Mark Hensler — Fri, 18 Jul 2003 09:54:44 +0000

After searching the manual just now, I'm not sure what version of mysql this requires. 6.1.4 User Variables talks about variable use, but does not mention what version of mysql this was implemented in.

You may first want to try this:

SELECT @uid:=userIndex FROM iUsers WHERE userID='$uID';
UPDATE iBusinesses SET userIndex=@uid WHERE key=val;

Mark Hensler — Fri, 18 Jul 2003 09:51:09 +0000

Not to my knowledge. But there may be a way around it. (mysql >= 4.0.11)

I haven't tested this, or played with it one bit. But while reading the mysql docs recently, I came accross transactional statments in 6.7.1 BEGIN/COMMIT/ROLLBACK Syntax.

From what I learned in the manual and looking at your queries above, you may want to try this: (after backing up the db)

START TRANSACTION;
SELECT @uid:=userIndex FROM iUsers WHERE userID='$uID';
UPDATE iBusinesses SET userIndex=@uid WHERE key=val;
COMMIT;

'You'll run this in one mysql_query() call. What's suppose to happen is mysql will assign the variable @uid the value of the select statement, then run the update query replacing the variable @uid with the value.

If you try this, let me know of any results. I've not had time to play with this (not found time to upgrade mysql), but I'm dying to know how well it works.