Jump to content


Photo

copy db record to another table


  • Please log in to reply
8 replies to this topic

#1 quasiman

quasiman
  • Members
  • PipPipPip
  • Advanced Member
  • 194 posts
  • LocationPortland, Oregon

Posted 26 September 2006 - 12:16 AM

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.

<?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());
?>

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
...FROM mailbox WHERE mailbox.mail_to_name = mail_attachments.message;
But I got so many errors from this it didn't seem like something I could do in PHP.

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 26 September 2006 - 02:21 AM

$unique_ID is a string... I'm confused.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 quasiman

quasiman
  • Members
  • PipPipPip
  • Advanced Member
  • 194 posts
  • LocationPortland, Oregon

Posted 26 September 2006 - 05:19 AM

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:

<?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());
?>
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

#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 26 September 2006 - 05:58 PM

$result1 is the SQL query string itself, not the value of the message field contents.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#5 quasiman

quasiman
  • Members
  • PipPipPip
  • Advanced Member
  • 194 posts
  • LocationPortland, Oregon

Posted 26 September 2006 - 09:52 PM

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.







mailIDmail_datemail_from_namemail_to_namemail_subjectmail_body
18/9/2006sales@host.comlongbottomsubject1lots_of_text1
28/10/2006bill@yahoo.comcrackersubject2lots_of_text2
38/11/2006john@aol.combillysubject3lots_of_text3
48/12/2006admin@you.compsychosubject4lots_of_text4









messagepartnametypeencodingdatausername
11.1NoNametext/plain4BLOB
11.2NoNametext/html4BLOB
22file.pdfapplication/octet-stream3BLOB
23image.jpgimage/jpeg3BLOB



#6 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 26 September 2006 - 10:29 PM

INSERT INTO ... SELECT WHERE is also an option.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#7 quasiman

quasiman
  • Members
  • PipPipPip
  • Advanced Member
  • 194 posts
  • LocationPortland, Oregon

Posted 26 September 2006 - 10:44 PM

And that is actually in the above script.


Nevermind, I figured out another way.

#8 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 27 September 2006 - 06:29 PM

Could you elaborate for the benefit of other users who come across this thread?
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#9 quasiman

quasiman
  • Members
  • PipPipPip
  • Advanced Member
  • 194 posts
  • LocationPortland, Oregon

Posted 30 September 2006 - 12:31 AM

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:
'" . mysql_escape_string(eregi_replace("@[a-z0-9\-]+\.[a-z0-9\-\.]+$","",$mail_to_name)) . "'

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.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users