Picture not being stored inside mysql database properly

Question

A value is being stored in the database but it is not the one i am expecting. I tried many methods before but this one kind of seems to work but the file name is not being stored and when I try to download the file directly from the database, it downloads a .bin file format which looks something like table_Name-column_Name.bin. The file name being stored is BLOB - ## B.

My Form

    <form class="form-horizontal" method="post" action="productsValidate.php" name="myForm" enctype="multipart/form-data">
   <fieldset>
      <legend>Add Product</legend>
      <div class="form-group">
         <label for="Product_Name" class="col-lg-2 control-label">Product Name</label>
         <div class="col-lg-10">
            <input type="text" class="form-control" id="Product_Name" placeholder="Name" required="required" name="Product_Name">
         </div>
      </div>
      <div class="form-group">
         <label for="Size" class="col-lg-2 control-label">Size</label>
         <div class="col-lg-10">
            <input type="text" class="form-control" id="Size" placeholder="Size" required="required" name="Size">
         </div>
      </div>
      <div class="form-group">
         <label for="Color" class="col-lg-2 control-label">Color</label>
         <div class="col-lg-10">
            <input type="text" class="form-control" id="Color" placeholder="Size" required="required" name="Color">
         </div>
      </div>
      <div class="form-group">
         <label for="price" class="col-lg-2 control-label">Price</label>
         <div class="col-lg-10">
            <input type="number" class="form-control" id="price" placeholder="price" required="required" name="price">
         </div>
      </div>
      <div class="form-group">
         <label for="image" class="col-lg-2 control-label">Select Image</label>
         <div class="col-lg-10">
            <input type="file" name="image" id="image">
         </div>
      </div>
      <div class="form-group">
         <label for="categoryId" class="col-lg-2 control-label">Category Id</label>
         <div class="col-lg-10">
            <?php
               //your connection to the db and query would go here
               include "../include/settings.php";
               $conn = new mysqli($host, $user, $pwd, $sql_db);
               if ($conn->connect_error) {
                   die("Connection failed: " . $conn->connect_error);
               }
               $sql = "SELECT distinct Category_Id FROM products";
               $result = mysqli_query($conn, $sql);
               ?>
            <select id="categoryId" name="categoryId">
               <option value = ""></option>
               <?php
                  while($row = mysqli_fetch_array($result)) {
                    echo '<option value='.$row['Category_Id'].'>'.$row['Category_Id'].'</option>';
                  }
                  ?> 
            </select>
         </div>
      </div>
      <div class="form-group">
         <label for="description" class="col-lg-2 control-label">Description</label>
         <div class="col-lg-10">
            <textarea type="text" class="form-control" id="description" placeholder="Description" required="required" name="description" pattern="[\sA-Za-z]+"></textarea>
         </div>
      </div>
      <div class="form-group">
         <div class="col-lg-6 col-lg-offset-2">
            <button type="submit" class="btn btn-primary">Add Product</button>
         </div>
      </div>
   </fieldset>
</form>

My Form validation

    <?php

    $name = $_POST["Product_Name"];
    $size = $_POST["Size"];
    $color = $_POST["Color"];
    $price = $_POST["price"];

    $image = addslashes($_FILES['image']['tmp_name']);
    $image = file_get_contents($image);
    $image = base64_encode($image);
    $image=basename( $_FILES["image"]["tmp_name"],".jpg");

    $category = $_POST['categoryId'];
    $description = $_POST['description'];
    insertProduct($name, $size, $color, $price, $image, $category, $description);

    function insertProduct($name, $size, $color, $price, $image, $category, $description){

        require_once ("../include/settings.php");   // Load MySQL log in credentials
        $conn = @mysqli_connect ($host,$user,$pwd,$sql_db); // Log in and use database
        if ($conn) { // check is database is avialable for use
            $query = "INSERT INTO products
                                    (Product_Id, Product_Name, Size, Color, Price, Picture, Category_Id, Description)
                                VALUES ('', '$name', '$size', '$color', '$price', '$image', '$category', '$description')";

            $result = mysqli_query ($conn, $query);
            if ($result) {                              // check if query was successfully executed
                echo 'Successfully Added';
            } else {
                echo 'Product could not be added';
            }
            mysqli_close ($conn);                   // Close the database connect
        } else {
            echo "<p>Unable to connect to our database for adding the product.</p>";
        }
    }
?>

Answer 1

I guess you're trying to store the actual encoded image in the database, not a pointer to it. It looks to me like your eleven-byte BLOB has the pointer in it instead.

Your code contains this sequence of lines.

$image = addslashes($_FILES['image']['tmp_name']);
$image = file_get_contents($image);
$image = base64_encode($image);
$image=basename( $_FILES["image"]["tmp_name"],".jpg");

The third line puts an encoded, not binary, version of the image into a text string. That's close to what you want, but you probably should not base64-encode it if you're putting in a BLOB.

The fourth line discards the image itself and overwrites it with an image name. I think that's wrong.

If you're going to use BLOB data this way, you also need to use mysqli's facilities to prepare your SQL statements, and then bind your parameters. The bind_param() function gives you a way to declare a parameter to be a blob. That's better than trying to trick php's string processing into accepting it.

All that being said, most people use a file system or content server rather than BLOBs to store and serve images to web clients. BLOB programming is a pain in the neck. Also, using a DBMS to store and retrieve images quickly becomes a performance bottleneck in an application that scales up.