php with mysql

Continuing from the last entry on using MySQL's new JSON data type , let us take a look at the keys in our sample database. Use JSON_KEYS to get a lit of the Keys withing the document. mysql> SELECT JSON_KEYS(info) FROM stooge; +-----------------+ | json_keys(info) | +-----------------+ | ["job", "name"] | | ["job", "name"] | | ["job", "name"] | | ["job", "name"] | +-----------------+ 4 rows in set (0.00 sec) So how many records have a 'job' key? mysql> select JSON_CONTAINS_PATH(info,'all','$.job') from stooge; +----------------------------------------+ | JSON_CONTAINS_PATH(info,'all','$.job') | +----------------------------------------+ | 1 | | 1 | | 1 | | 1 | +----------------------------------------+ 4 row

So lets get some data from a JSON column from a table in a MySQL database and update it. JSON data is is simply a column like a REAL, INTEGER, CHAR, or VARCAHR. So fetching the data from a JSON column is the same as any other column. The same database we have has two columns -- a integer for a identification number and a second for JSON data. Fetch Data #!/usr/bin/php <?php $mysqli = new mysqli("localhost", "root", "hidave", "test"); /* check connection */ if (mysqli_connect_errno()) { printf("Connect failed: %s\n", mysqli_connect_error()); exit(); } $query1 = "SELECT * FROM stooge"; $result = $mysqli->query($query1); while($row = $result->fetch_assoc()) { printf("%d - %s\n", $row["id"], $row["info"]); } $mysqli->close(); ?> When the program executes we see the following: 1 - {"name": "Moe Howard"} 2 - {"name": "Larry Fi

MySQL 5.7's new JSON data type has three functions to help you make sure your data is a valid utf8mb4 character set JSON document. They are JSON_ARRAY , JSON_QUOTE , and JSON_OBJECT . ( You can also obtain JSON values by casting values of other types to the JSON type using CAST(value AS JSON) ) What is the big difference? And when would you use one over another? JSON_ARRAY takes a string as input and returns a JSON array with the values from the string. mysql> select JSON_ARRAY('') +----------------+ | JSON_ARRAY('') | +----------------+ | [""] | +----------------+ 1 row in set (0.01 sec) mysql> SELECT JSON_ARRAY('Foo', 42, now()); +-------------------------------------------+ | JSON_ARRAY('Foo', 42, now()) | +-------------------------------------------+ | ["Foo", 42, "2016-02-15 07:31:56.000000"] | +-------------------------------------------+ JSON_QUOTE takes the string given as in

Lost post covered the use of the MySQL JSON functions to feed data into the database server and why the PHP json_encode did not provide what was needed. THis time we will look how to examine the data once it is in a JSON column. We started with a simple associative array and fed it into the database. mysql> SELECT * FROM foobar; +--------------------------+ | mydata | +--------------------------+ | {"a": 1, "b": 2, "c": 3} | +--------------------------+ 1 row in set (0.00 sec) MySQL 5.7 has a group of JSON functions for looking at attributes. See the MySQL Manual 12.16.5 Functions That Return JSON Value Attributes . And they do not always work in an intuitive fashion! JSON_DEPTH returns the maximum depth of a JSON document. It will return a NULL if passed a NULL and return an error if the document is not valid JSON. An empty array, object or scalar value has a depth of 1. Non empty arrays or objects with elements or members o

The MySQL JSON data type only accepts valid JSON documents. PHP has a handful of JSON functions but sadly json_encode does not provide what the database server wants. Lets start with a simple array. $alpha = array('a' => 1, 'b' => 2, 'c' => 3); This is a very simple associative array that we want to turn into a JSON doc with there elements, where 'a' is equal to 1 etcetera. This would look like this: {"a": 1, "b": 2, "c": 3} Using the built in PHP function json_encode we get this: {"a":1,"b":2,"c":3} Fantastic. We should be able to feed that into query, send it to the server, and be good to go. But when we try to feed that into MySQL it will return a syntax error. MySQL can be fussy about sending data in quotes, especially unescaped, in a query. So the associate array has to be serialized (fancy way to saw written out to a string) in a format MySQL can accept. That format means

MySQL 5.7 features a native JSON datatype. So just like a INT, CHAR, or other data type, you can now store valid JSON documents in a MySQL database. Previously you could put JSON formatted data into a CHAR, a TEXT, or similar data type. But it was a real pain to search this JSON data for items contained in them. But now you can store the JSON and there is a list functions to allow full access to this data in the column. shell$ mysql -u root -p test Enter password: Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 15 Server version: 5.7.9 MySQL Community Server (GPL) Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for h