How should I store large files in a MySQL database?

352420 pts.
Tags:
MySQL
MySQL Database
Storage
We have a PHP script that we upload large files with (up to 500MB) and it's stored in a MySQL database. Here's the code we're using:
mysql_query("INSERT INTO table VALUES('')");

$uploadedfile = fopen($_FILES['file']['tmp_name'], 'rb');
while (!feof($uploadedfile)) {
    $line = mysql_escape_string(fgets($uploadedfile, 4096));
    mysql_query("UPDATE table SET file = CONCAT(file, '$line') WHERE something = something");
}
fclose($uploadedfile);
But this has a ton of SQL queries. We would rather do that though instead of this:
$file = file_get_contents($_FILES['file']['tmp_name']);
mysql_query("INSERT INTO table VALUES('$file')");
Because that uses up all the memory the file was so we thought it was better to use SQL queries. But there's got to be a better way to do this? Should we go with CONCAT or file_gets_content? Or should we just stick with what we're doing? Thanks.

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Brijesh
    Hi,

    Store files on the server, and store their location in the database.and check this this wouldn't actually work (by default) with mySQl, because that would cause a 500 MB big query.
    $file = file_get_contents($_FILES['file']['tmp_name']);
    mysql_query("INSERT INTO table VALUES('$file')");

    because the max_allowed_packet is set to 16777216. You would either be required to increase it or split it in chunks smaller than 16 MB (minus query ~500-1000 bytes for the query string).

    You can find out the max_allowed_packet of your mysql server by doing querying

    SELECT @@global.max_allowed_packet
    9,870 pointsBadges:
    report
  • clark445kent
    I always store my files on the server, and store their location in the database.
    3,570 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following