Jump to content

Archived

This topic is now archived and is closed to further replies.

quasiman

copy db record to another table

Recommended Posts

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.

Share this post


Link to post
Share on other sites
$unique_ID is a string... I'm confused.

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
$result1 is the SQL query string itself, not the value of the message field contents.

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
INSERT INTO ... SELECT WHERE is also an option.

Share this post


Link to post
Share on other sites
And that is actually in the above script.


Nevermind, I figured out another way.

Share this post


Link to post
Share on other sites
Could you elaborate for the benefit of other users who come across this thread?

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.