Mysqli blob

[PHPNob]

I’m trying to store pdf files in the db using mysql blob and it always shows as 0 bytes if I use mysqli but it works fine with pdo

just for test purpose I added the database connection in the same file

<form action="submit_pdo.php" method="POST" accept-charset="utf-8" enctype="multipart/form-data">
        <div class="formgroup container-fluid">
            <label for="project_name">Project Name</label>
            <input type="text" name="project_name" id="project_name"/>
        </div>
        <div class="formgroup container-fluid">
            <input type="file" name="pdf_file" accept=".pdf"/>        </div>
        <br>
        <div class="formgroup container-fluid">
            <input type="submit" name="submit" value="Submit To Database"/>
        </div>
    </form>

PDO

<?php 
if($_SERVER['REQUEST_METHOD'] === 'POST'){
    // echo "<pre>";
    // var_dump($_POST);
    // var_dump($_FILES);
    // echo "</pre>";

    if ($_FILES['pdf_file']['error'] != 0) {
        echo 'Something wrong with the file.';
    }
    $project_name = htmlspecialchars($_POST['project_name']);
    $file_tmp = $_FILES['pdf_file']['tmp_name'];
    $file_name = $_FILES['pdf_file']['name'];
    $pdf_blob  = fopen($file_tmp, 'rb');
    if($pdf_blob === false) {
        return false;
    }

    $dsn = "mysql:host=localhost;dbname=files_db;charset=utf8mb4";
    $pdo = new PDO($dsn, "root", "");
    $query = "INSERT INTO `files_tbl` (`project_name`, `file_name`) values (:name, :file)";
    $stmt = $pdo->prepare($query);
    $stmt->bindParam(':name', $project_name, PDO::PARAM_STR);
    $stmt->bindParam(':file', $pdf_blob, PDO::PARAM_LOB );
    if($stmt->execute()) {
        echo 'Information saved';
    }
    else {
        echo 'Could not save information to the database';
    }

}else {
   
    header('Location: index.php');
}

mysqli

<?php

if($_SERVER['REQUEST_METHOD'] === 'POST'){
    // echo "<pre>";
    // var_dump($_POST);
    // var_dump($_FILES);
    // echo "</pre>";

    if ($_FILES['pdf_file']['error'] != 0) {
        echo 'Something wrong with the file.';
    }
    $project_name = htmlspecialchars($_POST['project_name']);
    $file_tmp = $_FILES['pdf_file']['tmp_name'];
    $file_name = $_FILES['pdf_file']['name'];
    $pdf_blob  = fopen($file_tmp, 'rb');
    if($pdf_blob === false) {
        return false;
    }
    
    $db =new mysqli("localhost","root", '', "files_db");
    $query = "INSERT INTO `files_tbl` (`project_name`, `file_name`)
    values (?, ?)";
    $stmt = $db->prepare($query);
    $stmt->bind_param("sb", $project_name, $pdf_blob);
    if($stmt->execute()) {
        echo 'Information saved';
    }
    else {
        echo 'Could not save information to the database';
    }

}else {

    header('Location: index.php');
}

[benanamen]

FYI, htmlspecialchars is an output function for HTML context. It is not for input.

[phdr]

Firstly, don’t store files in a database. Databases are for storing data. The filesystem is for storing files. The amount of work you have to go through to get the file into the database, get it out every time it is requested, and back it up (blob data is backed up as hex, so, the size is twice what the actual file size is) is not worth it.

Next, the mysqli extension is overly complicated, inconsistent, and in this case not very well documented. If you have working code using the simpler, much better designed, and universal PDO extension, why take a step backwards and use the mysqli extension for anything?

Lastly, the reason your mysqli code doesn’t work is because the file handle in $pdf_blob is NOT what you supply to the bind_param() call. The variable you supply in bind_param for the ‘b’ type either looks like it is completely ignored or it must be a null. To transfer the actual file data to the database server, you must either use the mysqli_stmt send_long_data() method, and loop over chunks of the file, if the file size is greater then the database server’s max_allowed_packet setting (default typically is 1M bytes) OR if the file size is less then the max_allowed_packet setting, you can specify a type ‘s’ and supply the contents of the file in the bind_param variable.

See this link for the send_long_data method - PHP: mysqli_stmt::send_long_data - Manual

[PHPNob]

I only started learning PHP last December and I did not write any project with mysqli so I was as knowledge wise Thank you so much I understand now!