Error message "Not unique table\/alias:"

Error message "Not unique table\/alias:"

pzh20pzh20 Posts: 66Questions: 15Answers: 0
edited March 2015 in Editor

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

This question has accepted answers - jump to:

«1

Answers

  • allanallan Posts: 61,438Questions: 1Answers: 10,049 Site admin

    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

  • pzh20pzh20 Posts: 66Questions: 15Answers: 0

    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

  • pzh20pzh20 Posts: 66Questions: 15Answers: 0

    Sorry, I will sort it.

    Pete

  • pzh20pzh20 Posts: 66Questions: 15Answers: 0

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

    Pete

  • allanallan Posts: 61,438Questions: 1Answers: 10,049 Site admin

    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

  • pzh20pzh20 Posts: 66Questions: 15Answers: 0
    edited March 2015

    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

  • allanallan Posts: 61,438Questions: 1Answers: 10,049 Site admin

    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

  • pzh20pzh20 Posts: 66Questions: 15Answers: 0

    Gone back to the same error

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

    Regards
    Pete

  • pzh20pzh20 Posts: 66Questions: 15Answers: 0
    edited March 2015

    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

  • allanallan Posts: 61,438Questions: 1Answers: 10,049 Site admin
    edited March 2015

    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

  • pzh20pzh20 Posts: 66Questions: 15Answers: 0
    edited March 2015

    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

  • pzh20pzh20 Posts: 66Questions: 15Answers: 0
    edited March 2015

    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

  • allanallan Posts: 61,438Questions: 1Answers: 10,049 Site admin

    ->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

  • pzh20pzh20 Posts: 66Questions: 15Answers: 0
    edited March 2015

    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

  • allanallan Posts: 61,438Questions: 1Answers: 10,049 Site admin

    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

  • pzh20pzh20 Posts: 66Questions: 15Answers: 0
    edited March 2015

    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

  • pzh20pzh20 Posts: 66Questions: 15Answers: 0
    edited March 2015

    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

  • pzh20pzh20 Posts: 66Questions: 15Answers: 0

    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

  • allanallan Posts: 61,438Questions: 1Answers: 10,049 Site admin

    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

  • pzh20pzh20 Posts: 66Questions: 15Answers: 0
    edited March 2015

    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

  • allanallan Posts: 61,438Questions: 1Answers: 10,049 Site admin

    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

  • pzh20pzh20 Posts: 66Questions: 15Answers: 0
    edited March 2015

    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

  • allanallan Posts: 61,438Questions: 1Answers: 10,049 Site admin

    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

  • pzh20pzh20 Posts: 66Questions: 15Answers: 0
    edited March 2015

    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

  • allanallan Posts: 61,438Questions: 1Answers: 10,049 Site admin

    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

  • pzh20pzh20 Posts: 66Questions: 15Answers: 0
    edited March 2015

    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

  • allanallan Posts: 61,438Questions: 1Answers: 10,049 Site admin

    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

  • pzh20pzh20 Posts: 66Questions: 15Answers: 0
    edited March 2015

    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

  • allanallan Posts: 61,438Questions: 1Answers: 10,049 Site admin
    edited March 2015

    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

  • pzh20pzh20 Posts: 66Questions: 15Answers: 0

    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

This discussion has been closed.