Empty PHP output from MySQL database for a longblob

Question

I have an Android application that takes a photo, converts the bitmap to Base64 and submits the Base64 string to a MySQL database (via PHP) to be stored as a longblob. This part works great! In fact, I can download the longblob from phpMyAdmin as a perfect Base64 string and easily convert to a JPEG photo.

The problem is my PHP code for getting the blob returns an empty string:

{
    "owner":"Unknown",
    "pet_name":"Unknown",
    "last_seen":"2019-04-09 11:17:19",
    "contact":"999-888-7654",
    "description":"rubber ducky, lotsa fun",
    ***"photo":""***,
    "location":"Some location"
}

The PHP getter:

function getReports() {
    $stmt = $this->con->prepare("SELECT owner, pet_name, last_seen, contact, description, photo, location FROM Pets");
    $stmt->execute();
    $stmt->bind_result($owner, $pet_name, $last_seen, $contact, $description, $photo, $location);

    $reports = array();

    while($stmt->fetch()) {
        $report  = array();
        $report['owner'] = $owner;
        $report['pet_name'] = $pet_name;
        $report['last_seen'] = $last_seen;
        $report['contact'] = $contact;
        $report['description'] = $description;
        $report['photo'] = $photo;
        $report['location'] = $location;

        array_push($reports, $report);
    }

    return $reports;
}

An interesting side note, if instead of the above code I use the below code I DO get the complete Base64 string, but with added escape () and newline (\n) characters all throughout:

//Select everything from table
$sql= "SELECT * FROM Pets";

//Confirm results
if($result = mysqli_query($con, $sql)) {
    //Results? Create array for results and array for data
    $resultArray = array();
    $tempArray = array();

    //Loop through results
    while($row=$result->fetch_object()) {
        // Add each result in results array
        $tempArray=$row;
        array_push($resultArray,$tempArray);
    }

    //Encode array to JSON and output results
    echo json_encode($resultArray);
}

I would like to find a way to fix the above PHP code. I'm thinking perhaps my string is too long for the $photo value? Any advice would be extremely appreciated.

Update: from Selecting Blob from MYSQL, getting null I did manage to the Base64 to output now instead of an empty string. however, I still have the problem of the escape and newline characters.

Any help here?

Note: The object-oriented interface to mysqli is significantly less verbose, making code easier to read and audit, and is not easily confused with the obsolete mysql_query interface where missing a single i can cause trouble. Example: $db = new mysqli(…) and $db->prepare("…") The procedural interface is largely an artifact from the PHP 4 era when mysqli API was introduced and should not be used in new code. — tadman
– tadman, Commented Apr 11, 2019 at 1:22
Updated question. I did figure out how to get the base64 to output instead of the empty "". But, it still contains the escape and newline chars. — JohnD
– JohnD, Commented Apr 11, 2019 at 1:54
Putting images in a database is usually a bad plan to start with, it requires many layers of encoding and decoding to get it out of the database and send it over the wire, and base64 encoding only makes things worse. Why not raw? — tadman
– tadman, Commented Apr 11, 2019 at 2:07
it still contains the escape and newline chars - most likely it's because you put it in that form into the database in first place. — Marcin Orlowski
– Marcin Orlowski, Commented Apr 11, 2019 at 2:11
Base64 isn't for storage. You might want to read Storing image in database directly or as base64 data? — Marcus Adams
– Marcus Adams, Commented Apr 11, 2019 at 16:50

JohnD · Accepted Answer · 2019-04-17 04:43:41Z

I managed to get the original function to output the Base64 line by casting as such:

    $stmt = $this->con->prepare("SELECT owner, pet_name, last_seen, contact, description, CAST(photo as CHAR(1000000) CHARACTER SET utf8) as photo, location FROM Pets");

While this allowed for the Base64 string to be received it still included the unwanted characters. The unwanted characters were caused by JSON_ENCODE. Below is what I used to fix it.

Basically, 1. remove the added chars and 2. tell JSON_ENCODE to not print the escape chars with JSON_UNESCAPED_SLASHES.

For the function getReports()

function getReports() {
    $stmt = $this->con->prepare("SELECT owner, pet_name, last_seen, contact, description, CAST(photo as CHAR(1000000) CHARACTER SET utf8) as photo, location FROM Pets");
    $stmt->execute();
    $stmt->bind_result($owner, $pet_name, $last_seen, $contact, $description, $photo, $location);

    $reports = array();

    while($stmt->fetch()) {
        $report  = array();
        $report['owner'] = $owner;
        $report['pet_name'] = $pet_name;
        $report['last_seen'] = $last_seen;
        $report['contact'] = $contact;
        $report['description'] = $description;
        $photo = str_replace("\n","",$photo);
        $photo = str_replace("\\/","/", $photo);
        $photo = stripcslashes($photo);
        $report['photo'] = $photo;
        $report['location'] = $location;

        array_push($reports, $report);
    }

    return $reports;
}

And in the Api script, changed the return from

echo json_encode($resultArray);

to

echo json_encode($resultArray, JSON_UNESCAPED_SLASHES);

Now everything works fantastic. Is this best practice? I am not sure. I'm certain storing base64 is probably not...

Collectives™ on Stack Overflow

Empty PHP output from MySQL database for a longblob

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related