来自MySQL数据库的PHP输出为longblob

问题描述

我有一个Android应用程序，该应用程序拍摄照片，将位图转换为Base64，然后将Base64字符串提交给MySQL数据库(通过PHP)，以存储为longblob.这部分效果很好！实际上，我可以从phpMyAdmin下载longblob作为完美的Base64字符串，并轻松将其转换为JPEG照片.

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.

问题是我的用于获取Blob的PHP代码返回一个空字符串:

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"
}

PHP获取器:

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;
}

一个有趣的旁注，如果我使用下面的代码代替上面的代码，但我确实获得了完整的Base64字符串，但是在整个过程中都添加了转义()和换行符(\ n):

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);
}

我想找到一种方法来修复上述PHP代码.我在想我的字符串对于 $ photo 值来说太长了吗?任何建议将不胜感激.

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.

更新:从从MYSQL选择Blob，获取null 到Base64以立即输出，而不是空字符串.但是，我仍然遇到转义符和换行符的问题.

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.

这里有帮助吗?

推荐答案

我设法通过转换如下方式获得了原始函数来输出Base64行:

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");

尽管这允许接收Base64字符串，但它仍包含不需要的字符.不需要的字符是由JSON_ENCODE引起的.以下是我用来修复它的内容.

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.

基本上，1.删除添加的字符，并2.告诉JSON_ENCODE不要使用JSON_UNESCAPED_SLASHES打印转义字符.

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

对于功能getReports()

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;
}

然后在Api脚本中，将返回值更改为

And in the Api script, changed the return from

echo json_encode($resultArray);

到

echo json_encode($resultArray, JSON_UNESCAPED_SLASHES);

现在一切正常.这是最佳做法吗?我不知道.我确定存储base64可能不是...

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

这篇关于来自MySQL数据库的PHP输出为longblob的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！