Why is this mysqli statement unable to insert image into the database?

Question

I am trying to save image to my database using mysqli in php. I maded a demo and I used a simple mysqli_query() and it worked. However, when I try to do the same using prepared statement; it doesnt work out. Here is my code:

$uploadedImage = array();
        $uploadedImageName = array();

        if(isset($_FILES['files']['tmp_name'])){

            $num_files = count($_FILES['files']['tmp_name']);
            echo $num_files;


            if($num_files == 5){
                for($i = 0; $i < $num_files; $i++){

                    $imgName = addslashes($_FILES['files']['tmp_name'][$i]);
                    $name = addslashes($_FILES['files']['name'][$i]);

                    if($_FILES['files']['tmp_name'][$i] != ""){

                        $imageContents = file_get_contents($imgName);
                        $encodedImage[$i] = base64_encode($imageContents);

                        $filename[$i] = $_FILES['files']['name'][$i];

                        array_push($uploadedImageName, $filename[$i]);
                        array_push($uploadedImage, addslashes($encodedImage[$i]));

                        echo "I got the file..<br>";

                        echo ",,".$filename[$i];
                        echo $encodedImage[$i];
                    }   
                }
            } else{
                echo "Number of files should be equal to 5";
                return;
            }



$sql = "INSERT INTO profile(first_name, middle_name, surname, imgname1, img1, imgname2, img2, imgname3, img3, imgname4, img4, imgname5, img5) VALUES (?, ?, ?,?, ?,?, ?,?, ?,?, ?,?, ?)";

        $stmt = $mysqli->prepare($sql);
        $stmt->bind_param("ssssbsbsbsbsb", $firstName, $middleName, $surname, $uploadedImageName[0], $uploadedImage[0], $uploadedImageName[1], $uploadedImage[1], $uploadedImageName[2], $uploadedImage[2], $uploadedImageName[3], $uploadedImage[3], $uploadedImageName[4], $uploadedImage[4]); // bind variables..    


if($stmt->execute()){
            echo 'success';
        } else{
            eccho 'failure';    
        }

It gives me success however, when I look into my database; I get no image under the image column. I wonder why this happens. May I know the reason why?

Whats is the error message ? Firstname , middle name is geting inserted ? — Dimag Kharab, Dec 14 '15 at 12:28
Have you printed $uploadedImageName just a step above the insert query? Did you get proper response? — Anto S, Dec 14 '15 at 12:29
@n01ze Yes. All the fields are getting inserted except the image which is a blob — Nevermore, Dec 14 '15 at 12:31
I really preffer to not store the images on the db. http://stackoverflow.com/questions/492549/how-can-i-insert-large-files-in-mysql-db-using-php — Gonzalo, Dec 14 '15 at 12:37
I prefer to store images on db cos my users may have same name for their images which thereafter may result in lots of confusion and causing the user some amount of annoyance. Hence.. — Nevermore, Dec 14 '15 at 12:40
Not realy understanding this [answer](https://stackoverflow.com/a/27516913/4262684) but it is about `addslashes` working in usual statement, but not in prepared statement. — cwps, Dec 14 '15 at 13:50
@cwps Even if I remove addslashes(); it doesnt work. Inserting image with simple statement works with blob however, it doesnt work with prepared statement.. heres a snippet: $stmt = $con->prepare($sql); $stmt->bind_param("sbsbsbsbsb", $uploadedImageName[0], $uploadedImage[0], $uploadedImageName[1], $uploadedImage[1],$uploadedImageName[2], $uploadedImage[2],$uploadedImageName[3], $uploadedImage[3],$uploadedImageName[4], $uploadedImage[4]); if($stmt->execute()){ echo 'Success'; } else{ echo 'Failure'; } This doesnt work.. bt the simple statement works — Nevermore, Dec 15 '15 at 03:45

score 0 · Answer 1 · answered Dec 14 '15 at 12:34

0

can you try like this, hope it will work

 $stmt = $conn->prepare("INSERT INTO your_table( imgname1) VALUES ( ? )");  //imgname1 is a blob column
 $stmt->bindParam(1, $uploadedImageName[0], PDO::PARAM_LOB);
 $conn->errorInfo();
 $stmt->execute();

answered Dec 14 '15 at 12:34

Dimag Kharab

4,439
1
24
45

ok. just a sec. I have lots of fields man. I dont think this will help. However, I will try. – Nevermore Dec 14 '15 at 12:39
Fatal error: Cannot pass parameter 3 by reference in your statement – Nevermore Dec 14 '15 at 12:43
It should work like this.. I dont understand why??! .. Its driving me crazy now. It is working with usual statement bt not with prepared statement and if I use statement over here; the code is breaking. – Nevermore Dec 14 '15 at 13:04
Even bindColumn doesnt work. Its just not working for the image. I am getting the imageName in the database. – Nevermore Dec 14 '15 at 13:20

score 0 · Answer 2 · answered Dec 16 '15 at 06:14

0

You have the following options:

You could try checking the max_allowed_packet size in mysql. It's a possibility that the files you are trying to upload are bigger than the allowed size.
You can try using mysqli_stmt::send_long_data. It is useful for saving blobs to the database.
Finally, you might want to use mysql_stmt::$error to see any error messages that could clarify why your blobs aren't being inserted.

answered Dec 16 '15 at 06:14

Nicholas F

141
4

They were just fine. It was not the reason why it wasnt working, – Nevermore Dec 17 '15 at 05:11

score 0 · Accepted Answer · answered Dec 17 '15 at 05:12

0

Using prepared statement, blob was not getting inserted. However, later I tried using simple statement. It worked.

answered Dec 17 '15 at 05:12

Nevermore

882
2
18
44

Why is this mysqli statement unable to insert image into the database?

3 Answers3