bahattab
15-01-2008, 08:07 PM
Storing User Submitted Data and Files in MySQL
From Angela Bradley (http://php.about.com/mbiopage.htm),
Your Guide to PHP / MySQL (http://php.about.com/).
FREE Newsletter. Sign Up Now! (http://php.about.com/gi/pages/mmail.htm)
Creating a Form
Insert Into - Adding Data from a Form (http://php.about.com/od/phpbasics/ss/mysql_files_2.htm)
Add File Uploads (http://php.about.com/od/phpbasics/ss/mysql_files_3.htm)
Adding File Uploads to MySQL (http://php.about.com/od/phpbasics/ss/mysql_files_4.htm)
Adding Uploads Explained (http://php.about.com/od/phpbasics/ss/mysql_files_5.htm)
Retrieving Files (http://php.about.com/od/phpbasics/ss/mysql_files_6.htm)
Removing Files (http://php.about.com/od/phpbasics/ss/mysql_files_7.htm)
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
$name=$_POST['name'];
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:
<? $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 (http://php.about.com/od/learnmysql/ss/mysql_4.htm) 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>
Adding File Uploads to MySQL
mysql_connect("your.server.com","username","password");
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_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','$f orm_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 (http://php.about.com/od/finishedphp1/qt/slash_function.htm) 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 (http://php.about.com/od/phpfunctions/g/fopen_php.htm) opens the file and FREAD (http://php.about.com/od/phpfunctions/g/fread_php.htm) 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
mysql_connect("your.server.com","username","password");
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_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
mysql_connect("your.server.com","username","password");
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_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.
http://php.about.com/od/phpbasics/ss/mysql_files_7.htm
From Angela Bradley (http://php.about.com/mbiopage.htm),
Your Guide to PHP / MySQL (http://php.about.com/).
FREE Newsletter. Sign Up Now! (http://php.about.com/gi/pages/mmail.htm)
Creating a Form
Insert Into - Adding Data from a Form (http://php.about.com/od/phpbasics/ss/mysql_files_2.htm)
Add File Uploads (http://php.about.com/od/phpbasics/ss/mysql_files_3.htm)
Adding File Uploads to MySQL (http://php.about.com/od/phpbasics/ss/mysql_files_4.htm)
Adding Uploads Explained (http://php.about.com/od/phpbasics/ss/mysql_files_5.htm)
Retrieving Files (http://php.about.com/od/phpbasics/ss/mysql_files_6.htm)
Removing Files (http://php.about.com/od/phpbasics/ss/mysql_files_7.htm)
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
$name=$_POST['name'];
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:
<? $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 (http://php.about.com/od/learnmysql/ss/mysql_4.htm) 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>
Adding File Uploads to MySQL
mysql_connect("your.server.com","username","password");
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_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','$f orm_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 (http://php.about.com/od/finishedphp1/qt/slash_function.htm) 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 (http://php.about.com/od/phpfunctions/g/fopen_php.htm) opens the file and FREAD (http://php.about.com/od/phpfunctions/g/fread_php.htm) 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
mysql_connect("your.server.com","username","password");
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_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
mysql_connect("your.server.com","username","password");
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_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.
http://php.about.com/od/phpbasics/ss/mysql_files_7.htm