PHP SQL update/select

TonyMontana posted this at 06:41 — 18th July 2003.

Joined: Apr 2001

Hi there,

It's possible to INSERT/SELECT in PHP/mySQL, such as:

<?php
$bizInsert=INSERT INTO iBusinesses (userIndex) SELECT userIndex FROM iUsers WHERE userID='$uID'\";
?>

But how about UPDATE/SELECT in one statement? ie:

<?php
$bizUpdate=UPDATE iBusinesses SET userIndex = SELECT userIndex FROM iUsers WHERE userID='$uID'\";
?>

Is there a way to do this?

Cheers,

TonyMontana

Mark Hensler posted this at 09:51 — 18th July 2003.

He has: 4,048 posts

Joined: Aug 2000

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.

Mark Hensler
If there is no answer on Google, then there is no question.

Mark Hensler posted this at 09:54 — 18th July 2003.

He has: 4,048 posts

Joined: Aug 2000

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
If there is no answer on Google, then there is no question.

TonyMontana posted this at 18:49 — 20th July 2003.

They have: 218 posts

Joined: Apr 2001

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