Error message "Not unique table\/alias:"

pzh20

I'm getting an error;
SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table\/alias: 'staffhours'

Here's the code I'm using

editor = new $.fn.dataTable.Editor( {
        ajax: "../php/n-staff.php",
        table: "#hourstable",
        fields: [ {
                label: "First name:",
                name: "staff.StaffForename"
            }, {
                label: "Last name:",
                name: "staff.StaffSurname"
                }, {
                label: "monday",
                name: "staffhours.monday"
            }, {
                label: "tuesday",
                name: "staffhours.tuesday"
            }, {
                label: "wednewday",
                name: "staffhours.wednewday"
            }, {
                label: "thursday",
                name: "staffhours.thursday"
            }, {
                label: "friday",
                name: "staffhours.friday"
            }, {
                label: "saturday",
                name: "staffhours.saturday"
            }, {
                label: "sunday",
                name: "staffhours.sunday"
            }, {
                label: "holiday",
                name: "staffhours.holiday"
            }, {
                label: "sickness",
                name: "staffhours.sickness"
            }
        ]

    } );
    editor.add( {
                type:    "hidden",
                name:    "weekendingdate",
                default: $("#weekendingdate").val()
            },
            {
                type:    "hidden",
                name:    "bu",
                default: $("#bu").val()
            }
             );
    var table = $('#hourstable').DataTable( {
        dom: "Tfrtip",
        ajax: "../php/n-staff.php",
        columns: [
            { data: null, defaultContent: 'staffHours.staffHoursID', orderable: false },
            { data: "staff.StaffForename" },
            { data: "staff.StaffSurname" },
            { data: "staffhours.monday" },
            { data: "staffhours.tuesday" },
            { data: "staffhours.wednesday" },
            { data: "staffhours.thursday" },
            { data: "staffhours.friday" },
            { data: "staffhours.saturday" },
            { data: "staffhours.sunday" },
            { data: "staffhours.holiday" },
            { data: "staffhours.sickness" }
        ],
        order: [ 2, 'asc' ],
        tableTools: {
            sRowSelect: "os",
            sRowSelector: 'td:first-child',
            aButtons: [
                { sExtends: "editor_create", editor: editor },
                { sExtends: "editor_edit",   editor: editor },
                { sExtends: "editor_remove", editor: editor }
            ]
        }
    } );

And here is the PHP code

include( "../../php/DataTables.php" );

// Alias Editor classes so they are easy to use
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Join,
    DataTables\Editor\Validate;

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'staffhours' )
    ->fields(
        Field::inst( 'staff.forename' )->validator( 'Validate::notEmpty' ),
        Field::inst( 'staff.Surname' )->validator( 'Validate::notEmpty' ),
        Field::inst( 'staffhours.monday' )->validator( 'Validate::numeric' ),
        Field::inst( 'staffhours.tuesday' )->validator( 'Validate::numeric' ),
        Field::inst( 'staffhours.wednesday' )->validator( 'Validate::numeric' ),
        Field::inst( 'staffhours.thursday' )->validator( 'Validate::numeric' ),
        Field::inst( 'staffhours.friday' )->validator( 'Validate::numeric' ),
        Field::inst( 'staffhours.saturday' )->validator( 'Validate::numeric' ),
        Field::inst( 'staffhours.sunday' )->validator( 'Validate::numeric' ),
        Field::inst( 'staffhours.holiday' )->validator( 'Validate::numeric' ),
        Field::inst( 'staffhours.sickness' )->validator( 'Validate::numeric' )
            
    )
    ->leftJoin( 'business_units', 'business_units.buID', '=', 'staff.buid' )
    ->leftJoin( 'staffhours', 'staffhours.StaffID', '=', 'staff.StaffID' )
    ->where('business_units.buID', 'bu')
    ->process( $_POST )
    ->json();

Not sure what the error means or where it's coming from.

Regards
Pete

allan

Interesting - I don't think I've come across that error before I'm afraid so I don't immediately know what is going wrong.

What action do you need to take in order for the error to occur? A simple load, or insert, edit or delete?

I assume that the database user being used has access to the tables staff, staffhours and business_units?

In the Database/Drivers/Mysql/Query.php file (in the Editor PHP libraries) you will find a line which is commented out that contains file_put_contents(). Could to comment that in and modify the file path that it writes to to be something suitable for your system. That will dump any queries that Editor makes into the file. Then once you get this error again if you could show me the query that is being run, that would be great.

Thanks,
Allan

pzh20

Allan,

This is a windows server running IIS. I seem to be getting another error now

Warning: file_put_contents(/tmp/b): failed to open stream: No such file or directory in C:\inetpub\wwwroot\LMDQOffice\js\extensions\Editor-PHP-1.4.0\php\Database\Driver\Mysql\Query.php on line 103

Pete

pzh20

Sorry, I will sort it.

Pete

pzh20

Where is it trying to write to? I cannot seem to get it to work.

Pete

allan

It will write the file to where ever the first parameter of file_put_contents() points to. You could perhaps try something like C:\temp\editorDebug.txt - or some other location if that doesn't exist.

The other option is to echo the SQL rather than writing it to a file. That will generate a JSON error on the client-side, but you will still be able to easily access the data using the browser's dev tools.

Allan

pzh20

OK, got it, it seems the code expects an ID field name, however, my table is staffhoursID for the id field.

SELECT  `staffhours`.`id` as 'staffhours.id', `staff`.`forename` as 'staff.forename', `staff`.`Surname` as 'staff.Surname', `staffhours`.`monday` as 'staffhours.monday', `staffhours`.`tuesday` as 'staffhours.tuesday', `staffhours`.`wednesday` as 'staffhours.wednesday', `staffhours`.`thursday` as 'staffhours.thursday', `staffhours`.`friday` as 'staffhours.friday', `staffhours`.`saturday` as 'staffhours.saturday', `staffhours`.`sunday` as 'staffhours.sunday', `staffhours`.`holiday` as 'staffhours.holiday', `staffhours`.`sickness` as 'staffhours.sickness' FROM  `staffhours` LEFT JOIN `business_units` ON `business_units`.`buID` = `staff`.`buid`  LEFT JOIN `staffhours` ON `staffhours`.`StaffID` = `staff`.`StaffID` WHERE `business_units`.`buID` = :where_0 

Also, I do not understand the where clause.

Pete

allan

my table is staffhoursID for the id field.

Ah! Use the optional third parameter for the constructor:

Editor::inst( $db, 'staffhours', 'staffhoursID' )

And that should do it!

The where in the query above is just how Editor builds it internally.

Regards,
Allan

pzh20

Gone back to the same error

{"error":"SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table\/alias: 'staffhours'","data":[]}

Regards
Pete

pzh20

Which part of the config generates the AS side of the query fields? I think the issue is the SELECT staffhours.staffhoursid as 'staffhours.staffhoursid'.

is it

label: "First name:",
name: "staff.StaffForename"

or

{ data: "staff.StaffForename" },

Pete

allan

Here is the query reformatted slightly (readable and to stop the forum mucking it up):

SELECT
    staffhours.staffhoursID as 'staffhours.id'
    staff.forename          as 'staff.forename'
    staff.Surname           as 'staff.Surname'
    staffhours.monday       as 'staffhours.monday'
    staffhours.tuesday      as 'staffhours.tuesday'
    staffhours.wednesday    as 'staffhours.wednesday'
    staffhours.thursday     as 'staffhours.thursday'
    staffhours.friday       as 'staffhours.friday'
    staffhours.saturday     as 'staffhours.saturday'
    staffhours.sunday       as 'staffhours.sunday'
    staffhours.holiday      as 'staffhours.holiday'
    staffhours.sickness     as 'staffhours.sickness'
FROM  staffhours
LEFT JOIN business_units ON business_units.buID = staff.buid 
LEFT JOIN staffhours     ON staffhours.StaffID  = staff.StaffID
WHERE business_units.buID = 'bu'

If you run that directly against the database, using phpMyAdmin or whatever your preference is, what do you get?

Which part of the config generates the AS side of the query fields? I think the issue is the SELECT staffhours.staffhoursid as 'staffhours.staffhoursid'.

It is simply the full field name (i.e. the table + column name in this case). It is just so that Editor can look the values up easily internally.

is it label: "First name:", name: "staff.StaffForename"
or
{ data: "staff.StaffForename" },

in DataTables use columns.data. In Editor use fields.name (you could use fields.data but things can get complicated if the name and data properties don't match, and we don't need that complication right now :-)

Allan

pzh20

It's the joins that are wrong, it works when it is like

FROM staff Left Join
staffhours On staffhours.StaffID = staff.StaffID left Join
business_units On business_units.buID = staff.buid

But I get the original error
Pete

pzh20

I think I fixed the problem. The from clause in the query is generated as ;

FROM  staff
LEFT JOIN staffhours ON staffhours.StaffID = staff.StaffID 
LEFT JOIN business_units ON business_units.buID = staff.buid

whereas the from query generated by Editor is

FROM  staffhours
LEFT JOIN business_units ON business_units.buID = staff.buid
LEFT JOIN staffhours     ON staffhours.StaffID  = staff.StaffID

I think that is due to the Editor::inst( $db, 'staffhours' ) statement.

I can now get it to run, but it produces no results. I think this is because of

->where('business_units.buID', 'bu')

bu is a variable that I pass across with

editor.add( {
                type:    "hidden",
                name:    "weekendingdate",
                default: $("#weekendingdate").val()
            },
            {
                type:    "hidden",
                name:    "bu",
                default: $("#bu").val()
            }
             );

but it is not the value but the name being used.

Pete

allan

->where('business_units.buID', 'bu')

The literal string bu is being used there. If you are passing in a value in the Editor form you can use $_POST['data']['db'] to access it. However, keep in mind that it is not defined when you request the data, unless you pass it in using ajax.data.

Regarding the FROM staff v FROM staffhours - whatever you pass into the Editor constructor is what will be used as the "main" table for the query.

Allan

pzh20

Thanks Allan, but I thought the way I was passing data was OK. What's the point of the above method is if the data is not available?

Is there an example of the Editor method?

I tried

ajax: {
url: "../php/n-staff.php",
data: {
"bu": $("#bu").val()
}
},
but nothing doing.

Pete

allan

The data is available on create and edit for that setup, but not on read or delete (since it is an Editor field, so it is only submitted on create and edit - no point in submitting a field value on read!).

So the question is, what was the intention of that where condition? It isn't really needed on create or edit since the primary key will take care of the unique value - and if you change the value of the buID field, it would no longer be able to update that field on edit since the submitted value wouldn't match what is in the database!

Allan

pzh20

The WHERE clause filters the data for the records that user is allowed to see and edit.

Seems reasonable to me.

bu is a variable stored when the use logs in. They can see and edit particular data based on this.

Pete

pzh20

Maybe I should explain more, this app is for users to enter/edit the staff hours for their staff in their business unit. Up until now, I wrote my own code to take form data and insert records into the database.

Certain users can add/edit data for other business units, but most only for theirs.

What I really need is the ability to add a new record with a dropdown with their staff listed, then add records for the days they worked. They enter data based on the weekending date which as yet I have not tried to implement.

I was starting by getting the existing data to be editable then moving on from there.

Pete

pzh20

OK, so I remove the where clause and I get all the table (although the first column contains staffHours.staffHoursID next to the checkbox!)

So the where clause is what's causing me problems, and it must be the way I am sending the data from the page to the PHP

Regards
Pete

allan

The WHERE clause filters the data for the records that user is allowed to see and edit.

Okay, I hadn't realised that before. Can I suggest that you don't involve the client-side for that variable in that case. It would be very easy for someone to manipulate the value and gain access that they shouldn't have.

Assuming you are using session information in PHP to store information about the logged in user what I would suggest is doing something like (obviously updated to use whatever variable you need):

->where('business_units.buID', $_SESSION['bu'])

Then remove the bu field from being submitted by Editor.

Regards,
Allan

pzh20

Thanks Allan,
This will work for the initial call if that was all there was to it. The problem is they may need to select a different BU that's not theirs as such. The page has 2 form fields outside of the table, BU and Weekendingdate, both of which I need to send to the where clause

Pete

allan

Okay - in that case use ajax.data and ajax.data to send the additional information to the server. DataTables example it is specifically for server-side processing, but will also work just fine with client-side processing. Editor is basically the same.

Allan

pzh20

Thanks again, but I have problems with the example as it doesn't seem to show how to use the variables passed in the server side script.

This is what I have for Editor and datatables

ajax: {
url: "../php/n-staff.php",
data: {
"bu": $("#bu").val(),
"wkend": $("#weekendingdate").val()
}
},

It seems to be only sending the wkend parameter as teh developer tools shows

http://localhost/lmdqoffice/js/extensions/Editor-PHP-1.4.0/examples/php/n-staff.php?wkend=2015-02-15&_=1425384571694

Regards
Pete

allan

How you access it depends upon the server-side scripting environment used, which is why it isn't documented in the DataTables documentation.

In this case, I would suggest you add type: 'POST' to your DataTables ajax option to POST the data (since Editor does that, then you are using a consistent method), then you would simply use $_POST['bu'] in PHP.

Allan

pzh20

I tried that bet get

Notice: Undefined index: bu in C:\inetpub\wwwroot\LMDQOffice\js\extensions\Editor-PHP-1.4.0\examples\php\n-staff.php on line 36

Notice: Undefined index: wkend in C:\inetpub\wwwroot\LMDQOffice\js\extensions\Editor-PHP-1.4.0\examples\php\n-staff.php on line 37
{"data":[],"options":[]}

here' the code

->leftJoin( 'staffhours', 'staffhours.StaffID', '=', 'staff.StaffID' )
    ->leftJoin( 'business_units', 'business_units.buID', '=', 'staff.buid' )
    ->where('business_units.buID', $_POST['bu'])
    ->where('staffhoursdate', $_POST['wkend'])
    ->process( $_POST )
    ->json();

Regards
Pete

allan

Did you change the type to POST for ajax? Also have you posted the bu and wkend options using ajax.data? Can you show me your Javascript code for this please?

Allan

pzh20

Allan,

I think all the code is in snippets abobe, but here it is in full;

$(document).ready(function() {
    editor = new $.fn.dataTable.Editor( {
        ajax: {
            url: "../php/n-staff.php",
            data: {
                    "bu": $("#bu").val(),
                "wkend": $("#weekendingdate").val()
            }
        },
        table: "#hourstable",
        fields: [ {
                label: "First name:",
                name: "staff.staffforename"
            }, {
                label: "Last name:",
                name: "staff.staffSurname"
                }, {
                label: "monday",
                name: "staffhours.monday"
            }, {
                label: "tuesday",
                name: "staffhours.tuesday"
            }, {
                label: "wednewday",
                name: "staffhours.wednewday"
            }, {
                label: "thursday",
                name: "staffhours.thursday"
            }, {
                label: "friday",
                name: "staffhours.friday"
            }, {
                label: "saturday",
                name: "staffhours.saturday"
            }, {
                label: "sunday",
                name: "staffhours.sunday"
            }, {
                label: "holiday",
                name: "staffhours.holiday"
            }, {
                label: "sickness",
                name: "staffhours.sickness"
            }
        ]

    } );
    editor.add( {
                type:    "hidden",
                name:    "wkend",
                default: $("#weekendingdate").val()
            },
            {
                type:    "hidden",
                name:    "bu",
                default: $("#bu").val()
            }
             );
    editor
        .on( 'open', function ( e, type ) {
            if ( type === 'inline' ) {
                // Listen for a tab key event when inline editing
                $(document).on( 'keydown.editor', function ( e ) {
                    if ( e.keyCode === 9 ) {
                        e.preventDefault();

                        // Find the cell that is currently being edited
                        var cell = $('div.DTE').parent();

                        if ( e.shiftKey && cell.prev().length && cell.prev().index() !== 0 ) {
                            // One cell to the left (skipping the first column)
                            cell.prev().click();
                        }
                        else if ( e.shiftKey ) {
                            // Up to the previous row
                            cell.parent().prev().children().last(0).click();
                        }
                        else if ( cell.next().length ) {
                            // One cell to the right
                            cell.next().click();
                        }
                        else {
                            // Down to the next row
                            cell.parent().next().children().eq(1).click();
                        }
                    }
                } );
            }
        } )
        .on( 'close', function () {
            $(document).off( 'keydown.editor' );
        } );

    $('#hourstable').on( 'click', 'tbody td:not(:first-child)', function (e) {
        editor.inline( this, {
            submitOnBlur: true
        } );
    } );

    var table = $('#hourstable').DataTable( {
        dom: "Tfrtip",
        ajax: {
            url: "../php/n-staff.php",
            data: {
                    "bu": $("#bu").val(),
                "wkend": $("#weekendingdate").val()
            }
        },
        columns: [
            { data: null, defaultContent: 'staffHours.staffHoursID', orderable: false },
            { data: "staff.staffforename" },
            { data: "staff.staffSurname" },
            { data: "staffhours.monday" },
            { data: "staffhours.tuesday" },
            { data: "staffhours.wednesday" },
            { data: "staffhours.thursday" },
            { data: "staffhours.friday" },
            { data: "staffhours.saturday" },
            { data: "staffhours.sunday" },
            { data: "staffhours.holiday" },
            { data: "staffhours.sickness" }
        ],
        
        order: [ 2, 'asc' ],
        tableTools: {
            sRowSelect: "os",
            sRowSelector: 'td:first-child',
            aButtons: [
                { sExtends: "editor_create", editor: editor },
                { sExtends: "editor_edit",   editor: editor },
                { sExtends: "editor_remove", editor: editor }
            ]
        }
    } );
} );

Regards
Pete

allan

Hi Pete,

Thanks for the code. It looks like the type option that I've mentioned above hasn't been added to the ajax option. I would suggest using:

        ajax: {
            url: "../php/n-staff.php",
            type: "POST",
            data: {
                    "bu": $("#bu").val(),
                "wkend": $("#weekendingdate").val()
            }
        },

Otherwise DataTables makes a GET request, but you want POST since that is what your PHP script is looking for.

Allan

pzh20

Wow, nearly there. Thanks so much for your help. All that's happening now is the table is displaying the name of the ID field as follows;

First name  Last name   Monday  Tuesday Wednesday   Thursday    Friday  Saturday    Sunday  Holiday Sickness
staffHours.staffHoursID Richard Allinson    8.45    6.30    9.00    8.15    6.30    7.00    8.30    8.15    6.15
staffHours.staffHoursID Sergiu  Moiseev 8.45    7.15    6.30    6.30    8.30    6.45    6.45    8.00    6.15
staffHours.staffHoursID Moise   Sombie  8.45    8.45    8.00    7.00    8.30    6.15    7.00    8.45    8.45
staffHours.staffHoursID Moise   Sombie  7.15    7.30    6.45    7.45    8.45    8.00    6.30    6.30    8.45

I assume it's to do with

columns: [
{ data: null, defaultContent: 'staffHours.staffHoursID', orderable: false },

but if I remove it, the header columns do not line up.

Pete

allan

If you want the first name in the first column, why are you not using data: 'staff.staffforename' for that column?

edit I see from above you have that as your second column. It looks like you just need to remove the first column definition from the DataTable.

Allan

pzh20

I took the example and used it's definitions. My HTML table does not have a column for the ID, the Jquery does as ;

columns: [
{ data: null, defaultContent: 'staffHours.staffHoursID', orderable: false },
{ data: "staff.staffforename" },
{ data: "staff.staffSurname" },.......

however, if I remove it, the columns do not align as if there is still something being generated for a heading. I get the checkbox and the firstname next to eachother but no heading, then the surname under the firstname heading.

Pete