If you want to store binary data like images and html files directly in your MySQL database, this column is for you!
I will show how you can store the data via the HTML forms “File” feature in your database and how you can access and use this data in your web project.
If you have read the article “PHP, MySQL and Images” by William Samplonius here on phpbuilder.com, this might be interesting for you as William stores the binary data somewhere on your hard disk (using a shell command), instead of storing the image directly in the Sql-Database.
Overview:
* Create a new database on your SQL Server
* A sample php3 script you can use to store data in your database
* A sample php3 script with which you can access the stored data
Create a new database on your SQL Server
First of all, you have to create a new database on your SQL server in which your script will store the binary data.
For my example I use the following structure. To create this database, you have to do the following steps:
* login to the MySQL monitor
* enter the command “create database binary_data;”
* enter the command “use binary_data;”
* copy and paste the following instructions (the table structure) to the monitor
* the database table should be created
CREATE TABLE binary_data (
id INT(4) NOT NULL AUTO_INCREMENT PRIMARY KEY,
description CHAR(50),
bin_data LONGBLOB,
filename CHAR(50),
filesize CHAR(50),
filetype CHAR(50)
);
A sample php3 script you can use to store data in your database
With the php script store.php3 you can transfer files via a html form interface into the created database.
store.php3
<?php
// store.php3 - by Florian Dittmer <[email protected]>
// Example php script to demonstrate the storing of binary files into
// a SQL database. More information can be found at https://phpbuilder.com/
?>
<html>
<head><title>Store binary data into SQL Database</title></head>
<body>
<?php
// code that will be executed if the form has been submitted:
if ($submit) {
// connect to the database
// (you may have to adjust the hostname,username or password)
MYSQL_CONNECT("localhost","root","password");
mysql_select_db("binary_data");
$data = addslashes(fread(fopen($form_data, "r"), filesize($form_data)));
$result=MYSQL_QUERY("INSERT INTO binary_data (description,bin_data,filename,filesize,filetype) ".
"VALUES ('$form_description','$data','$form_data_name','$form_data_size','$form_data_type')");
$id= mysql_insert_id();
print "<p>This file has the following Database ID: <b>$id</b>";
MYSQL_CLOSE();
} else {
// else show the form to submit new data:
?>
<form method="post" action="<?php echo $PHP_SELF; ?>" enctype="multipart/form-data">
File Description:<br>
<input type="text" name="form_description" size="40">
<input type="hidden" name="MAX_FILE_SIZE" value="1000000">
<br>File to upload/store in database:<br>
<input type="file" name="form_data" size="40">
<p><input type="submit" name="submit" value="submit">
</form>
<?php
}
?>
</body>
</html>
So if you execute this script, you will see a simple html form. Use the “browse” button to select a file (for example: an image) and press the “submit” button.
