Unable to create a NEW location.
Unable to create a NEW location.
Allan,
I have to ask you to help me with this problem. I have tried to solve this and have looked at your examples in Editor and Joins but I am not sure what I am doing wrong and I know that what I am attempting to do should be fairly straight forward. Can you look this over and help me solve the problem?
I have included the code I am using for the Client and Server. The Server code shows four case studies (Case: 1-4 ) I have run to see if I could figure out where the problem occurs. Each case has been run under two variations of the leftJoin (“A” and “B”). I have included the errors that result under each of the 8 trials I ran in the code.
The tables I am using are simple:
The ‘client’ table - contains the PRIMARY KEY ‘clientid’ in column: clientid associated with the ‘clientname’ in the column: clientname.
The ‘location’ table - contains the PRIMARY KEY ‘locid’ in column: locid associated with the ‘clientid’ in column: clientid. There can be several ‘locid’s’ per ‘clientid for the various geographical locations associated with a client.
I am able to EDIT and DELETE locations without a problem but when I try to create a NEW location for a client I am running into the problems.
Can you tell me what I am doing wrong. I thought I had the logic worked out.
I have a Debugger instance code llaklx
Would appreciate any help you might provide.
Regards,
Paul
<?php
// DataTables PHP library
include( "../../php/DataTables.php" );
use
DataTables\Editor,
DataTables\Editor\Field,
DataTables\Editor\Format,
DataTables\Editor\Join,
DataTables\Editor\Validate;
// Case 1
Editor::inst( $db, 'location', 'locid') // leftJoin "A"
// DataTables warning: table id=Location - SQLSTATE[42000]: Syntax error or access violation: 1066
// Not unique table/alias: 'location'
// leftJoin "B"
// DataTables warning: table id=Location - Duplicate field detected -
// a field with the name `location.clientid` already exists.
->fields(
Field::inst( 'client.clientname'),
Field::inst( 'client.clientid'),
Field::inst( 'location.locid'),
Field::inst( 'location.clientid')
->options('location', 'clientid', 'clientid'),
Field::inst( 'location.clientid'),
Field::inst( 'location.locname'),
Field::inst( 'location.locaddress'),
Field::inst( 'location.locphone'),
Field::inst( 'location.locfax'),
Field::inst( 'location.loccontact'),
Field::inst( 'location.locemail')
// Case 2
// Editor::inst( $db, 'client', 'clientid') // leftJoin "A"
// // Syntax Error: SQLSTATE[42000]: Syntax error or access violation:
// // 1110 Column 'clientid' specified twice
//
// // leftJoin "B"
// // DataTables warning: table id=Location - SQLSTATE[42000]: Syntax error or access violation:
// // 1066 Not unique table/alias: 'client'
//
//
// ->fields(
// Field::inst( 'client.clientname'),
// Field::inst( 'client.clientid'),
// Field::inst( 'location.locid'),
// Field::inst( 'location.clientid')
// ->options('location', 'clientid', 'clientid'),
// Field::inst( 'location.locname'),
// Field::inst( 'location.locaddress'),
// Field::inst( 'location.locphone'),
// Field::inst( 'location.locfax'),
// Field::inst( 'location.loccontact'),
// Field::inst( 'location.locemail')
// Case 3
// Editor::inst( $db, 'location', 'locid') // leftJoin "A"
// // This code generates the following error: DataTables warning: table id=Location - SQLSTATE[42000]: Syntax error or access violation: 1066
// // Not unique table/alias: 'location'
//
//
// // leftJoin "B"
// // A NEW location is created and the "locid" (Primary Key in the 'location' table) is incremented
// // but it assigns a 'clientid' = 0 to the newly created location
//
//
// ->fields(
// Field::inst( 'client.clientname'),
// Field::inst( 'client.clientid'),
// Field::inst( 'location.locid'),
// Field::inst( 'location.clientid'),
// Field::inst( 'location.locname'),
// Field::inst( 'location.locaddress'),
// Field::inst( 'location.locphone'),
// Field::inst( 'location.locfax'),
// Field::inst( 'location.loccontact'),
// Field::inst( 'location.locemail')
// Case 4
// Editor::inst( $db, 'client', 'clientid') // leftJoin "A"
// // A NEW location is created and the "locid" (Primary Key in the 'location' table) is incremented
// // but it assigns a 'clientid' = 0 to the newly created location
//
// // leftJoin "B"
// // This code generates the following error: DataTables warning: table id=Location - SQLSTATE[42000]: Syntax error or access violation: 1066
//// // Not unique table/alias: 'cl// when using leftJoin "B"
//
// ->fields(
// Field::inst( 'client.clientname'),
// Field::inst( 'client.clientid'),
// Field::inst( 'location.locid'),
// Field::inst( 'location.clientid'),
// Field::inst( 'location.locname'),
// Field::inst( 'location.locaddress'),
// Field::inst( 'location.locphone'),
// Field::inst( 'location.locfax'),
// Field::inst( 'location.loccontact'),
// Field::inst( 'location.locemail')
//
//
)
->leftJoin('location', 'location.clientid', '=', 'client.clientid' ) //leftJoin "A"
// ->leftJoin('client', 'client.clientid', '=', 'location.clientid' ) //leftJoin "B"
->process($_POST)
->json();
?>
Replies
Can you show me your Javascript for Editor here as well please? Are you trying to insert into two different tables (client and location) or only into one (location)? If the latter, then you can rule out cases 2 and 4 as options.
Case 1 looks the most likely to me - and as the error states:
Just remove the second:
Field::inst( 'location.clientid').Thanks,
Allan
That was it Allan. Works like a charm. Wish I hadn't made such a dumb error. Thanks again for looking at this. I am only trying to insert the new location into the location table so Case 1 leftJoin "B" was the answer once I removed the extraneous Field::inst('location.clientid').
Cheers,
Paul