binary file content shown in php print_r but not saved in mysql

Question

i have to save the files from input fields to database,i'll do it this way:

    $mkey = mysqli_insert_id($mysqli);
    $i=0;
    while (isset($_FILES['file'.$i])) {
        $filepath = ini_get('upload_tmp_dir')."/".basename($_FILES['file'.$i]['tmp_name']);
        $filepath = addslashes($filepath);
        $handle = fopen($filepath, "rb");
        $content = fread($handle, filesize($filepath));
        $stmt = $mysqli->prepare("INSERT INTO attachment (filename,filecontent,mkey) VALUES (?,?,?)");
        $stmt->bind_param("sbi",$_FILES['file'.$i]['name'],$content,$mkey);
        $stmt->execute();
        fclose($handle);
        $i++;
    }

no error occured,and the other 2 fields mkey and filename are filled in database columns,but the content no,$content shown with print_r in my browser and i sure the $content variable is not empty,but mysql shows me nothing of the content.

It's probably not a good idea trying to store binary files in SQL; if you do decide that's what you want to do, then you should at least convert the data to hex first. — l'L'l
– l'L'l, Commented Dec 25, 2014 at 0:03
@l'L'l definitely not a good idea,but i've to deal with this in this case. converting to hex?!and when i want to load it back convert it again?? — Francesco
– Francesco, Commented Dec 25, 2014 at 0:07
read about mysqli_stmt_send_long_data this should be used to store binary files into database, also - you don't have any error checking in your code... — Iłya Bursov
– Iłya Bursov, Commented Dec 25, 2014 at 0:10
Look at bin2hex() and then when you want to load it back use hex2bin(). Here's something similar that might help: stackoverflow.com/a/5534337/499581. Lashane's suggestion will work too, but for images they need to be utf8_encoded first. — l'L'l
– l'L'l, Commented Dec 25, 2014 at 0:10

Funk Forty Niner · Accepted Answer · 2017-05-05 19:32:01Z

2

RTM ;-)

If data size of a variable exceeds max. allowed packet size (max_allowed_packet), you have to specify b in types and use mysqli_stmt_send_long_data() to send the data in packets.

So I have never done this myself but i would assume it needs to look something like this based on your code and the example on the functions doc page:

    $filepath = ini_get('upload_tmp_dir')."/".basename($_FILES['file'.$i]['tmp_name']);
    $filepath = addslashes($filepath);
    $handle = fopen($filepath, "rb");
    $content = null;

    $stmt = $mysqli->prepare("INSERT INTO attachment (filename,filecontent,mkey) VALUES (?,?,?)");
    $stmt->bind_param("sbi",$_FILES['file'.$i]['name'], $content, $mkey);

    while (!feof($handle)) {
        // $maxPacketSize would be the size of your max packet setting for mysql,
        // or something safely assumed to be below it
        $stmt->send_long_data(1, fread($handle, $maxPacketSize));
    }
    fclose($handle);
    $stmt->execute();

edited May 5, 2017 at 19:32

Funk Forty Niner

74.2k14 gold badges71 silver badges146 bronze badges

answered Dec 25, 2014 at 0:14

prodigitalson

60.6k10 gold badges106 silver badges118 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Francesco Over a year ago

tnx,i don't know anything about this!

prodigitalson Over a year ago

Neither did I until researching it for you :-)

Collectives™ on Stack Overflow

binary file content shown in php print_r but not saved in mysql

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related