Friday, November 22, 2013

MySQL: copy a row value to a new row and update some of values






Below is PHP code to copy a row value in table projects and update the values of  date_created and name columns: The method is used for copy modules and research projects in PHP projects.
Her we use insert into ...set..
$nl  = "\r\n";
$insert_set    = "INSERT INTO `%1s` SET %2s;".$nl;
$sql_insert = '';
$s1 = '';
$s2 = array();
$sql    = "SELECT * FROM projects WHERE project_id=$project_id";
$result = mysql_query($sql,$db);
if($row = mysql_fetch_assoc($result)) {
   $s1 = 'projects';
   $s2 = array();
   foreach($row as $key=>$value){
      if($key == 'project_id'){
         $s2[] = 'project_id=0';
         continue;
      }
      if($key == 'date_created'){
         $s2[] = "date_created = NOW()";
         continue;
      }
      if($key == 'name'){
         $s2[] = "name ='$project_name'";
         continue;
      }
      $value = addslashes($value);
      $s2[] = "$key='$value'";
   }

   $sql_insert = (vsprintf($insert_values,Array($s1,implode(',',$s2))));
   mysql_query($sql_insert,$db);
   $new_project_id = mysql_insert_id();
}


The  table projects is created as following, projects and project_id are primary key with auto increment:
CREATE TABLE  `research_projects_new1`.`projects` (
  `project_id` mediumint(8) unsigned NOT NULL auto_increment,
  `name` varchar(255) NOT NULL default '',
  `owner_id` mediumint(8) unsigned NOT NULL default '0',
  `date_created` datetime NOT NULL default '0000-00-00 00:00:00',
  `date_start` datetime NOT NULL default '0000-00-00 00:00:00',
  `date_end` datetime NOT NULL default '0000-00-00 00:00:00',
  `arms` tinyint(3) unsigned NOT NULL default '0',
  `groups` tinyint(3) unsigned NOT NULL default '0',
  `subjects` smallint(5) unsigned NOT NULL default '0',
  `auto_study_code` tinyint(1) NOT NULL default '1',
  `subject_demographics` text,
  `subject_to_group` enum('ordered','random','manual') NOT NULL default 'ordered',
  `reviewer_to_subject` enum('ordered','random','manual') NOT NULL default 'ordered',
  `reviewer_assignment_rule` enum('subject','arm_group','arm','group') NOT NULL default 'arm_group',
  `subjects_per_review_slot` tinyint(3) unsigned NOT NULL default '0',
  PRIMARY KEY  (`project_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8; 


The MySQL statement is:
INSERT INTO `projects` SET project_id=0,name ='small project COPY',owner_id='37',date_created = NOW(),date_start='2008-07-21 00:00:00',date_end='2008-07-25 00:00:00',arms='1',groups='2',subjects='10',auto_study_code='1',subject_demographics='N;',subject_to_group='manual',reviewer_to_subject='ordered',reviewer_assignment_rule='subject',
subjects_per_review_slot='1'; 

We can also use insert into ...values
Insert into table values and insert into table set in MySQL are identical.
Example
INSERT INTO table_1 (a, b, c) VALUES (1,2,3)
is the same as
INSERT INTO table_1 SET a=1, b=2, c=3
 PHP code to copy a row value to a new row and update some of values
$nl  = "\r\n";
$insert_values = "INSERT INTO `%1s` VALUES(%2s);".$nl;

$sql_insert = '';
$s1 = '';
$s2 = array();
//start with the base entry edu_courses
$sql    = "SELECT * FROM projects WHERE project_id=$project_id";
$result = mysql_query($sql,$db);
if($row = mysql_fetch_assoc($result)) {
   $s1 = 'projects';
   $s2 = array();
   foreach($row as $key=>$value){
      if($key == 'project_id'){
         $s2[] = '0';
         continue;
      }
      if($key == 'date_created'){
         $s2[] = "NOW()";
         continue;
      }
      if($key == 'name'){
         $s2[] = "'$project_name'";
         continue;
      }
      $value = addslashes($value);
      $s2[] = "'$value'";
   }
   $sql_insert = (vsprintf($insert_values,Array($s1,implode(',',$s2))));

   mysql_query($sql_insert,$db);
   $new_project_id = mysql_insert_id();
}


The MySQL statement is:
 INSERT INTO `projects` VALUES(0,'small project COPY','37',NOW(),'2008-07-21 00:00:00','2008-07-25 00:00:00','1','2','10','1','N;','manual','ordered','subject','1');

No comments:

Post a Comment