new/update fails with join

naj

Clearly I am missing something here. I have the following DB tables:

When I attempt to create a new record or update an existing record, I get the following error returned from the server:
[quote]


Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`sdemo`.`students`, CONSTRAINT `students_ibfk_1` FOREIGN KEY (`teacher_id`) REFERENCES `teachers` (`id`))' in /var/www/schools/demo/php/DT/Database/Driver/Mysql/Query.php:98
Stack trace:
#0 /var/www/schools/demo/php/DT/Database/Driver/Mysql/Query.php(98): PDOStatement->execute()
#1 /var/www/schools/demo/php/DT/Database/Query.php(551): DataTables\Database\DriverMysqlQuery->_exec()
#2 /var/www/schools/demo/php/DT/Database/Query.php(197): DataTables\Database\Query->_insert()
#3 /var/www/schools/demo/php/DT/Database/Database.php(122): DataTables\Database\Query->exec()
#4 /var/www/schools/demo/php/DT/Editor/Editor.php(490): DataTables\Database->insert(Array, Array)
#5 /var/www/schools/demo/php/DT/Editor/Editor.php(333): DataTables\Editor->_insert()
#6 /var/www/schools/demo/store/test/data.php(36): DataTables\Editor->process(Array)
#7 {main}
thrown in /var/www/schools/demo/php/DT/Database/Driver/Mysql/Query.php on line 98

[/quote]

I pretty much used the example as a template. It looks like it's trying to update both of the tables that I am joining to display on my web page. I really only want it add or update records in one of them. Can someone lend me a clue?

-Norm

MY DB tables were created as follows:

[code]
DROP TABLE IF EXISTS `teachers`;
CREATE TABLE `teachers` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(64) NOT NULL,
`room_no` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB;

DROP TABLE IF EXISTS `students`;
CREATE TABLE `students` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(64) NOT NULL,
`grade` int(11) NOT NULL,
`teacher_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
CONSTRAINT FOREIGN KEY (`teacher_id`) REFERENCES teachers(id)
ON DELETE RESTRICT
) ENGINE=INNODB;

INSERT INTO `teachers` (name, room_no)
VALUES ('Mrs. Smith', 123),
('Mrs. Jones', 101),
('Mr. Bennett', 104);
[/code]

I have following Javascript for my very simple two column table

[code]
(function($){

$(document).ready(function() {
var editor = new $.fn.dataTable.Editor( {
"ajaxUrl": "data.php",
"domTable": "#data",
"fields": [
{
"label": "Name",
"name": "Name",
"type": "text"
},
{
"label": "Teacher",
"name": "teacher.id",
"type": "select"
}
]
} );

$('#data').dataTable( {
"sDom": "T<'top'>flrt<'bottom'>ip",
"bJQueryUI": true,
"iDisplayLength": 20,
"aLengthMenu": [ [ 20, 75, -1 ], [ 20, 75, "All" ] ],
"sAjaxSource": "data.php",
"aoColumns": [
{
"mData": "Name"
},
{
"mData": "teachers.name",
"sDefaultContent": ""
}
],
"oTableTools": {
"sRowSelect": "multi",
"aButtons": [
{ "sExtends": "editor_create", "editor": editor },
{ "sExtends": "editor_edit", "editor": editor },
{ "sExtends": "editor_remove", "editor": editor }
]
},
"fnInitComplete": function ( settings, json ) {
// Set the allowed values for the select and radio fields based on
// what is available in the database
editor.field('teacher.id').update( json.teachers );


}
} );
} );
}(jQuery));
[/code]

The following server side PHP program:

[code]
$editor = Editor::inst( $db, 'students' )
->field(
Field::inst( 'Name' )
->validator( 'Validate::required' ),
Field::inst( 'teacher_id' )
)
->join(
Join::inst( 'teachers', 'object' )
->join( 'teacher_id', 'id' )
->field(
Field::inst('id'),
Field::inst('name')
)
);

$out = $editor
->process( $_POST )
->data();

if ( !isset($_POST['action']) ) {
$out['teachers'] = $db
->select( 'teachers', 'id as value, name as label' )
->fetchAll();
}


// Send it back to the client
echo json_encode( $out );
[/code]

gent59

I have seen the same problem!!! please geve us a solution thanks

allan

@naj - Sorry for the delay in reply - I clean missed this message before! You can use the `set()` method of the Join class to tell it to not perform INSERTs or UPDATEs on the join: http://editor.datatables.net/docs/current/php/class-DataTables.Editor.Join.html#_set . Use `set( false )` .

@gent59 - I can't say since you don't include any code or a link, but I'm assuming its the same issue.

Allan