Search TABLE(A) Update TABLE (B)

[microsim]

Hello

 

I have three tables

 

User

 

diysmps

 

usergroup

 

 

usergroupid is common in the three tables.

 

diysmps is joined on usergroup, so running this sql

 

 

 

SELECT

diysmps.name,

diysmps.email,

diysmps.sumbit,

diysmps.message,

diysmps.ID,

usergroup.title

FROM diysmps

INNER JOIN usergroup ON diysmps.usergroupid = usergroup.usergroupid

ORDER BY diysmps.ID DESC

 

see results i get in picture attached. the result (PENDIN REGISTRATION) is what i need to be updated WHEN the user register to the forums.

 

when the user register to the forums, an account with his EMAIL will be created into the (USER) table.

 

So, I need to look out for the email FROM (diysmps TABLE) into the user table _search user table for email from diysmps table_ and if found to update the USERGROUPID in (diysmps TABLE) withe the USERGROUPID from (USER) table (related to the email searched)

so, when user registers, status from PENDING REGISTRATION to REGISTERED will be shown

 

I know it looks complex for me, but thats how i can describe

 

Thanks all

 

[smoseley]

Basic multi-table update syntax..

 

UPDATE `A`, `B`

SET `A`.`name` = `B`.`name`

WHERE `A`.`col` = `B`.`col`;

 

I don't really get what you're asking for, but it sounds as simple as this (no join on the groups table should be necessary):

UPDATE  `users`, `dysymps`
SET `users`.`usergroupid` = 1  /* or whatever the id is for "registered"*/
WHERE `users`.`usergroupid`!= 1  
    AND `users`.`email` = `dysymps`.`email`;

[microsim]

Basic multi-table update syntax..

 

UPDATE `A`, `B`

SET `A`.`name` = `B`.`name`

WHERE `A`.`col` = `B`.`col`;

 

I don't really get what you're asking for, but it sounds as simple as this (no join on the groups table should be necessary):

UPDATE  `users`, `dysymps`
SET `users`.`usergroupid` = 1  /* or whatever the id is for "registered"*/
WHERE `users`.`usergroupid`!= 1  
    AND `users`.`email` = `dysymps`.`email`;

 

Hello

 

Thanks for the answer, but I dont think this is the answer.

 

I explained what I need in first post, but if not clear, will try again

 

 

 

Search table A (USER) for a specific record (EMAIL ADDRESS)

 

If (EMAIL ADDRESS) found in TABLE A (USER) update (Usergroup-id field) in Table B (diysmps) with (Usergroup-id field) found in table A (USER)

 

 

Example:-

 

 

Table diysmps (TABLE B)

 

User:- Markos        Email:- [email protected]      Usergroup-id:-  1 (pending registration)

 

 

==> search in (user) TABLE A for [email protected], found? yes then

 

 

 

 

Update Usergroup-id field in (diysmps) TABLE B with the usergroupid found in TABLE A

 

 

 

hope its better now

[microsim]

Any one can help?

[xyph]

This question should be in the SQL forum

 

mysql> SELECT * FROM table_a;
+----------+---------------+
| group_id | email         |
+----------+---------------+
|        1 | [email protected] |
|        2 | [email protected]   |
+----------+---------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM table_b;
+----------+---------------+
| group_id | email         |
+----------+---------------+
|        3 | [email protected] |
|        4 | [email protected]   |
+----------+---------------+
2 rows in set (0.00 sec)

mysql> UPDATE table_a as a, table_b as b
    -> SET b.group_id = a.group_id
    -> WHERE a.email = '[email protected]'
    ->    AND b.email = '[email protected]';
Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM table_b;
+----------+---------------+
| group_id | email         |
+----------+---------------+
|        3 | [email protected] |
|        2 | [email protected]   |
+----------+---------------+
2 rows in set (0.00 sec)

[microsim]

Hi,

 

 

Thank you for help, that code worked perfectly when running from SQL,

 

I am using PHPRUNNER software, and used it as

 

 

global $dal;

for ($i=0; $i<count($keys); $i++)

{

  // set ReportsTo field to 'Bob Smith'

  $sql = "

select * from diysmps;

 

Select * from user;

 

UPDATE diysmps,user

 

SET diysmps.usergroupid  = user.usergroupid  WHERE diysmps.email="

 

    . $keys[$i]["email"];

  CustomQuery($sql);

}

 

$result["txt"] = "Records were updated successfully.";

 

and its not working.

 

 

 

while

 

    global $dal;

for ($i=0; $i<count($keys); $i++)

{

  // set ReportsTo field to 'Bob Smith'

  $sql = "Update diysmps set sumbit='Link Sent' where ID="

    . $keys[$i]["ID"];

  CustomQuery($sql);

}

 

$result["txt"] = "Records were updated successfully.";

 

 

is working

[xyph]

I can't help you with third party code generators, besides advise you to not use them.

 

Also, 'its not working' is a terrible way to report an error.