3

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?

Improve this question
6
  • 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. Commented Jul 27, 2015 at 16:31
  • This question has been answered here: [BOB/JSON/PHP][1] [1]: stackoverflow.com/questions/4855447/… 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. Commented Jul 27, 2015 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? Commented Jul 27, 2015 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();. Commented Jul 27, 2015 at 18:15
  • Thank you @Twisty Do you know where I can find an example of this? Commented Jul 27, 2015 at 23:11

1 Answer 1

Reset to default
0

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);
}
?>
Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

In the mysql database, should the filepath show up like this? uploads/31852_429263167315_4338820_n.jpg Or should it be a full URL?
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.
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?
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.
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?
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.