php with mysql

MySQL 8 is going to have new batch of JSON functions and last time JSON_PRETTY() was covered in some details. The recent release of 8.0.1 provides an opportunity to try these new functions and a few that might have been missed with 8.0.0. Unquoting The -> shortcut for JSON_EXTRACT() was introduced with MySQL 5.7. And now there is the unquoting extraction operator or ->> to simplify things again! Think of it as JSON_UNQUOTE wrapped around JSON EXTRACT . The following there queries produce the same output. mysql> SELECT JSON_UNQUOTE(JSON_EXTRACT(doc,"$.GNP")) FROM countryinfo WHERE _id = "USA"; +-----------------------------------------+ | JSON_UNQUOTE(JSON_EXTRACT(doc,"$.GNP")) | +-----------------------------------------+ | 8510700 | +-----------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT JSON_UNQUOTE(doc->"$.GNP") FROM countryinfo WHERE _id = "USA"...

JSON is a great way to share data and is highly praised in being human readable. Well, compared to XML and some other standards it is easier to read. But sometimes the data looks mushed or wilted. mysql> SELECT doc FROM countryinfo WHERE _id='USA'; ... {"GNP": 8510700, "_id": "USA", "Name": "United States", "IndepYear": 1776, "geography": {"Region": "North America", "Continent": "North America", "SurfaceArea": 9363520}, "government": {"HeadOfState": "George W. Bush", "GovernmentForm": "Federal Republic"}, "demographics": {"Population": 278357000, "LifeExpectancy": 77.0999984741211}} 1 row in set (0.00 sec) And 'easy' is relative, right? Well, what if you had a way to make it easier to read, er, pretty? MySQL 8.0.1 has a few new JSON functions but one of them is designe...

MySQL 8 features invisible indexes. But what good is an index if you can see it? </ br> Is It Really Invisible You probably know by now that indexes can really speed up data searches. And many of you have mastered the use of EXPLAIN to see if the optimizer is using an index for your query. Which means you should know that sometimes for some queries an index does not help. mysql> CREATE TABLE t2 ( -> i INT NOT NULL, -> j INT NOT NULL, -> UNIQUE j_idx (j) -> ) ENGINE = InnoDB; Query OK, 0 rows affected (0.01 sec) mysql> insert into t2 values (1,2),(3,4),(5,6),(7,8); Query OK, 4 rows affected (0.00 sec) mysql> explain select j from t2 where j>2; +----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+---...

SQL Injection is a highly feared and often misunderstood problem. The basic phobia is that someone hijacks your SQL request and suddenly has full access to everything in your database. Well, it usually is not that easy and it is actually easy to avoid. Rule 1: Never Trust User Supplied The usual example is something like a query SELECT * FROM customer_data WHERE customer_id='$id' and the programmer was expecting an integer for the customer_id . But a dastardly use inserts some horrible SQL code to pirate the information so the query looks like SELECT * FROM customer_data WHERE customer_id=1 OR customer_id > 0 and suddenly all your customer data is out free in the universe waiting for who knows what. The code could have checked to see if the value of customer_id was truly an integer or returning an error if not. The is_int function was designed to do just this. if is_int($customer_id) { //Do all the stuff we want to do if we have a integer //submitted for ...

The MySQL Document Store and X Devapi have a lot of very interesting features but right now my programming language of choice, PHP, is not yet supported. My Python is rusty and learning Node.JS is progressing. But the ability to search data from a database without knowing Structured Query Language (SQL) is going to appeal to many. Example One import mysqlx import string session = mysqlx.get_session({ 'host': 'localhost', 'port': 33060, 'user': 'dstokes', 'password': 'Hell0Dave!'}) schema = session.get_schema('world_x'); collection = schema.get_collection('countryinfo') print "Find three records***\n" result = collection.find().limit(3).execute() docs = result.fetch_all() for i, data in enumerate(docs): print "{iteration}: {data}".format(iteration = i, data=data) print "Find USA***\n" result = collection.find('_id = "USA"').execute() row = result.fetch_all() for i,...

In the first two blogs entries on this series we set up a connection to MySQL and sent off a query. Now we need to get the data back from the database and into the application. An Embarrassment of Riches PHP has many options for what we want to do. But for the best place to start with was checking that rows were actually returned from a query. Below the results from a query are returned to a variable named $result . We can find out how many rows were returned from the server by examining $result->num_rows . if (!$result = $mysqli->query($sql)) { // Again, do not do this on a public site, but we'll show you how // to get the error information echo "Error: Our query failed to execute and here is why: \n"; echo "Query: " . $sql . "\n"; echo "Errno: " . $mysqli->errno . "\n"; echo "Error: " . $mysqli->error . "\n"; exit; } // succeeded, but do we have a result? if ( $resu...

Last time we set up a connection from a PHP program to a MySQL server. This time we will progress a little further in that direction. Query Data is asked for from the MySQL server by using a query written in a language named Structured Query Language (SQL). Now that we have a connection open to the server, we can pass out request to the server. Manual Labor The PHP Manual is wonderful 99% of time. If you take a peek at the page for mysqli::query there is a great example of a simple query. Many of learned to program by copying/pasting from books/manuals and this is a great us of the examples in the PHP manual. Except it may not work for you. MySQL is usually case SeNsATiVe, so 'A' may not be the same thing as 'a'. But this is dependent to some extent on your operating system where 'A' = 'a'. I was using the example from the manual and ... it did not work. What Happened Here is an excerpt of the code, somewhat cut down: <?php $mysqli = ne...