PHP and MySQL 5.7 JSON

February 11, 2016

I have been presenting on MySQL 5.7 and the new JSON data type to PHP Programmers at several shows and the one big request has been MORE EXAMPLES!!!!!!

Part of my presentation includes the big differences between PHP’s json_encode and the various MySQL supplied functions like JSON_ARRAY and JSON_OBJECT.  In my preliminary test I found the native PHP function did not always encode the information in a way that the MySQL server found palatable. The MySQL server wants properly valid JSON docs and unfortunately we have an impedance mismatch here.

Lets start with a simple associative array 
$alpha = array('a' => 1, 'b' => 2, 'c' => 3);  
and we want a JSON document that looks like 
{"a": 1, "b": 2, "c": 3}
 If we pass this array through json_encode (echo “json_encoded: ” . json_encode($alpha) . “\n”;)  we will end up with json_encoded: {“a”:1,”b”:2,”c”:3} which is not what is needed and the MySQL server will reject it with the ol’ ‘You have an error in your SQL syntax’ message.  Other php functions like serializeprint_r, and var_dump are pretty good with associative arrays but do not come close to providing the needed valid JSON.

THE JSON_HEX_QUOTE argument to json_encode changes the data to “a”, “1”, “b”, “2”, “c”, “3” }which is not any better. And it is a subtle change that seems, to me at least, to change keys and values to all keys, no values.

With no built in function, it is time to roll the array into a string formatted as desired.

$alphaz = "";
foreach ($alpha as $key => $value) {
                 $alphaz .= "'$key', $value, ";
}
$alphaz = substr($alphax,0,(strlen($alphaz) - 2)); 
/* Need to remove last ,\n */

This can be passed to the MySQL JSON_OBJECT function. Since it is valid JSON we can insert it into the database.

INSERT INTO foobar (mydata) VALUES (JSON_OBJECT('a', 1, 'b', 2, 'c', 3))

And in the database is the desired document.


mysql> select * from foobar;
+--------------------------+
| mydata |
+--------------------------+
| {"a": 1, "b": 2, "c": 3} |
+--------------------------+
1 row in set (0.00 sec)

mysql>

I will write more on this subject. Meanwhile feel free to try my little test program.

#!/usr/bin/php
 1, 'b' => 2, 'c' => 3);
echo var_dump($alpha);
echo "json_encoded: " . json_encode($alpha) . "\n";
echo "json_encoded HEX: " . json_encode($alpha,JSON_HEX_QUOT) . "\n";

$alphaz = "";
foreach ($alpha as $key => $value) {
        $alphaz .= "'$key', $value, ";
}
$alphaz = substr($alphaz,0,(strlen($alphaz) - 2)); /* strip last ,\n */
$query1 = "SELECT JSON_OBJECT($alphaz)";
echo "$query1\n";
if ($result = $mysqli->query($query1)) {
    $row = $result->fetch_row();
    printf("JSON is %s!\n", $row[0]);
} else {
        printf("Errormessage: %s\n", $mysqli->error);
        echo "FOO!!!!\n";
}
$query2 = "INSERT INTO foobar (mydata) VALUES (JSON_OBJECT($alphaz))";
echo "$query2\n";
if ($result = $mysqli->query($query2)) {
        echo "Inserted\n";
} else {
        printf("Errormessage: %s\n", $mysqli->error);
}
$query3 = "SELECT * FROM foobar";
if ($result = $mysqli->query($query3)) {
    $row = $result->fetch_row();
    printf("JSON is %s!\n", $row[0]);
} else {
        printf("Errormessage: %s\n", $mysqli->error);
}

$mysqli->close();
?>


Growing the MySQL Community

January 28, 2016

I will the first speaker at the FOSDEM MySQL and Friends Devroom and unlike the other speakers I will not be presenting on new technical developments, better ways to run MySQL, or anything technical. But it may be the most important talk of the day. How can we grow the MySQL Community?

How do we attract new developers and database administrators and then guide them through the steps to a ‘journeyman’ level before showing them the way to becoming seniors in their fields? There is a shortage of talent and this cuts two ways. First hiring managers get frustrated in their searches for talent and then they reduce the scope of what they want out of their database. Second the same hiring manager later will assume there is still no talent for them to hire and alter their demand accordingly. I want to bring in as many people as possible into the MySQL ecosystem because there are jobs out there in an ever increasingly tough economy and because I think MySQL solves so many problems efficiently. Nobody starts as a fully fledged developer or DBA and the start of the career path needs exposition, easing, and encouragement. So how do we grow our numbers?

There is a very similar community to the MySQL Community and that is found in the PHP world. In the last few years they have really pulled together to bring in newbies, tighten relationship between subgroups (efforts like the Framework Interop group), provide mentoring at all personal development levels, establishing active career coaching for experienced developers, and even mental health support. This has all been done by the PHP Community and they are growing, cohesive, and frankly I am envious.

A strong user community brings value to a product. I am seeking your input on how can get we more of ‘us’? What can we do to improve certification to make it a truce touchstone of a job candidates value? How can we best leverage groups like the International Oracle User Group’s MySQL Council and other sources of customer feedback to guide the development of the MySQL Products. What information are you seeking that you are not getting and what is the best way to get it to you? Are their better ways to train novices in subjects like data normalization, replication, account management, security or what have you that we have missed. What can we do to help you network with you peers? Do need a mentor or want to mentor (and yes you can be both at the same time).

So how do we grow the MySQL Community and help grow MySQL?


The Most Popular MySQL 5.7 Features

January 27, 2016

Three events into the new year and I have been getting a lot of feedback from you all on MySQL 5.7. So far there are some changes in 5.7 that are very popular.

The change in username name length from sixteen to thirty two characters seems to be universally loved. Yea!!!

The new JSON data type has caught a lot of attention. A few of you think that data should be normalized as Codd intended and find that simple shoving a document into a column is an no-no. But the vast majority love this new feature. Many ISPs are going to have to upgrade to get to 5.7 to meet demand for this new data type.

Generated columns seem to be a hot spot for several of you. Usually you do not see a lot of business logic in MySQL databases compared to, say, an Oracle database. And if there is some sort of business logic then it is implemented with a trigger. I have had some intense discussions with some folks who are going to be doing very interesting things with generated columns.

The least popular? Not being able to run 5.7 in a half gig, low cost rented environment. (How did I ever get by with 384k words in a Dec-10 or 640k with DOS??) The secure by default is causing come problems with folks who have spent decades without a forced root password and automatic expiration of password is being tackled by MySQL Engineering as it is currently seen as too drastic by some customers.

So thank you folks who came out to SkiPHP, San Diego PHP, and SCaLE in the past few weeks. Please let me know you feelings on MySQL 5.7 at FOSDEM, SunshinePHP, and RMOUG training days!


San Diegans — learn about MySQL’s New JSON Data Type Tonight

January 19, 2016

I will be speaking at the San Diego PHP Meetup tonight January 19th at 7pm. The location is Business.com Media, 1900 Wright Place in Carlsbad (33.122360, -117.28909) and the topic will be MySQL’s JSON Data Type. I hope to see you there!


See you in Salt Lake City, San Diego, Pasadena, Brussels or Frisco!

January 8, 2016

The last half of January is going to be busy but here is where to catch me and some MySQL SWAG.

First I will be speaking at SkiPHP on the 14th and 15th. Then is it off to San Diego meet up with the San Diego PHP Meetup on the 19th. Next is the Southern California Linux Expo where MySQL is sponsoring a track from the 21st to the 24th. That is followed by a quick trip to the MySQL and Friend Room at FOSDEM to end the month. Finally back to DFW where there will be a meeting of the North Texas MySQL Users Group on the 15th of February.


Annual Holiday Shopping List

December 14, 2015

Database folks are a pain in the rear to shop for at holiday time. So print out a copy of this blog post, circle the items that you really like, and give this to someone who does the shopping for presents.

It is once again time for the MySQL holiday present list for that database person in your life. These quirky folks have unique tastes in presents and this list reflects this in abundance.

First item: Hello Kitty Screen Cleaner.

Hello Kitty Screen Cleaner available at many retailers near you and the perfect gift for those who spend long hours at their monitors.

Hello Kitty Screen Cleaner available at many retailers near you and the perfect gift for those who spend long hours at their monitors.

Everyone loves a clean screen and Hello Kitty Screen Cleaner is the second best thing to have a real cat complete with rough tongue to scrub your screen. Does Hello Kitty Screen Cleaner work better than say Thor Screen Cleaner? Doubtful but the bright pink and friendly face help remind your data nerd to keep things hygienic.

Second item: DBA Coffee Cup
DBA Coffee Cup

Third: Zazzle.com DBA t-shirts

Forth: Think Geek Coffee Cup

Fifth: And you can always buy tickets for the new Star Wars movie because, as you will be told, there is so much to see on the ‘tenth and twelfth’ viewing.

Any other suggestions? Please leave them in a comment.


Lucky Sevens — MySQL 5.7 and PHP 7

December 10, 2015

MySQL 5.7 and PHP 7 are the latest versions releases of two ofthe LAMP Stack pillars. In the past I have detailed how to use the MySQL apt-get repository to upgrade Ubuntu to the latest and greatest. But that about PHP 7? This is a fairly simple update for someone running a traditional LAMP (Linux Apache MySQL and PHP) server. There are also great directions out there on updating Nginx for those so inclined to be found with a quick search.

sudo apt-get install python-software-properties
sudo add-apt-repository ppa:ondrej/php-7.0
sudo apt-get update
sudo apt-get purge php5-fpm
sudo apt-get install php7.0-cli php7.0-common libapache2-mod-php7.0 php7.0 php7.0-mysql php7.0-fpm php7.0-curl php7.0-gd php7.0-mysql
cp /usr/local/php7/libphp7.so /usr/lib/apache2/modules/

Then a quick test program:
<?php
$mysqli = new mysqli("localhost", "root", "hidave", "world");

/* check connection */
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}

/* print MySQL server version */
printf("

PHP version: %s

", phpversion());
printf("

MySQL version: %s

", $mysqli->server_info);
printf ("

System status: %s

", $mysqli->stat());

$mysqli->close();
?>

And the result:
PHP version: 7.0.0-5+deb.sury.org~vivid+1

MySQL version: 5.7.9

System status: Uptime: 2102 Threads: 1 Questions: 12 Slow queries: 0 Opens: 107 Flush tables: 1 Open tables: 26 Queries per second avg: 0.005

Please note that you will have to update your code if you use the old and no deprecated mysql calls instead of the supported mysqli calls.


Follow

Get every new post delivered to your Inbox.

Join 988 other followers