Creating a Form Sometimes it is useful to collect data from your website users and store this information in a MySQL database. We have already seen you can populate a database using PHP, now we will add the practicality of allowing the data to be added through a user friendly web form. The first thing we will do is create a page with a form. For our demonstration we will make a very simple one: <form action="process.php" method="post"> Your Name: <input type="text" name="name"><br> E-mail: <input type="text" name = "email"><br> Location: <input type="text" name = "location"><br> <input type="submit" value="Submit"> </form> Insert Into - Adding Data from a Form Next you need to make process.php, the page that our form sends its data to. Here is an example of how to collect this data to post to the MySQL database: <? $name=$_POST['name']; $email=$_POST['email']; $location=$_POST['location']; mysql_connect("your.hostaddress.com", "username", "password") or die(mysql_error()); mysql_select_db("Database_Name") or die(mysql_error()); mysql_query("INSERT INTO `data` VALUES ('$name', '$email', '$location')"); Print "Your information has been successfully added to the database."; ?> As you can see the first thing we do is assign variables to the data from the previous page. We then just query the database to add this new information. Of course before we try it we need to make sure the table actually exists. Executing this code should create a table that can be used with our sample files: CREATE TABLE data (name VARCHAR(30), email VARCHAR(30), location VARCHAR(30)); Add File Uploads Now you know how to store user data in MySQL, so let's take it one step farther and learn how to upload a file for storage. First let's make our sample database: CREATE TABLE uploads (id INT(4) NOT NULL AUTO_INCREMENT PRIMARY KEY, description CHAR(50), data LONGBLOB, filename CHAR(50), filesize CHAR(50), filetype CHAR(50) ); The first thing you should notice is a field called id that is set to AUTO_INCREMENT. What this data type means is that it will count up to assign each file a unique file ID starting at 1 and going to 9999 (since we specified 4 digits). You will also probably notice that our data field is called LONGBLOB. There are many types of BLOB as we have mentioned before. TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB are your options, but we set ours to LONGBLOB to allow for the largest possible files. Next, we will create a form to allow the user to upload her file. This is just a simple form, obviously you could dress it up if you wanted: <form method="post" action="upload.php" enctype="multipart/form-data"> Description:<br> <input type="text" name="form_description" size="40"> <input type="hidden" name="MAX_FILE_SIZE" value="1000000"> <br>File to upload:<br> <input type="file" name="form_data" size="40"> <p><input type="submit" name="submit" value="submit"> </form> Be sure to take notice of the enctype, it is very important! Adding File Uploads to MySQL Next we need to actually create upload.php, which will take our users file and store it in our database. Below is sample coding for upload.php. <?php mysql_connect("your.server.com","username","password"); mysql_select_db("database_name"); $data = addslashes(fread(fopen($form_data, "r"), filesize($form_data))); $result=MYSQL_QUERY("INSERT INTO uploads (description, data,filename,filesize,filetype) ". "VALUES ('$form_description','$data','$form_data_name','$form_data_size','$form_data_type')"); $id= mysql_insert_id(); print "<p>File ID: <b>$id</b><br>"; print "<p>File Name: <b>$form_data_name</b><br>"; print "<p>File Size: <b>$form_data_size</b><br>"; print "<p>File Type: <b>$form_data_type</b><p>"; print "To upload another file <a href=http://www.yoursite.com/yourpage.html> Click Here</a>"; ?> Learn more about what this actually does on the next page. Adding Uploads Explained The first thing this code actually does is connect to the database (you need to replace this with your actual database information.) Next it uses the ADDSLASHES function. What this does is add back slashes if needed into the file name so that we won't get an error when we query the database. For example if we have Billy'sFile.gif, it will convert this to Billy\'sFile.gif. FOPEN opens the file and FREAD is a binary safe file read, so that the ADDSLASHES is applied to data within the file if needed. Next we add all of the information our form collected into our database. You will notice we listed the fields first, and the values second so we don't accidently try to insert data into our first field (the auto assigning ID field.) Finally we print out the data for the user to review. Retrieving Files We already learned how to retrieve plain data from our MySQL database. Likewise, storing your files in a MySQL database wouldn't be very practical if there wasn't a way to retrieve them. The way we are going to learn to do this is by assigning each file a URL based on their ID number. If you will recall when we uploaded the files we automatically assigned each of the files an ID number. We will use that here when we call the files back. Save this code as download.php <?php mysql_connect("your.server.com","username","password"); mysql_select_db("database_name"); $query = "SELECT data,filetype FROM uploads where id=$id"; $result = MYSQL_QUERY($query); $data = MYSQL_RESULT($result,0,"data"); $type = MYSQL_RESULT($result,0,"filetype"); Header( "Content-type: $type"); print $data; ?> Now to retrieve our file, we point our browser to: http://www.yoursite.com/download.php?id=2 (replace the 2 with whatever file ID you want to download/display) This code is the base for doing a lot of things. With this as a base you can add in a database query that would list files, and put them in a drop down menu for people to choose. Or you could set ID to be a randomly created number so that a different graphic from your database is randomly displayed each time a person visits. The possibilities are endless. Removing Files Here is a very simple way of removing files from the database. You want to be careful with this one!! Save this code as remove.php <?php mysql_connect("your.server.com","username","password"); mysql_select_db("database_name"); $query = "DELETE FROM uploads where id=$id"; $delete = MYSQL_QUERY($query); print "File ID $id has been removed from the database"; ?> Like our previous code that downloaded files, this script allows files to be removed just by typing in their URL: http://yoursite.com/remove.php?id=2 (replace 2 with the ID you want to remove.) For obvious reasons, you want to be careful with this code. This is of course for demonstration, when we actually build applications we will want to put in safeguards that ask the user if they are sure they want to delete, or perhaps only allow people with a password to remove files. This simple code is the base we will build on to do all of those things.
Pages to are hidden for
"Storing Data Files in MySQL"Please download to view full document