Update MySQL DB table column with data from another table column

[drunkencelt]

Hi,

 

Lost the plot on this one and just not quite sure how I should be approaching this:

 

What I would like to do is insert data from one table column into another but each row in one must correspond to the row in the other.  I suspect I will need some PHP to do it but being a novice with direct database manipulation (although experiesed with Drupal configuration) I'm not sure how to do it.  I know a little PHP and a little SQL and can usually work out what code does but starting from scratch is a bit above me although I am slowly learning.  Would appreciate any help.

 

Specifically:  I have a table 'content_type-respondent-profile' with columns 'nid' and 'field_user_email_value'.  I also have a table 'content_field_email' with columns 'nid' and 'field_email_email'.  Data is currently held in the latter table that I wish to insert into the former BUT the columns 'nid' must match each other.

 

Is this simple to do?  I would really appreciate some suggestions how to approach this or some sample code.  Or directing to where there might be some goot resources that will help me figure.

 

Many thanks.

 

 

[S3cr3t]

Hey,

 

if Im correct you only have to do is one query that collects your current data in an array and let a FOR loop insert it into another table.

 

$dbResult = $database->exec("SELECT * FROM urtable")->fetchAll();

for($i = 0; $i < count($dbResult); $i++)
{
   $database->exec("INSERT INTO othertable SET nid = ". $dbResult[$i]['nid'] .", field_email_email = '". $dbResult[$i]['field_user_email_value'] ."'");
}

 

That is only an example with a custom DB.class . But it may help you.

[drunkencelt]

Hi S3cr3t,

 

Thanks for your help.  I forgot to mention the second table was not empty so before the field data was copied it needed to check the value of another field matched with a second field from the first table.  Your reply put me on the right track though and with a bit of SQL/PHP revision I was able to come up with the following which does the job nicely for me.

 

while($row = mysql_fetch_array($result)or die(mysql_error()))
  {
  $query = "UPDATE content_type_respondent_profile 
  SET field_user_email_value = '".$row['field_email_email']."' WHERE nid = '".$row['nid']."'";
  
  mysql_query($query, $dbc)or die(mysql_error());
    
  }

 

 

Thanks for your help.

 

Great forum.