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.

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);