• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Upload Files to MySQL

Explorer ,
Dec 14, 2009 Dec 14, 2009

Copy link to clipboard

Copied

I have some code where I'm uploading different file types to my server. This works great. However, I'm trying to upload the file name to the MySQL database but I can't seem to get it to go. Any help on this would be greatly appreciated. Thanks.

My Code:

<CFSET FileDir = ExpandPath("..\manage\users\#acct#\#fileLoc#\")>

<cffile action="upload" destination="#FileDir#" filefield="fileUPLOAD" nameconflict="overwrite" result="Upload">

<cffile ACTION="Read" FILE="#FileDir#" variable="FileData">
  
<cfquery name="UploadFile" datasource="#db#">
    INSERT INTO documents (User_ID, title, file_name)
    VALUES (#FORM.UserID#, '#FORM.title#', <cfqueryparam cfsqltype="cf_sql_varchar" value="#FileData#"> )
</cfquery>  

TOPICS
Advanced techniques

Views

1.6K

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Dec 14, 2009 Dec 14, 2009

Copy link to clipboard

Copied

You say you want to store the file name, but your code shows you trying to store the contents.  The file name gets generated during the upload process.  Cfdump your upload variable and see if it contains anything useful.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Dec 14, 2009 Dec 14, 2009

Copy link to clipboard

Copied

I don't see anything, just outputs a the name of the field. I don't have anything useful on this.

I am trying to capture the filename and I have a seperate field where they type in a better name for the file they are uploading.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Valorous Hero ,
Dec 14, 2009 Dec 14, 2009

Copy link to clipboard

Copied

<cffile action="upload" destination="#FileDir#" filefield="fileUPLOAD" nameconflict="overwrite" result="Upload">

<cfdump var="#Upload#">

This doesn't show you anything interesting?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Dec 14, 2009 Dec 14, 2009

Copy link to clipboard

Copied

I get an error everytime...

 Variable UPLOAD is undefined.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Valorous Hero ,
Dec 14, 2009 Dec 14, 2009

Copy link to clipboard

Copied

What about the older

<cfdump var="#cffile#">


Your <cfffile....> tag does have the result="Upload" paramter correct?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Dec 14, 2009 Dec 14, 2009

Copy link to clipboard

Copied

Okay, so I did a dump on <CFFILE

This is what I get....

An error occurred when performing a file operation read on file E:\WEB\bkllp\manage\users\kanner\Ray-z173n7d\.

This is the proper file location folder.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Valorous Hero ,
Dec 14, 2009 Dec 14, 2009

Copy link to clipboard

Copied

Are you doing these dumps after the <cffile action="upload"...> OR after the <cffile action="read"...>  My suggestion was to put it after the former not the latter.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Dec 14, 2009 Dec 14, 2009

Copy link to clipboard

Copied

This is what I have...

<cffile action="upload" destination="#FileDir#" filefield="fileUPLOAD" nameconflict="overwrite" result="UploadFile">

<cfdump var="#cffile#">

<cffile ACTION="Read" FILE="#FileDir#" variable="FileData">
  
<cfquery name="UploadFile" datasource="kanner">
    INSERT INTO documents (User_ID, title, file_name)
    VALUES (#FORM.UserID#, '#FORM.title#', <cfqueryparam cfsqltype="cf_sql_varchar" value="#FileData#"> )
</cfquery> 

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Valorous Hero ,
Dec 14, 2009 Dec 14, 2009

Copy link to clipboard

Copied

Try this:

<cffile action="upload" destination="#FileDir#" filefield="fileUPLOAD" nameconflict="overwrite" result="UploadFile">

<cfdump var="#cffile#"><cfabort>

<cffile ACTION="Read" FILE="#FileDir#" variable="FileData">
  
<cfquery name="UploadFile" datasource="kanner">
    INSERT INTO documents (User_ID, title, file_name)
    VALUES (#FORM.UserID#, '#FORM.title#', <cfqueryparam cfsqltype="cf_sql_varchar" value="#FileData#"> )
</cfquery> 

It is the <cffil action="read"...> line that is throwing that previous error, because you are just passing it a directory, not a directory with a file name.

But then, I'm hopping to show you how you do not need the <cffile action="read"...> line.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Dec 14, 2009 Dec 14, 2009

Copy link to clipboard

Copied

well, this is what I got...

struct [empty]

would really like to know an easier way to accomplish my goal here. thanks for your help on this.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Valorous Hero ,
Dec 14, 2009 Dec 14, 2009

Copy link to clipboard

Copied

still_smiling wrote:

well, this is what I got...

struct [empty]

would really like to know an easier way to accomplish my goal here. thanks for your help on this.

Then something is wrong, because this normally IS easy, and that structure should have plenty of information to make this simple.

You are sure you are properly uploading the desired files?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Dec 14, 2009 Dec 14, 2009

Copy link to clipboard

Copied

The file Upload works just perfectly.

<cfquery name="GetName" datasource="kanner">
    Select file, accountant, FirstName, LastName, Email
    From users
    WHERE User_ID = #Form.UserID#
</cfquery>


<cfset acct = #GetName.accountant#>
<cfset fileLoc = #GetName.file#>

<CFSET FileDir = ExpandPath("..\manage\users\#acct#\#fileLoc#\")>

<cffile action="upload" destination="#FileDir#" filefield="fileUPLOAD" nameconflict="overwrite" result="UploadFile">

Then the client wanted to add a "Title" to the file. Make it easier for thier users to know what they are downloading. So I add a text field called "title" on the file upload page. So, in order for the File and the Title to correspond I thought I should upload the file name and title into the database. So I added this code to do that.

<cffile ACTION="Read" FILE="#FileDir#" variable="FileData">
  
<cfquery name="UploadFile" datasource="kanner">
    INSERT INTO documents (User_ID, title, file_name)
    VALUES (#FORM.UserID#, '#FORM.title#', <cfqueryparam cfsqltype="cf_sql_varchar" value="#FileData#"> ) </cfquery>

So when the user logs into view the available files to download they would see their directory listing along with the "TItle". and I used the following code to display available file downloads.

<cfparam name="url.sort" default="datelastmodified desc">
      <cfdirectory directory="#ExpandPath("./users/#GetName.accountant#/#GetName.file#")#" action="list" name="dir" sort="#url.sort#">

This is the whole picture of what I'm trying to accomplish.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Dec 14, 2009 Dec 14, 2009

Copy link to clipboard

Copied

If you do this:

<cffile action="upload" destination="#FileDir#" filefield="fileUPLOAD" nameconflict="overwrite" result="UploadFile">

<cfdump var="#UploadFile#">

<cfabort>

and you see an empty structure, something went wrong.

Later, there are two problems with this:

<cffile ACTION="Read" FILE="#FileDir#" variable="FileData">

<cfquery name="UploadFile" datasource="kanner">
    INSERT INTO documents (User_ID, title, file_name)
    VALUES (#FORM.UserID#, '#FORM.title#', <cfqueryparam cfsqltype="cf_sql_varchar" value="#FileData#"> ) </cfquery>

The first problem is that FileDir is a directory path and does not contain the name of the file.  This should cause your cffile tag to fail.  The second is that once you successfully read the file,  the FileData variable will not contain the name of the file, it will contain the contents.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Dec 15, 2009 Dec 15, 2009

Copy link to clipboard

Copied

I was doing the dump wrong. Here are my results.

struct
ATTEMPTEDSERVERFILECF-uploading.pdf
CLIENTDIRECTORYCF-uploading.pdf
CLIENTFILECF-uploading.pdf
CLIENTFILEEXTpdf
CLIENTFILENAMECF-uploading
CONTENTSUBTYPEpdf
CONTENTTYPEapplication
DATELASTACCESSED{d '2009-12-15'}
FILEEXISTEDYES
FILESIZE389107
FILEWASAPPENDEDNO
FILEWASOVERWRITTENYES
FILEWASRENAMEDNO
FILEWASSAVEDYES
OLDFILESIZE389107
SERVERDIRECTORYE:\WEB\bkllp\manage\users\kanner\Courtney-Ray-z173n7d
SERVERFILECF-uploading.pdf
SERVERFILEEXTpdf
SERVERFILENAMECF-uploading
TIMECREATED{ts '2009-12-15 14:48:08'}
TIMELASTMODIFIED{ts '2009-12-15 14:48:08'}

So, how do I insert the name of the file into the database? Would I use ClientFile?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Valorous Hero ,
Dec 15, 2009 Dec 15, 2009

Copy link to clipboard

Copied

I would suspect serverfile is more relevant.  That is the name of the file on your server as apposed to the name that the file was on the

client.  Those are not always the same.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Dec 15, 2009 Dec 15, 2009

Copy link to clipboard

Copied

okay, but how do I reference it my query?

I tried this and received an error.

<cfquery name="UploadFile" datasource="kanner">
    INSERT INTO documents (User_ID, title, file_name)
    VALUES (#FORM.UserID#, '#FORM.title#', <cfqueryparam cfsqltype="cf_sql_varchar" value="#UploadFile.SERVERFILE#"> ) </cfquery>

I also tried.

<cfquery name="UploadFile" datasource="kanner">
    INSERT INTO documents (User_ID, title, file_name)
    VALUES (#FORM.UserID#, '#FORM.title#', <cfqueryparam cfsqltype="cf_sql_varchar" value="#CFFILE.SERVERFILE#"> ) </cfquery>

Do I still need to use this line? I just need to store the name of the file, not the data. I had this commented out during the previous queries. Didn't think I needed this line.

<cffile ACTION="Read" FILE="#FileDir#" variable="FileData">

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Dec 15, 2009 Dec 15, 2009

Copy link to clipboard

Copied

Looks like you are overwriting your UploadFile variable.  You use it in your cffile action = upload tag, and then again as the name of your query.  That is probably causing your problem.

Do you still need to read the file?  Not if all you want to do is store the name in your db.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Dec 17, 2009 Dec 17, 2009

Copy link to clipboard

Copied

LATEST

Thank you. I have figured it out and it works like a champ. I'm posting the final code in case someone else needs it.

<cffile action="upload" destination="#FileDir#" filefield="fileUPLOAD" nameconflict="overwrite" result="UploadFile">

<cfset FileNameUpload = #UploadFile.serverfile#>
<cfset UserIDUpload = #Form.UserID#>
<cfset FileTitle = #Form.title#>

<cfquery name="DoUpload" datasource="#DS#">
    INSERT INTO documents (User_ID, title, file_name)
    VALUES ('#UserIDUpload#', '#FileTitle#', '#FileNameUpload#')
</cfquery>

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation