Issue when trying to upload file

orionaselite

I have a table named users in this table at some point I have a field named profile_pic. In this field all I store is a filename that is based on the user_id which is my primary key. e.g. user #3's profile pic would be named 3.jpg and 4's would be 4.jpg. I started by trying to use the file upload example https://editor.datatables.net/examples/advanced/upload.html

In my php file I have the following

Field::inst( 'profile_pic' )    
    ->setFormatter( 'Format::nullEmpty' )
     ->upload( Upload::inst( $_SERVER['DOCUMENT_ROOT'].'../../upload/profile_pics/__ID__.__EXTN__' )
                ->db( 'users', 'user_id', array(
                    'profile_pic'    => Upload::DB_FILE_NAME
                    
                ) )
                ->allowedExtensions( [ 'jpg' ], "Please provide a .jpg image" )
            ),

My initial issue is that I get a SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '' for key 'email' I know what that mean but i was updating the profile_pic field not the email field. ideas

allan

profile_pic' => Upload::DB_FILE_NAME

This will store the name of the file that was uploaded. It it not store the file name that you have created (__ID__.__EXTN__). To be honest, if you want profile_pic to contain just the id, it is redundant since you already have that information in user_id. Just concat .jpg to the id when you request the file.

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '' for key 'email'

I don't see email being used anywhere in the above code, so I'm not sure why that would be happening. Can you show the full code please?

Allan

orionaselite

Excuse the Greek Localization but I am guessing you don't mind it

<script>
var editor; // use a global for the submit and return data rendering in the examples



(function($){

$(document).ready(function() {
     editor = new $.fn.dataTable.Editor( {
        ajax: 'php/table.users.php',
        table: '#users',
        fields: [
            {
                "label": "Εικόνα Προφίλ",
                "name": "profile_pic",
                "type": "upload",
                display:function ( data, type, row ) {
                var path ='http://localhost/test/upload/profile_pics/'+data;    
                //return '<img class="img-circle" src="'+path" width="100"/>';
                return '<img width="100" class="img-circle" src="'+path+'"/>';                },
                clearText: "Διαγραφή",
                noImageText: 'Καμία εικόνα'
            },
            
            {
                "label": "Ονοματεπώνυμο",
                "name": "full_name"
            },
            {
                "label": "Email",
                "name": "email"
            },
            {
                "label": "Κωδικός",
                "name": "password",
                "type": "password"
            },
            {
                "label": "Ημ. Γεννήσεως",
                "name": "dob",
                "type": "datetime",
                "format": "d-m-Y"
            },
            {
                "label": "Αρ. Τηλεφώνου",
                "name": "phone_number"
            },
            {
                "label": "Κατάσταση",
                "name": "status",
                type:  "select",
                options: [
                    { label: "Ανενεργός",value: 0 },
                    { label: "Ενεργός",value: 1 },
                    { label: "Διαγραμμένος",value: 2}
                    
                ]
            }
        ],
        
                i18n: {
            create: {
                button: "Νέα εγγραφή",
                title:  "Δημιουργία νέας εγγραφής",
                submit: "Δημιουργία"
            },
            edit: {
                button: "Επεξεργασία",
                title:  "Επεξεργασία εγγραφής",
                submit: "Αποθήκευση"
            },
            remove: {
                button: "Διαγραφή",
                title:  "Διαγραφή",
                submit: "Διαγραφή",
                confirm: {
                    _: "Είστε βέβαιοι ότι θέλετε να διαγράψετε %d γραμμές;",
                    1: "Είστε βέβαιοι ότι θέλετε να διαγράψετε 1 γραμμή;"
                }
            },
            error: {
                system: "Προέκυψε κάποιο σφάλμα δοκιμάστε ξανα"
            },
            datetime: {
                previous: 'Προηγούμενη',
                next:     'Επόμενη',
                months:   [ 'Ιανουάριος', 'Φερβρουάριος', 'Μάρτιος', 'Απ΄ριλιος', 'Μάιος', 'Ιούνιος', 'Ιούλιος', 'Αύγουστος', 'Σεπτέμβιος', 'Οκτώβριος', 'Νοέμβριος', 'Δεκέβριος' ],
                weekdays: [ 'Κυρ.', 'Δευ.', 'Τρι.', 'Τετ.', 'Πεμ.', 'Παρ.', 'Σαβ.' ]
            }
        }
    } );
    
    /*start code for inline buttons*/
    // Edit record
    $('#users').on( 'click', 'a.editor_edit', function (e) {
        e.preventDefault();
 
        editor
            .title( 'Edit record' )
            .buttons( { "label": "Update", "fn": function () { editor.submit() } } )
            .edit( $(this).closest('tr') );
    } );
 
    // Delete a record
    $('#users').on( 'click', 'a.editor_remove', function (e) {
        e.preventDefault();
 
        editor
            .title( 'Edit record' )
            .message( "Are you sure you wish to delete this row?" )
            .buttons( { "label": "Delete", "fn": function () { /*editor.submit()*/ } } )
            .remove( $(this).closest('tr') );
    } );
    /*end code for inline buttons*/
// Activate an inline edit on click of a table cell
    //$('#users').on( 'click', 'tbody td:not(:first-child, :nth-child(9))', function ()(
    $('#users').on( 'click', 'tbody td:not(:first-child)', function (e) {
        editor.inline( this, {
            buttons: { label: '&gt;', fn: function () { this.submit(); } }
        } );
    } );    
    var table = $('#users').DataTable( {
        "language":
        {
    "sDecimal":           ",",
    "sEmptyTable":        "Δεν υπάρχουν δεδομένα στον πίνακα",
    "sInfo":              "Εμφανίζονται _START_ έως _END_ από _TOTAL_ εγγραφές",
    "sInfoEmpty":         "Εμφανίζονται 0 έως 0 από 0 εγγραφές",
    "sInfoFiltered":      "(φιλτραρισμένες από _MAX_ συνολικά εγγραφές)",
    "sInfoPostFix":       "",
    "sInfoThousands":     ".",
    "sLengthMenu":        "Δείξε _MENU_ εγγραφές",
    "sLoadingRecords":    "Φόρτωση...",
    "sProcessing":        "Επεξεργασία...",
    "sSearch":            "Αναζήτηση:",
    "sSearchPlaceholder": "Αναζήτηση",
    "sThousands":         ".",
    "sUrl":               "",
    "sZeroRecords":       "Δεν βρέθηκαν εγγραφές που να ταιριάζουν",
    "oPaginate": {
        "sFirst":    "Πρώτη",
        "sPrevious": "Προηγούμενη",
        "sNext":     "Επόμενη",
        "sLast":     "Τελευταία"
    },
    "oAria": {
        "sSortAscending":  ": ενεργοποιήστε για αύξουσα ταξινόμηση της στήλης",
        "sSortDescending": ": ενεργοποιήστε για φθίνουσα ταξινόμηση της στήλης"
    }
},
        "lengthMenu": [ [10, 25, 50, -1], [10, 25, 50, "All"] ],
        ajax: 'php/table.users.php',
        columns: [
            {
                data: null,
                defaultContent: '',
                className: 'select-checkbox',
                orderable: false
            },
            {
                "data": "profile_pic",
                render: function ( data, type, row ) {
                var path ='http://localhost/test/upload/profile_pics/'+data;    
                //return '<img class="img-circle" src="'+path" width="100"/>';
                return '<img width="100" class="img-circle" src="'+path+'"/>';
                }
            },
            {
                "data": "full_name"
            },
            {
                "data": "email"
            },
            {
                "data": "password",
                render: function ( data, type, row ) {return '****';}
            },
            {
                "data": "dob"
            },
            {
                "data": "phone_number"
            },
            {
                "data": "status",
                render: function(data,type,row){
                if (data==0){return '<label for=\"status\" id="lblstatus" class="label label-warning">Ανενεργός</label>';}
                if (data==1){return '<label for=\"status\" id="lblstatus" class="label label-success">Ενεργός</label>';}
                if (data==2){return '<label for=\"status\" id="lblstatus" class="label" style="background-color:red;">Διαγραμμένος</label>';}
                }
            },
            {
                data: null,
                defaultContent: '<div class=\"btn-group\"><a id=\"edit-button\" data-toggle=\"tooltip\" title=\"Επεξεργασία\" class=\"btn btn-xs btn-default editor_edit\"><i class=\"fa fa-pencil\"></i></a><a id=\"delete-button\" data-toggle=\"tooltip"\ title=\"Διαγραφή\" class=\"btn btn-xs btn-danger editor_remove\"><i class=\"fa fa-times\"></i></a><a id=\"change-status-button\" data-toggle=\"tooltip\" title=\"Delete\" class=\"btn btn-xs btn-stop\"><i class=\"fa fa-stop\"></i></a></div>',
                //defaultContent: '<a href="" class="editor_edit">Edit</a> / <a href="" class="editor_remove">Delete</a>',
                className: 'center'
            }
            
            
        ],
        order: [ 1, 'asc' ],
        select: {
            style:    'os',
            selector: 'td:first-child'
        },
        select: true,
        //lengthChange: false
    } ); 
    
    
    new $.fn.dataTable.Buttons( table, [
        { extend: "create", editor: editor},
        { extend: "edit",   editor: editor},
        { extend: "remove", editor: editor}
    ] );

    table.buttons().container()
        .appendTo( $('.col-sm-6:eq(0)', table.table().container() ) );
} );

}(jQuery));

</script>

orionaselite

My PHP code in case you need it

<?php

// DataTables PHP library and database connection
include( "lib/DataTables.php" );

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


// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'users', 'user_id' )
    ->fields(
        Field::inst( 'profile_pic' )    
    ->setFormatter( 'Format::nullEmpty' )
     ->upload( Upload::inst( $_SERVER['DOCUMENT_ROOT'].'../upload/profile_pics/__ID__.__EXTN__' )
                ->db( 'users', 'user_id', array(
                    'profile_pic'    => Upload::DB_FILE_NAME
                    
                ) )
                ->allowedExtensions( [ 'jpg' ], "Παρακαλώ η εικόνα να είναι μορφής .jpg" )
            ),
        Field::inst( 'full_name' )
            ->validator( 'Validate::notEmpty' ),
        Field::inst( 'email' )
            ->validator( 'Validate::notEmpty' )
            ->validator( 'Validate::email' ),
        Field::inst( 'password' )
            ->setFormatter( function ( $val ) {
        return md5( $val );
    })
            ->validator( 'Validate::notEmpty' ),
        Field::inst( 'dob' )
            ->validator( 'Validate::notEmpty' )
            ->validator( 'Validate::dateFormat', array( 'format'=>'d-m-Y' ) )
            ->getFormatter( 'Format::date_sql_to_format', 'd-m-Y' )
            ->setFormatter( 'Format::date_format_to_sql', 'd-m-Y' ),
        Field::inst( 'phone_number' )
            ->validator( 'Validate::notEmpty' ),
        Field::inst( 'status' )
            ->validator( 'Validate::notEmpty' )
    )
    ->where( 'status', 2, '!=' )
    ->process( $_POST )
    ->json();

orionaselite

My updated php code now look like this. I am hoping now the name will be based on the user_id. That duplicate error from mysql is still there so I am stuck

<?php

// DataTables PHP library and database connection
include( "lib/DataTables.php" );

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


// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'users', 'user_id' )
    ->fields(
        Field::inst( 'profile_pic' )    
    ->setFormatter( 'Format::nullEmpty' )
     //->upload( Upload::inst( $_SERVER['DOCUMENT_ROOT'].'../upload/profile_pics/__ID__.__EXTN__' )
      ->upload(Upload::inst( function ( $file, $id ) {
            move_uploaded_file( $file['tmp_name'], '../upload/profile_pics/'.$id );
            return $id;
        } )
                ->db( 'users', 'user_id', array(
                    'profile_pic'    => Upload::DB_FILE_NAME
                    
                ) )
                ->allowedExtensions( [ 'jpg' ], "Παρακαλώ η εικόνα να είναι μορφής .jpg" )
            ),
        Field::inst( 'full_name' )
            ->validator( 'Validate::notEmpty' ),
        Field::inst( 'email' )
            ->validator( 'Validate::notEmpty' )
            ->validator( 'Validate::email' ),
        Field::inst( 'password' )
            ->setFormatter( function ( $val ) {
        return md5( $val );
    })
            ->validator( 'Validate::notEmpty' ),
        Field::inst( 'dob' )
            ->validator( 'Validate::notEmpty' )
            ->validator( 'Validate::dateFormat', array( 'format'=>'d-m-Y' ) )
            ->getFormatter( 'Format::date_sql_to_format', 'd-m-Y' )
            ->setFormatter( 'Format::date_format_to_sql', 'd-m-Y' ),
        Field::inst( 'phone_number' )
            ->validator( 'Validate::notEmpty' ),
        Field::inst( 'status' )
            ->validator( 'Validate::notEmpty' )
    )
    ->where( 'status', 2, '!=' )
    ->process( $_POST )
    ->json();

allan

So to check my understanding - when you upload a file, in response to the upload Ajax request you get "Integrity constraint violation: 1062 Duplicate entry '' for key 'email'"?

orionaselite

Yes the steps I do are as follows
1. I select a row end click edit
2. I get the modal that allows for editing and I click on "choose file" and I select a file of the required format
3. Then I get the error

allan

Thanks for the clarification.

I think the issue is that you are using the users table both as a file store (line 26) and as store for the user information (line 17).

Have you have a separate database table that will store the information about the file and just left join onto it? That would then match the Editor examples.

Allan

orionaselite

I haven't although I saw that in the examples. I will see what I can do because the database I am using is also connected to something else which I was hoping not to change. In any case I will have a think so that I can use a different table. If it works out I will post back. Thanks for the advice either way

orionaselite

Using a second table really really helped but I really need to use the primary key of my initial table in the filename.

I have a table now called images I store the filenames and an id in there that is auto increment as the primary key. As you can see in line #23-24 I have

move_uploaded_file( $file['tmp_name'], '../../upload/profile_pics/'.$id.'.jpg');
            return $id.'.jpg';

What I actually need is $id = user_id (which is the primary of table users). To be more precise:

1. Say I have 13 users in the users table there primary key is user_id that is an auto increment.
2. user #4 changes his profile picture
3. The pic should upload to ../../upload/profile_pics with a new name 4.jpg and overwrite the old pic using a name derived from user_id from tables users .
4. Currently it uses the id from table images. I want it to use the user_id from table users. There is a reason I need this to be like this although i know there may be better ways generally

Any ideas?

<?php

// DataTables PHP library and database connection
include( "lib/DataTables.php" );

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


// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'users', 'user_id' )
    ->fields(
        Field::inst( 'profile_pic' )    
    ->setFormatter( 'Format::nullEmpty' )
     //->upload( Upload::inst( $_SERVER['DOCUMENT_ROOT'].'../upload/profile_pics/__ID__.__EXTN__' )
      ->upload(Upload::inst( function ( $file, $id ) {
            move_uploaded_file( $file['tmp_name'], '../../upload/profile_pics/'.$id.'.jpg');
            return $id.'.jpg';
        } )
                ->db( 'images', 'id', array(
                    'filename'    => Upload::DB_FILE_NAME
                    
                ) )
                ->allowedExtensions( [ 'jpg' ], "Παρακαλώ η εικόνα να είναι μορφής .jpg" )
            ),
        Field::inst( 'full_name' )
            ->validator( 'Validate::notEmpty' ),
        Field::inst( 'email' )
            ->validator( 'Validate::notEmpty' )
            ->validator( 'Validate::email' ),
        Field::inst( 'password' )
            ->setFormatter( function ( $val ) {
        return md5( $val );
    })
            ->validator( 'Validate::notEmpty' ),
        Field::inst( 'dob' )
            ->validator( 'Validate::notEmpty' )
            ->validator( 'Validate::dateFormat', array( 'format'=>'d-m-Y' ) )
            ->getFormatter( 'Format::date_sql_to_format', 'd-m-Y' )
            ->setFormatter( 'Format::date_format_to_sql', 'd-m-Y' ),
        Field::inst( 'phone_number' )
            ->validator( 'Validate::notEmpty' ),
        Field::inst( 'status' )
            ->validator( 'Validate::notEmpty' )
    )
    ->where( 'status', 2, '!=' )
    ->process( $_POST )
    ->json();

orionaselite

The above code actually does exactly what I need thanks @allan I just wasn't paying attention to the changes in the db