php with mysql

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 o...

I am starting a series of videos on the MySQL Document Store. The Document Store allows those who do not know Structured Query Language (SQL) to use a database without having to know the basics of relational databases, set theory, or data normalization. The goal is to have sort 2-3 minute episodes on the various facets of the Document Store including the basics, using various programming languages (Node.JS, PHP, Python), and materializing free form schemaless, NoSQL data into columns for use with SQL. The first Episode, Introduction, can be found here. Please provide feedback and let me know if there are subjects you would want covered in the near future.

The MySQL Document Store functionality allows developers to use a relation database with or without SQL (structured Query Language), also known as NoSQL. The example in this blog is hopefully a simple look at this new feature of MySQL. The example data used is from JSONStudio.com and is a JSON formatted data set for US zip (postal) codes (656K compressed). So download your copy of this data set and lets get to work. Create a collection Collections are tables and below we create a collection name ' zip ' in the test database in the Python dialect. mysqlsh -u root -p --py test Creating an X Session to root@ localhost:33060/test Enter password: Default schema `test` accessible through db. Welcome to MySQL Shell 1.0.4 Development Preview Copyright (c) 2016, 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', '\h...

Is it cheating? Is using MySQL without Structured Query Language (SQL) or putting all your data into one column proper? Impossible a year ago and probably thought as a poor/crazy practice until recently, this is a new type of MySQL usage. NoSQL has had a big impact in the SQL world with several relational products from vendors like MySQL, Microsoft, Postgresql and others offering NoSQL interfaces and JSON data types to their databases. Several old timers have come to me asking if putting lots of data in a JSON column is cheating? After all data normalization is part of relational databases and the way to efficiency and speed is well organized data. This higgledy–piggledy fashion of putting an entire document in a column without breaking it down to its component sections does violate the first rule of data normalization. And that has worked for decades pretty well. But things change. Many new development projects start with no idea what their data is going to look like. Maybe, if t...

The MySQL Document Store introduced with version 5.7.12 allows developers to create document collections without have to know Structured Query Language. The new feature also comes with a new set of terminology. So let us create a collection and see what it in it (basically creating a table for us SQL speakin' old timers). So start the mysqlsh program, connect to the server, change to the world-x schema (database) switch to Python mode, a create a collection (table). What did the server do for us? Switching to SQL mode, we can use describe to see what the server has done for us. We have a two column table. The first is named doc and is used to store JSON. And there is also a column named _id and please notice this column is notated as STORED GENERATED. The generated column extracts values from a JSON document and materializes that information into a new column that then can be indexed. But what did the system extract for us to create this new column? Lets use SHOW CREATE TAB...

Last time this blog covered digging into a JSON document in a MySQL 5.7 table. The goal was to pull certain records matching a particular criteria. Both Peter Zaitsev and Morgan Tocker get my thanks for their kind comments. My example was a little contrived in that an application would be used to fine tune seeking for a particular key value pair. I was trying to pull single records which is kind of silly when it is much easier to use PHP to parse the data. What follows below is a sample PHP script to grab out the matching records and then feed the results, the JSON document, into an array. #!/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(); } $foo = array(); $query0 = "SELECT* FROM restaurant WHERE json_contains(data, '{\"grade\": \"A\"}', ...