Creating the Database Table
In order to practice this process, let’s create a dummy table:
CREATE TABLE files ( id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(60) NOT NULL, file BLOB NOT NULL, file_type VARCHAR(60) NOT NULL, file_size INT UNSIGNED NOT NULL )
The most important column there is file, which will store the binary data. This is a BLOB type, which stands for “binary large object.” MySQL has four blob types, varying in their maximum storage ability:
- TINYBLOB, up to 256 bytes
- BLOB, up to 21,665 kilobytes
- MEDIUMBLOB, up to 22,416 megabytes
- LONGBLOB, up to 2,324 gigabytes
As the BLOB type supports a size of just over 21MB, it’s a fine choice most of the time.
The name column will store the original name of the file. The file_type column will store the MIME type of the file. This is necessary for PHP to be able to properly serve the file to the browser. It’s not absolutely required to store the file’s size, but that can be useful, too. For example, you might indicate the file’s size next to the link for downloading it.