how to inserting files on mysql using LARAVEL

[jaspertan]

hi guys ,

Need help on how to insert files (.docx,pdf and etc.) using laravel .

[cereal]

Do you want to save the files or only the names? In the former case then use something like this:

<form method="post" enctype="multipart/form-data" action="/upload_file">
    <input type="file" name="file" />
    <input type="submit" name="submit" value="upload" />
</form>

And these methods in the router.php file:

Route::get('/upload_form', function()
{
    $data['files'] = Attachment::get();
    return View::make('uploads.form', $data);

});

Route::post('/upload_file', function()
{

    $rules = array(

        'file' => 'required|mimes:doc,docx,pdf',

        );

    $validator = Validator::make(Input::all(), $rules);

    if($validator->fails())
    {
        return Redirect::to('/upload_form')->withErrors($validator);
    }

    else
    {
        if(Input::hasFile('file'))
        {
            $f = Input::file('file');
            $att = new Attachment;
            $att->name = $f->getClientOriginalName();
            $att->file = base64_encode(file_get_contents($f->getRealPath()));
            $att->mime = $f->getMimeType();
            $att->size = $f->getSize();
            $att->save();

            return Redirect::to('/upload_form');
        }
    }

});

I'm assuming the usage of a model Attachment and a table schema like this:

CREATE TABLE `attachments` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `file` longblob NOT NULL,
  `mime` varchar(255) DEFAULT NULL,
  `size` int(10) unsigned NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

If you still have problems show us your code. Bye.

[jaspertan]

i want to save the attached document to database

here is my code

on adding to database code

$data = Input::get('file);

DB::table('tablename')->insert(array(

    'doc_attachment'=> $data             \\ 'table name (blob datatype)'
)
);

when i use this code i can only store filename.

how can i store the content using this kind of approach

thanks in advance guys

[cereal]

Use Input::file('file') instead of Input::get('file'), the file() method gives you access to the $_FILES array.

After doing this you need to read the contents from the path of the temporary file created by PHP while uploading the file, which is given by the method getRealPath(). To read the binary data, you can use PHP functions like file_get_contents() or fread(), for example:

file_get_contents(Input::file('file')->getRealPath());

In your case, this should work:

$data = file_get_contents(Input::file('file')->getRealPath());
DB::table('tablename')->insert(array(
    'doc_attachment'=> $data
    )
);

But doc_attachment needs to be a blob column type, blob it self can store up to 65KB; if the files are big, then use mediumblob capable of 16MB of storage.

[jaspertan]

after doing this kind of coding i got this error,
Call to a member function getRealPath() on a non-object

[cereal]

Can you show the form and your updated code? Also which version of Laravel are you using?

[jaspertan]

im using laravel 4.0

i follow the code you posted:
on Controller:

$data = fread(Input::file('file')->getRealPath());

DB::table('docs')->insert(  
        array(
            'data'=>$data

On View:

  <input type="file" name="upload" />               


<input type="submit" name="save" id="submit2" value="Submit">

[cereal]

Ok, I'm assuming your form is using the enctype attribute, correct?

<form method="post" enctype="multipart/form-data" action="/upload_file">

Otherwise the file will not be uploaded. Also the name attribute in your input field is upload not file (as in my example), so your code becomes:

Input::file('upload')->getRealPath())

To be sure, you should always validate the form request, or at least use:

if(Input::hasFile('upload'))
{
    // save data
}

else
{
    // reject
}

[jaspertan]

i can store the data to database but im not sure if that is my file i've uploaded what code should i use to check the inserted blob file

[jaspertan]

thanks Cereal for help .

I can now store the document to database .

my next problem is how to download the file ive inserted .

[cereal]

Try this in your router.php file:

Route::get('/getfile/{id}', function($id)
{
    $file = Attachment::find($id);
    $data = $file->doc_attachment;
    return Response::make($data, 200, array('Content-type' => $file->mime, 'Content-length' => $file->size));

});

Again, if you're not using Eloquent, then use DB::select() which returns an array, so it would look like this:

Route::get('/getfile/{id}', function($id)
{
    $file = DB::select('SELECT * FROM tablename WHERE id = ?', array($id));
    $data = $file[0]->file;
    return Response::make($data, 200, array('Content-type' => $file[0]->mime, 'Content-length' => $file[0]->size));

});

I suggest you to save the mime-type and the file size while uploading the file, look at my first example, otherwise you have to declare it in someway.

[jaspertan]

by the way i tried to look at my database and i saw that the files i've been stored was convert to .TMP ?

[jaspertan]

i already put this on my route do i need to map this on my onclick button ?

[cereal]

No, you're still saving the path and the file name which is the temporary name given by the $_FILES array. In your previous code, you wrote only:

$data = fread(Input::file('file')->getRealPath());

Which is wrong if you do not use also fopen() & co. I supposed you were posting dimostrative code, so read the documentation for fread() or use file_get_contents() as in my example.

i already put this on my route do i need to map this on my onclick button ?

Show your code, please.

Docs:

• http://www.php.net/manual/en/function.fread.php
• http://www.php.net/manual/en/function.file-get-contents.php

[cereal]

by the way i tried to look at my database and i saw that the files i've been stored was convert to .TMP ?

Or you're trying to get the extension from getRealPath()? For the extension use:

$ext = Input::file('upload')->getClientOriginalExtension();

[jaspertan]

i already able to upload files on the database using this code

$data = file_get_contents (Input::file('file')->getRealPath());

Thanks Cereal for help

Download is the next thing i need to do.

my code is this .

Route::get('/getfile/{id}', function($id)
{
    $file = DB::select('SELECT * FROM tablename WHERE id = ?', array($id));
    $data = $file[0]->file;
    return Response::make($data, 200, array('Content-type' => $file[0]->mime, 'Content-length' => $file[0]->size));
});

HTML button:

<input type="text" >
<input type="submit" onclicked=location.ahref="/getfile/{id}">

but i cant download the file

[cereal]

Good! Are you using Blade? If yes then the id must be $id and surrounded at least from two consecutive curly brackets, three if you want also to sanitize the variable, for example:

/getfile/{{{$id}}} # with Blade

Regarding the onclick attribute, it should work if you try:

onclick="javascript:location.href='/getfile/{{{$id}}}'"

[jaspertan]

Undefined Offset 0 error result after doung that kind of coding

[cereal]

Ok, it will be easier to help you if we can see the controller from which you load the ids of all the files uploaded, for example you're probably using something like:

$data['id'] = DB::select("SELECT * FROM tablename");
return View::make('viewlist', $data);

The DB::select() will return an array, so the above code will translate to:

onclick="javascript:location.href='/getfile/Array'"

To get all the ids you have to use a foreach loop:

@foreach($id as $sid)
    <input type="submit" onclick="javascript:location.href='/getfile/{{{$sid}}}'" />
@endforeach

If you're not using Blade (the templating engine), then the correct code is:

<?php

    foreach($id as $sid)
    {
        echo "
        <input type=\"submit\" onclick=\"javascript:location.href='/getfile/{$sid}'\" />
        ";
    }

?>

[jaspertan]

where should i place the for loop ?

this is my code:

for view:

<html>
<head>
</head>
<title>
</title>


<input type="text" name="id">
<?php

foreach($id as $sid)
    {
        echo "
        <input type=\"submit\" onclick=\"javascript:location.href='/getfile/{$sid}'\" />
        ";
    }
?> 
?>

<body>

<input type="submit" onclick="javascript:location.href='/getfile/{{{$id}}}'">

</body>
</html

FOR Controller:

$data = file_get_contents(Input::file('upload')->getRealPath());
         if (Input::hasfile('upload'))
          {
            DB::table('docs')->insert(  
                array(
                'data'=>$data

                )
            );  

          } 
            else 
{
                echo "not successful";
}
 return View::make('upload');

}
}

The Route:

Route::get('/getfile/{id}', function($id)
{
    $file = DB::select('SELECT * FROM docs  WHERE docs_id = ?', array($id = Input::get('id')) 
    $data = $file[0]->file;
    return Response::make( $data, 200, array('Content-type' => $file[0]->mime, 'Content-length' => $file[0]->size));
});

[cereal]

So, I see:

1. a view to display the buttons in which you want to load the links to get the files
2. a controller to save the file
3. and another to download it.

What is missing is the method from which you will list all the files, for example:

Route::get('/listfiles')
{
    $data['id'] = DB::select("SELECT * FROM tablename");
    return View::make('viewlist', $data);
}

The view viewlist.php:

<html>
    <head>
        <title>List of files</lists>
    </head>
    <body>
        <?php
            foreach($id as $sid)
            {
                echo "
                <input type=\"submit\" onclick=\"javascript:location.href='/getfile/{$sid}'\" />
                ";
            }
        ?>
    </body>
</html>

[jaspertan]

hi cereal i want to display all content of my database to an html table including the uploaded file if i select

The output will be like this

+Filename++data+
+content++content+

when i click the content of data i can able to download the specific data i i've clicked

is it possible?

[cereal]

Yes, in the viewlist, replace the previous loop with a table, for example:

<?php if($id) { ?>
<table>
    <thead>
        <tr>
            <th>Filename</th>
            <th>Data</th>
        </tr>
    </thead>
    <tbody>
        <?php
        foreach($id as $sid)
        {
            echo "
            <tr>
                <td>
                    $sid->name
                </td>
                <td>
                    <button type=\"button\" onclick=\"javascript:location.href='/getfile/$sid->id'\">download</button>
                </td>
            </tr>
            ";
        }
        ?>
    </tbody>
    <tfoot>
        <tr>
            <td></td>
        </tr>
    </tfoot>
</table>
<?php } // end if ?>

[jaspertan]

hi cereal i tried this kind of coding.

in model:

public static function selecthey()

    {
        return  DB::table('docs')->where('gov_docs_id', '=', 35)->get();
    }

}

on my controller

$id =new Register();
        $test=Register::selecthey();
            foreach($test as $t => $s){

                echo $s->gov_docs_id."<br>";
                echo $s->personnel_id."<br>";
                echo $s->docs_name."<br>";
                echo $s->docs_num."<br>";
                echo $s->docs_date."<br>";
                echo $s->filename."<br>";
}               echo $s->data."<br>";





i can retrieve all the data using this code but the file i've been inserted was in a unreadable how can i download that kind of file?

thanks

[cereal]

Can you show your table structure? In a MySQL client run:

show create table docs;

And post the results here. The problem can depend on the data saved in the database table: truncated because of the column type, or it can depend on the system used to define the mime type you are assigning to the downloaded file.

If you can post also few rows from the database, just to see how you're saving the data, could be helpful.

[jaspertan]

CREATE TABLE docs (
gov_docs_id int(11) NOT NULL AUTO_INCREMENT,
personnel_id int(11) DEFAULT NULL,
docs_name varchar(255) DEFAULT NULL,
docs_num int(11) DEFAULT NULL,
docs_date datetime DEFAULT NULL,
filename varchar(255) DEFAULT NULL,
data longblob,
PRIMARY KEY (gov_docs_id)
) ENGINE=InnoDB AUTO_INCREMENT=44 DEFAULT CHARSET=latin1

[cereal]

Ok, then how this should work?

Route::get('/getfile/{id}', function($id)
{
    $file = DB::select('SELECT * FROM docs  WHERE gov_docs_id = ?', array($id = Input::get('id')) 
    $data = $file[0]->file;
    return Response::make( $data, 200, array('Content-type' => $file[0]->mime, 'Content-length' => $file[0]->size));
});

In your table the columns mime and size used as parameters in the response are missing, so the current code will return an error.

Pay attention to the last line of the above script:

return Response::make( $data, 200, array(
    'Content-type' => $file[0]->mime,
    'Content-length' => $file[0]->size)
    );

Where the first argument is the blob data, the second argument is the HTTP status (in this case 200) and the third argument is an array with the headers to send with the response: in this case

Content-type is important and it is used to define the type of data you're sending back to the browser, if it is a PDF the mime will be application/pdf, if docx the mime is:

application/vnd.openxmlformats-officedocument.wordprocessingml.document

And so on for each file-type you want to allow in your database.

Content-length is used to send the size of the file.

You should define this information.

As I previously suggested, check my first example, since there I'm already saving this information, otherwise you have to compute them each time you try to download a file.

Also, saving a file directly into a database is not always a good idea, it would be better to save only the filename. But it's your choice.

[jaspertan]

hi cereal ,

The function youve gave me is not working .

i can click the button download but i cant retrieve the data i want to retrieve.

[cereal]

Hi, what kind of error you get?

[jaspertan]

i didnt get any error when i clicked the button,

it seems that there is no effect after i clicked the download button i cant download the uploaded file.

here is my code .

Route::get('/getfile/{id}', function($id)
{
    $file = DB::select('SELECT * FROM tablename WHERE id = ?', array($id));
    $data = $file[0]->file;
    return Response::make($data, 200, array('Content-type' => $file[0]->mime, 'Content-length' => $file[0]->size));
});