18 Replies Latest reply on Dec 18, 2009 2:35 PM by still_smiling

    Upload Files to MySQL

    still_smiling

      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>  

        • 1. Re: Upload Files to MySQL
          Dan Bracuk Level 5

          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.

          • 2. Re: Upload Files to MySQL
            still_smiling Level 1

            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.

            • 3. Re: Upload Files to MySQL
              ilssac Level 5
              <cffile action="upload" destination="#FileDir#" filefield="fileUPLOAD" nameconflict="overwrite" result="Upload">
              
              <cfdump var="#Upload#">
              

               

              This doesn't show you anything interesting?

              • 4. Re: Upload Files to MySQL
                still_smiling Level 1

                I get an error everytime...

                 

                 Variable UPLOAD is undefined.
                • 5. Re: Upload Files to MySQL
                  ilssac Level 5

                  What about the older

                   

                  <cfdump var="#cffile#">

                   


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

                  • 6. Re: Upload Files to MySQL
                    still_smiling Level 1

                    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.

                    • 7. Re: Upload Files to MySQL
                      ilssac Level 5

                      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.

                      • 8. Re: Upload Files to MySQL
                        still_smiling Level 1

                        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> 

                        • 9. Re: Upload Files to MySQL
                          ilssac Level 5

                          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.

                          • 10. Re: Upload Files to MySQL
                            still_smiling Level 1

                            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.

                            • 11. Re: Upload Files to MySQL
                              ilssac Level 5

                              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?

                              • 12. Re: Upload Files to MySQL
                                still_smiling Level 1

                                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.

                                • 13. Re: Upload Files to MySQL
                                  Dan Bracuk Level 5

                                  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.

                                  • 14. Re: Upload Files to MySQL
                                    still_smiling Level 1

                                    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?

                                    • 15. Re: Upload Files to MySQL
                                      ilssac Level 5

                                      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.

                                      • 16. Re: Upload Files to MySQL
                                        still_smiling Level 1

                                        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">

                                        • 17. Re: Upload Files to MySQL
                                          Dan Bracuk Level 5

                                          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.

                                          • 18. Re: Upload Files to MySQL
                                            still_smiling Level 1

                                            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>