Documents
Resources
Learning Center
Upload
Plans & pricing Sign in
Sign Out

Storing Data Files in MySQL

VIEWS: 26 PAGES: 5

									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.

								
To top