duplicate rows, but change one field to new value?

v3nt

hello there, i need to duplicate some items from 2 tables but at the same time update one of the fields to a reference id of another table.

I'm midway from building a function for it but it seems a bit long winded and think there' maybe a better way of doing it. Always keen to improve my scripting!

If anyone has any pointers or ideas please let me know. The function in progress is below.

function duplicateEmail($emailID){

$query = "SELECT * FROM email WHERE email_ID='$emailID'";
$result = mysql_query($query) or die ("Error in query: $query. " . mysql_error());

while ($row = mysql_fetch_array($result)) {
	$emailTitle = $row['email_subject'];
	$emailMailinglist = $row['email_mailinglist'];	
	$emailTemplate = $row['email_template'];	
	$emailTestAddress = $row['email_testAddress'];	
	$emailNotes = $row['email_notes'];	

	$newEmailQuery ="INSERT INTO email

			(email_created,email_subject,email_mailinglist,email_template,email_notes,email_testAddress) 
			VALUES 
			('".date('YmdGi')."','$emailTitle copy','$emailMailinglist','$emailTemplate','$emailNotes','$emailTestAddress')";

	$newEmailresult = mysql_query($newEmailQuery) or die ("Error in query: $query. " . mysql_error());
	$duplicateId=mysql_insert_id();

	$dupElementsQuery = "SELECT * FROM emailcontent, emailsubcontent WHERE emailcontent_emailID='$emailID' AND emailsubcontent_emailID='$emailID'";
	$dupElementsResult = mysql_query($dupElementsQuery) or die ("Error in query: $query. " . mysql_error());

	while ($row = mysql_fetch_array($dupElementsResult)) {

		// select relevent rows, duplicate them and set *_emailID to $duplicateId

	}

}

return $duplicateId;

}

efficacious

I'll need more information to help you with this.

You want to copy information from one table to another but change the id?

laserlight

i need to duplicate some items from 2 tables but at the same time update one of the fields to a reference id of another table.

Are you sure the duplication is necessary in the first place? You may want to use a join instead.

efficacious

he would only be able to use the join if he has some sort of a matching reference in both tables tho.

laserlight

he would only be able to use the join if he has some sort of a matching reference in both tables tho.

Obviously (otherwise one would get a Cartesian product with little use), but this has to be true in order for the duplication to make sense in the first place.

v3nt

basically i'm duplicating a master entry so it can be altered slightly for a different version of an email, but i also need to create new versions of the children elements in emailcontent, emailsubcontent that work with the new email ID.

laserlight

basically i'm duplicating a master entry so it can be altered slightly for a different version of an email, but i also need to create new versions of the children elements in emailcontent, emailsubcontent that work with the new email ID.

It sounds like you have the same email, just with different formatting that you wish to store rather than generate on the fly. As such, why not have another text column in your original email table, since the relationship between email and formatted email is one-one?

If you have multiple formats, then perhaps this is a one-many relationship, so your second table would then have a foreign key to the original email table, along with a column for the format type and another column for the formatted email.

v3nt

seems the long way round, i want to be able to add infinite duplicates if needed which can be done easily enough with rows but having too many columns is a bit of a pain.

It will be the same email at first, but then it will be modified with some type tweaks, image replacements etc.

We'll also need different versions as each version will be getting sent to different mailing lists.

so there's nothing like

"DUPLICATE/SELECT FROM [tables] WHERE [table]emailID ='22' "
"UPDATE [just created duplicates] SET [table]emailID ='22' "

efficacious

laserlight;10886532 wrote:
Obviously (otherwise one would get a Cartesian product with little use), but this has to be true in order for the duplication to make sense in the first place.

why would that have to be true in order for duplication to make sense.. you can duplicate information for use on many different processes. Without any matching references.. (Your personal text is true but you forgot to switch your w:queasy🙂

efficacious

v3nt - just create 1 query to pull 1 query to duplicate and 1 query to update

or 1 to pull and 1 to duplicate and update.. you might find it easier to keep track of with 3 seperate querys

laserlight

efficacious wrote:
why would that have to be true in order for duplication to make sense.. you can duplicate information for use on many different processes. Without any matching references.

What about referential integrity and data integrity? Of course you can duplicate whatever you want, but here we are talking about a relational database.

v3nt wrote:
seems the long way round, i want to be able to add infinite duplicates if needed which can be done easily enough with rows but having too many columns is a bit of a pain.

It will be the same email at first, but then it will be modified with some type tweaks, image replacements etc.

We'll also need different versions as each version will be getting sent to different mailing lists.

In that case my second table suggestion will work. The point is that you have referential integrity: the same email, but in different formats. This is a one-many relationship, and such relationships are typically expressed in this way.

EDIT:
That said, unless you are using InnoDB storage for MySQL, you cannot actually enforce the referential integrity... but still...

efficacious

laserlight;10886543 wrote:
What about referential integrity and data integrity? Of course you can duplicate whatever you want, but here we are talking about a relational database.

ok so why are we even talking about this.. you've just confirmed my reasoning for trying to make sure he had some sort of reference in place. And made your previous (rude) response irrelevant.

laserlight

efficacious wrote:
ok so why are we even talking about this.. you've just confirmed my reasoning for trying to make sure he had some sort of reference in place. And made your previous (rude) response irrelevant.

Sorry if I sounded rude, but you sure sounded rude when you stated the obvious in response to my suggestion, and then went on to imply that duplicating wholesale was fine, contrary to what I stated. Now I understand that you were merely trying to check if v3nt understood that there should be a relationship expressed in terms of a foreign key in the first place.

efficacious

My apologies if I sounded rude. thanks for your apology.