A way to read/write JSON in a MySQL database. jsonhib is available for both Node.js and PHP.
Here's a simple jsonhib object (in Node.js):
// create a jsonhib object
var jh = new jsonhib({
'link_identifier': conn,
'sort_column': 'n', // array index column name
'json_column': 'json' // freeform JSON column name
});
Or, in PHP:
// create a jsonhib object
$jh = new jsonhib(array(
'sort_column'=>'n',
'json_column'=>'json'
));
Here's an example of reading a table:
// assume 'mytable' is a MySQL table with these columns: id, name
jh.readRows('mytable', 'WHERE id > 0', function(s) {
var str = s;
});
// str now contains [{"id": 1, "name": "bob"}, {"id": 2, "name": "fred"}]
jh.readDesc('mytable', function(s) {
var str = s;
});
// str now contains {"id": 0, "name": ""}
In PHP:
// assume 'mytable' is a MySQL table with these columns: id, name
$str = $jh->readRows('mytable', 'WHERE id > 0');
// $str now contains [{"id": 1, "name": "bob"}, {"id": 2, "name": "fred"}]
$str = $jh->readDesc('mytable');
// $str now contains {"id": 0, "name": ""}
Here's an example of writing a table:
// insert a row
jh.insertRow('mytable', '', -1, '{"id": 1, "name": "bob"}', function() {
});
// delete a row
jh.deleteRow('mytable', '', 0, function() {
});
// update a row
jh.updateRow('mytable', 'WHERE id=1', 0, '{"id": 1, "name": "eric"}', function() {
});
// move a row (huh?)
jh.moveRow('mytable', 'WHERE id=1', 0, 1, function() {
});
In PHP:
// insert a row
$jh->insertRow('mytable', '', -1, '{"id": 1, "name": "bob"}');
// delete a row
$jh->deleteRow('mytable', '', 0);
// update a row
$jh->updateRow('mytable', 'WHERE id=1', 0, '{"id": 1, "name": "eric"}');
// move a row (huh?)
$jh->moveRow('mytable', 'WHERE id=1', 0, 1);
If you can add or alter mytable
to have a json_column
(e.g. json
), then any JSON data that doesn't fit into a
MySQL column will be stored in that column. So, jsonhib will store the age
in the json_column
since mytable
does
not have a MySQL age
column:
jh.insertRow('mytable', '', -1, '{"id": 1, "name": "bob", "age": 35}', function() {
});
In PHP:
$jh->insertRow('mytable', '', -1, '{"id": 1, "name": "bob", "age": 35}');
If you can add or alter mytable
to have a sort_column
(e.g. n
), then reading and writing JSON array values won't
be in random order; it will use that column to save indices for your JSON array. jsonhib will quietly use this column
to save values from 0 ... n-1 to keep array indices for your JSON array. With that, readRows()
, insertRow()
, deleteRow()
, updateRow()
and moveRow()
will work exactly as expected.