Copy data from one MySQL table to another when data intersect using PHP

Question

I'm new to programming and would really appreciate your help. :) So, I have a USER table and a SALES table. On the SALES table I only have name and last name of the users. On the USER table I have name, last name, USER_ID, email and etc...

I need to copy the USER_ID from the USER table to the SALES table when NAME and LAST NAME are a match.

Here is the structure:

USER_TABLE_A
USER_ID_A
NAME_LASTNAME_A

SALES_TABLE_B
ROW_ID_B
NAME_B
LASTNAME_B
USER_ID_B (empty)

So far I got both tables to show data when they intersect but have no idea where to go from here. Could anyone please help?

    $sql1 = mysql_query("SELECT name_B, lastname_B, user_id_B, row_id_B FROM sales_table_B WHERE name_B IS NOT NULL AND lastname_B IS NOT NULL", $db);
    $sql2 = mysql_query("SELECT name_lastname_A, user_id_A FROM user_table_A WHERE name_lastname_A IS NOT NULL", $db);
    $a1 = array();
while ($row = mysql_fetch_array($sql1)) {
    $id = $row['row_id_B'];
    $name1.$id = $row['name_B']." ".$row['lastname_B'];
    array_push($a1, $name1.$id);
}
    $a2 = array();
while ($row2 = mysql_fetch_array($sql2)) {
    $id2 = $row2['user_id_A'];
    $name2.$id2 = $row2['name_lastname_A'];
    array_push($a2, $name2.$id2);
} 
    $result = array_intersect($a1,$a2);
    print_r($result);

Thanks in Advance!

Please [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). [These extensions](http://php.net/manual/en/migration70.removed-exts-sapis.php) have been removed in PHP 7. Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really pretty easy](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Mar 03 '16 at 21:19
In order to copy you're going to have to use an `UPDATE` query. — Jay Blanchard, Mar 03 '16 at 21:19
you can do merging of tables in a SELECT statement using JOIN. see http://dev.mysql.com/doc/refman/5.7/en/join.html — recycler, Mar 03 '16 at 21:43

score 0 · Answer 1 · answered Mar 03 '16 at 22:17

Under the assumption that NAME_LASTNAME_A column in USER_TABLE_A is a concatenation of NAME_B and LASTNAME_B columns of SALES_TABLE_B, following UPDATE query should update the ids:

update sales_table_b
set b.user_id_b = (
 select user_id_a from user_table_a
 where name_lastname_a = concat(b.name_b, ' ' , b.lastname_b)
 limit 1
 where exists(
  select user_id_a from user_table_a
  where name_lastname_a = concat(b.name_b, ' ' , b.lastname_b)
 )
)

Please note that in case of multiple users having same first and last name, id of the first matching user will be considered.

Thank you so much Darshan!! Your answer was missing a ) after limit 1 but with the adjustment it worked beautifully! — Vini Goulart, Mar 04 '16 at 15:00

score 0 · Accepted Answer · answered Mar 04 '16 at 14:59

Thank you so much Darshan!! Your answer was missing a ) after limit 1 but with the adjustment it worked beautifully! Here is the code that worked:

UPDATE sales_table_b
SET user_id_b = (SELECT user_table_a.user_id_a
                 FROM user_table_a
                 WHERE user_table_a.name_lastname_a = CONCAT(sales_table_b.name_b, ' ' , sales_table_b.lastname_b) LIMIT 1)
WHERE EXISTS (SELECT *
          FROM user_table_a
          WHERE user_table_a.name_lastname_a = CONCAT(sales_table_b.name_b, ' ' , sales_table_b.lastname_b))

Copy data from one MySQL table to another when data intersect using PHP

2 Answers2