Image upload through CF to MySQL?

Data entry form to upload images to MySQL database using CF

Page 1 of 1

4 Replies - 5449 Views - Last Post: 07 July 2008 - 08:39 PM Rate Topic: -----

#1 loki421  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 04-July 08

Image upload through CF to MySQL?

Post icon  Posted 04 July 2008 - 04:02 AM

[font=Verdana][size=2]

Hi all! This is my first post here so please bare with me if it's in the wrong place or if it's not posted correctly :-)

Ok, now to the point. I have created a data entry form in DW CS3. I am using ColdFusion linked to MySQL. I have sucessfully created the form and the data entered into it populates the MySQL database (woohoo!! first time i've done anything like this! Yay!). The problem i have is that the images don't upload to the database. I have an image upload button that users can use to browse their hard drive, but when I click submit, the image file doesn't upload to the db, everything else does. I know i'm probably only missing one or two commands but can't seem to work this out.

I hope you guys and gals can help me with this :-)

Ok, so here's the code:

[code]<cfset CurrentPage=GetFileFromPath(GetBaseTemplatePath())>
<cfif IsDefined("FORM.MM_InsertRecord") AND FORM.MM_InsertRecord EQ "form1">
<cfquery datasource="affinity">
INSERT INTO vehicle_details (vehicle_description, contract_details, available_to, deposit, business_price, personal_price, additional_infomation, manufacturer)
VALUES (<cfif IsDefined("FORM.vehicle_description") AND #FORM.vehicle_description# NEQ "">
<cfqueryparam value="#FORM.vehicle_description#" cfsqltype="cf_sql_clob" maxlength="200">
<cfelse>
''
</cfif>
, <cfif IsDefined("FORM.contract_details") AND #FORM.contract_details# NEQ "">
<cfqueryparam value="#FORM.contract_details#" cfsqltype="cf_sql_clob" maxlength="150">
<cfelse>
''
</cfif>
, <cfif IsDefined("FORM.available_to") AND #FORM.available_to# NEQ "">
<cfqueryparam value="#FORM.available_to#" cfsqltype="cf_sql_clob" maxlength="45">
<cfelse>
''
</cfif>
, <cfif IsDefined("FORM.deposit") AND #FORM.deposit# NEQ "">
<cfqueryparam value="#FORM.deposit#" cfsqltype="cf_sql_clob" maxlength="45">
<cfelse>
''
</cfif>
, <cfif IsDefined("FORM.business_price") AND #FORM.business_price# NEQ "">
<cfqueryparam value="#FORM.business_price#" cfsqltype="cf_sql_clob" maxlength="45">
<cfelse>
''
</cfif>
, <cfif IsDefined("FORM.personal_price") AND #FORM.personal_price# NEQ "">
<cfqueryparam value="#FORM.personal_price#" cfsqltype="cf_sql_clob" maxlength="45">
<cfelse>
''
</cfif>
, <cfif IsDefined("FORM.additional_infomation") AND #FORM.additional_infomation# NEQ "">
<cfqueryparam value="#FORM.additional_infomation#" cfsqltype="cf_sql_clob" maxlength="45">
<cfelse>
''
</cfif>
, <cfif IsDefined("FORM.manufacturer") AND #FORM.manufacturer# NEQ "">
<cfqueryparam value="#FORM.manufacturer#" cfsqltype="cf_sql_clob" maxlength="45">
<cfelse>
''
</cfif>
)
</cfquery>
<cflocation url="adminwelcome.cfm">
</cfif>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>admin inside</title>
<style type="text/css">
<!--
body,td,th {
font-family: Verdana, Arial, Helvetica, sans-serif;
font-size: small;
color: #999999;
}
body {
background-color: #FFFFFF;
background-image: url();
background-repeat: no-repeat;
}
#container {
width: 900px;
margin-top: 60px;
margin-right: auto;
margin-left: auto;
}
#container #form1 #button2 {
float: right;
}
-->
</style>
</head>

<body>
<div id="container">
<form id="form1" name="form1" method="POST" action="<cfoutput>#CurrentPage#</cfoutput>">
<table width="521" border="0">
<tr>
<td width="211">Vehicle Description</td>
<td width="245"><label>
<input type="text" name="vehicle_description" id="vehicle_description" />
</label></td>
<td width="51"><label>
<input type="button" name="button5" id="button5" value="Help!" />
</label></td>
</tr>
<tr>
<td>Contract Details</td>
<td><label>
<input type="text" name="contract_details" id="contract_details" />
</label></td>
<td>&nbsp;</td>
</tr>
<tr>
<td>Available To</td>
<td><label>
<input type="text" name="available_to" id="available_to" />
</label></td>
<td>&nbsp;</td>
</tr>
<tr>
<td>Deposit</td>
<td><label>
<input type="text" name="deposit" id="deposit" />
</label></td>
<td>&nbsp;</td>
</tr>
<tr>
<td>Business Price</td>
<td><label>
<input type="text" name="business_price" id="business_price" />
</label></td>
<td>&nbsp;</td>
</tr>
<tr>
<td>Personal Price</td>
<td><label>
<input type="text" name="personal_price" id="personal_price" />
</label></td>
<td>&nbsp;</td>
</tr>
<tr>
<td>Additional Info</td>
<td><label>
<input type="text" name="additional_infomation" id="additional_infomation" />
</label></td>
<td>&nbsp;</td>
</tr>
<tr>
<td>Manufacturer</td>
<td><label>
<input type="text" name="manufacturer" id="manufacturer" />
</label></td>
<td>&nbsp;</td>
</tr>
<tr>
<td>Image</td>
<td><input name="userfile[]" type="file" /></td>
<td>&nbsp;</td>
</tr>
<tr>
<td>Brochure</td>
<td><label>
<input name="userfile[]" type="file" />
</label></td>
<td>&nbsp;</td>
</tr>
<tr>
<td>&nbsp;</td>
<td>&nbsp;</td>
<td>&nbsp;</td>
</tr>
<tr>
<td><input type="submit" name="Submit" id="button" value="Save Vehicle " /></td>
<td><label>
<input type="button" name="button2" id="button2" value="Cancel" onclick="history.back()" />
</label></td>
<td>&nbsp;</td>
</tr>
<tr>
<td><label>
<input type="submit" name="button6" id="button6" value="Delete Vehicle" />
</label></td>
<td>&nbsp;</td>
<td>&nbsp;</td>
</tr>
<tr>
<td>&nbsp;</td>
<td>&nbsp;</td>
<td>&nbsp;</td>
</tr>
</table>
<input type="hidden" name="MM_InsertRecord" value="form1" />
</form>
</div>
</body>
</html>[code]

Many, many thanks in advance peeps :-)

Is This A Good Question/Topic? 0
  • +

Replies To: Image upload through CF to MySQL?

#2 wiredwizard  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 0
  • View blog
  • Posts: 76
  • Joined: 07-May 05

Re: Image upload through CF to MySQL?

Posted 04 July 2008 - 09:51 AM

OK first things first

You have a couple of options depending on where you are hosting.

1st you can upload your image to mysql as a blob (I've found that alot of host don't allow this)

2nd you upload your file to a directory in your website root then just and image name to database (most common way of doing this)

there is a great tutorial for 2nd option at http://www.community...e.cfm?cid=71EDA
Was This Post Helpful? 0
  • +
  • -

#3 loki421  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 04-July 08

Re: Image upload through CF to MySQL?

Posted 05 July 2008 - 12:52 AM

View Postwiredwizard, on 4 Jul, 2008 - 09:51 AM, said:

OK first things first

You have a couple of options depending on where you are hosting.

1st you can upload your image to mysql as a blob (I've found that alot of host don't allow this)

2nd you upload your file to a directory in your website root then just and image name to database (most common way of doing this)

there is a great tutorial for 2nd option at http://www.community...e.cfm?cid=71EDA


Thanks for you time wiredwizard. :^: Couple of things though, i am using BLOB in MySQL, but the file won't upload to it through this data entry form (see code above). I think i must be missing a peice of code somewhere? :P

I don't think naming the images will be practical as there will be several users on the data entry form/admin site at anyone time, so trying to co-ordinate image numbering would be a logistical nightmare!! :blink:

So i don't suppose you happen to know where i'm going wrong with this form do you? All the text fields get added to the database, just the images wont go......

I am using DW CS3, CF hosted locally and MySQL.

Hope you can help..........
Was This Post Helpful? 0
  • +
  • -

#4 jspidy  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 11
  • Joined: 25-February 05

Re: Image upload through CF to MySQL?

Posted 07 July 2008 - 06:56 PM

Have u set the variable for the image so the copies the name to the database
you will have to define the variable first with cfset i think it goes something like this
??<cfset FORM.Image = cffile.serverfile> ??something like that anyway>
hope that helps
Jay
Was This Post Helpful? 0
  • +
  • -

#5 sansclue  Icon User is offline

  • D.I.C Regular

Reputation: 29
  • View blog
  • Posts: 316
  • Joined: 21-November 07

Re: Image upload through CF to MySQL?

Posted 07 July 2008 - 08:39 PM

View Postloki421, on 5 Jul, 2008 - 12:52 AM, said:

I have an image upload button that users can use to browse their hard drive, but when I click submit, the image file doesn't upload to the db, everything else does.


To insert the image into your database you will need to:

1) Upload the image(s) with the <cffile> tag before your insert query
http://livedocs.adob...253.htm#3540091

2) Read the binary content of the image(s) into a variable using <cffile action="readBinary"..>
http://livedocs.adob...251.htm#3545306

3) Add the variable and image(s) column to your insert query

View Postloki421, on 5 Jul, 2008 - 12:52 AM, said:

I don't think naming the images will be practical as there will be several users on the data entry form/admin site at anyone time, so trying to co-ordinate image numbering would be a logistical nightmare!! :blink:


That's not really a problem. You can use CFFILE's nameConflict="makeUnique". So if the file name already exists, CF will automatically generate a unique name. Storing the file name/path is usually a better option than storing the image as a blob in the database. One reason is that blob's can bloat your database very quickly.

Quote

<td>Image</td>
<td><input name="userfile[]" type="file" /></td>
...
<td>Brochure</td>
<td><label>
<input name="userfile[]" type="file" />


Totally unrelated here but

1. Both of your "file" fields have the same name. There are cases where you _do_ want to give form fields the same name. But in this case.. it is probably better to give them different names. To avoid potential problems.

2. Your "file" field names contain some funky characters: "[]". It is probably allowed, but because of that you may be forced to use array notation to access the value.

For that reason I personally tend to stick with the old conventions for form field names:

- Form field name should start with an alpha character
- Should consist only of of letters, numbers and underscores "_"
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1