Row to Column in MySQL

Say you have two tables like these:

) ENGINE = 'InnoDB';
CREATE TABLE user_attributes(
attribute_name VARCHAR(255) NOT NULL,
attribute_value VARCHAR(255) NOT NULL,
UNIQUE KEY (user_id, attribute_name)
) ENGINE = 'InnoDB';

And you have these values:


To get a result like this:


Use this query

ANY_VALUE( as name,
GROUP_CONCAT(IF (ua.attribute_name = 'GENDER', ua.attribute_value, NULL)) AS 'GENDER',
GROUP_CONCAT(IF(ua.attribute_name = 'PREMIUM_USER', ua.attribute_value, NULL)) AS 'PREMIUM_USER',
FROM user u
JOIN user_attributes ua ON ( = ua.user_id)

Dbfiddle link:

PHP openssl_encrypt tip

Recently I had to encrypt some data in PHP and send it to a Java App, the Java app was unable to decrypt the message.

I experimented with (data) padding, changing ciphers and changing the options for openssl_encrypt, but, none of those worked.

It was a requirement at the Java end for the Key to be hashed (sha256) and then used for encryption. The thing that worked for me was converting the hexed key to binary and using that.

$iv = 'aBCaDU9phtMwtNeV';
$key = 'B47C5126B42C9E192FAEAA5AA1892136';
$string ='testtesttesttest';

openssl_encrypt($string, "AES-256-CBC", hex2bin(hash('sha256', $key)), 0, $iv);
=> "E5vjBpIdWXo2NNuXkPzsEDVX6YVR3oFvHDwX+LohRsg="

# Bash
# >>> bin2hex('aBCaDU9phtMwtNeV')
# => "614243614455397068744d77744e6556"
# hash('sha256', 'B47C5126B42C9E192FAEAA5AA1892136')
# => "58754fcb239dfd17dfba62da3a57556980c69158d23dae6a1c24a174afeb676c"
printf %s "testtesttesttest" | openssl enc -e -aes-256-cbc -base64 -K 58754fcb239dfd17dfba62da3a57556980c69158d23dae6a1c24a174afeb676c -iv 614243614455397068744d77744e6556

Using Generators to flatten a JSON doc in PHP

To flatten a JSON like this:

   "addresses" : [
       "line1" : "123 Livingstone Rd",
       "id" : 23444555,
       "addressSummaryType": "Building",
       "subAddresses": {
         "Level1": [
             "line1" : "Level 2",
             "id" : 266887373,
             "Level2": [
                 "line1": "Suite 201",
                 "id" : 276888890
       "addressSummaryType": "Building",
       "line1" : "124 Livingstone Rd",
       "id" : 2562672


array(2) {
  string(18) "123 Livingstone Rd"
array(2) {
  string(26) "Level 2 123 Livingstone Rd"
array(2) {
  string(36) "Suite 201 Level 2 123 Livingstone Rd"
array(2) {
  string(18) "124 Livingstone Rd"

I used the following code:

foreach(flattenAddress($addressObj->addresses) as $flattenedAddress) {

 function flattenAddress($addresses, $baseAddress = '', $level = 0)
     foreach ($addresses as $address) {
         $nextLevel = "Level" . ($level + 1);
         if (property_exists($address, "addressSummaryType")) { //root
             $baseAddress = $address->line1;
             yield ["id" => $address->id, "address" => $baseAddress];
             if (property_exists($address, "subAddresses")) {
                 yield from flattenAddress($address->subAddresses->{$nextLevel}, $baseAddress, ($level + 1));
                 continue; // recurse don't reset level
         } else { //subaddresses
             $subAddress =  $address->line1 . " " . $baseAddress;
             yield ["id" => $address->id, "address" => $subAddress];
             if (property_exists($address, $nextLevel)) {
                 yield from flattenAddress($address->{$nextLevel}, $subAddress, ($level + 1));
                 continue; // recurse don't reset level
         $level = 0; //reset for next root

Add extra validation rules to Laravel ForgotPassword

I wanted to add a captcha to the Forgot Password form, so I ended up doing this to the default ForgotPasswordController:

use Validator;
use \Illuminate\Http\Request;
class ForgotPasswordController extends Controller {
protected function validateEmail(Request $request)
            'email' => 'required|email',
            'captcha' => 'required|captcha', // extra param to be validated

Only relevant bits are shown there.

ES6 support on iOS9

Recently while building a Cordova based app, few users complained that they were seeing blank pages or cryptic error messages. Someone mentioned they were on iOS9. Over the weekend, as I ran the app on a simulator, I noticed an error in Safari. And, that’s when I realized ES6 features and syntax is not fully supported in iOS9.¬† Please see here: ES6 Compatibility (check unstable platforms)

So be-aware of ES6 features compatibility (unless you can transpile/polyfill every bit of code)

Jenkins parallel pipeline

I was very excited to know about the ‘parallel’ feature in Jenkins Pipeline, but, there are many gotchas while making¬† use of the pipeline feature (many of which are documented here: Jenkins Pipeline Example). After trying and reading a few different solutions, following worked for me (notice in screenshot that the browser jobs run in parallel !)

pipeline {
  agent any
  stages {
    stage('Build') {
      steps {
        echo 'hello'
    stage('Test') {
      steps {
        script {
            def jobs = [:]
            def browsers = ["Chrome", "Firefox"]
            for (int i = 0; i < browsers.size(); i++) {
                def browser = browsers.get(i)
                def jobName = "printing $browser"
                jobs[jobName] = doJob(browser)
            parallel jobs
    stage('Deploy') {
      steps {
        echo 'deploying'

def doJob(browser) {
    return {
        node {
            echo "testing in $browser"

HHVM notes

  • Impressive throughput improvements (>100%) with the app that I am working on.
  • phpinfo() doesn’t output what you would expect.
  • xhprof output_dir doesn’t get read from ini files, need to set that up in the constructor of XHProfRuns_Default.
  • Set hhvm.server.thread_count to a high value (>=MaxRequestWorkers), otherwise a few slow MySQL queries could bring the server to halt, minimal doc here: HHVM server architecture (worker thread => hhvm.server.thread_count). Suggest to keep it higher while JITing is happening.
  • If using Newrelic, tough luck!
    Unofficial Newrelic HHVM extension uses XHProf internally, so cannot get any data out of your own XHProf usage.
    The extension above relies on agent SDK that has no support for MySQL slow traces.
    Very low MySQL time in transactions.
    Strange traces in transactions.
  • CGI differences (apache_getenv not available use $_SERVER, SCRIPT_NAME will not be the same as REQUEST_URI).
  • Use realpath in imageftbox, relative paths for fonts don’t work.
  • Use Apache 2.4 as it has FastCGI support.
  • hhvm.log.header = true to have datetime in hhvm log.
  • HHVM log will also contain slow sql.
  • .hhbc was getting very huge, turned out it was due to Smarty file caching being enabled (the cached files were themselves php files that HHVM was compiling).
  • .hhbc file is sqlite(3) file that one can query (that is how I worked out the above).
  • High timeout values in memcached was leading to very high System CPU usage.
  • @ wasn’t suppressing (this could be Newrelic related)
  • Friendly folks in the hhvm IRC channel (get link from HHVM homepage), need to be online during daytime in the US.

casperjs output to html

Documenting what I had to do.

Used XSLT from here:
nosetest xslt

Problems and Fixes:

  1. Firefox was inserting “transformiix” as the root element, this caused the DOCTYPE to be spit out. I fixed by adding:
    doctype-public="-//W3C//DTD HTML 4.0//EN"/

    And removed the doctype declartion in the above xslt

  2. My version of casperjs was setting xml namespace to


    After unsuccessfully trying to match the namespace in the xslt, I gave up and removed the namespace from the xunit xml, by

    sed -i 's@ xmlns="" @ @' "output.xml"
  3. Inserted the style into xml so can be rendered in the browser

    sed -i 's@ encoding="UTF-8"?>@ encoding="UTF-8"?><?xml-stylesheet type="text/xsl" href="nosetests.xslt" ?>@' "output.xml"
  4. Removed timestamp info from the xslt as it was in UTC timezone and could be very confusing when looking at the results (attempts to convert to my TZ in client were unsuccessful)

Backgrounded PHP jobs in ‘STOPPED’ state

This could be specific to the PHP package from Ubuntu. Please consider the following a disclaimer.

$ php -v
PHP 5.3.5-1ubuntu7.2 with Suhosin-Patch (cli) (built: May  2 2011 23:00:17) 
Copyright (c) 1997-2009 The PHP Group
Zend Engine v2.3.0, Copyright (c) 1998-2010 Zend Technologies
    with Xdebug v2.1.0, Copyright (c) 2002-2010, by Derick Rethans
$ cat /etc/lsb-release 

I have a file with the following contents:

$ cat stdin.php
echo microtime(), "n";

and I was doing something like this (backgrounds the script. I was using a slightly modified version to make concurrent SOAP requests)

$ for i in `seq 1 2`; do echo $i; sleep 2s; php /tmp/stdin.php &gt;&gt; /tmp/k7.out & done
$ jobs
[1]-  Stopped                 php /tmp/stdin.php &gt;&gt; /tmp/k7.out
[2]+  Stopped                 php /tmp/stdin.php &gt;&gt; /tmp/k7.out

There are a few threads on the Internet relating to the issue and there are a few solutions.
First let us kill the ‘STOPPED’ jobs

$ kill %1
$ kill %2
[1]   Terminated              php /tmp/stdin.php &gt;&gt; /tmp/k7.out
$ jobs
[2]+  Terminated              php /tmp/stdin.php &gt;&gt; /tmp/k7.out

One of the solutions that worked for me was by supplying something to STDIN (perhaps STDIN was blocking, but then again, stream_set_blocking (STDIN, 0) wasn’t of much help).

$ for i in `seq 1 2`; do echo $i; sleep 2s; php /tmp/stdin.php &gt;&gt; /tmp/k7.out &lt; /dev/null & done

and you could simulate arguments

$ for i in `seq 1 2`; do echo $i; sleep 2s; echo arg1 arg2 | php /tmp/stdin.php &gt;&gt; /tmp/k7.out & done
$ tail -f k7.out
0.82262900 1316606606
array(2) {
  string(4) "arg1"
  string(4) "arg2"
0.83546500 1316606608
array(2) {
  string(4) "arg1"
  string(4) "arg2"