Can't edit (update) join table.

nidedi

Hello. I have two simple tables: customer and location. In location I have the primary key from customer and a few other attributes. What I want is to join these two tables and be able to edit any of the columns in the resulting table. But it won't let me, saying the following:
An error has occurred - Please contact the system administrator
The response I get is this:
Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '1' for key 'PRIMARY'' in C:....

These are the tables:
A] location[id(PK), Address, CP, City, Country, Phone, Email, Comments, ID_Customer_Location(FK), ID_Partner_Location(FK)];
- ID_Customer_Location is a foreign key. A customer can have more than one location.
- ID_Partner_Location is a foreign key, having to do with another table (Partner).
Both of these keys can be NULL. When I create the location for a customer, ID_Partner_Location is NULL. When I create it for a partner, ID_Customer_Location is NULL.

B] Customer[ID_Customer(PK), Gender, Firstname, Surname, Birthday, Customer_number, ID_Sponsor (FK)];
- A sponsor can have 0 or more customers. There is no separate table for sponsors.


This is the code:

1] PHP:

$editor = Editor::inst( $db, 'location' )
->fields(
Field::inst( 'ID_Customer_Location' ),
Field::inst( 'Address' ),
Field::inst( 'CP' ),
Field::inst( 'City' ),
Field::inst( 'Country' ),
Field::inst( 'Phone' ),
Field::inst( 'Email' ),
Field::inst( 'Comments' )
)
->pkey('id')
->join(
Join::inst( 'customer', 'object' )
->join( 'ID_Customer_Location', 'ID_Customer')
//->set(false)
->fields(
Field::inst( 'ID_Customer' ),
Field::inst( 'Firstname' ),
Field::inst( 'Surname' )
)
);

// The "process" method will handle data get, create, edit and delete
// requests from the client
$out = $editor
->process($_POST)
->data();

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

2] JS:

var editor; // use a global for the submit and return data rendering in the examples

$(document).ready(function() {
editor = new $.fn.dataTable.Editor( {
"ajaxUrl": "/index.php/c_datatable/join_location_and_customer",
"domTable": "#location_and_customer_table",
"fields": [ {
"label": "ID_Customer:",
"name": "customer.ID_Customer"
}, {
"label": "Firstname:",
"name": "customer.Firstname"
}, {
"label": "Surname:",
"name": "customer.Surname"
}, {
"label": "Address:",
"name": "Address"
}, {
"label": "CP:",
"name": "CP"
}, {
"label": "City:",
"name": "City"
}, {
"label": "Country:",
"name": "Country"
}, {
"label": "Phone:",
"name": "Phone"
}, {
"label": "Email:",
"name": "Email"
}, {
"label": "Comments:",
"name": "Comments"
}
]
} );

$('#location_and_customer_table').dataTable( {
"sDom": "Tfrtip",
"sAjaxSource": "/index.php/c_datatable/join_location_and_customer",
"aoColumns": [
{ "mData": "customer.ID_Customer" },
{ "mData": "customer.Firstname" },
{ "mData": "customer.Surname" },
{ "mData": "Address" },
{ "mData": "CP" },
{ "mData": "City" },
{ "mData": "Country" },
{ "mData": "Phone" },
{ "mData": "Email" },
{ "mData": "Comments" }
],
"oTableTools": {
"sRowSelect": "single",
"aButtons": [
{ "sExtends": "editor_create", "editor": editor },
{ "sExtends": "editor_edit", "editor": editor },
{ "sExtends": "editor_remove", "editor": editor }
]
}
} );
} );


3] HTML:




ID_Customer
Firstname
Surname
Address
CP
City
Country
Phone
Email
Comments




ID_Customer
Firstname
Surname
Address
CP
City
Country
Phone
Email
Comments




Can you tell me what I do wrong?

Thank you.

allan

> Duplicate entry '1' for key 'PRIMARY'' in C:....

It look alike there is an insert being done, or an update, trying to add the same primary key as already exists in the table, which is invalid. What is the rest o the error message? That will say what field and table are triggering the error.

Allan

nidedi

This is the complete response when trying to update an attribute belonging to the child table. It doesn't really help me understand what I need to adjust in my code.




( ! ) Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '1' for key 'PRIMARY'' in C:\wamp\www\project_name\application\libraries\datatables\examples\php\lib\Database\Driver\Mysql\Query.php on line 98
( ! ) PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '1' for key 'PRIMARY' in C:\wamp\www\project_name\application\libraries\datatables\examples\php\lib\Database\Driver\Mysql\Query.php on line 98
Call Stack
#TimeMemoryFunctionLocation
10.0013705096{main}( )..\index.php:0
20.0046798136require_once( 'C:\wamp\www\project_name\system\core\CodeIgniter.php' )..\index.php:202
30.04982397352call_user_func_array
( )..\CodeIgniter.php:359
40.04982397432c_datatable->join_location_and_customer( )..\CodeIgniter.php:359
51.08803948144DataTables\Editor->process( )..\c_datatable.php:84
61.08863949800DataTables\Editor->_update( )..\Editor.php:336
71.09353975632DataTables\Editor\Join->update( )..\Editor.php:536
81.09353975632DataTables\Editor\Join->_update_row( )..\Join.php:496
91.09373976520DataTables\Database->push( )..\Join.php:620
101.09523976448DataTables\Database->insert( )..\Database.php:141
111.09553979520DataTables\Database\Query->exec( )..\Database.php:122
121.09563979648DataTables\Database\Query->_insert( )..\Query.php:197
131.09633981888DataTables\Database\DriverMysqlQuery->_exec( )..\Query.php:551
141.09633981936PDOStatement->execute( )..\Query.php:98

allan

It doesn't really help me either actually! I expected it to say what table it is failing on. I think we'd need a dump of your database schema to see what is going wrong.

Allan

nidedi

How can I send this schema to you? I have it, but I can't post it here because it's too long.

nidedi

These are the parts of the schema that you may need to see in order to figure out what's going on. Some of them are directly used in the join (location and customer), while others are linked to them either directly or indirectly.

SET FOREIGN_KEY_CHECKS=0;

//...

-- ----------------------------
-- Table structure for `complementary_measurement`
-- ----------------------------
DROP TABLE IF EXISTS `complementary_measurement`;
CREATE TABLE `complementary_measurement` (
`ID_Complementary_Measurement` int(11) NOT NULL AUTO_INCREMENT ,
`Name_Complementary_Measurement_FR` varchar(10) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL ,
`Name_Complementary_Measurement_EN` varchar(10) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL ,
`ID_Measurement` int(11) NOT NULL ,
PRIMARY KEY (`ID_Complementary_Measurement`),
FOREIGN KEY (`ID_Measurement`) REFERENCES `measurement` (`ID_Measurement`) ON DELETE CASCADE ON UPDATE CASCADE,
INDEX `ID_Measurement` (`ID_Measurement`) USING BTREE
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=latin1 COLLATE=latin1_swedish_ci
AUTO_INCREMENT=1

;


-- ----------------------------
-- Table structure for `customer`
-- ----------------------------
DROP TABLE IF EXISTS `customer`;
CREATE TABLE `customer` (
`ID_Customer` int(11) NOT NULL AUTO_INCREMENT ,
`Gender` enum('Male','Female') CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT 'Male' ,
`Firstname` varchar(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL ,
`Surname` varchar(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL ,
`Birthday` date NULL DEFAULT NULL ,
`Customer_number` varchar(10) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL ,
`ID_Sponsor` int(11) NULL DEFAULT NULL ,
PRIMARY KEY (`ID_Customer`),
FOREIGN KEY (`ID_Sponsor`) REFERENCES `customer` (`ID_Customer`) ON DELETE NO ACTION ON UPDATE NO ACTION,
INDEX `ID_Sponsor` (`ID_Sponsor`) USING BTREE
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=latin1 COLLATE=latin1_swedish_ci
AUTO_INCREMENT=47

;


-- ----------------------------
-- Table structure for `location`
-- ----------------------------
DROP TABLE IF EXISTS `location`;
CREATE TABLE `location` (
`Address` varchar(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL ,
`CP` int(5) NULL DEFAULT NULL ,
`City` varchar(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL ,
`Country` varchar(10) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL ,
`Phone` varchar(10) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT '' ,
`Email` varchar(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL ,
`Comments` varchar(1000) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL ,
`ID_Customer_Location` int(11) NULL DEFAULT NULL ,
`ID_Partner_Location` int(11) NULL DEFAULT NULL ,
`id` int(11) NOT NULL AUTO_INCREMENT ,
PRIMARY KEY (`id`),
FOREIGN KEY (`ID_Customer_Location`) REFERENCES `customer` (`ID_Customer`) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (`ID_Partner_Location`) REFERENCES `partner` (`ID_Partner`) ON DELETE CASCADE ON UPDATE CASCADE,
INDEX `ID_Customer_Location` (`ID_Customer_Location`) USING BTREE ,
INDEX `ID_Partner_Location` (`ID_Partner_Location`) USING BTREE
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=latin1 COLLATE=latin1_swedish_ci
AUTO_INCREMENT=10010

;


-- ----------------------------
-- Table structure for `measurement`
-- ----------------------------
DROP TABLE IF EXISTS `measurement`;
CREATE TABLE `measurement` (
`ID_Measurement` int(11) NOT NULL AUTO_INCREMENT ,
`Name_Measurement_FR` varchar(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL ,
`Name_Measurement_EN` varchar(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL ,
`Unit` enum('kg','cm','none') CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT 'cm' ,
`Gender_measurement` enum('Male','Female','Both') CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT 'Both' ,
PRIMARY KEY (`ID_Measurement`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=latin1 COLLATE=latin1_swedish_ci
AUTO_INCREMENT=12

;

-- ----------------------------
-- Table structure for `measurement_adjustment`
-- ----------------------------
DROP TABLE IF EXISTS `measurement_adjustment`;
CREATE TABLE `measurement_adjustment` (
`ID_Measurement_Adjustment` int(11) NOT NULL ,
`ID_Adjustment_Measurement` int(11) NOT NULL ,
FOREIGN KEY (`ID_Adjustment_Measurement`) REFERENCES `adjustment` (`ID_Adjustment`) ON DELETE NO ACTION ON UPDATE NO ACTION,
FOREIGN KEY (`ID_Measurement_Adjustment`) REFERENCES `measurement` (`ID_Measurement`) ON DELETE NO ACTION ON UPDATE NO ACTION,
INDEX `ID_Measurement_Adjustment` (`ID_Measurement_Adjustment`) USING BTREE ,
INDEX `ID_Adjustment_Measurement` (`ID_Adjustment_Measurement`) USING BTREE
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=latin1 COLLATE=latin1_swedish_ci

;

nidedi

Other things from the schema:

-- ----------------------------
-- Auto increment value for `additional_selling_price`
-- ----------------------------
ALTER TABLE `additional_selling_price` AUTO_INCREMENT=1;

-- ----------------------------
-- Auto increment value for `adjustment`
-- ----------------------------
ALTER TABLE `adjustment` AUTO_INCREMENT=1;

-- ----------------------------
-- Auto increment value for `complementary_measurement`
-- ----------------------------
ALTER TABLE `complementary_measurement` AUTO_INCREMENT=1;

-- ----------------------------
-- Auto increment value for `customer`
-- ----------------------------
ALTER TABLE `customer` AUTO_INCREMENT=47;

-- ----------------------------
-- Auto increment value for `fabric`
-- ----------------------------
ALTER TABLE `fabric` AUTO_INCREMENT=3;

-- ----------------------------
-- Auto increment value for `group`
-- ----------------------------
ALTER TABLE `group` AUTO_INCREMENT=3;

-- ----------------------------
-- Auto increment value for `lining`
-- ----------------------------
ALTER TABLE `lining` AUTO_INCREMENT=4;

-- ----------------------------
-- Auto increment value for `location`
-- ----------------------------
ALTER TABLE `location` AUTO_INCREMENT=10010;

-- ----------------------------
-- Auto increment value for `measurement`
-- ----------------------------
ALTER TABLE `measurement` AUTO_INCREMENT=12;

-- ----------------------------
-- Auto increment value for `option`
-- ----------------------------
ALTER TABLE `option` AUTO_INCREMENT=1;

-- ----------------------------
-- Auto increment value for `order`
-- ----------------------------
ALTER TABLE `order` AUTO_INCREMENT=2;

-- ----------------------------
-- Auto increment value for `order_products`
-- ----------------------------
ALTER TABLE `order_products` AUTO_INCREMENT=1;

-- ----------------------------
-- Auto increment value for `package`
-- ----------------------------
ALTER TABLE `package` AUTO_INCREMENT=1;

-- ----------------------------
-- Auto increment value for `partner`
-- ----------------------------
ALTER TABLE `partner` AUTO_INCREMENT=4;

-- ----------------------------
-- Auto increment value for `personalization`
-- ----------------------------
ALTER TABLE `personalization` AUTO_INCREMENT=1;

-- ----------------------------
-- Auto increment value for `product`
-- ----------------------------
ALTER TABLE `product` AUTO_INCREMENT=4;

-- ----------------------------
-- Auto increment value for `tailor`
-- ----------------------------
ALTER TABLE `tailor` AUTO_INCREMENT=3;

-- ----------------------------
-- Auto increment value for `type_partner`
-- ----------------------------
ALTER TABLE `type_partner` AUTO_INCREMENT=3;

-- ----------------------------
-- Auto increment value for `value_measurement`
-- ----------------------------
ALTER TABLE `value_measurement` AUTO_INCREMENT=19;

nidedi

Any idea?