使用一个表将不同的MIME类型上载到DataBase

Question

I'm working on a project that the user will be Upload several files with different MIME Types and I want to save the files to a DataBase(Mysql with InnoDB engeen). Now These are my Questions:
1/ Should I Create several Tables for every MIME type or may be different rows for every type?
2/ I tested BLOB in rows type in mysql for field's type, but it seems there is a problem with DB!!!
--either I tried MEDIUM BLOB and LONG BLOB--
3/ If I have to save every MIME type in a different row or table, Which type is OK for this iletypes:
a/pdf b/jpeg c/png d/gif e/video/mp4 f/application/word

Answer 1

Here is a small example I just tested

Table: files

id (primary key, auto increment)

filename (varchar 255)

type (varchar 255)

file (largeblob)

encoding: utf8_unicode_ci

<?php


$hostname = 'localhost';
$username = 'root';
$password = '';

try
{
    $dbh = new PDO("mysql:host=$hostname;dbname=test", $username, $password);
    echo 'Connected to database';
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $sql = $dbh->prepare("INSERT INTO `files` (filename, type, file) VALUES('img.png', 'image/png', :bin)");
    $sql->bindParam("bin", file_get_contents("img.png"));
    $sql->execute();

    $sql = $dbh->prepare("SELECT * FROM `files` WHERE `filename`='img.png'");
    $sql->execute();
    $result = $sql->fetch();

    $file = fopen("new-".$result['filename'], "w+");
    $w = fwrite($file, $result['file']);
    fclose($file);

}
catch(PDOException $e)
{
    echo $e->getMessage();
}
?>

For what you are trying to do:

1. Instead of fixed file you use the temporary file that was uploaded.

2. You use the MIME type of that file (you can get it with php)

3. When you need to read the file you simply read the BLOB field and write it to a file using the filename as the name.

I did not notice any problems with the BLOB fields. If you can't resolve that problem you could use a TEXT field and store base64_encode(file) into that.