PHP and MySQL Without the SQL

Embedding Structured Query Language (SQL) within PHP, or other programming languages, has been problematic for some. Mixing two programming languages together is just plainly not aesthetically pleasing. Especially when you have a declarative language (SQL) mixed with a procedural-object oriented language. But now, with the MySQL XDevAPI PECL extension, PHP developers can now stop mixing the two languages together together.

MySQL Document Store

The MySQL Document Store eliminates the heavy burden for SQL skills. It is designed to be a high speed, schema-less data store and is based on the MySQL JSON data type. This gives you roughly a gigabyte of store in a document format to do with as needed. So you do not need to architect you data before hand when you have no idea how it will evolve. No need to normalize your data. Now behind the scenes is a power MySQL database server but you are no longer writing SQL to use it!

But Is The Code Ugly?

If you looked at previous editions of this blog then you have seen examples of using the MySQL XDevAPI PECL extension. There is another example below of how to search for the information under various keys in a JSON document. The great news is that the code is all very modern looking PHP with no messy SQL statements thumb-tacked onto the code. This should ongoing support by those with little or no SQL skills.
Previous you would have had to stick SELECT JSON_EXTRACT(doc,'Name') AS 'Country', JSON_EXTRACT(doc,geography) as 'Geo', JSON_EXTACT(doc,'geography.Region) FROM world_x WHERE _id = "USA" as a string in the PHP code. If you prefer the -> operator to replace JSON_EXTRACT, the code can be trimmed down to SELECT doc->"$.Name" AS 'Country', doc->"$.geography" AS 'Geo', doc->"$.geography.Region" FROM world_x WHERE _id = "USA".
But the XDevAPI simplifies these queries into $result = $collection->find('_id = "USA"')->fields(['Name as Country','geography as Geo','geography.Region'])->execute();. This is much easier to understand than the previous two queries for most. And this example shows how to chain down the document path as it specifies all of the geography hey's values and also just the data under geography.Region. It also show how to alias columns from the document store to a label of the developers choice.

#!/usr/bin/php
<?PHP
// Connection parameters
  $user = 'root';
  $passwd = 'hidave';
  $host = 'localhost';
  $port = '33060';
  $connection_uri = 'mysqlx://'.$user.':'.$passwd.'@'.$host.':'.$port; 
  echo $connection_uri . "\n";

// Connect as a Node Session
  $nodeSession = mysql_xdevapi\getNodeSession($connection_uri);
// "USE world_x"
  $schema = $nodeSession->getSchema("world_x");
// Specify collection to use
  $collection = $schema->getCollection("countryinfo");

// Query the Document Store
  $result = $collection->find('_id = "USA"')->fields(['Name as Country','geography as Geo','geography.Region'])->execute();

// Fetch/Display data
  $data = $result->fetchAll();
  var_dump($data);
?>

And The Output

mysqlx://root:hidave@localhost:33060
array(1) {
  [0]=>
  array(3) {
    ["Geo"]=>
    array(3) {
      ["Region"]=>
      string(13) "North America"
      ["Continent"]=>
      string(13) "North America"
      ["SurfaceArea"]=>
      int(9363520)
    }
    ["Country"]=>
    string(13) "United States"
    ["geography.Region"]=>
    string(13) "North America"
  }
}

User Guide

The MySQL Shell User Guide is a great place to start learning how to interactively start using the Document Store.