Let me rephrase my question since no one replied, i guess i was not clear.
I have 3 tables:
_students
_1ee0
_2ee0
-- ----------------------------
-- Table structure for _1ee0
-- ----------------------------
CREATE TABLE `_1ee0` (
`id` int(11) NOT NULL auto_increment,
`fname` varchar(50) default NULL,
`lname` varchar(50) default NULL,
`student_no` int(11) default NULL,
`email` varchar(50) default NULL,
`comments` varchar(50) default NULL,
`course` varchar(50) default NULL,
`course status` varchar(50) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for _2ee0
-- ----------------------------
CREATE TABLE `_2ee0` (
`id` int(11) NOT NULL auto_increment,
`fname` varchar(50) default NULL,
`lname` varchar(50) default NULL,
`student_no` int(11) default NULL,
`email` varchar(50) default NULL,
`comments` varchar(50) default NULL,
`course` varchar(50) default NULL,
`course status` varchar(50) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for _students
-- ----------------------------
CREATE TABLE `_students` (
`id` int(11) NOT NULL auto_increment,
`fname` varchar(50) default NULL,
`lname` varchar(50) default NULL,
`student_no` int(11) default NULL,
`course` varchar(50) default NULL,
`email` varchar(50) default NULL,
`Completed` varchar(50) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
I want to insert hardcoded values into _students(in its field called completed) when the value of the fields course status in either _1ee0 or _2ee0 tables is Completed.
I tired using events as follows:
function BeforeEdit(&$values, $where)
{
// Parameters:
// $values - Array object.
// Each field on the Edit form represented as 'Field name'-'Field value' pair
// $where - string with WHERE clause pointing to record to be edited
//********** Insert a record into another table ************
global $conn;
$strSQLInsert = "insert into _students (Completed) values('1ee0');
db_exec($strSQLInsert,$conn);
return true;
// return true if you like to proceed with editing this record
// return false in other case
}
This works but it inserts '1ee0' in _students as a new record (in a new row).. I want to insert the value in the row corresponding to the entry which is being edited in _1ee0 (i have uniq identifiers for each recors)
When I add where _students.id =1"; to the insert statement, I get errors (Syntax error). What am I missing? I want to add the hardcoded values in another table with matching field values
(for instance "id" in _students and "id" in _1ee0 match)
Hope my question is clear, Please help.
Thanks in advance