Skip to main content
Inspiring
December 14, 2009
Question

Upload Files to MySQL

  • December 14, 2009
  • 1 reply
  • 2235 views

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>  

This topic has been closed for replies.

1 reply

Inspiring
December 14, 2009

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.

Inspiring
December 14, 2009

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.

Inspiring
December 15, 2009

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.


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.