Storing and retrieving documents directly from a database via DataTables Editor

svenos

Hi,

I would like to create a simple form for uploading and downloading documents (like pdf's and doc's) directly to/from the database.
(I have decided to store the files directly in the database due to maintenance and safety reasons but that's another discussion)

I have two tables: The first one contains editable info for the user and the second one contains the files themselves.

These are the tables:

Table 1: files_header                         Table 2: files_content
+----+--------------+-----------+---------+   +----+-----------------+-------+------+---------+
| id | file_name    | file_desc | file_id |   | id | name            | size  | type | content |
+----+--------------+-----------+---------+   +----+-----------------+-------+------+---------+
| 1  | Contract X   | Important | 1       |   | 1  | contract.pdf    | 4532  | pdf  | 12e1l.. |
| 2  | Appendix Y   |           | 4       |   | 4  | appendix.doc    | 9408  | doc  | jk34j.. |
| 3  | Evidence Z   |           | 9       |   | 9  | Screenshot.png  | 2054  | png  | 90sdc.. |
+----+--------------+-----------+---------+   +----+-----------------+-------+------+---------+

The content column in the second table contains the files themselves in a blob format.

This is my html:

<table id="fileUpload" class="display" cellspacing="0" width="100%">
    <thead>
        <tr>
            <th>Name</th>
            <th>Description</th>
            <th>File</th>
        </tr>
    </thead>
</table>

This is my Script:

editor = new $.fn.dataTable.Editor( {
    ajax: "php/control.php",
    table: "#fileUpload",
    fields: [ {
            label: "Name:",
            name: "file_name"
        }, {
            label: "Description:",
            name: "file_desc"
        }, {
            label: "Document:",
            name: "file_id",
            type: "upload",
            display: function ( file_id ) {
                return " ????? ";
            },
            clearText: "Clear"
        }
    ]
} );

var table = $("#fileUpload").DataTable( {
    dom: "Brtip",
    ajax: "php/control.php",
    columns: [
        { data: "file_name" },
        { data: "file_desc" },
        {
            data: "File_IntID",
            render: function ( File_IntID ) {
                return ID ?
                    " ????? " :
                    null;
            },
            defaultContent: "No document",
            title: "Document"
        }
    ],
    select: true,
    buttons: [
        { extend: "create", editor: editor },
        { extend: "edit",   editor: editor },
        { extend: "remove", editor: editor }
    ]
} );

In this javascript we can already see my challenge (the question marks "?????"). I would like get the file as a download link in the table.
I have no idea how get this work with DataTables.

But let me show the server side script first.

This is my PHP:

include( "../DataTables/Editor-PHP-1.7.0/php/DataTables.php" );
    
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate,
    DataTables\Editor\ValidateOptions;
 
Editor::inst( $db, 'files_header', 'File_UniqueID' )
    ->fields(
        Field::inst( 'id' ),
        Field::inst( 'file_name' ),
        Field::inst( 'file_desc' ),
        Field::inst( 'file_id' )
            ->setFormatter( Format::ifEmpty( null ) )
            ->upload( Upload::inst( ????? )
                ->db( 'files_content', 'id', array(
                    'name'    => Upload::DB_FILE_NAME,
                    'size'    => Upload::DB_FILE_SIZE,
                    'type'    => Upload::DB_MIME_TYPE,
                    'content' => Upload::DB_CONTENT
                ) )
                ->validator( Validate::fileSize( 100000000, 'Files must be smaller than 100MB' ) )
                ->validator( Validate::fileExtensions( array( 'pdf', 'doc', 'ppt', '...' ), "Please upload a document" ) )
            )
    )
    ->process( $_POST )
    ->json();

Again, I have no idea how to pass something from the PHP to the JS in order to create a download link or at least some functionality to retrieve the file.

I have searched the net for days and tried a lot by myself but couldn't find an answer.
It would be awesome if somebody has an answer to this.

Thank you very much!

allan

If you are storing the files in the database, you'd need to create a PHP script that would get the file requested and then output it. That isn't something that the Editor libraries will do for you I'm afraid, but is a fairly simple SELECT statement that you would run to get the data and then output it.

On the client-side, using a rendering function would be the way to do it:

data: 'file_id',
render: function ( data, type, row ) {
  return '<a href="downloadFile.php?id='+data+'">Download</a>';
}

You could do something similar in the Editor upload field's display function, but more likely you'd just want to display the file name there rather than a download link?

Allan

svenos

Thank you for your answer!

Well, that makes sense. With this way I can also include my own authorization routines in the "downloadFile.php" (or whatever the name will be).

I'll try that this week and post the full code here for reference.

svenos

Hey there,

I just made that simple download php and it works like a charm.
Thank you for that!

I'll post my complete "Upload and Download from/to Database" code after my last question (because with that answer it'll be complete).

I save the file content server-side (php) with that part of the code:

        ->upload( Upload::inst( $_SERVER['DOCUMENT_ROOT'].'/uploads/__ID__.__EXTN__' )
                ->db( 'FileContent', 'FileContent_ID', array(
                    'FileContent_FileName'    => Upload::DB_FILE_NAME,
                    'FileContent_FileSize'    => Upload::DB_FILE_SIZE,
                    'FileContent_FileType'    => Upload::DB_MIME_TYPE,
                    'FileContent_Content' => Upload::DB_CONTENT
                ) )
            )

I don't want to have the file on my file system since I save it to the database. Can I get rid of this part the code somehow?
Upload::inst( $_SERVER['DOCUMENT_ROOT'].'/uploads/__ID__.__EXTN__' )

If I read your documenation correctly the argument is optional. However, if I leave it empty or fill it with something else I always get an error message.
How can I achive to not get or move anything on the file system?

Thank you!

Best regards
Sven

svenos

Promised working code for files saved within the database:

"File" is our primary table and includes the table description.
"FileContent" contains the files themselves.
/tmp/__ID__.__EXTN__ is the tempory folder/path for the files. Make sure to select a more secure place if you have sensible data.

PHP Script

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


            // Build our Editor instance and process the data coming from _POST
            Editor::inst( $db, 'File', 'File_ID' )
                ->fields(
                    Field::inst('File.File_ID' ),
                    Field::inst('File.Contract_ID' ),
                    Field::inst('File.File_Name' ),
                    Field::inst('File.File_Desc' ),
                    Field::inst('File.FileContent_ID' )
                        ->setFormatter( Format::ifEmpty( null ) )
                    ->upload( Upload::inst( '/tmp/__ID__.__EXTN__' )
                    ->db( 'FileContent', 'FileContent_ID', array(
                                'FileContent_FileName'    => Upload::DB_FILE_NAME,
                                'FileContent_FileSize'    => Upload::DB_FILE_SIZE,
                                'FileContent_FileType'    => Upload::DB_MIME_TYPE,
                                'FileContent_Content' => Upload::DB_CONTENT
                            ) )
                            ->validator( Validate::fileSize( 100000000, 'Files must be smaller than 100MB' ) )
                            ->validator( Validate::fileExtensions( array( 'png', 'jpg', 'jpeg', 'gif', 'zip', 'rar', 'doc', 'docx', 'docm', 'docb', 'ppt', 'pptx', 'pptm', 'xls', 'xlsx', 'xlsm', 'xlsb', 'odt', 'ott', 'oth',please  'odm', 'oyou', 'ots', 'odp', 'odg', 'otp', 'pdf', ', 'xfdf' ), "Please upload a document, presentation, spreadsheet, image or zip file" ) )
                        )
                )
                ->process( $_POST )
                ->json();   

Javascript

functions/document_get.php is my php script that provides the file via the file ID (make sure to integrate security mechanisms if necessary).

            editor = new $.fn.dataTable.Editor( {
                ajax: "functions/control_documents.php",
                table: "#filesTable",
                fields: [, {
                        label: "Name:",
                        name: "File.File_Name"
                    }, {
                        label: "Description:",
                        name: "File.File_Desc"
                    }, {
                        label: "Download:",
                        name: "File.FileContent_ID",
                        type: "upload",
                        display: function ( data, type, row ) {
                            return '<a href="functions/document_get.php?id='+data+'">Download</a>';
                        },
                        clearText: "Clear",
                        noImageText: "No document"
                    }
                ]
            } );

            var table = $("#filesTable").DataTable( {
                dom: "rtBp",
                ajax: "functions/control_documents.php",
                columns: [
                    { data: "File.File_Name" },
                    { data: "File.File_Desc" },
                    {
                        data: "File.Fi,
                language: {
                    processing:     "<?php echo $text['Table']['Processing']; ?>",
                    search:         "<?php echo $text['Table']['Search']; ?>",
                    lengthMenu:     "<?php echo $text['Table']['Elements']; ?>",
                    info:           "<?php echo $text['Table']['Sorting']; ?>",
                    infoEmpty:      "<?php echo $text['Table']['Sorting0']; ?>",
                    infoFiltered:   "<?php echo $text['Table']['Filter']; ?>",
                    infoPostFix:    "",
                    loadingRecords: "<?php echo $text['Table']['LoadingRecords']; ?>",
                    zeroRecords:    "<?php echo $text['Table']['ZeroRecords']; ?>",
                    emptyTable:     "<?php echo $text['Table']['EmptyTable']; ?>",
                    paginate: {
                        first:      "<?php echo $text['Table']['First']; ?>",
                        previous:   "<?php echo $text['Table']['Previous']; ?>",
                        next:       "<?php echo $text['Table']['Next']; ?>",
                        last:       "<?php echo $text['Table']['Last']; ?>"
                    },
                    aria: {
                        sortAscending:  ": <?php echo $text['Table']['OrderAscending']; ?>",
                        sortDescending: ": <?php echo $text['Table']['OrderDescending']; ?>"
                    }
                }leContent_ID",
                        render: function ( data, type, row ) {
                            return data ?
                                '<a href="functions/document_get.php?id='+data+'">Download</a>' :
                                null;
                        },
                        defaultContent: "No image",
                        title: "Document"
                    }
                ],
                select: 'single',
                buttons: [
                    { extend: "create", editor: editor },
                    { extend: "edit",   editor: editor },
                    { extend: "remove", editor: editor }
                ]
            } );

Html

                    <table id="filesTable" class="display" cellspacing="0" width="100%">
                        <thead>
                            <tr>
                                <th>Name</th>
                                <th>Description</th>
                                <th>Download</th>
                            </tr>
                        </thead>
                    </table>

Greenmoor

Hello

Thank you for this - it seems very similar to what I'm trying to achieve. Do I need a PHP page if I'm using MVC? Here is the link to my original post - please can you advise? Thanks

https://datatables.net/forums/discussion/55887/download-database-file-mvc-app#latest