DataTables
Advanced interaction
features for your tables.
Editor
Comprehensive editing
library for DataTables.
Manual
Download
≡ Show site navigation

Can't edit (update) join table.

Can't edit (update) join table.

nidedinidedi Posts: 13Questions: 1Answers: 0
edited June 2013 in Editor
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.

Replies

  • allanallan Posts: 63,498Questions: 1Answers: 10,471 Site admin
    > 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
  • nidedinidedi Posts: 13Questions: 1Answers: 0
    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
  • allanallan Posts: 63,498Questions: 1Answers: 10,471 Site admin
    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
  • nidedinidedi Posts: 13Questions: 1Answers: 0
    edited June 2013
    How can I send this schema to you? I have it, but I can't post it here because it's too long.
  • nidedinidedi Posts: 13Questions: 1Answers: 0
    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

    ;
  • nidedinidedi Posts: 13Questions: 1Answers: 0
    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;
  • nidedinidedi Posts: 13Questions: 1Answers: 0
    Any idea?
This discussion has been closed.

DataTables

DataTables designed and created by SpryMedia Ltd.
© 2007-2023 MIT licensed. Privacy policy. Supporters.
SpryMedia Ltd is registered in Scotland, company no. SC456502.