douwo8358 2016-03-03 21:16
浏览 113
已采纳

当数据使用PHP交叉时,将数据从一个MySQL表复制到另一个MySQL表

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!

  • 写回答

2条回答 默认 最新

  • douza1373 2016-03-04 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))
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?

悬赏问题

  • ¥15 数学的三元一次方程求解
  • ¥20 iqoo11 如何下载安装工程模式
  • ¥15 本题的答案是不是有问题
  • ¥15 关于#r语言#的问题:(svydesign)为什么在一个大的数据集中抽取了一个小数据集
  • ¥15 C++使用Gunplot
  • ¥15 这个电路是如何实现路灯控制器的,原理是什么,怎么求解灯亮起后熄灭的时间如图?
  • ¥15 matlab数字图像处理频率域滤波
  • ¥15 在abaqus做了二维正交切削模型,给刀具添加了超声振动条件后输出切削力为什么比普通切削增大这么多
  • ¥15 ELGamal和paillier计算效率谁快?
  • ¥15 蓝桥杯单片机第十三届第一场,整点继电器吸合,5s后断开出现了问题