Adsense
Popular Posts
- MySQL workbench -"Could not decrypt password cache"
- peer review (3)
- PHP, dump varailbes in a format way
- "Unusual traffic from your computer network" from blogger
- Install APXS in Redhat Linux
- JavaScript, remove trailing insignificant zeros after toFixed function
- Update member directory
- Set Windows path command line
- JavaScript: add days to current date
- DataTable table order by numeric, not by text
Wednesday, November 13, 2013
MYSQL: copy row value and modify
For example I have table projects, projects, project_id is primary key, 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;
Below is PHP code to copy a row value and change the date_created and name column values:
Her we use inset 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 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
$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');
Labels:
MySQL
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment