For column content we'll use BLOB data type. BLOB is a binary large object that can hold a variable amount of data. MySQL have four BLOB data types, they are :

• TINYBLOB
• BLOB
• MEDIUMBLOB
• LONGBLOB

Since BLOB is limited to store up to 64 kilobytes of data we will use MEDIUMBLOB so we can store larger files ( up to 16 megabytes ).

Uploading a file to MySQL is a two step process. First you need to upload the file to the server then read the file and insert it to MySQL.

For uploading a file we need a form for the user to enter the file name or browse their computer and select a file. The input type="file" is used for that purpose.

Example : upload.php
Source code : upload.phps

An upload form must have encytype="multipart/form-data" otherwise it won't work at all. Of course the form method also need to be set to method="post". Also remember to put a hidden input MAX_FILE_SIZE before the file input. It's to restrict the size of files.

After the form is submitted the we need to read the autoglobal $_FILES. In the example above the input name for the file is userfile so the content of $_FILES are like this :

$_FILES['userfile']['name']
The original name of the file on the client machine.

$_FILES['userfile']['type']
The mime type of the file, if the browser provided this information. An example would be "image/gif".

$_FILES['userfile']['size']
The size, in bytes, of the uploaded file.

$_FILES['userfile']['tmp_name']
The temporary filename of the file in which the uploaded file was stored on the server.

$_FILES['userfile']['error']
The error code associated with this file upload. ['error'] was added in PHP 4.2.0

Example : upload.php 

PHP saves the uploaded file with a temporary name and save the name in $_FILES['userfile']['tmp_name']. Our next job is to read the content of this file and insert the content to database. Always make sure that you use addslashes() to escape the content. Using addslashes() to the file name is also recommended because you never know what the file name would be.

That's it now you can upload your files to MySQL. Now it's time to write the script to download those files.

Downloading Files From MySQL Database

When we upload a file to database we also save the file type and length. These were not needed for uploading the files but is needed for downloading the files from the database.

The download page list the file names stored in database. The names are printed as a url. The url would look like download.php?id=3. To see a working example click here. I saved several images in my database, you can try downloading them.

Example :  

Execute a database backup query from PHP file

Below is an example of using SELECT INTO OUTFILE query for creating table backup :

To restore the backup you just need to run LOAD DATA INFILE query like this :

It's a good idea to name the backup file as tablename.sql so you'll know from which table the backup file is

Run mysqldump using system() function

The system() function is used to execute an external program. Because MySQL already have built in tool for creating MySQL database backup (mysqldump) let's use it from our PHP script

In above example I use the constant MYSQL_ASSOC as the second argument to mysql_fetch_array(), so that it returns the row as an associative array. With an associative array you can access the field by using their name instead of using the index . Personally I think it's more informative to use $row['subject'] instead of $row[1].

PHP also provide a function called mysql_fetch_assoc() which also return the row as an associative array.

You can also use the constant MYSQL_NUM, as the second argument to mysql_fetch_array(). This will cause the function to return an array with numeric index.

Using the constant MYSQL_NUM with mysql_fetch_array() gives the same result as the function mysql_fetch_row().

In above example, list() assign the values in the array returned by mysql_fetch_row() into the variable $name, $subject and $message.

Of course you can also do it like this

So you see you have lots of choices in fetching information from a database. Just choose the one appropriate for your program

Freeing the memory ?

In some cases a query can return large result sets. As this results are stored in memory there's a concern about memory usage. However you do not need to worry that you will have to call this function in all your script to prevent memory congestion. In PHP all results memory is automatically freed at the end of the script's execution.

But you are really concerned about how much memory is being used for queries that return large result sets you can use mysql_free_result(). Calling this function will free all memory associated with the result identifier ( $result ).

Using the above example you can call mysql_free_result() like this :