Example to connect to SQLite with PHP and return JSON

You can spend a long time googling for examples of how to do this with PDO. The real juice is in lines 17 and 18. I record it here for posterity.

* Code to query an SQLite database and return
* results as JSON.

// Specify your sqlite database name and path //
$dir = 'sqlite:mydir/myDatabase.sqlite';

// Instantiate PDO connection object and failure msg //
$dbh = new PDO($dir) or die("cannot open database");

// Define your SQL statement //
$query = "SELECT * FROM myTable";

// Iterate through the results and pass into JSON encoder //
foreach ($dbh->query($query) as $row) {
echo json_encode($row[0]);

OSX Litecoint (LTC) mining Example with bfgminer and Macports

This post is a little OT so I’ll keep it terse. The goal is to perform LTC mining on OSX Maverics 10.9.2. But here’s the challenge – compile everything natively from source code, on old equipment. There are several good packages and binaries floating around which you should try first. If you prefer to know what’s in them and how they’re built, here’s a step by step tutorial to roll your own. Most of the documentation for this exercise is buried in obscure forum threads and uses the Homebrew package manager. This post is for MacPorts users; and those still running MacBooks from mid 2009(!) and because I enjoy a GNU challenge… Screen Shot 2014-03-17 at 3.36.47 PM

Your first task is to identify your graphics card with the help of the Apple website and check it supports OpenCL. In my case, it’s a rather sucky NVIDIA GeForce 9400M. It seems  the System Preferences, Displays Tool in Maverics can’t identify some of these older graphics devices. Once you know your graphics hardware supports OpenCL, the process is as follows:

1. Select an online mining pool and open an account. You’ll need to setup a “worker” with a username and password.

2. Update your MacPorts package manager (or install it if you don’t have it).

sudo port selfupdate
sudo port upgrade outdated

3. Check your Xcode Command Line Tools are working so you can actually run the compiler. If you haven’t installed Xcode you can grab it (free) from the OSX App Store.

pkgutil --pkg-info=com.apple.pkg.CLTools_Executables

Try this on something older than Maverics (like Lion or Snow Leopard)

pkgutil --pkg-info=com.apple.pkg.DeveloperToolsCLI

4. Install all the lib dependencies (C header files) needed to compile bfgminer. Looking at the README around lines 84-86 you can see what you’ll need to install. Start at the beginning of the list and work through like so,

sudo port install libusb
sudo port install libevent
sudo port install libmicrohttpd

Ignore (but note) any errors as MacPorts doesn’t have packages for all these. In these cases you’ll have to install them do by hand. Create a working directory,

 mkdir ~/Miner; cd Miner; ls 

To install uthash headers, clone them from the git repo,

git clone https://github.com/troydhanson/uthash

Then copy thee C headers (.h files) to /usr/local/include

 sudo cp ./uthash/src/*.h /usr/local/include 

The process for the hidapi drivers for USB devices is a little different. I include these in case you want to try your hand at SHA-256 based crypto currencies and deploy a USB ASIC.

git clone https://github.com/signal11/hidapi
sudo make
sudo make install

5. Now, the coin miner itself. I chose bfgminer, as Scrypt based LTC is no longer supported by cgminer. Clone the git repo into your working directory,

cd ./Miner
git clone https://github.com/luke-jr/bfgminer
cd ./bfgminer; ls

Run the autogen.sh script. Not clear what this does but it appears to run GNU autoconf and generate a Configure script.



Next, run the Configure script.

./configure --enable-scrypt --enable-opencl CFLAGS=”-g -O2 -Wall”


Now, compile and read but ignore any Warning messages.

sudo make
sudo make install

Create a bfgminer config file called bfg.conf. This is a json file and can look something like this example. Obviously, enter the specific details of the mining pool you registered with –

    "pools" : [
        "url" : "stratum+tcp://crypto-coin-pool.com:3335",
        "user" : "<worker_name.worker_number>",
        "pass" : "<worker pwd>",
        "pool-priority" : "0"

Start things up with,

 ./bfgminer --scrypt -c ./bfg.conf -w 128 

The explanation for the -w 128 can be found here. It’s to do with the sucky performance capabilities of the GeForce card. With any luck, your bfgminer will start up… bfgminer no dev

If it doesn’t find your GPU device you’ll need to configure it with the [M]anage devices option (press “m”, obviously). Then hit the “+” sign and enter “auto”. manage devs

With any luck, you should be good to go…bfg running

Now let it run for a while… In this example it ran for 21:33 mins:secs before anything meaningful started showing up.

Successful mining

You can see (red boxes) submitted work packages being accepted by the online  mining pool. You can expect to see these every 3 mins or so.

Simple D3.js Force Layout Example in less than 100 lines of code

Force Layout ExampleThis post is the follow through on the commitment I made in this one. Namely, a simple D3.js example (in the style of a Tut) aimed at Healthcare Analysts.

I’ve chosen one of the most simple and flexible diagram styles – the Force Layout Diagram.

Following my own advice in this post, I’ll start with the data. I’ve chose JSON for readability over csv. However, health care analysts (not being programmers) are pretty unfamiliar with JSON. So if this is too inaccessible, I’ll do a csv or SQL version.

I created two files containing my JSON data. The first (nodes.json) contains a list of my force layout nodes. Each object/node in the list has an implicit index number starting at position zero. Creighton Hospital would occupy the zero position in this example.

You can cut and paste this into a text editor and save as nodes.json into the working folder on your webserver.

        "name": "Creighton Hospital"
        "name": "Heart Hospital"
        "name": "Marshall Regional Medical Center"
        "name": "McKennan Hospital & University Health Center"
        "name": "Queen of Peace Hospital"
        "name": "Sacred Heart Hospital"
        "name": "St. Luke's Hospital"
        "name": "St. Mary's Hospital"
        "name": "Milbank Area Hospital"
        "name": "Pipestone County Medical Center"
        "name": "St. Michael's Hospital"
        "name": "Wagner Community Memorial Hospital"

The second file (links.json) identifies the start and end points for the connections between the nodes. Nodes are identified by the implied index position identified earlier. So, “source”:1 in links.json would be a reference to “Heart Hospital” in nodes.json. Note that some nodes intentionally have no connections but appear to connect to themselves.

You can cut and paste this into a text editor and save as links.json into the working folder on your webserver, alongside your nodes.json file.

        "source": 0,
        "target": 0
        "source": 1,
        "target": 1
        "source": 1,
        "target": 7
        "source": 2,
        "target": 2
        "source": 3,
        "target": 1
        "source": 3,
        "target": 3
        "source": 3,
        "target": 9
        "source": 4,
        "target": 4
        "source": 6,
        "target": 5
        "source": 6,
        "target": 3
        "source": 8,
        "target": 8
        "source": 10,
        "target": 3
        "source": 11,
        "target": 3
        "source": 11,
        "target": 11

I’ve chosen to put the node and link data in two separate files to make the mental separation between the two types required for a Force Layout diagram.

Getting used to identifying nodes this way (by index number) and separating them from their links, will help make the leap to Sankey diagrams a little less challenging.

This pattern also helps if you’re going to load this data dynamically from a database as each requires different SQL queries to generate them. Trying to concatenate SQL results into one JSON file on the fly can be an unnecessary PITA.

I’ve chosen to load my two data files with the queue library (lines 42-45). I like this approach as it avoids async page vs data loading issues (lots of questions at StackOverflow on this).

You can copy this combined JavaScript and HTML code and paste it into your index.html file.

I apologize in advance for the crappy code syntax highlighting and formatting offered by wordpress.com.

<!DOCTYPE html>
<meta http-equiv="X-UA-Compatible" content="IE=edge" />
<html lang="en">
      type="text/javascript" i
<script type="text/javascript">

/* Set the diagrams Height & Width */
    var h = 500, w = 950;
/* Set the color scale we want to use */
    var color = d3.scale.category20();
/* Establish/instantiate an SVG container object */
    var svg = d3.select("body")
/* Build the directional arrows for the links/edges */
                    .attr("id", String)
                    .attr("viewBox", "0 -5 10 10")
                    .attr("refX", 15)
                    .attr("refY", -1.5)
                    .attr("markerWidth", 6)
                    .attr("markerHeight", 6)
                    .attr("orient", "auto")
                    .attr("d", "M0,-5L10,0L0,5");
/* Pre-Load the json data using the queue library */
    .defer(d3.json, "nodes.json")
    .defer(d3.json, "links.json")
/* Define the main worker or execution function */
function makeDiag(error, nodes, links, table) {
    /* Draw the node labels first */
   var texts = svg.selectAll("text")
                    .attr("fill", "black")
                    .attr("font-family", "sans-serif")
                    .attr("font-size", "10px")
                    .text(function(d) { return d.name; }); 
    /* Establish the dynamic force behavor of the nodes */
    var force = d3.layout.force()
    /* Draw the edges/links between the nodes */
    var edges = svg.selectAll("line")
                    .style("stroke", "#ccc")
                    .style("stroke-width", 1)
                    .attr("marker-end", "url(#end)");
    /* Draw the nodes themselves */                
    var nodes = svg.selectAll("circle")
                    .attr("r", 20)
                    .attr("opacity", 0.5)
                    .style("fill", function(d,i) { return color(i); })
    /* Run the Force effect */
    force.on("tick", function() {
               edges.attr("x1", function(d) { return d.source.x; })
                    .attr("y1", function(d) { return d.source.y; })
                    .attr("x2", function(d) { return d.target.x; })
                    .attr("y2", function(d) { return d.target.y; });
               nodes.attr("cx", function(d) { return d.x; })
                    .attr("cy", function(d) { return d.y; })
               texts.attr("transform", function(d) {
                        return "translate(" + d.x + "," + d.y + ")";
               }); // End tick func
}; // End makeDiag worker func

Much of the “.style” that’s going on can be moved into a css file. For newcomers to all this, I’ve chosen to leave it here to make tweaking simpler. So now you should have three files in the folder on your webserver.


You can now point your web browser at the folder on your webserver and behold your force layout diagram.

Top Tip for learning D3.js

WP 20 PostsI’m probably just a little slow on the update but it took me a long time (too long) to realize this key point –

To get productive with D3.js quickly, invest your time in coercing your data to match that of the examples. As opposed to coercing the example code to work with your data format.

Coercing your data requires use of tools you’re already familiar with (Excel, SQL, Shell Script  whatever). Staying in your comfort zone here will help (quickly) get something pretty into your DOM, which you can then hack on.

The alternative will have you pouring over lots of code and API documentation. The wonderful example code is often quite sophisticated and inaccessible to JavaScript novices and the API documentation is comprehensive but dense and somewhat terse.

While this effort is educational, it can be frustrating to get something working (and subsequently demotivating). When you’re starting out, quick and easy wins help enormously. Much motivation can come from these early dopamine responses (Ooh bar chart! Woot! Woot!).

Hence examples like this, this and this to expose D3 friendly data.

Again, focus your time on getting your data to match the examples rather than changing the examples to handle your data.

Oh, and buy Scott Murray’s, “Interactive Data Visualization” published by O’Reilly Media.

Hopefully this will help open up D3 for you more quickly than it did for me.

JSON from MS SQL Server with PHP Example

Someone much smarter than me, pointed out that my previous solution would hit a size limit (max cell width). This stored proc returns the JSON stream as a single table cell (albeit a big one). So their alternative solution is to do the JSON conversion on the webserver with PHP’s json_encode().

It works nicely.

EDIT: Don’t forget the UTF-8 encoding or you’ll experience random NULLs in your JSON.

/* Set Connection Credentials */
$uid = "";
$pwd = "";
$connectionInfo = array( "UID"=>$uid,

/* Connect using SQL Server Authentication. */
$conn = sqlsrv_connect( $serverName, $connectionInfo);

if( $conn === false ) {
     echo "Unable to connect.</br>";
     die( print_r( sqlsrv_errors(), true));

/* TSQL Query */
$tsql = "SELECT TOP 100 * FROM AtxJSON";
$stmt = sqlsrv_query( $conn, $tsql);

if( $stmt === false ) {
     echo "Error in executing query.</br>";
     die( print_r( sqlsrv_errors(), true));

/* Process results */
$json = array();

do {
     while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
     $json[] = $row;
} while ( sqlsrv_next_result($stmt) );

/* Run the tabular results through json_encode() */
/* And ensure numbers don't get cast to trings */
echo json_encode($json,<code> JSON_NUMERIC_CHECK</code>);
/* Free statement and connection resources. */
sqlsrv_free_stmt( $stmt);
sqlsrv_close( $conn);