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();
?>


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.


MySQL at the United Nations

July 6, 2015

MySQL will be at the United Nations for NYC Camp! I will be speaking on Scaling MySQL and NoSQL, HTTP, and JSON with MySQL July 16th to 19th. The goal of the camp is to provide free training and is 100% free.


JSON and the MySQL Argonauts

June 8, 2015

The MySQL 5.7.7 JSON lab release has been getting a lot of attention. At a recent conference, I was cornered by a developer who wanted to jump in with both feet by running this release on his laptop on the flight home. Jason and the Argonaughts Movie Poster However the developer was not sure how to begin.

1. Down load the MySQL JSON release from http://labs.mysql.com/. You will get the choice of a Linux binary or source code. Please grab the binary if you are using Linux and un-gzip/tar the download.

2. Shut down the current running version of MySQL. I was lucky in this case that the developer was using a recent copy of Ubuntu.

3. Change directory to the ~/Downloads/mysql-5.7.7-labs-json-linux-el6-x86_64 directory.

4. sudo ./bin/mysqld_safe –user=mysql&

5. ./bin/mysql -u root -p, then provde the password.

6. Enter a \s to get the status. This will confirm that you are using the JSON labs release.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.7-labs-json 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 help. Type '\c' to clear the current input statement.

mysql>

If you are doing more than simple tests, run mysql_upgrade to update the system tables. But you can skip this step for a quick and dirty exploration but do not expect your JSON data to be around when you go back to the previous version of MySQL.

7. Now you can start testing the JSON data type. I recommend starting with reading JSON Labs Release: JSON Functions, Part 1 — Manipulation JSON Data, JSON Labs Release: JSON Functions, Part 2 — Querying JSON Data, and JSON Labs Release: Native JSON Data Type and Binary Format. Then follow up with JSON Labs Release: Effective Functional Indexes in InnoDB to understand how to create indexes.


A Tale of Two Conferences — Midwest PHP and Kansas Linuxfest

March 23, 2015

MySQL and I were proud to present talks at two recent events — Midwest PHP and Kansas Linuxfest — and it made me realize how vital the smaller shows are to the Opensource Community at large and MySQL in particular. I go to many larger shows like SCaLE where thousands attend. But is the smaller shows where I really get a chance to see what folks are doing with MySQL, learn about new software, and have a reasonable chance to enjoy the ‘hallway track’.

Both these shows would be considered small. Just over two hundred for MidwestPHP and somewhere in the one forties for Kansas. But both shows brought in top speakers from far and wide and combined them with top local talent. Often time the local talents are the real gems as they are often new faces with great ideas. Not all the great ideas are in the Silicon Valley or Austin.

MidwestPHP has been around for a few years and the speakers who attend know we will get spoiled by the organizers. This year many of us were humbled while playing ‘Cards Against Humanity’ by a rabbi at the speakers dinner social. We also know that the audience is coming to learn, will ask hard questions, and you had better bring your ‘A-game’. This is a PHP crowd that is looking for the latest and greatest information on their language of choice but are also actively looking at how to best incorporate CI, Databases, Containers, and other related tech.

This was the first year for the Kansas Linuxfest and in many ways it was like a family reunion. ‘Oh, you are the Linux family from Wichita? We’re the Linux family from Lawrence!’. This first year was as much establishing ties to the various Linux users in the state of Kansas as it was a Linuxfest. Kansas Linux Fest Logo There were a wide variety of subjects covered from Python & Pex to Go and often times two or three great talks where scheduled at the same time.

Both shows will be back in 2016 and you should seriously consider putting time on your calendar for them. If you have a local opensource event or user group in your area, it is well worth the effort to connect with them. It is not only a valuable way to network with others like yourself but you can learn from others.

I recommend going to a least one session on a subject you know little or nothing about. It often will spark ideas to aid in current projects or provide insights on how others approach challenges.

The big shows are great and I enjoy them. But seek out a smaller show as you get a much better chance to interact with those around you. The smaller regional shows also provide better grounds for hunting for folks to add to your professional network.

And for those of you attending Collaborate 15 please see me at the MySQL Demo Pod.


Toad for MySQL

February 27, 2015

Long before MySQL, DBAs have been using Toad. Toad is short for Tool for Oracle Application Developers and I remember the Oracle DBAs raving about how TOAD made so many things simpler. Now it is many years later and Dell has released TOAD for MySQL.

On the product page we are promised Toad™ for MySQL is a freeware development tool that enables you to quickly and efficiently create and execute queries, automate database object management, and develop SQL code. Toad MySQL provides utilities to compare, extract, and search for objects. The MySQL tool also enables you to record and play back keyboard commands, and transfer data across MySQL databases. The MySQL developer tool increases efficiency by helping you manage projects, import/export data, and administer the database. Sounds pretty good!

But how does it stack up against MySQL Workbench on my Windows Laptop?

Microsoft .NET Framework 4.5 is required before installing Toad.

Toad For MySQL? Yup, Toad is now available for MySQL

Toad For MySQL? Yup, Toad is now available for MySQL

Starting ‘Toad for MySQL Freeware’ starts off with links for video tutorials, other freeware, blogs, and a link to visit the Toad for MySQL Community.

Connections
Connecting to Toad is similar to Workbench but then there is just so much information to build a DSN.

Entity Relationship Mapping
Tables are dragged manually from the Object Explorer to the diagram window and foreign key mappings is automatically drawn. This is in contrast to Workbench that maps all tables.

Queries

Queries and results from that query in a separate pane.

Queries and results from that query in a separate pane.

Toad will provide hints for columns or keywords as you type queries. You can not get an Explain Plan as as their is an odd error about violation of a constraint and their is no Visual Explain. You do have an option for a drag-n-drop pivot table from query results. You can created tables, edit existing tables, drop tables and all the functions you would expect.

Build Queries
You can drag-n-drop to build queries. I grabbed the world.City table, dropped it on the canvas, and clicked on the Name Column. Next I dragged the Country table and clicked on that tables’ Name column. Toad automatically generated the SQL to to perform an inner join. And that is cool and should help infrequent query writers.

Not Workbench
Workbench has more functionality for things like system administration, backups, import, user admin, system statistics, and DBA oriented work.

So What IS Toad?
Toad is a query tool and fantastic for those who do not need all the admin functionality of Workbench. If you write SQL this may be an option for you. There is no Explain, Visual Explain, or any info on a query plan. And the ability to drag-n-drop tables may be what you need.

I will tray incorporating Toad in my normal work and see what else pops up.


Follow

Get every new post delivered to your Inbox.

Join 941 other followers