Friday, January 13, 2017

PHP and MySQL Basics III -- Resulting Results

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 ($result->num_rows === 0) {
    // Oh, no rows! Sometimes that's expected and okay, sometimes
    // it is not. You decide.
    echo "No data returned.";
    exit;
}

This is a case where a programmer needs to know their data. In some cases you will not have a record or records returned because there is no data. Other times no data returned is a sign of big problems. So you have to have some education on what you expect back, and what you do not expect back.

Example

<?php
$mysqli = new mysqli("localhost", "root", "hidave", "world_x");

/* check connection */
if ($mysqli->connect_errno) {
    printf("Connect failed: %s\n", $mysqli->connect_error);
    exit();
}

/* Select queries return a resultset */
$query="SELECT Name, CountryCode, District FROM city LIMIT 10";

if ($result = $mysqli->query($query)) {

        if ($result->num_rows){
                printf("Select returned %d rows.\n", $result->num_rows);

                /* free result set */
                $result->close();
        } else {
                echo "No data returned";
        }
} else {   // if ($result)
   printf("Query failed: %s", $mysqli_error);
}

$mysqli->close();
?>

Sometime you just need the number of records, like number of outstanding customer orders. But in this case we are making sure we have some data to work with before proceedings.

So Now We Have Data

Now you have at least three choices -- rare, medium, or well done. Err, make that an associative array, an array or an object. Each have their uses and it is okay to have a favorite you use more.
$query="SELECT Name, CountryCode, District FROM city LIMIT 10";

if ($result = $mysqli->query($query)) {

        if ($result->num_rows){
                printf("Select returned %d rows.\n", $result->num_rows);
                $assoc = $result->fetch_assoc();
                $row = $result->fetch_row();
                $obj = $result->fetch_object();
        } else {
                echo "No data returned";
        }
} else {   // if ($result)
   printf("Query failed: %s", $mysqli_error);
}

So you make you choice of method and take the results. Here we use fetch_assoc(), fetch_row(), or fetch_object(). Depending on how you want to refer to the data, you use the one that fits the situation. Of course they are similar in use.

//associated array keys = column name, data = data from DB
printf("Sample assoc array %s -> %s\n", $assoc['Name'], $assoc['CountryCode']);

// simple row
printf("Sample row array %s -> %s\n", $row[0], $row[1]);

//object
printf("Sample object %s -> %s\n", $obj->Name, $obj->CountryCode);

Yes, you need to know all three as you will be looking at old code or someone else code that does not use your favorite. And sometimes you may need an object rather than a simple row.

Full Listing

<?php
$mysqli = new mysqli("localhost", "root", "hidave", "world_x");

/* check connection */
if ($mysqli->connect_errno) {
    printf("Connect failed: %s\n", $mysqli->connect_error);
    exit();
}

/* Select queries return a resultset */
$query="SELECT Name, CountryCode, District FROM city LIMIT 10";

if ($result = $mysqli->query($query)) {

        if ($result->num_rows){
                printf("Select returned %d rows.\n", $result->num_rows);
                $assoc = $result->fetch_assoc();
                $row = $result->fetch_row();
                $obj = $result->fetch_object();
        } else {
                echo "No data returned";
        }
} else {   // if ($result)
   printf("Query failed: %s", $mysqli_error);
}
//associated array keys = column name, data = data from DB
printf("Sample assoc array %s -> %s\n", $assoc['Name'], $assoc['CountryCode']);

// simple row
printf("Sample row array %s -> %s\n", $row[0], $row[1]);

//object
printf("Sample object %s -> %s\n", $obj->Name, $obj->CountryCode);

$result->close();
$mysqli->close();
?>

Wednesday, January 11, 2017

PHP and MySQL Basics II - Case Sense

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 = new mysqli("localhost", "user", "secret", "world_x");

/* check connection */
if ($mysqli->connect_errno) {
    printf("Connect failed: %s\n", $mysqli->connect_error);
    exit();
}

$mysqli->close();
?>

Run the program and ... nothing.

So What Happened?

What happened is a subtle problem that novices will smack into very hard. Take a look at this section of the example.
/* Select queries return a resultset */
if ($result = $mysqli->query("SELECT Name FROM City LIMIT 10")) {
    printf("Select returned %d rows.\n", $result->num_rows);

    /* free result set */
    $result->close();
}

If you try the query SELECT Name FROM City LIMIT 10; with the MySQL command line client program you will get the answer. And the answer is:

mysql> SELECT Name FROM City LIMIT 10;
ERROR 1146 (42S02): Table 'world_x.City' doesn't exist
mysql>

I am using the new world_x example database where the city is NOT capitalized instead of the old world database where it is! This lesson can be summed as check you schema/table/column names for case sensitivity. Except that there is another problem here.

In the real world occasional the database/table/column that you carefully double checked was spelled correctly and with the proper case sensitivity will go away. It may have been renamed, deleted, munged, or what have you. What is needed is a way to check to see if there was an error if the query can not run.

Lets change the code slightly:

/* Select queries return a resultset */
if ($result = $mysqli->query("SELECT Name FROM City LIMIT 10")) {
    printf("Select returned %d rows.\n", $result->num_rows);

    /* free result set */
    $result->close();
} else {
    printf("Query failed: %s\n", $mysqli->error);
}

Always Check for Return Codes

By simply adding about 40 characters, the reliability of the program shoots up immensely AND we get an exact answer if what went wrong.
Query failed: Table 'world_x.City' doesn't exist
Same error as when we tried by query by hand. But now our code can handle this issue. We could even try to catch the error, send a note via a message queue to the operations staff about the nature of the problem, and possible limp along until things are resolved. Or we could just call exit()

When you are offered a return code be sure to check it. Yes, it may be over kill in simple examples. But the payoff comes when things go bad and you are scrambling to find out eleven months from now why your program is suddenly not working.

Tuesday, January 10, 2017

PHP and MySQL Basics

PHP and MySQL have had a long intertwined path together. I have been talking with a lot of newbies in the past several months who are trying to become PHP developers but are amazed at all the ancillary parts that go along with PHP such as unit testing, databases, JavaScript, continuous integration, and much more. Add in that there are two MySQL APIs -- PDO & MySQLi -- and an older deprecated mysql API still often found in the wild. This blog is the start of a series for new PHP developers to learn to program with a database.

Client Server Model

The PHP code when it seeks to talk to a MySQL (or most other databases) will make a connection to a port at an IP address. Usually MySQL is listening on port 3306. If you are developing an accessing a database on your local computer the IP address used will generally be at 127.0.0.1. The software that goes between the PHP application and the database is called a connector.

So your code on you local system an be talking to a database server on your local system or through a network connection. It does not matter which.

Can't connect to MySQL server on 'x.x.x.x' (111)

The Can't connect error can be especially frustrating. An experienced developer will know what to check from tears of experience. But this is a column on basics so we need to spell out the steps.
  1. Is the IP address correct? It is easy to fat finger IP address and ironically 127.0.01 on many Linux boxes will connect up to 127.0.0.1.
  2. Is there a instance of MySQL running at that IP address?
  3. Is that instance listening on the generic port 3306? Is may be running someplace else and you will have to chance down that port number.
  4. Can the MySQL command line shell or other tool connect to the instance? MySQL Workbench, PhPMyAdmin, the cli tools, and everything else authenticate through the same steps so if they work and your PHP program does not then most likely the fault is in the PHP code.

Setting up the client server connection

The PHP Manual is worth its weight in gold and you should refer to it often. Its examples are clear, or usually as clear as can be, and concise. Below is an excerpt example from the manual.

<?php

$mysqli = new mysqli("127.0.0.1", "user", "password", "database", 3306);
if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}

echo $mysqli->host_info . "\n";
?>

Note that the IP address, user name of "user", password of "password", and the port of 3306 will need to be changed to fit the installation. The mysqli call sets up the connection between the application and the MySQL database server.

Please note that you should protect usernames and password or any other information that could allow someone to compromise the server and data.

The if statement is invoked when there is an error code is returned from the $mysql->connect_errono call. Subsequently the error message from the server can be printed out using $mysqli->error. The error message itself can be terse but often points out what is wrong in the code.

Bad arguments

What follows below are three bad connection strings.
// Bad IP address
$mysqli = new mysqli("19.10.0.3", "root", "barfoo", "world_x", 3306);
if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}

// Bad account information
$mysqli = new mysqli("127.0.0.1", "root", "foobar", "world_x", 3306);
if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}

// Bad database specified
$mysqli = new mysqli("127.0.0.1", "root", "foobar", "world_xx\", 3306);
if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}

Part of mastering any computer programming language is learning to understand the error messages. The three examples above return similar but distinctly different messages.

The first of the trip provides the following error:


PHP Warning:  mysqli::__construct(): (HY000/2002): Network is unreachable in /home/dstokes/php/m02.php 
Failed to connect to MySQL: (2002) Network is unreachable

It would be nice to get more information than 'Network in unreachable' but it provides a starting point to diagnose the problem. Generally the more specific the problem, the more specific the error message.

The third of the trio attempts to connect to a database named 'world_xx' when we really wanted 'world_x'.

PHP Warning:  mysqli::__construct(): (HY000/1049): Unknown database 'world_xx' in /home/dstokes/php/m02.php
Failed to connect to MySQL: (1049) Unknown database 'world_xx'

Sadly for beginners it takes time and experience to get to the point where you can instantly look at an error and know what has gone wrong (or have a pretty good idea of what has gone wrong). But do not worry as many of us learn by correcting OUR mistakes and learning not to repeat them.

Connection Good

So after establishing a good connection to the MySQL server, we can now query it for data.
Next Time -- what happened to my query??

Friday, January 6, 2017

Using MySQL to Output JSON

MySQL has had a JSON data type since version 5.7 was released way back in '15. But did you know you could produce JSON output from non-JSON columns? It is very simple and saves a lot of time over trying to format it in your application.

World Database

We will be using the good old World database that MySQL has used for years in documentation, examples, and in the classroom. Starting with a simple query we will build up to something more complex.

SELECT Name, District, Population FROM City;

This will output the data from the table in a tabular format.

'Kabul', 'Kabol', '1780000'
'Qandahar', 'Qandahar', '237500'

Array or Object?

We have two options for composing JSON data: JSON_ARRAY and JSON_OBJECT.

Of the two, you will find JSON_ARRAY the least fussy. It will JSON-ize your data very easily. It takes a list of values or an empty list and returns a JSON array.

We add this function to our example query and it becomes SELECT JSON_ARRAY(Name, District, Population) FROM City;

And the output looks like:


'[\"Kabul\", \"Kabol\", 1780000]'
'[\"Qandahar\", \"Qandahar\", 237500]'
...

JSON_OBJECT wants key/value pairs and will complain if the key name is NULL or you have an odd number of objects. If we try SELECT JSON_OBJECT(Name, District, Population) FROM City; we will get Error Code: 1582. Incorrect parameter count in the call to native function 'JSON_OBJECT'. This fuctions sees the odd number of arguments as a 'key' and the evens as the 'value' in key/value pairs and therefore we should not have an odd number of arguments. We could stick in a dummy literal string into the select but odds are that we want the three fields specified but need to turn them into key/value pairs. So lets add 'keys' and let the database supply the values.

SELECT JSON_OBJECT('City', Name, 'Dist', District, 'Pop', Population) FROM City;,

And the output looks like:


'{\"Pop\": 1780000, \"City\": \"Kabul\", \"Dist\": \"Kabol\"}'
'{\"Pop\": 237500, \"City\": \"Qandahar\", \"Dist\": \"Qandahar\"}'
...

Conclusion

Developers need to work smarter and not harder. And I have been harping on letting the database do the heavy lifting for years. This is an example of letting the database format your information for you rather than feeding it into a function within your application. Sure you can do it but this saves you a step or two and reduces the complexity of your application.

Wednesday, December 14, 2016

A Simple Python Example Program for the MySQL Document Store

Last time we looked at a simple example program using the X Devapi and Node.JS. This time lets look at the Python version. Well, not actually the same. This time instead of looking for the Canadian record, the program limits the query to the first two records found.

Besides Python 2.7, you will need to install the Google Protobuf code plus the development release of the Python Connector and a recent version of MySQL 5.7.

The Code


import mysqlx

session = mysqlx.get_session({          # Authenticate to server
  'host':       'localhost',
  'port':       33060,
  'user':       'dstokes',
  'password':   'S3cR3t%'})

# Connect to Schema 'world_x'
schema = session.get_schema('world_x');

# Set collection to 'countryinfo'
collection = schema.get_collection('countryinfo')

# Ask for two records
result = collection.find().limit(2).execute()

docs = result.fetch_all()

# Print requested records
for i, data in enumerate(docs):
   print "{iteration}: {data}".format(iteration = i, data=data)

# Clean up
session.close()

A Note About the 'Collection' versus 'Table'

Take a quick peek at the table used for the example from the world_x database (see last entry for location and details for installation). There are actually two '_id's in the data. There is one that is the generated column you see below and the other is in the JSON column named doc. The collection itself is the JSON data. How to prove? Change the line starting with result in the above example and replace it with result = collection.find('GNP > 100000').limit(2).execute() or something similar with a key from the data.
mysql> desc countryinfo;
+-------+-------------+------+-----+---------+------------------+
| Field | Type        | Null | Key | Default | Extra            |
+-------+-------------+------+-----+---------+------------------+
| doc   | json        | YES  |     | NULL    |                  |
| _id   | varchar(32) | NO   | PRI | NULL    | STORED GENERATED |
+-------+-------------+------+-----+---------+------------------+
2 rows in set (0.00 sec)

Results


$python test.py
0: {"GNP": 828, "Name": "Aruba", "government": {"GovernmentForm": "Nonmetropolitan Territory of The Netherlands", "HeadOfState": "Beatrix"}, "demographics": {"LifeExpectancy": 78.4000015258789, "Population": 103000}, "_id": "ABW", "IndepYear": null, "geography": {"SurfaceArea": 193, "Region": "Caribbean", "Continent": "North America"}}
1: {"GNP": 5976, "Name": "Afghanistan", "government": {"GovernmentForm": "Islamic Emirate", "HeadOfState": "Mohammad Omar"}, "demographics": {"LifeExpectancy": 45.900001525878906, "Population": 22720000}, "_id": "AFG", "IndepYear": 1919, "geography": {"SurfaceArea": 652090, "Region": "Southern and Central Asia", "Continent": "Asia"}}

Next Time

Next time we will build on these two simple example programs.

Monday, December 12, 2016

A Simple Node.JS Example Program for the MySQL Document Store

I have chatting with others who want to use the new X Devapi Document Store features but have not been able to find simple example programs. Finding the bridge between 'Hello World' and something useful can often be discouraging.

Example Code

Here is a very short Node.JS Docstore example program that accesses the 'countryinfo' collection. More on why Node.JS and what software you need below. But for now regard this code to get one specific record from the database.

var mysqlx = require('@mysql/xdevapi');

mysqlx.getSession({             //Auth to server
        host: 'localhost',
        port: '33060',
        dbUser: 'root',
        dbPassword: 'password'
}).then(function (session) {    // use world_x.country.info
     var schema = session.getSchema('world_x');
     var collection = schema.getCollection('countryinfo');

collection                      // Get row for 'CAN'
  .find("$._id == 'CAN'")
  .limit(1)
  .execute(doc => console.log(doc))
  .then(() => console.log("\n\nAll done"));

  session.close();
})

Code Explained

On the first line the X Devapi library is loaded. Line three is authentication to the server followed by getting to the world_x/countryinfo schema. The we find the record for Canada. Is this simpler than "SELECT * FROM countryinfo WHERE _ID = 'CAN'"? Maybe, but I also see where this approach has some facets that need exploiting, er, exploring.

Results

So what does the code do?

:~/xdevn$ node demo.js
{ GNP: 598862,
  _id: 'CAN',
  Name: 'Canada',
  IndepYear: 1867,
  geography: 
   { Region: 'North America',
     Continent: 'North America',
     SurfaceArea: 9970610 },
  government: 
   { HeadOfState: 'Elisabeth II',
     GovernmentForm: 'Constitutional Monarchy, Federation' },
  demographics: { Population: 31147000, LifeExpectancy: 79.4000015258789 } }


All done

Why Node??

I like to try to expand my programming skills every year. MySQL recently released a new driver for the X Devapi Document Store for those who program in Node.JS. JavaScript has become the 'and' in the way many developers describe their jobs. They program in their favorite language 'and' in JavaScript to get their work done. PHP, Python, Rails and the like all have their coders also writing JavaScript. I have not done anything series with JavaScript since they first started trying to call it ECMAscript. At the recent ConFoo I started asking about learning modern JavaScript (and variants) and was pointed at Node.JS. Those of you looking to add Node.JS to your skill set can benefit from the books from Leanpub.com and their Node books (Inexpensive, easy to read, and wonderfully detailed).

The new Connector/Node.JS

First download the Node.JS connector for the MySQL Document Store. Also grab a copy of the world_x database, an update of the World database MySQL has been using for a long time in examples. Follow the directions for installing both of these packages. You will also need to load Google's Protobuffers.

The tutorial is very good but I needed a small stepping stone.Hopefully I can build on this and add the analogs in Python too.

Monday, November 28, 2016

2017 Database Administrator Holiday Gift Guide

TO DBAs:Database Administrators and other computer type folks are very hard to buy presents for at this time of year. Please help your family and friends out out by printing this off and notating which of the following items your would find acceptable.

TO The Friends and Family of a DBA: Yes, they are a pain in the pa-toot to buy a holiday gift for but hopefully this lest will make things a little easier. There are a lot of t-shirt on this list and this is a change to get that one threadbare relic from a long ago computer conference sent to the Valhalla for mangy cotton blend fabrics. Good luck and hopefully you will see something below that will keep you from just handing over a gift card. o

Amazon

Need to work on your laptop in bed or a variety of angles? Then this Furinno X7-BK Ergonomics Aluminum Adjustable Cooling Fan Laptop Tray Table is what you want, available in many colors.

Zazzle

Zazzle.com has a nice collection of DBA centric gifts.
And in female styles also.

Think Geek

Infectious Disease Ball

Cafe Press

Cafe Press has a line of t-shirts, also in m/f sizes and styles
Mugs too!

TeeSpring

Teespring has some interesting shirts

Bonanza

Bonanza.com also has shirts

Uncommongood

Yes, we al have too many phone chargers. Here is uncommongoods.com way to help identify them uniquely.
Other suggestions: Passes for movie theaters (new Star Wars movie on the way), Amazon gift cards, or Bacon of the Month Club membership.