Best way to embed blob in MySQL with PHP

Question

I am working on a system in which I insert files into a database. There are two ways I can insert blob into DB, so I'm curious which one is better.

First, to get the content, and then bind the content parameter as a string when pasting:

$fp = fopen($tmpName, 'r'); $content = fread($fp, filesize($tmpName)); $content = addslashes($content); fclose($fp); ... $prep_stmt = "INSERT INTO dokumenty (name, size, type, content, autor, poznamka) VALUES (?, ?, ?, ?, ?, ?)"; $stmt = $mysqli->prepare($prep_stmt); $stmt->bind_param('sissis',$fileName,$fileSize,$fileType,$content,$user_id,$poznamka ); 

Another way is to use send_long_data as follows:

  $content = NULL; ... $prep_stmt = "INSERT INTO dokumenty (name, size, type, content, autor, poznamka) VALUES (?, ?, ?, ?, ?, ?)"; $stmt = $mysqli->prepare($prep_stmt); $stmt->bind_param('sisbis',$fileName,$fileSize,$fileType,$content,$user_id,$poznamka ); $stmt->send_long_data(3, file_get_contents($tmpName)); 

My question is: which way is better to use, since both work?

Answer 1

Using the send_long_data method will allow you to decide when the file is too large compared to the max_allowed_packet value in your particular MySQL server setup. However, the fact that your two codes work demonstrates that you have not reached this limit in your tests. The send_long_data method was designed to repeatedly call in a loop with partial "chunks" of data that are too large to be sent in a single pass. In addition, I do not approve of the way you wrote the second code, because you cannot handle the error while reading the file. I suggest a third way to code this:

 define ('YOUR_MYSQL_MAX_ALLOWED_PACKET', 8192); $fp = fopen($tmpName, "r"); // TODO check here if fopen succeed $prep_stmt = "INSERT INTO dokumenty (name, size, type, content, autor, poznamka) VALUES (?, ?, ?, ?, ?, ?)"; $stmt = $mysqli->prepare($prep_stmt); while (!feof($fp)) { $stmt->send_long_data(3, fread($fp, YOUR_MYSQL_MAX_ALLOWED_PACKET)); } fclose($fp); 

If the value of the constant YOUR_MYSQL_MAX_ALLOWED_PACKET should be adjusted for your specific MySQL setup.