A

Anonymous

Answered

Inserting Image Name into the database

Forum|Forum|19 years ago
April 25, 2007
12 replies
3283 views

I have created a form with a file field to upload an image and insert its name directly into the mysql database. The column type is varchar where the name will be inserted through the form. Now i am getting two problems. All the fields get filled out in the database except the image name. The image name must be inserted into the productImage column. It remanins null. No image is uploaded to the destination folder.

And also on submitting the form, I get this error.
Error Executing Database Query.
Column 'productID' cannot be null

I am attaching the full code. Could someone help out? All i require is that the file is uploaded and name inserted into mysql database in specified column.

This topic has been closed for replies.

Correct answer existdissolve

I forgot about getting bck to you sooner.
I tried the code and i feel it has some errors so i made some changes and i am still getting errors that The form field "ul_path1" did not contain a file.
I had created a new table with only those 3 columns listed in the query and still not functioning.

There are only two errors that I see in your code:

First, you need cfoutput tags around the looping form---otherwise, the #i# naming of the fields will not work, and all three will simply be named "productID#i#", etc...

Second, you need to dynamically name the variable "file", not cffile.Serverfile. As each file is uploaded, the cffile.Serverfile will be associated with the loop value of file.

I am attaching your same code with the changes commented out so that you can see what needs to happen. BTW, I have tested this code, and it works as expected.

Good luck

A

Anonymous

Everything else is clear apart from the insert part.
Previously when I had only one record to insert, it worked without any problem.
The form is supposed to insert 3 records at a time into respective columns.

It would be easier to just tell me how the insert statement for the three image uploads to take place should be structered. It still results in a blank table on form submission with no image uploads or entry of records within the table.

In the database table there is only one image field so why this: INSERT.....(image1, image2, image3)?

Below is my insert statement.
INSERT into products
(productID, Description, Brand, sizeOnOrder, productImage, individualCost, supplierID, individualWeight, unitWeight, unitsInStock, unitsOnOrder, quantityPerUnit, unitCost, shippingCostPerUnit, unitStandAloneCost)

#file# or '#file1#' gets the name of the image entered in the form file field.

<cfif isdefined("form.sizeOnOrder#i#") and Evaluate("form.sizeOnOrder#i#") NEQ ''>
<cfqueryparam value="#Evaluate('form.sizeOnOrder#i#')#" cfsqltype="cf_sql_varchar"/>
<cfelse>
NULL
</cfif>,
'#file1#',
'#file2#',
'#file3#',

<cfif isdefined("form.individualCost#i#") and Evaluate("form.individualCost#i#") NEQ ''>
<cfqueryparam value="#Evaluate('form.individualCost#i#')#" cfsqltype="cf_sql_float"/>
<cfelse>
NULL
</cfif>

I have even put it as #file# to get the image names dynamically but get the same result as above or even if it was named cffile.Serverfile. I do understand the coding as you explained above but have no idea now how to make this work. The logic must be the same whether you insert and upload one image or more than one.

existdissolve

Inspiring

Okay, I think I understand what you're after. Try this:

<cfif isDefined("FORM.submit")>

<cfloop index="i" from="1" to="3">
<cffile action="upload" destination="#ExpandPath(".")#" accept="image/*" filefield="ul_path#i#" nameconflict="overwrite">
<cfset file = #cffile.Serverfile#>
</cfloop>

<cfloop index="i" from="1" to="3">
<cfquery datasource="mydb">
INSERT into products
(productID, Description, Brand, sizeOnOrder, productImage, individualCost, supplierID, individualWeight, unitWeight, unitsInStock, unitsOnOrder, quantityPerUnit, unitCost, shippingCostPerUnit, unitStandAloneCost)

<cfif isdefined("form.sizeOnOrder#i#") and Evaluate("form.sizeOnOrder#i#") NEQ ''>
<cfqueryparam value="#Evaluate('form.sizeOnOrder#i#')#" cfsqltype="cf_sql_varchar"/>
<cfelse>
NULL
</cfif>,

'#file#',

<cfif isdefined("form.individualCost#i#") and Evaluate("form.individualCost#i#") NEQ ''>
<cfqueryparam value="#Evaluate('form.individualCost#i#')#" cfsqltype="cf_sql_float"/>
<cfelse>
NULL
</cfif>

</cfquery>
</cfloop>
</cfif>

As you have already set the #file # values in the first loop, you should be able to now access them through another loop on the INSERT statment, and each value of #file# should be parsed out in order during the loops over the insert. In essence, you are doing exactly the same as every other field in terms of looping, only for the image you are accessing the value of serverFile set *after* the upload (e.g., <cfset file ....>), rather than the actual field name.

existdissolve

Inspiring

Sorry, the #file# in the Insert statement should, obviously, have the loop value after it -- '#file#'. The forum interpreted the "i" as html code.

A

Anonymous

It is still not working.
Above you stated to dynamically name the input fields.
Do u mean that all upload file fields must be coded like this?
<cfoutput><input name="ul_path#i#" type="file" id="ul_path#i#" /></cfoutput>
instead of <td><input name="ul_path1" type="file" id="ul_path1" /></td>.

I get the error that "i" has not been defined.

I have 3 form fileds on the page.

I have my cfloop tag as below.

<cfloop index="i" from="1" to="2">
You uploaded <cfoutput>#file #</cfoutput>
<cffile action="upload" destination="#ExpandPath(".")#" accept="image/*" filefield="ul_path#i#" nameconflict="overwrite">
<cfset file = "#cffile.Serverfile#">
<cfquery datasource="976evil_db">
INSERT into products .....................
</cfloop>

Here the form fields are not named dynamically but as before.
<td><input name="ul_path1" type="file" id="ul_path1" /></td>
<td><input name="ul_path2" type="file" id="ul_path2" /></td>
<td><input name="ul_path3" type="file" id="ul_path3" /></td>

And on submitting the form, neither do i get an error nor any data is entered or any images uploaded. So on form submission, it just goes to a blank table with no results only displaying the column names.

existdissolve

Inspiring

Are you looping over all the fields three times, or only the image field? That is, are you inserting 3 of each field, or do you only have 3 images that you want to upload with the rest of the fields?

If you are only trying to upload and insert 3 images in a regular, static form, go ahead and statically name them like you did.

Then, loop over the uploads like I mentioned before:

<cfloop index="x" from="1" to="3"> (I'm using "x" to stop the italicizing)
<cffile action="upload" destination="#ExpandPath(".")#" accept="image/*" filefield="ul_path#x#" nameconflict="overwrite">
<cfset file = #cffile.Serverfile#>
</cfloop>

If they are named ul_path1, ul_path2, and ul_path3, this loop should be fine to upload their images.

Also, by placing the cfset inside the same loop as the upload, you can set a variable that will record the serverFile name of your uploaded image. So, file will result in file1, file2, file3. You can then access these variables in your insert statement to record the names of the images in your database...

INSERT.....(image1, image2, image3)
VALUES (
'#file1#',
'#file2#',
'#file3#'
)....................

And even if you are looping over the whole form, you can still access these looped variables the same way:

<cfloop index="x" from="1" to="3">
INSERT................
#file#
......................
</cfloop>

Hopefully this is helpful.

A

Anonymous

In the above code, there are 3 file fields bit only one cffile tag. I am unsure of this but i had created 2 more cffile tags(one cffile tag per file field) but that was the wrong approach. I had tried a few other things but all that happens is on form submit, it does go to the form action page but but no data is displayed neither any images are uploaded.

existdissolve

Inspiring

Here's something I worked up for two simultaneous file uploads (obviously, with the way the cfloop is set up, you could have as many as you'd like, static or dynamic).

I was having problems at first, because the #serverfile# only grabs the most recent upload. However, by "setting" its value in the actual upload loop, you can then access it later on (as in the sample confirmation cfoutput above).

On the actual form, you'll want to be sure to dynamically name the input field so that it can be dynamically accessed above:

Here's what I was using to test this:

Hope this is getting you closer to a solution!

A

Anonymous

The productID field is of type varchar. It is not an autoincrement int type column.
As a test, i had set it previously as an autoincrement column of int type, but when inserting the record through the form, it will not bring up any error but it would result in empty rows in the database table. Previously the form had 5 columns. So here the user will enter data into one column and that data would be classed as one record and would be inserted into the table as a record. So if 2 out of 5 columns were entered and since productID was set as int autoincrement, the table will have data for productID 1 and productID 2 and productID 3, 4 and 5 will also be created but will have no other data since nothing was entered for those columns. I hope you get my point. The productID is not null error only occurs when i changed it to type varchar.

A

Anonymous

Thanks, that seemed to work.
the names get entered within the exact table column.
however i still get the Column 'productID' cannot be null error.

existdissolve

Inspiring

What kind of data are you entering into the productID field?

A

Anonymous

Like before all data from form gets entered into the database their respective columns except the productImage name column.

The image upload takes place with no problem to the destination folder.

existdissolve

Inspiring

What I think needs to happen is that the <cfif isDefined(FORM.totalrows)> needs to be removed. Have both the File Upload and Insert occur within a single condition of "FORM.submit".

If it still continues to throw problems, could you post the most recent code?

A

Anonymous

If I remove the <cfif isdefined("form.totalrows")> line, and then the closing cfif tag, this would result in a syntax error. If form.totalrows is not defined then it will not loop through each row.

I have tried it and it causes an error.

existdissolve

Inspiring

I'm having trouble understanding why you need the <cfif isDefined("FORM.totalrows)">. After all, you have coded the value (3) onto the form element, and as it is hidden, you can't really change it at this moment. And even if you do make it dynamic, there is no need to base the existence of the INSERT query on it, for it will be there when the form is submitted anyway. Moreover, without the <cfif isDefined("FORM.totalrows")>, your loop will still work as it is calling on the form scope, of which the form element "totalrows" will be a part on submit.

With that said, this is what I would try:

existdissolve

Inspiring

All foreign keys are going to require some value, as they are referencing an existing value elsewhere. For example, if you have an "Orders" table that has foreign keys on userID and productID, userID is always going to require a value of some sort, even if you only want to enter a value for productID.

Moreover, there should not be scenarios in which the foreign keys are blank--after all, in the example above, one would never want to have a product entered into an order without a user---that just wouldn't work.

So one way or another, those foreign keys are going to need values, whether they are populated from other tables, or from default values that you pass in your form.

As to the final example you noted, could you be more specific? I don't understand how you are entering 5 items at a time and only populating the primary key for each---isn't the point of entering 5 at a time the fact that you want 5 different items?

Or are you talking about a "count" of 5, like with a shopping cart? In that case, you might make a column that will grab the "count" of the entry while only entering one item.

If you can give a tangible example of how your related tables work together, that would help.

A

Anonymous

Regarding the productID key, it is the foreign key in another table but that table contains other foreign keys. All are of type varchar. I have created an insert form for some of the tables within the database where you can insert 5 records at a time. Now if any of the columns are empty, I get a similar error stating keyID cannot be null. If they were autoincrement columns, i would not get an error but this would result in blank rows in the database apart from the primary key since it is auto-incremented even if it is blank..

What i mean is i have a form for products for example where i can perform multiple inserts of 5 items at a time.productID would be of type int autoincrement.
Here if i enter 1 row, the data will be inserted into the databse but it will also result in 4 more inserts with no data except for the primary key.
I hope you get my point.

A

Anonymous

Regd the productID, it is the primary key in the table but it is also a foreign key in another table, it won't be possible to havea foreign key as an autoincrement column or more than one autoincrement column per table.

existdissolve

Inspiring

quote:

Originally posted by: abhishek77
Regd the productID, it is the primary key in the table but it is also a foreign key in another table, it won't be possible to havea foreign key as an autoincrement column or more than one autoincrement column per table.

Do you have another auto-increment on this particular table where productID is the autoincrement? If not, there should not be a problem with using this auto-increment column in this table as a foreign key on another, for the auto-increment is only applied on this table, while the productID value of this table is being obtained on another.

Show more replies

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded