Trouble storing uploaded file into MySql

Question

Hi I'm currently developing a quick prototype for my degree project. I have a previous form where users will select some options from dynamic dropdowns and when these have been saved into the tables the next page is for a user to upload a file. This file is then stored into a separate table with a foreign key referencing one other table. The problem is with the file upload page, I haven't included the the variable that represents the foreign key as i wanted to see if i could get it to work first. If anyone could help it would be much appreciated.

The error i receive is either for the the name of the upload file not being a valid column name in the fields of the insert query or

If I try to change the sql i get insert or update can not be done on a child table as the referential integrity is breached.

Am I being thick? Ive been stuck for a few hours now.

heres the php

if (isset($_POST['action']) and $_POST['action'] == 'upload')
{

$uploadfile = $_FILES['upload']['tmp_name'];
$uploadname = $_FILES['upload']['name'];

if(is_uploaded_file($uploadname)) {


$uploadtype = $_FILES['upload']['type'];
$uploaddata = file_get_contents($uploadfile);



// Prepare user-submitted values for safe database insert

$uploadname = mysql_real_escape_string($uploadname);
$uploadtype = mysql_real_escape_string($uploadtype);
$uploaddata = mysql_real_escape_string($uploaddata);

$sql = "INSERT INTO product_logs (fileName, mimeType, fileData)
        VALUES (".$uploadname.",".$uploadtype.",".$uploaddata.")";
$exesql=mysql_query($sql) OR die(mysql_error());
}
else {
echo 'Error: File could not be uploaded.';
}   
}
include("file.html.php");

and here is the file.html.php

<form action="" method="post" enctype="multipart/form-data">
        <div>
            <label for="upload">Upload File:
            <input type="file" id="upload" name="upload"/></label>
        </div>
        <div>
            <input type="hidden" name="action" value="upload"/>
            <input type="submit" value="Upload"/>
        </div>
    </form>

Here is the structure of the product_logs table

Field       Type           Null Key       Default         Extra
logID       int(5)         NO   PRI         NULL           auto_increment
dateCreated date           NO               NULL    
malResultID int(6)         NO   MUL         NULL    
mimeType    varchar(50)    NO               NULL    
fileData    mediumblob     NO               NULL    
fileName    varchar(255)   NO               NULL

Thanks everyone I managed to fix the problem. – Darryn Probert Mar 16 '13 at 15:29 — Darryn Probert, Mar 16 '13 at 15:29

score 0 · Answer 1 · answered Mar 16 '13 at 14:40

0

you forgot single quote here

$sql = "INSERT INTO product_logs (fileName, mimeType, fileData)
VALUES ('".$uploadname."','".$uploadtype."','".$uploaddata."')";

and stop using mysql_* its deprecated, use mysqli or pdo

answered Mar 16 '13 at 14:40

Yogesh Suthar

30,424
18
72
100

score 0 · Answer 2 · answered Mar 16 '13 at 14:40

You forgot quotes around your values in the query:

$sql = "INSERT INTO product_logs (fileName, mimeType, fileData)
        VALUES ('".$uploadname."','".$uploadtype."','".$uploaddata."')";
                ^--             ^ ^               ^ ^               ^

please note that storing files in a DB is rarely a good idea, and leads to major problems down the road.

If you're allowing large uploads, please note that you're wasting large amounts of memory holding TWO copies of the uploaded file in memory. A slightly LESS bad method would be

$sql = "INSERT .... " . mysql_real_escape_string(file_get_contents(...));

saving you at least one copy of the file's worth of space.

score 0 · Accepted Answer · answered Mar 16 '13 at 14:40

0

You should change your Sql like-

$sql = "INSERT INTO product_logs (fileName, mimeType, fileData)
        VALUES ('".$uploadname."','".$uploadtype."','".$uploaddata."')";

Also as we have seen your table name is product_logs

can you share the structure of the product_logs table? so that we have a better clarification of your question.

Thanks

answered Mar 16 '13 at 14:40

Piyas De

1,786
15
27

Field Type Null Key Default Extra logID int(5) NO PRI NULL auto_increment dateCreated date NO NULL malResultID int(6) NO MUL NULL mimeType varchar(50) NO NULL fileData mediumblob NO NULL fileName varchar(255) NO NULL – Darryn Probert Mar 16 '13 at 15:00
So you have to enter dateCreated, malResultID also in your sql statement as these fields are also not null. Another Question - is malResultID is referred as foreign key from any other table? – Piyas De Mar 16 '13 at 15:06
Sorry I re-edited my original post with the structure Im a bit of a newbbie so if I put them to null this will allow me to just try the upload to see if it works? Thank you very much for your reply – Darryn Probert Mar 16 '13 at 15:11
Yes, if you put dateCreated, malResultID as null, the above statement should work. Hope this will sove your problem. Thanks – Piyas De Mar 16 '13 at 15:14
Thanks my friend that helped a lot I cant believe I was doing something so stupid. Many thanks again – Darryn Probert Mar 16 '13 at 15:27

Trouble storing uploaded file into MySql

3 Answers3