copy data from one table into another?

Question

when a user signs up to my site they complete a registration form and have their details put into a table called ptb_registrations.

after this they receive an email with a verification code, they click the email link and go to the verification link, upon entering the correct code i want a query to run.

this query needs to act as an insert query that will copy the data from one table (ptb_registrations) into another (ptb_users).

can someone help me with this as it's not working and im not sure if im heading in the right direction. thanks

// Make a safe query $query = sprintf("INSERT INTO ptb_users (first_name, last_name, email, password, dob) SELECT firstname, lastname, email, password dob WHERE not exists (select 1 from ptb_registrations where ptb_registrations.registration_code='$verificationcode';", mysql_real_escape_string($newpassword)); mysql_query($query)or die('Could not update members: ' . mysql_error());

insert into table1 as select * from table2; // make sure you have same structure for both the tables. — Lucifer
– Lucifer, Commented Mar 14, 2013 at 13:20
Missing comma between password and dob, and a missing closing parenthesis for the WHERE's sub-select. — pritaeas
– pritaeas, Commented Mar 14, 2013 at 13:21
You should get a message Could not update members: some error here What error is shown there? But as for me, your problem is that you have no comma between password and dob in select. — Viktor S.
– Viktor S., Commented Mar 14, 2013 at 13:22

Viktor S. · Accepted Answer · 2013-03-14 13:32:50Z

As far as I can see, you have a lot of syntax mistakes in your query:

no comma between password and dob in select statement, From table for select statement is not specified. Also, where condition looks strange (and there is no closing bracket for not exists statement). As for me, you should have a query like:

$query = "INSERT INTO ptb_users (first_name, last_name, email, password, dob) SELECT firstname, lastname, email,'". mysql_real_escape_string($newpassword)."', dob FROM ptb_registrations WHERE ptb_registrations.registration_code=''". mysql_real_escape_string($$verificationcode).";"

This query will take a row from ptb_registrations where registration code is equal to one received from request and insert it into ptb_users (and put a new password as a user password)

Prahalad Gaggar · Accepted Answer · 2013-03-14 13:33:32Z

You can try something like this:

SELECT firstname, lastname, email, password,dob into yourtablename from (tablename) ^ you are missing something here i.e your tablename WHERE not exists (select 1 from ptb_registrations where ptb_registrations.registration_code='$verificationcode';", mysql_real_escape_string($newpassword)

This will create new table with same data structure

Collectives™ on Stack Overflow

copy data from one table into another?

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related