copy db record to another table

[quasiman]

I've asked a similar question here before, but this has a twist...

I only want to copy one value from the first table to the 2nd, where the ID's are the same in each.  The 2nd table is dependant on the first table, so these will always match and there can be multiple matches at the 2nd table.

Here's what I'm trying - it doesn't give me any errors, but also doesn't work.

[code=php:0]
<?php
$unique_ID="SELECT message FROM mail_attachments";
mysql_query($unique_ID) or die(mysql_error());

$query="INSERT INTO mail_attachments (username)
SELECT mail_to_name FROM mailbox
WHERE mailID='$unique_ID'";
mysql_query($query) or die(mysql_error());
?>
[/code]

The "message" field from "mail_attachments" is not auto_incremented, it is the same number as "mailID" in "mailbox".
I also tried writing it out like an example I found on the mysql page [code]...FROM mailbox WHERE mailbox.mail_to_name = mail_attachments.message;
[/code]
But I got so many errors from this it didn't seem like something I could do in PHP.

[fenway]

$unique_ID is a string... I'm confused.

[quasiman]

Your confusion is confusing me  ;)

Are you saying that because I have $unique_ID in single quotes, then it becomes a string?  I've rewritten the queries slightly based on that assumption, and now I am coming up with an error:

[code=php:0]
<?php
$result1 = mysql_query("SELECT message FROM mail_attachments");
mysql_query($result1) or die(mysql_error());

$result2 = mysql_query("INSERT INTO mail_attachments (username) SELECT mail_to_name FROM mailbox WHERE mailID=$result1");
mysql_query($result2) or die(mysql_error());
?>
[/code]
Here's the error:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Resource id #31' at line 1

[fenway]

$result1 is the SQL query string itself, not the value of the message field contents.

[quasiman]

Here's my basic table structure.  The first table has the mail message, the 2nd has it's associated attachments.  I need to insert the mail_to_name value into the attachments table as username.  As you can see, each attachment is associated to mail messages by the "message" field.

[table]
[tr][td]mailID[/td][td]mail_date[/td][td]mail_from_name[/td][td]mail_to_name[/td][td]mail_subject[/td][td]mail_body[/td][/tr]
[tr][td]1[/td][td]8/9/2006[/td][td]sales@host.com[/td][td]longbottom[/td][td]subject1[/td][td]lots_of_text1[/td][/tr]
[tr][td]2[/td][td]8/10/2006[/td][td]bill@yahoo.com[/td][td]cracker[/td][td]subject2[/td][td]lots_of_text2[/td][/tr]
[tr][td]3[/td][td]8/11/2006[/td][td]john@aol.com[/td][td]billy[/td][td]subject3[/td][td]lots_of_text3[/td][/tr]
[tr][td]4[/td][td]8/12/2006[/td][td]admin@you.com[/td][td]psycho[/td][td]subject4[/td][td]lots_of_text4[/td][/tr]
[/table]


[table]
[tr][td]message[/td][td]part[/td][td]name[/td][td]type[/td][td]encoding[/td][td]data[/td][td]username[/td][/tr]
[tr][td]1[/td][td]1.1[/td][td]NoName[/td][td]text/plain[/td][td]4[/td][td]BLOB[/td][td][/td][/tr]
[tr][td]1[/td][td]1.2[/td][td]NoName[/td][td]text/html[/td][td]4[/td][td]BLOB[/td][td][/td][/tr]
[tr][td]2[/td][td]2[/td][td]file.pdf[/td][td]application/octet-stream[/td][td]3[/td][td]BLOB[/td][td][/td][/tr]
[tr][td]2[/td][td]3[/td][td]image.jpg[/td][td]image/jpeg[/td][td]3[/td][td]BLOB[/td][td][/td][/tr]
[/table]

[fenway]

INSERT INTO ... SELECT WHERE is also an option.

[quasiman]

And that is actually in the above script.


Nevermind, I figured out another way.

[fenway]

Could you elaborate for the benefit of other users who come across this thread?

[quasiman]

I'm not sure how others would benefit from this, but alright - for the sake of argument.

When mail is coming in, the field mail_to_name was being inserted with username@host.com, so for my insert variable I put:
[code]'" . mysql_escape_string(eregi_replace("@[a-z0-9\-]+\.[a-z0-9\-\.]+$","",$mail_to_name)) . "'[/code]

Then for the attachments I did the same thing - inserting as 'username' instead of the full address beforehand so I don't need to worry about updating anything.