How to bind varying number of inputs when some are blob and must be sent send_long_data() in PHP

Question

I am working on a school assignment and I need to process a form with values such as location, price, description, and a 1 to 4 images up to 5MB each. I need to upload to a database, but I cannot get the images sent using send_long_data(). I do not know how to process only some of the inputs as send long data. I have been able to cobble together code for binding an array of inputs by reference using call_user_func_array(), but for over a day now I have had no luck getting it to work. New to coding and I am stumped. Any help is appreciated.

**<?php



//show logged in/logout header
include 'header.php';


include 'connectLankasListDB.php';

$image_count = 0;


if(!isset($_SESSION['username'])) {

    echo ('Please login before posting.');
    sleep(2);
    header('Location: login.html');




} elseif (empty($_POST['title']) or 
        empty($_POST['price']) or
        empty($_POST['description']) or
        empty($_POST['email']) or
        empty($_POST['confirm_email'])) {

    //All fields not filled out
    echo 'Please fill out title, price, description, email, and email-confirmation fields.';
    echo '<br/>';
    echo '<a href="new_post.html">New Post</a>';


//email not a valid email, prompt to enter correct email    
} elseif (filter_var($_POST['email'], FILTER_VALIDATE_EMAIL) === false) {
    die("Enter a valid email address.");


//terms agreement checkbox not checked
} elseif ($_POST['terms'] === '0') {
    die("Please agree to terms and conditions");


//email and confirmation email match, continue with script   
} elseif ($_POST['email'] !== $_POST['confirm_email']) {
    echo 'Email and confirmation email do not match--try again!';


//Check that image files are correct type and within the size limit   
} elseif (isset($_FILES['images1'])) {
    //print_r($_FILES);
    $image_count = count(array_filter($_FILES['images1']['tmp_name'])); //number of uploaded images
    $allowed_extensions = array("jpg", "jpeg", "png", "gif", "bmp");
    for ($x=0; $x < $image_count; $x++) {

        $file_name = $_FILES['images1']['name'][$x];
        $file_size = $_FILES['images1']['size'][$x];
        $file_tmp = $_FILES['images1']['tmp_name'];


        //$ext = substr($file_name, strlen($file_name)-4,strlen($file_name));
        $ext = explode(".", $file_name);
        $file_ext = strtolower(end($ext));
        echo $file_ext;


        if (!in_array($file_ext, $allowed_extensions)) {
            die("Only jpg, jpeg, png, gif, and bmp files allowed!");

        } elseif ($file_size > 5000000) {
            die("File size limit (5MB) exceed!");
        }



    }
}

    //user has filled in all required fields
    //validate and sanitize, store to variables
    $sub_category = filter_input(INPUT_POST, 'sub_category', FILTER_SANITIZE_STRING);
    $location = filter_input(INPUT_POST, 'location', FILTER_SANITIZE_STRING);
    $title = filter_input(INPUT_POST, 'title', FILTER_SANITIZE_STRING);
    $price = filter_input(INPUT_POST, 'price', FILTER_SANITIZE_NUMBER_FLOAT);
    $description = filter_input(INPUT_POST, 'description', FILTER_SANITIZE_STRING);
    $email = filter_input(INPUT_POST, 'email', FILTER_VALIDATE_EMAIL);
    $timestamp = date("Y-m-d H:i:s");
    $terms = '1';


    //retrieve Location_ID from Location table for Posts table
    $sql1 = "SELECT Location_ID FROM Location WHERE LocationName = '$location'";
    $query1 = mysqli_query($conn, $sql1) or die(mysqli_error($conn));

    $result1 = mysqli_fetch_assoc($query1);
    $location_id = $result1["Location_ID"];

    //retrieve Subcategory_ID from SubCategory table for Posts table
    $sql2 = "SELECT SubCategory_ID FROM SubCategory WHERE SubCategoryName = '$sub_category'";
    $query2 = mysqli_query($conn, $sql2);

    $result2 = mysqli_fetch_assoc($query2);
    $subcategory_id = $result2["SubCategory_ID"];

    /*   
    //save to Posts table
    mysqli_query($conn, "INSERT INTO Posts
                (Location_ID, title, price, description, email, SubCategory_ID, TimeStamp, Agreement)
                VALUES
                ('" . $location_id . "', '" . $title . "', '" . $price . "', '". $description . "', '" . $email ."',"
                . "'" . $subcategory_id ."', '" . $timestamp ."', '" . $terms . "')")
                OR die(mysqli_error($conn));*/

    **//query for insert with no images
    $ins_query_fields = "Location_ID, Title, Price, Description, Email, SubCategory_ID,TimeStamp, Agreement";
    $ins_query_vals = "?,?,?,?,?,?,?,?";
    $type_args = "ssssssss";
    $bind_vars = array($location_id, $title, $price, $description, $email, $subcategory_id, $timestamp, $terms);


    $tmp_name_array = array();  
    $pic_name_array = array();

    //print_r($_FILES['images1']['tmp_name']);
    //prepare query based on number of images
    if ($image_count > 0) {
        $i = 1;

        for($n = 0; $n < $image_count; $n++) {
            $ins_query_fields .= ", Image_" . $i;
            array_push($pic_name_array, "Image_". $i);
            $ins_query_vals .= ",?";
            $type_args .= "s";
            ${"Image_". $i} = $_FILES['images1']['tmp_name'][$n];
            array_push($tmp_name_array, ${"Image_". $i}); 
            $i++;
        }

        $bind_vars = array_merge($bind_vars, $tmp_name_array);
    }    

    **//save image files to Posts table
    ///////////////////////////////////////
    $stmt = $conn->prepare("INSERT INTO Posts($ins_query_fields) VALUES($ins_query_vals)");

    //    
    //bind params by reference
    $inputArray[] = &$type_args;
    $j = count($bind_vars);
    for($i=0; $i < $j; $i++) {
        $inputArray[] = &$bind_vars[$i];
    }

    //print_r($inputArray);

    //use call_user_func_array
    call_user_func_array(array($stmt, 'bind_param'), $inputArray);

    //$stmt->execute();
    //print_r($bind_vars);
    print_r($tmp_name_array);
    if ($image_count > 0) {
        $index = count($bind_vars) - $image_count - 1;
        for($i = 0; $i < $image_count; $i++) {
            $contents = $tmp_name_array[$i];
            //$fp = fopen($bind_vars[($index) + $i], "r");
            $fp = fopen($contents, "r");
            $size = 0;

            while ($data = fread($fp, 1024)) {

                $size += strlen($data);
                $stmt->send_long_data($index, $data);

            }

        }
    }


    if ($stmt->execute()) {

    } else {
        die($conn->error);
    }****



    echo 'Your post has been saved.<br/>';
    echo '<br/>';        
    echo '<a href="index.php">Go to Main Page</a>';



?>**

Ok, I tried to separate the data upload, which is a fixed number of variables, and the image blob uploads, which i have tried doing with a loop. The first data posts, the images do not. Is this even workable this way? There must be some fundamental principle about this that I am not understanding. Here is the revised code, omitting the validation steps.

``if ($image_count > 0) {
    $i = 1;
    $pic_query_holder_x = "";
    $tmp_name_array = array();  
    $pic_in_fields = array();
    $pic_type_args = "";
    for($n = 0; $n < $image_count; $n++) {
        //$ins_query_fields .= ", Image_" . $i;
        array_push($pic_in_fields, "Image_". $i);
        $pic_query_holder_x .= ",?";
        $pic_type_args .= "s";
        ${"Image_". $i} = $_FILES['images1']['tmp_name'][$n];
        array_push($tmp_name_array, ${"Image_". $i}); 
        $i++;
    }
    $pic_query_holder = ltrim($pic_query_holder_x, ',');
    $pic_bind_vars = $tmp_name_array;
    echo '<br/>';
    echo $pic_query_holder;
    echo '<br/>';
    print_r($tmp_name_array);
}    

//save image files to Posts table
///////////////////////////////////////
$stmt = $conn->prepare("INSERT INTO Posts($ins_query_fields) 
VALUES($ins_query_vals)");

//    
//bind params by reference
$inputArray[] = &$type_args;
$j = count($bind_vars);
for($i=0; $i < $j; $i++) {
    $inputArray[] = &$bind_vars[$i];
}

//use call_user_func_array
call_user_func_array(array($stmt, 'bind_param'), $inputArray);
$stmt->execute();

//$index = count($bind_vars) - $image_count -1;
//$fp = fopen($tmp_name_array[$index], "r");


//$stmt->execute();
//print_r($pic_in_fields);
//print_r($pic_query_holder);
if ($image_count > 0) {


    //bind params
    $in_array[] = &$pic_type_args;
    $k = count($tmp_name_array);
    for ($i=0; $i < $k; $i++) {
        $in_array[] = &$tmp_name_array[$i];
    }

    //$index = count($tmp_name_array) - $image_count - 1;
    for($i = 0; $i < $image_count; $i++) {
        //prepare statement
        $go_pics = $conn->prepare("INSERT INTO Posts($pic_in_fields[$i]) VALUES(?)");
        $contents = $tmp_name_array[$i];
        //$fp = fopen($bind_vars[($index) + $i], "r");
        $fs = fopen($contents, "r");
        $size = 0;

        while ($data = fread($fs, 1024)) {

            $size += strlen($data);
            $go_pics->send_long_data($i, $data);

        }
    //print_r($in_array);
    $go_pics->execute();   
    }

}
enter code here

Answer 1

<?php
$stmt = $mysqli->prepare("INSERT INTO messages (message) VALUES (?)");
$null = NULL;
$stmt->bind_param("b", $null);
$fp = fopen("messages.txt", "r");
while (!feof($fp)) {
 $stmt->send_long_data(0, fread($fp, 8192));
}
fclose($fp);
$stmt->execute();
?>

This is straight from the PHP manual. Feof() is just there to ensure that the file exists and was opened properly. With a proper loop, you can access the image file and insert the blob into the database. Alternatively, you can prepare a series of blobs and do your database update or insert separately. Either way, I think this code should get you in the right direction.

Just run this segment to insert or update with blob data after you handle the non blobs.

Also, this SO page may help you too. Insert Blobs in MySql databases with php

Answer 2

You can try tinker with this working template below. This uses send_long_data() to upload images to database. This adds a record with columns of different datatypes in just one query.

Didn't use call_user_func_array() as I don't think it's needed.

<?php
    $conn = new mysqli("127.0.0.1", "root", "", "db");

    if(isset($_POST['submit'])) {
        $null1 = NULL;
        $null2 = NULL;
        $null3 = NULL;
        $null4 = NULL;
        $title = isset($_POST['title']) ? $_POST['title'] : '';
        $email = isset($_POST['email']) ? $_POST['email'] : '';

        $stmt = $conn->prepare("INSERT INTO Posts (Image_1, Image_2, Image_3, Image_4, title, email ) VALUES (?,?,?,?,?,?);");
        $stmt->bind_param("bbbbss", $null1, $null2, $null3, $null4, $title, $email );

        for($i = 0; $i < count( $_FILES['images1']['tmp_name'] ); $i++) {
            if(!empty($_FILES['images1']['tmp_name'][$i])) {
                $target_file = $_FILES['images1']['tmp_name'][$i];

                $fp = fopen($target_file, "r");
                while (!feof($fp)) {
                    $stmt->send_long_data($i, fread($fp, 8192));
                }
                fclose($fp);
                unlink($_FILES['images1']['tmp_name'][$i]);
                echo "Uploading image blob success!<br/>\n";
            }
        }

        $stmt->execute();
        $stmt->close();
        $conn->close();
    }
?>

<form action="" method="post" enctype="multipart/form-data">
 <input type="text" name="title" value="this is the title"/><br/>
 <input type="text" name="email" value="john@yahoo.com"/><br/>
 <input type="file" name="images1[]" value=""/><br/>
 <input type="file" name="images1[]" value=""/><br/>
 <input type="file" name="images1[]" value=""/><br/>
 <input type="file" name="images1[]" value=""/><br/><br/>
 <input type="submit" name="submit" value="Upload Data"/>
</form>