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

@webdevilopers webdevilopers 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

@webdevilopers webdevilopers commented Apr 15, 2019

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment