Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
UPDATE and INSERT statements with Doctrine DBAL and MySQL JSON type
<?php
class JsonObject
{
/** @var string $result */
private $result;
private function __construct(string $result)
{
$this->result = $result;
}
/**
* @param array $array
* @return JsonObject
* @see https://elephantdolphin.blogspot.com/2016/02/mysqls-json-functions-verses-phps-json.html
*/
public static function fromArray(array $array): JsonObject
{
$result = "";
foreach ($array as $key => $value) {
$result .= "'$key', '$value', ";
}
return new self(substr($result,0,(strlen($result) - 2)));
}
/**
* @return string
*/
public function getResult(): string
{
return $this->result;
}
}
<?php
// Initial INSERT
$data = ['foo' => json_encode($foo->toArray())];
$dbal->connection->insert('foos', $data);
// Convert array to JSON Object
$newData = $newFoo->toArray());
$object = JsonObject::fromArray($newData);
// UPDATE
$stmt = $dbal->connection->prepare(sprintf(
"UPDATE `foos` SET
foo = JSON_ARRAY_APPEND(foo, '$', JSON_OBJECT(%s))
WHERE foo_id = :fooId", $object
));
$stmt->bindValue('fooId', $data['fooId']);
$stmt->execute();
@webdevilopers

This comment has been minimized.

Copy link
Owner Author

commented Apr 15, 2019

The original method was taken from here by @mysqlmentor:

The method works fine as long as I am using strings only.
For more complex use cases I guess I will add a toJsonObject custom method on my read/view models.

@webdevilopers

This comment has been minimized.

Copy link
Owner Author

commented Apr 15, 2019

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.