Brownman Revival Brownman Revival - 2 years ago 189
SQL Question

Saving Files as blob in database ajax php pdo

$fileCount = count($_FILES);
for ($i = 0; $i < $fileCount; $i++) {
$fp = fopen($_FILES["file_".$i]['tmp_name'], 'rb');
$stmt4 = $dbh - > prepare("INSERT INTO files_tbl (pin,remarks,fileblob,file_type,nameoffile,filesize) VALUES (?,?,?,?,?,?)");
$stmt4 - > bindValue(1, $pin, PDO::PARAM_STR);
$stmt4 - > bindValue(2, $remarks, PDO::PARAM_STR);
$stmt4 - > bindParam(3, $fp, PDO::PARAM_LOB);
$stmt4 - > bindParam(4, $_FILES["file_".$i]['type'], PDO::PARAM_STR);
$stmt4 - > bindValue(5, $_FILES["file_".$i]['name'], PDO::PARAM_STR);
$stmt4 - > bindValue(6, $_FILES["file_".$i]['size'], PDO::PARAM_STR);
$stmt4 - > execute();

This is how i insert file as blob in php. It is saving a file but it is not saving properly. When i say it is not saving properly i mean something is wrong along the way. When i compare saving the file using my project and manually adding the file in XAMPP there is a difference in fileblob for example i save a file manually in xampp the fileblob is
[BLOB - 488.9 KiB]
when i use the project is becomes
[BLOB - 479.2 KiB]
. I think this the reason when i try to show the file from database it is showing a blank page(when the file i show is the file i insert using project) but if the file i try to show is the file i insert manually in xampp it is showing the file.

What could be wrong in my insert?why am i not saving the proper blob


<input type="file" id="filecontent" name="filecontent" multiple="">


var file = $('#filecontent')[0].files;
for (var i = 0; i < file.length; i++) {
formData.append("file_" + i, file[i]);

//more data are passed to formData
//formData.append("file", file[i]);

url: '../include/AddNew.php',
type: 'POST',
dataType: "json",
data: formData,
processData: false, // tell jQuery not to process the data
contentType: false, // tell jQuery not to set contentType
success: function(data) {
error: function(data) {
//alert("Error!"); // Optional


when i tried to put
before the line
for ($i = 0; $i < $fileCount; $i++) {
the output is

[file_0] => Array
[name] => whomovedmycheese - Copy.pdf
[type] => application/pdf
[tmp_name] => C:\Users\HogRider\xampp\tmp\phpE775.tmp
[error] => 0
[size] => 500624

[file_1] => Array
[name] => whomovedmycheese.pdf
[type] => application/pdf
[tmp_name] => C:\Users\HogRider\xampp\tmp\phpE786.tmp
[error] => 0
[size] => 500624





Answer Source

As per PHP/PDO/MySQL: inserting into MEDIUMBLOB stores bad data, try using the following line to construct your PDO object:

$dbh = new PDO($dsn, $username, $password, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES latin1 COLLATE latin1_general_ci"));


I think there are, as Ben M notes in the linked question, two bad design decisions at work here.

There is this concept of a connection charset. The idea is that the SQL text can be in any charset and is then converted upon retrieval by the SQL server.

This does not work that well with binary data as it is not text and, thus, must not, by definition, be in any charset, but is still transferred using string literals.

This issue can be worked around by quoting BLOB data during transfer (either using the BASE64_* functions or by hex-escaping) and, indeed, that is what many people are doing.

The second design decision is in PDO/PHP: PDO does not do any charset conversion (it can’t, because strings in PHP are inherently charset-agnostic) so PHP is the only (or one of the few languages) where the choice of the SQL transfer charset is actually important because it needs to match the encoding the input strings are actually in.

In other languages, the transfer charset just needs to be expressive enough to encompass any characters that might be used in strings. In today’s world of emojis, this, most likely is only guaranteed by unicode charsets (utf-8 and the like). However, none of these are binary-safe (in that not every possible combination of bytes yields a valid string) so even if we could work around the PHP issue, we’d still be left with problem #1.

In an ideal world, SQL commands would always be in the ASCII charset during transfer and every string value would have a charset argument, of which “binary” could be a possible value, supplied with it. MySQL actually has such a construct for strings, which it calls an “introducer”. “_binary”, however, does not seem to be a valid value.

This charset information would then be used by the other end to convert the string value into its the native charset (either the column’s for client-to-server transfers or the programming language’s string charset for server-to-client transfers).

That way, the only thing that would have to be escaped in BLOB values would be the string delimiter (" or ').

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download