Manual & Dynamic Data Transfer - Table:to:Table

[adriscoll]

Hello.

 

I would like to transfer information from one table to another.  Currently, the code below works well.

 

$result = mysql_query(
"INSERT INTO table2 (name, email)
SELECT firstname, email
FROM table1
WHERE email='generic@email.com' ") or die(mysql_error());

 

However,  I would like to add another layer of complexity but my effiorts have not worked well.  I would like to add default information into 2 new fileds within table2 (registered & confirmed).  In table 2, these should both defaul to a value of "1".  My feeble attempt is below. 

 

$result = mysql_query(
"INSERT INTO table2 (name, email, registered='1', confirmed='1')
SELECT firstname, email
FROM table1
WHERE email='generic@email.com' ") or die(mysql_error());

 

Any help would be appreciated.

[PFMaBiSmAd]

Should work (you can select literal values) -

 

INSERT INTO table2 (name, email, registered, confirmed)
SELECT firstname, email, 1, 1
FROM table1
WHERE email='generic@email.com'

[adriscoll]

PFM*,

 

thank you again.  I guess there is no need to send a PM over to you now about this one.  Very easy fix, so thank you again.

 

I did have a question about the data input and an error I keep coming across.  When I run this query, everything goes fine, and when it hits a duplicate email address it is savvy enough to not allow it.  Awesome.  "Duplicate entry 'generic@email.net' for key 2".  However, I keep running into an issue where it will load all of the appropriate new email addresses that occur before the above mentioned dup in the list, but not after it hits the dup email.

 

Any thoughts?

[PFMaBiSmAd]

Specify IGNORE to ignore rows that would cause duplicate-key violations.

INSERT IGNORE INTO table2 (name, email, registered, confirmed)
SELECT firstname, email, 1, 1
FROM table1
WHERE email='generic@email.com'

 

I hope you are not executing a SELECT query to get all the email addresses from the table and then looping through them, with the posted query inside of a loop? If you remove the WHERE condition from the posted query, it will select all the rows at once and insert them all at once into the second table.

 

 

[adriscoll]

PFM,

 

The WHERE statement should have been removed in my code before I posted this.  I have a small database and was setting it up to scroll through all entries.  I know that is not the best method, but, once I could get that to work, I was then going to do it based on entries made on the current day and run it at 11:59pm.