从表a复制某些列并插入表b？

please can someone help i have been working on this query for hours. i am trying to copy certain columns from my table ptb_registrations and insert them as a new row into ptb_users.

请有人帮助我一直在处理这个问题几个小时。我正在尝试从我的表ptb_registrations中复制某些列,并将它们作为新行插入到ptb_users中。

my ptb_registrations table looks like this:

我的ptb_registrations表看起来像这样:

id  |   username  |  password   | firstname   |  lastname   |   email 

1        dave        password       james          tanner       email@email.com
2        jonx10      gundam00       eric           davies       something@email.com
3        33csgge     henry10        daniel         gilmart      mail@inbox.com

from these columns i only want to insert id, firstname, lastname, email and password NOT username.

从这些列我只想插入id,名字,姓氏,电子邮件和密码而不是用户名。

So my ptb_user table looks like this:

所以我的ptb_user表看起来像这样:

   id   | user_id  |  password   | firstname   |  lastname   |   email 

    1        1        password       james          tanner       email@email.com
    2        2        gundam00       eric           davies       something@email.com
    3        3        henry10        daniel         gilmart      mail@inbox.com

id is an auto increment value, and i want user_id to have the same value as the auto_incremented id column when the data is inserted too (if this is possible so where id is 1 - user_id will be 1 also)

id是一个自动增量值,我希望user_id在插入数据时具有与auto_incremented id列相同的值(如果可能,那么id为1 - user_id也将为1)

so far i have tried loads of different ways to get the columns inserted from ptb_registrations and into ptb_users but nothing is working for me.

到目前为止,我已经尝试了许多不同的方法来从ptb_registrations和ptb_users插入列,但没有什么对我有用。

cans someone please show me where i am going wrong, thank you.

罐头有人请告诉我我哪里出错了,谢谢。

 // Make a safe query
    $query ="insert into ptb_users(id, first_name)
    select ptb_registrations.id, ptb_registrations.firstname
    from ptb_registrations, temp
    where ptb_users.id=temp.id;
    ";

    mysql_query($query)or die('Could not update members: ' . mysql_error());

1 个解决方案

#1

There's no way to guarantee that your new AUTO_INCREMENT will match your previous user ID's... What happens if there is a break in the sequence? Your best bet is to re-assign the new table's id after you've inserted the records.

无法保证您的新AUTO_INCREMENT将与之前的用户ID匹配...如果序列中断,会发生什么?您最好的选择是在插入记录后重新分配新表的ID。

Your query should something like this:

您的查询应该是这样的:

INSERT INTO ptb_user (user_id, password, firstname, lastname, email)
SELECT id, password, firstname, lastname, email
FROM ptb_registrations
ORDER BY id

P.S. I hope you're not saving the passwords in plain-text.

附:我希望你不是用纯文本保存密码。

#1