JSON array containing an image stored as a blob

Question

I have created a json array from a mySQL table with this code:

$sql = "SELECT * FROM new";

if ($result = mysqli_query($con, $sql))
{
$resultArray = array();
$tempArray = array();

while($row = $result->fetch_object())
{
$tempArray = $row;
array_push($resultArray, $tempArray);
}

echo json_encode($resultArray);
}
function_exists('json_encode');

This works with text inputs only and when I try to add in an image (stored as a BLOB) the JSON output disappears completely. I am well aware storing images in a database is not a great practice but I need to have it this way. Could anyone help me with this? How can I have the images as part of the JSON array?

Storing images in the database is a bad idea, as you're apparently aware. Storing images in the database *as JSON* is way worse. Are you sure you need to have it this way? This is going to blow up in the worst possible way some time in the future. — tadman, Jul 27 '15 at 16:31
This question has been answered here: [BOB/JSON/PHP][1] [1]: http://stackoverflow.com/questions/4855447/how-to-use-blob-with-json-and-php Storing images in a database is a bad practice and has penalties for the database query speed. You should save them with an obfuscated link. This also saves PHP rebuilding the image. — Ventus, Jul 27 '15 at 16:32
Currently I am uploading images as blobs from an iOS app. Do you know how I can upload images from the app into a mySQL database so that they can be outputted in a JSON array? — coderdojo, Jul 27 '15 at 16:39
When you upload, just write the image to the filesystem, and store it's path in the DB. Then when you need that image data you can read the image using `file_get_contents()` and output the data along with others using `json_encode();`. — Twisty, Jul 27 '15 at 18:15
Thank you @Twisty Do you know where I can find an example of this? — coderdojo, Jul 27 '15 at 23:11
$image1 = basename($_FILES['image']['tmp_name']); $tmp_img = $_FILES['image']['tmp_name']; $query = mysql_query("INSERT INTO Locations (name, image) VALUES ('$name','$tmp_img')"); Something like this? @Twisty — coderdojo, Jul 27 '15 at 23:45

Twisty · Accepted Answer · 2015-07-28T16:06:32.613

Per the comments, this will be a 2 part answer.

1) When the file is uploaded, it should be stored to a specific path. The path and file name should then be stored in the database. The following example is not tested, and should be adjusted to fit your environment:

<?php
// PHP File Upload Handler
// Store File to specific path and Insert Path and details to DB

$uploaddir = '/var/www/uploads/';
$uploadfilepath = $uploaddir . basename($_FILES['userfile']['name']);
$uploadfilename = basename($_FILES['userfile']['name'])
$uploadfiletype = $_FILES['userfile']['type'];
$uploadfilesize = $_FILES['userfile']['size'];

if (move_uploaded_file($_FILES['userfile']['tmp_name'], $uploadfilepath)) {
    $mysqli = new mysqli("localhost", "my_user", "my_password", "world");
    $stmt = $mysqli->prepare(""INSERT INTO mediaTable (name, path, type, size) VALUES (?, ?, ?, ?)");
    $stmt->bind_param("sssi", $uploadfilename, $uploadfilepath, $uploadfiletype, $uploadfilesize);
    $stmt->execute();
    $stmt->close();
    $mysqli->close();
    $results = array(
        "status" => "success",
        "name" => $uploadfilename,
        "type" => $uploadfiletype,
        "size" => $uploadfilesize,
        "path" => $uploadfilepath
    );
} else {
    $results = array(
        "status" => "error",
        "Could not save uploaded file."
    );
}
?>

2) When we want to retrieve the file, and return it via JSON, it would look like this (again, untested):

<?php
// Get Media file (POST input) from database and return data via JSON

if(isset($_POST['fn'])){
    $filename = $_POST['fn'];
    $results = array();
    $mysqli = new mysqli("localhost", "my_user", "my_password", "world");
    $stmt = $mysqli->prepare("SELECT name, path, type, size FROM mediaTable WHERE name=?");
    $stmt->bind_param("s", $filename);
    $stmt->execute();
    $stmt->bind_result($fname, $fpath, $ftype, fsize);
    while ($stmt->fetch()) {
         $results[] = array("name" => $fname, "path" => $fpath, "type" => $ftype, "size" => $fsize, "base64" => '');
    }
    $stmt->close();
    $mysqli->close();

    foreach($results as $file){
        $file['base64'] = file_get_contents($file['path']);
    }
} else {
    $results["error"] = "No file submitted";
}

if(isset($results)){
    echo json_encode($results);
}
?>

In the mysql database, should the filepath show up like this? uploads/31852_429263167315_4338820_n.jpg Or should it be a full URL? — coderdojo, Jul 28 '15 at 14:39
I would leave it as a relative path like that. Whatever you find is easier to use when you need to call the image path later. The advantage of using the full FS path, is when you go to read the file, your script can be any place on your server and it will find the file. If you leave it as relative, you will have to make sure the script is in the right location or the path is amended to reach the right path. — Twisty, Jul 28 '15 at 16:05
Sorry one last question, so the file path is 'uploads/photo.jpg' in the database, should the JSON be returning this also? or should it return the full path? — coderdojo, Jul 29 '15 at 14:59
I guess it depends. IT was my understanding that you wanted to pass back the Base64 JPG content. How do you plan to use it? If you're using it within an `IMG` tag, I would pass back just the relative path. — Twisty, Jul 29 '15 at 15:19
I think I need to have it as the full URL path instead of the relative path. How would I change it so I can return the full path? — coderdojo, Jul 29 '15 at 15:22
In the JSON output I wanted the filepath to be `http://www.website/com/uploads/photo.jpg` This is how I did it `$tempArray->filepath = "http://{$_SERVER['HTTP_HOST']}/{$tempArray->filepath}";` — coderdojo, Jul 29 '15 at 19:25

JSON array containing an image stored as a blob

1 Answers1