John Davidson

Message:

I have PHP 8 and MySQL 8 running using the xdevapi. Everything is working well with it, but I am running into an issue storing data in a document in a collection.

I have the following methods in a class:

use YYY\DatabaseDS;



final class Draft

{

    private mysql_xdevapi\Collection $collection;   



    /**

     * @param DatabaseDS $pdo The database connection.

     */

    public function __construct(

        public DatabaseDS $pdo,

    ) {

        $this->collection = $this->pdo->getCollection('drafts');

    }



    /**

     * @param string $draftID The primary key for the draft.

     * @param array  $data    The SOW data in object format

     * @return void

     */

    private function updateDraftData(string $draftID, array $data): void

    {

        $doc = json_encode($data);

        $this->collection->replaceOne($draftID, $doc);

    }



    /**

     * @param string $hash The identifying hash for the draft.

     * @return array

     */

    private function getDraftStructure(string $hash): array

    {

        $results = $this->collection->find('hash = :hash')

            ->bind(['hash' => $hash])

            ->execute();



        return $results->fetchOne();

    }

}

I also have a document similar to this one:

{"name": "Alfred", "age": 42, "job": "Butler/Maid"}

When saving the document, the value for job is being escaped so it looks like Butler\/Maid. The problem comes when retrieving the value. I would expect the value I retrieve to be identical to what I saved to the document, but the escaped value is still there. If I save it again, it saves as Butler\\\/Maid escaping everything another time.

Is there a way to retrieve the value without having to manually change it back? Or am I going to have to look at going back to using multiple database systems again?

---

My Answer:
It seems like the issue is with how the data is being encoded and decoded when storing and retrieving it from the MySQL 8 document store.

One way to solve this issue is to use the `json_decode` function in PHP to decode the JSON data retrieved from the document store. This function will automatically unescape any escaped characters in the JSON string.

You can modify your `getDraftStructure` method to decode the JSON data before returning it, like this:

php
/**
* @param string $hash The identifying hash for the draft.
* @return array
*/
private function getDraftStructure(string $hash): array
{
$results = $this->collection->find('hash = :hash')
->bind(['hash' => $hash])
->execute();

$data = $results->fetchOne();

// Decode the JSON data before returning it
return json_decode($data, true);
}


By using `json_decode($data, true)`, the JSON data retrieved from the document store will be decoded into an associative array in PHP, and any escaped characters will be unescaped automatically.

This should solve the issue of retrieving the data with escaped characters from the MySQL 8 document store.