SQLSTATE[42000]: Syntax error or access violation: 1110 Column 'columnName' specified twice

pansengtat

I was creating a brand new page with displays a single table.

There are two tables added to an existing database.

1 table is called Projects, and it only has:

• ID: this auto-increments and is unique

• Name: this is unique, and stores as a VARCHAR

1 table is called Orders, and it has many columns; but most important are:

• ID: this auto-increments, and is unique

• OrderNum: this is unique, and stores as a VARCHAR

• ProjectName: this is not unique, and stores as a VARCHAR

Relationship is that 1 Project can have many Orders. To get data from other tables, the Order must link through the Project's Name using Orders.ProjectName.

The scenario is that I created two instances of Editor in the JS file: 1 for creating and 1 for editing.

Reason is that for creation,

• Orders and Projects must have an entry each, provided that the Name in Projects didn't already exists; if it does, creation of entry in Projects should be ignored, and proceed with creation of entry in Orders,

whereas for editing:

• All details in the selected entry in Orders should be updated.

When I either create/edit an entry in table Orders, I get this error:

SQLSTATE[42000]: Syntax error or access violation: 1110 Column 'Name' specified twice

This obviously refers to the Projects table's column Name. But what gives?

I was cracking my head for over 1 full-day, and I still cannot debug or find the source of the problem, so I abstracted the code here hoping that I could get some assistance in debugging:

jQuery: Editor Instance

editor = new $.fn.dataTable.Editor( {
    ajax: "php/OrderEditor.php",
    table: "#Order",
    fields: [
        {
            label:      "Project Name: ",
            name:       "Orders.ProjectName"
        }, {
            label:      "Project Name (Hidden): ",
            name:       "Projects.Name",
            type:       "hidden"
        }
    ]
} );

//--- Check and duplicate ---//
editor.on('preSubmit', function(e,obj,action){
    if ( $.trim(obj.data.Orders.ProjectName) === '' ) {
        this.error('Orders.ProjectName', 'Project Name is required.');
        return false;
    }
    else {
        obj.data.Projects.Name = $.trim(obj.data.Orders.ProjectName);
    }
});

jQuery: Creator Instance

adder = new $.fn.dataTable.Editor({
    ajax: "php/OrderAdder.php",
    table: "#Order",
    fields: [
        {
            label:      "Project Name: ",
            name:       "Projects.Name"
        }, {
            label:      "Project Name (Hidden): ",
            name:       "Orders.ProjectName",
            type:       "hidden"
        }
    ]
} );

//--- Check and duplicate ---//
adder.on('preSubmit', function(e,obj,action){
    if ( $.trim(obj.data.Projects.Name) === '' ) {
        this.error('Projects.Name', 'Project Name is required.');
        return false;
    }
    else {
        obj.data.Orders.ProjectName = $.trim(obj.data.Projects.Name);
    }
});

Client-side: PHP - Creator

$data = Editor::inst($db, 'Orders', 'ID')
    ->field(
        Field::inst('Orders.ProjectName')->validator('Validate::notEmpty'), 
        Field::inst('Projects.Name')->validator('Validate::notEmpty')
        //... Lots of stuff here
    )
    ->leftJoin('Projects', 'Orders.ProjectName', '=', 'Projects.Name')
    //... Even more stuff here
    ->process($_POST)
    ->data();

Client-side: PHP - Editor

$data = Editor::inst($db, 'Orders', 'ID')
    ->field(
        Field::inst('Orders.ProjectName')->validator('Validate::notEmpty'), 
        Field::inst('Projects.Name')->validator('Validate::notEmpty'),
        //... Lots of stuff here
        Field::inst('Extra.HoursSpent')->set(false)
    )
    ->leftJoin('Projects', 'Orders.ProjectName', '=', 'Projects.Name')
    ->leftJoin('Extra', 'Orders.OrderNumber', '=', 'Extra.TaggedOrderNumber')
    //... Even more stuff here
    ->process($_POST)
    ->data();

allan

The best way of debugging this is to open up the Query.php file for the database type you are using and add (or uncomment, as I generally leave a debug line in there) a debug line that will dump the generated query to a file (or to the output, but be aware that it will cause Editor to give an invalid JSON response error). That way you will be able to see the SQL being built.

Perhaps you could post it here?

One other thing, are you using v1.3.3 of the Editor libraries?

Thanks,
Allan

pansengtat

I wasn't aware that there was an update for Editor, mine is v1.3.2.
I did find a workaround my issue, so instead of comparing Projects.Name with Orders.ProjectName, I changed the ProjectName column to a BIGINT(20) unsigned column ProjectID, and do comparison with Projects.ID instead, as comparison with a unique Projects.Name on a mySQL database is unfeasible and also impractical (I managed to talk to my higher-ups about taking some liberties in implementing my new page). I also changed the Editor instance to use a select type in the Project Name field as opposed to a free-text field, so that users are forced to select a list of current options.

allan

Nice one. Good to hear that you resolved the problem.

Allan