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