10 Replies - 1290 Views - Last Post: 25 February 2012 - 04:46 AM Rate Topic: -----

#1 deucalion0  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 48
  • Joined: 27-November 10

What would be the best way to upload and store a file to a database,

Posted 24 February 2012 - 08:38 AM

I want to create a form where the user can create an account and then be bale to log in and upload images to the database. The image must be uploaded to a folder called uploaded and the path stored in the photos table under photos. I am using MySQL, is there a certain function that I can use that will store the image path when the image is uploaded, and also any suggestions on how I can keep the images separate per user? just in case another user uploads the same image name.

I would appreciate any help with this, thank you!

Is This A Good Question/Topic? 0
  • +

Replies To: What would be the best way to upload and store a file to a database,

#2 tlhIn`toq  Icon User is offline

  • Please show what you have already tried when asking a question.
  • member icon

Reputation: 5316
  • View blog
  • Posts: 11,373
  • Joined: 02-June 10

Re: What would be the best way to upload and store a file to a database,

Posted 24 February 2012 - 08:45 AM

Are you really meaning to ask "Can someone architect my program for me?"

Have you written anything for your project so far?

Database tutorials

What have to you tried to code so far? - Click the link

I don't want you to write my code, just give me ideas on how to solve my problem. - Read this article

"It doesn't work" isn't good enough - How you can help us, to help you

What part of what you have tried isn't working?
Are you getting error messages, or is it just not giving you the results you wanted?

(In other words, you at least have to make and show us your good faith effort to do your own coding. We are here to help you, not do it for you.)
What does this error mean?
Was This Post Helpful? 0
  • +
  • -

#3 macosxnerd101  Icon User is offline

  • Self-Trained Economist
  • member icon




Reputation: 10194
  • View blog
  • Posts: 37,646
  • Joined: 27-December 08

Re: What would be the best way to upload and store a file to a database,

Posted 24 February 2012 - 08:56 AM

Consider the following database design:
users
-----------------------------------------
user_id (PK) | username | password_hash |
-----------------------------------------

files
-------------------------------------------------
file_id (PK) | user_id (PK, FK) | file_location |
-------------------------------------------------



In terms of database structure, you can identify the files by a primary id, as well as associate them with the user through the user_id. However, if two files have the same name, they can't exist in the same directory. So on the server, separate directories for users may make things easier. You should also check that the file the user is uploading doesn't exist on the server. If so, give them the option to overwrite the old file with the new one.

We also have a tutorial on uploading files in PHP.
Was This Post Helpful? 1
  • +
  • -

#4 deucalion0  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 48
  • Joined: 27-November 10

Re: What would be the best way to upload and store a file to a database,

Posted 24 February 2012 - 09:05 AM

Apologies, for my rather poor attempt at asking for advice, I am under pressure and should have taken my time to write my post better or not at all. I indeed do not want anyone to architect my whole application, I want to take pride in my work, let me start again.

So I created a form that uploads an image, the form:

<html>
<body>

<form action="upload_script.php" method="post"
enctype="multipart/form-data">
<label for="file">Filename:</label>
<input type="file" name="file" id="file" /> 
<br />
<input type="submit" name="submit" value="Submit" />
</form>

</body>
</html>



I have a script action that uploads eh specific file to a specific folder called uploaded.:

<?php
if ((($_FILES["file"]["type"] == "image/png")
|| ($_FILES["file"]["type"] == "image/jpeg")
|| ($_FILES["file"]["type"] == "image/pjpeg"))
&& ($_FILES["file"]["size"] < 20000000000))
  {
  if ($_FILES["file"]["error"] > 0)
    {
    echo "Return Code: " . $_FILES["file"]["error"] . "<br />";
    }
  else
    {
    echo "Upload: " . $_FILES["file"]["name"] . "<br />";
    echo "Type: " . $_FILES["file"]["type"] . "<br />";
    echo "Size: " . ($_FILES["file"]["size"] / 1024) . " Kb<br />";
    echo "Temp file: " . $_FILES["file"]["tmp_name"] . "<br />";

    if (file_exists("upload/" . $_FILES["file"]["name"]))
      {
      echo $_FILES["file"]["name"] . " already exists. ";
      }
    else
      {
      move_uploaded_file($_FILES["file"]["tmp_name"],
      "uploaded/" . $_FILES["file"]["name"]);
      echo "Stored in: " . "uploaded/" . $_FILES["file"]["name"];
      }
    }
  }
else
  {
  echo "Invalid file";
  }
?>



I have a database on my server that I can connect to using the following, which I am trying to integrate a way to pass the file name into the photos column of the photos table:


<?php
$con = mysql_connect("localhost","******","******");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("deucalio_photostore", $con);



mysql_query("INSERT INTO photos (photos) VALUES ("'. $_FILES["file"]["name"].'")");


echo'entered';
mysql_close($con);
?>




I am trying to get the above query to merge with the upload script, once I can figure that out then I can think about figuring out a way to automatically create a folder in uploaded perhaps with the same name as the userID which was created when a user registered, I have thought a lot about this, but it is a difficult project for me to do without any help.


I deleted the rest of the code I had because it was wrong, so this all I have for now.


Here is my database structure so far.


users

-----------------------------------------

userID (PK) | email | password |

-----------------------------------------

 
photos

-------------------------------------------------

photoID (PK) | userID (FK) | photo |

-------------------------------------------------






Sorry again for the lack of effort in my initial post.


Thanks.

This post has been edited by deucalion0: 24 February 2012 - 09:08 AM

Was This Post Helpful? 1
  • +
  • -

#5 JackOfAllTrades  Icon User is offline

  • Saucy!
  • member icon

Reputation: 5960
  • View blog
  • Posts: 23,238
  • Joined: 23-August 08

Re: What would be the best way to upload and store a file to a database,

Posted 24 February 2012 - 09:52 AM

Well, obviously you will need to insert the userID into the photos table when you add the photo. Is photo supposed to be the path to the file? If so, you need to make sure pass the final path to the file (once it's moved) to the query.
Was This Post Helpful? 0
  • +
  • -

#6 creativecoding  Icon User is offline

  • Hash != Encryption
  • member icon


Reputation: 922
  • View blog
  • Posts: 3,195
  • Joined: 19-January 10

Re: What would be the best way to upload and store a file to a database,

Posted 24 February 2012 - 09:54 AM

I would md5 the file and store that. Takes a little more time and power but that way there will be no duplicate files.

$ext = end(explode('.', $_FILES["pictures"]["name"]));
$file = md5_file($_FILES["pictures"]["tmp_name"]);
$file .= '.' . $ext;
move_uploaded_file($_FILES["pictures"]["tmp_name"], "uploads/" . $file);


Was This Post Helpful? 0
  • +
  • -

#7 deucalion0  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 48
  • Joined: 27-November 10

Re: What would be the best way to upload and store a file to a database,

Posted 24 February 2012 - 11:24 AM

Creative codeing, I used your code and I had no errors but I do not see they MD5 working, am I using it correctly:


<?php


if ((($_FILES["file"]["type"] == "image/png")
|| ($_FILES["file"]["type"] == "image/jpeg")
|| ($_FILES["file"]["type"] == "image/pjpeg"))
&& ($_FILES["file"]["size"] < 20000000000))
  {
  if ($_FILES["file"]["error"] > 0)
    {
    echo "Return Code: " . $_FILES["file"]["error"] . "<br />";
    }
  else
    {
    echo "Upload: " . $_FILES["file"]["name"] . "<br />";
    echo "Type: " . $_FILES["file"]["type"] . "<br />";
    echo "Size: " . ($_FILES["file"]["size"] / 1024) . " Kb<br />";
    echo "Temp file: " . $_FILES["file"]["tmp_name"] . "<br />";

    if (file_exists("upload/" . $_FILES["file"]["name"]))
      {
      echo $_FILES["file"]["name"] . " already exists. ";
      }
    else
	
      {
	  $move = true;
      $ext = end(explode('.', $_FILES["file"]["name"]));
		$file = md5_file($_FILES["file"]["tmp_name"]);
		$file .= '.' . $ext;
		move_uploaded_file($_FILES["file"]["tmp_name"], "uploaded/" . $_FILES["file"]["name"]);
      echo "Stored in: " . "uploaded/" . $_FILES["file"]["name"];
      }
    }
  }
else
  {
  echo "Invalid file";
  }
  
  $path=$_FILES["file"]["name"];
  
  
 if($move)
{

 $con = mysql_connect("localhost","******","******");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("deucalio_photostore", $con);

	//mysql_query("INSERT INTO photos (photo) VALUES ('wedd')");
	mysql_query("INSERT INTO photos (userID, directory) VALUES('3','second')");
	


mysql_close($con);
  }
  
?>


Any help on that would be great as I really cannot figure out how to separate the images in the uploaded folder per user, even with a user, in case they upload another image with the same name, both should be stored in the database.


Many thanks!!!!
Was This Post Helpful? 0
  • +
  • -

#8 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3637
  • View blog
  • Posts: 5,764
  • Joined: 08-June 10

Re: What would be the best way to upload and store a file to a database,

Posted 24 February 2012 - 11:48 AM

I would suggest just using the database ID as the filename. That would both guarantee unique filenames and remove the need to actually store a filepath in the database. - All you'd have to do is insert the file meta-data into the database first, grab the new ID, and then move the file.
Was This Post Helpful? 0
  • +
  • -

#9 Jstall  Icon User is offline

  • Lurker
  • member icon

Reputation: 434
  • View blog
  • Posts: 1,042
  • Joined: 08-March 09

Re: What would be the best way to upload and store a file to a database,

Posted 24 February 2012 - 11:54 AM

Hi,

I don't really see the point of creating a separate directory for each user's images. All it would do is make more work for yourself. If you follow Atli's advice you will know your file names are unique.
Was This Post Helpful? 0
  • +
  • -

#10 deucalion0  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 48
  • Joined: 27-November 10

Re: What would be the best way to upload and store a file to a database,

Posted 24 February 2012 - 12:10 PM

Hi, you are right, I am trying to follow atli's advice, so I can change the name of the uploaded file to the ID number of the photo. So if it is ID 112, then the name becomes 112.png for example, I am researching how to do this at the minute, it is proving a pain.

Thanks!!!
Was This Post Helpful? 0
  • +
  • -

#11 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3637
  • View blog
  • Posts: 5,764
  • Joined: 08-June 10

Re: What would be the best way to upload and store a file to a database,

Posted 25 February 2012 - 04:46 AM

You don't really need the extension. If you do this you'll want to pass the file through a PHP script when it is downloaded, so that you can set the name to what it should be. You would put the extension on it there, so it would be simpler to just leave it off the file when you store it.

The logic for this is pretty straight forward. In the upload script, you'd do something like:

  • Verify the file upload. - That is: Make sure the $_FILES["file"] element isset, and check the $_FILES["file"]["error"] element.

  • Open a connection to your database. I'd recommend you use Improved MySQL Extension or PDO rather than the old mysql_* functions.

  • Grab the image meta-data and insert it into the database. You'd only really need the filename and the mime type.

  • Get the ID of the row that was just inserted. Both MySQLi and PDO make this very simple, with the mysqli::insert_id attribute and the PDO::lastInsertId function.

  • Use the ID to create the filename. Something like /var/uploads/256, where 256 would be the ID. - Note that you don't have to put the upload directory into the website itself; it doesn't have to be in a folder where people can access it via the HTTP server.

  • Move the file there using move_uploaded_file.


On the other end, when you want the file to be downloaded, you would want to use a single download.php file to do that. Of course you can improve this later by using URL rewriting to make the URLs look more natural, but I won't go into that here. - This code could work something like this:

  • Make sure the ID exists in the $_GET array (again, using isset) and make sure it's a valid ID. - In the case of integer IDs, it's enough to just cast it to an int and make sure it's above 0.
    if (!isset($_GET["id"]) || ((int)$_GET["id"]) <= 0)
        die("Invalid ID!");
    
    


  • Make sure a file matching this ID exists. You don't have to mess with the database here, just use file_exists to verify that there is actually a file matching this ID.

  • Now you'd want to fetch the meta-data for the file from the database. Open a MySQL connection, execute a SELECT query to fetch the filename and mime type, and make sure it's valid.

  • Then all you need to do is send the file to the user, by setting the proper response headers to tell the browser to expect a file download:
    header("Content-Type: {$mime}");
    header("Content-Disposition: attachment; filename=\"{$fileName}\"");
    header("Content-Transfer-Encoding: binary");
    header("Content-Length: " . filesize($filePath));
    readfile($filePath);
    exit;
    
    


Now all you have to do to download a file is request: download.php?id=256 and the server will send you the file, with it's original name and mime type.
Was This Post Helpful? 2
  • +
  • -

Page 1 of 1