File Upload via PHP/MySQL

As per the request of Spaculus I have decided to put together a tutorial discussing how to upload an image to your server, and store the information about that uploaded image into your MySQL database. This is a function that can often come in handy when developing a user membership system with avatar support, or a venue/event listing that displays an image for a venue/event. For this tutorial we are going to assume that we are creating a simple user membership system with avatar support. Upon registration the user will be asked to select an avatar they wish to use for themselves.

Please note that the user registration process shown here does not include any error checking, nor does it check the database if the same username exists. This is merely done as a demonstration and to better show how you can utilize the various functions outlined within this tutorial.

We’ll start with setting up our database. You can either use phpMyAdmin to manually create all of your rows, or simply execute the following SQL:

CREATE TABLE `users` (
`id` int(6) NOT NULL auto_increment,
`username` varchar(50) NOT NULL default '',
`password` varchar(50) NOT NULL default '',
`avatar` text NOT NULL default '',
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;

Now that we have our database setup, let’s create a simple registration form:

Username: 
Password: 
Avatar: 

Take note that I have set the form’s encoding type to multipart/form-data – without this the server simply will not process the image that we’re trying to upload.  I’m submitting my form to a file called submit.php – but you can obviously call yours whatever you want – just remember to change the filename in the code above.

Now let’s start working on our submit.php file. First thing we’ll want to do here is connect to our database. I won’t go into detail on this part here – however I do have a full tutorial written up about how to connect to a mysql database, feel free to give that a read through if you need any help. Once we’ve connected to the database we’ll use PHP’s $_POST array to save all the information submitted via our form into variables, and set the target folder where we wish to store all our uploaded files. Note that while all the text fields will be submitted via the $_POST array, the avatar gets submitted via the $_FILES array.  The $target variable sets the relative folder where the users files will be submitted; make sure you create this folder prior to running the script the first time or you will encounter an error.

 mysql_connect($dbhost, $dbuser, $dbpass);
 mysql_select_db($db);
 $username= trim($_POST['username']);
 $password= trim($_POST['password']);
 $avatar=($_FILES['avatar']['name']);
 $target = "avatars/";
 $target = $target . basename( $_FILES['avatar']['name']);
 mysql_close();
 ?>
Now that we have all our variables setup we can start with the first main part - saving all of this information into our database. For this we will use the following query:
mysql_query("INSERT INTO `users` VALUES ('','$username', '$password', '$avatar')");
Next we take the uploaded file and move it into our target directory. If the file gets uploaded correctly - we'll let the user know. Otherwise we need to degrade gracefully and let the user know there was an error.
 if(move_uploaded_file($_FILES['avatar']['tmp_name'], $target))
{
 echo "The image ". basename( $_FILES['uploadedfile']['name']). " has been uploaded.";
}
 else
{
 echo "Sorry, there was a problem uploading your venue photo file.";
}
Finally we need to tie everything together and make sure our MySQL query from before has executed without any errors. To do this we'll set the whole query into a variable, then test to see if that variable is null or not. If it's not null - we  know that our query has successfully been executed. The final code for submit.php looks like this:
 $dbuser = "mysql_username";
 $dbpass = "mysql_password";
 $dbhost = "localhost";
 $db = "db_name";
 mysql_connect($dbhost, $dbuser, $dbpass);
 mysql_select_db($db);
 $username= trim($_POST['username']);
 $password= trim($_POST['password']);
 $avatar=($_FILES['avatar']['name']);
 $target = "avatars/";
 $target = $target . basename( $_FILES['avatar']['name']);
$results = mysql_query("INSERT INTO `users` VALUES ('','$username', '$password', '$avatar')");
if(move_uploaded_file($_FILES['avatar']['tmp_name'], $target))
 {
  echo "The image ". basename( $_FILES['uploadedfile']['name']). " has been uploaded.";
  echo ""
 } 
else 
 { 
echo "Sorry, there was a problem uploading your venue photo file."; 
 } 
if ($results) { 
echo "New User Has Been Added!"; 
} 
mysql_close();