Blob always inserting 1B when inserting with PDO

Question

When I try to upload a picture and then put it in the database as a BLOB.

The code that I use works perfectly when inserting into another table, so I basically just used the code of another insert query.

This is the PHP:

<?php
error_reporting(-1);
if (isset($_POST['gallery-upload'])) {
    $hostname='**';
    $username='**';
    $password='**';
    $file = $_FILES['image'] ['tmp_name'];
    $tmpName  = $_FILES['image']['tmp_name'];
    $filter = $_POST['filter'];

    $fp = fopen($tmpName, 'rb');

    try {
        $dbh = new PDO("mysql:host=$hostname;dbname=dddoecje_campu",$username,$password);
        $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // <== add this line
        $stmt = $dbh->prepare("INSERT INTO `gallery`(`id`, `image`, `filter`) VALUES ('',:image,:filter)");
        $stmt->bindParam(':filter', $filter);
        $stmt->bindParam(':image', $fp, PDO::PARAM_LOB);
        $dbh->errorInfo();
        $stmt->execute();
        // use exec() because no results are returned
        header("Location: index.php");

    }
    catch(PDOException $e)
    {
        echo $sql . "<br>" . $e->getMessage();
    }

    $dbh = null;
}
?>

And this is the form:

<div class="col-md-4">
    <div class="panel panel-default">
        <div class="panel-body">
            <form action="upload-gallery.php" method="post" enctype="multipart/form-data">
        <div class="form-group">
            <i class="icon-cloud-upload"></i>
            <span>Select file</span>
            <input type="file" accept="image/png, image/jpeg, image/gif" data-max-size="5000" name="image">
        </div>
                <select name="filter">
                    <option value="all">All</option>
                    <option value="area">Area</option>
                    <option value="resort">Resort</option>
                    <option value="rinjani">Rinjani</option>
                </select>
                <button style="margin-top: 10px;" name="gallery-upload" class="btn btn-default"><?php echo $upload_gallery;?></button>
        </form>
        </div>
  </div>
</div>

This is what I see in the database after I insert the image:

Everything is inserting fine, but only the BLOB doesn't insert for some reason. Also without the data-max-size it doesn't work.

your title reads as "insert 1B", what do you mean by that, 1 byte? and how large is the file you're trying to upload and what is the column's type/size it can handle? — Funk Forty Niner, Nov 09 '16 at 19:14
also `$file = $_FILES['image'] ['tmp_name'];` should be `$file = $_FILES['image'] ['name'];`. You already have your tmp declaration here `$tmpName = $_FILES['image']['tmp_name'];`. Did you try `$stmt->bindParam(':image', $file, PDO::PARAM_LOB);` also with this comment? What I said here should work. — Funk Forty Niner, Nov 09 '16 at 19:15
For your first answer, check the edit. For your second answer, tried what you put there, didn't work — Rik, Nov 09 '16 at 19:21
Don't save files in a database. http://stackoverflow.com/a/38829952/267540 — e4c5, Nov 10 '16 at 04:50

score -1 · Answer 1 · answered Nov 09 '16 at 19:25

-1

You have opened a file handler that points to your file, and are passing a filehandler as a parameter.

Try to read the full contents referenced by your file handler into a variable, and pass that variable to the prepared statement. You can do this by using the fgets() command.

I can't easily show you how to do it since I'm writing this answer on my phone.

Open the file (which you've done)
Read the file into a variable While (!feof($fp)) {$var .= fgets($fp);}

answered Nov 09 '16 at 19:25

Karl Buys

439
5
12

Inserting with LOBs, you just bind in a file pointer and PDO will read from that. You don't need to read in the whole file yourself. – Jonathan Kuhn Nov 09 '16 at 19:29
@jonathan Where I was going with this answer is that in an instance where his code is failing to insert the data successfully, the methodology proposed serves as an alternate way in which he could solve his problem, assuming it is related to that specific file stream. – Karl Buys Nov 09 '16 at 19:50

Blob always inserting 1B when inserting with PDO

1 Answers1