MySQL - For each Row Print Columns/Flags with value equal to 1

One of my colleague came to me with this problem. It enjoyed solving it. Here, it is for you. Say, you are given a table like this with a given number of columns.

   id  Flag_1  Flag_2  Flag_3  Flag_4  Flag_5

------  --------  -------  --------  --------  -------

    1       1       1       1       1         1

    2       0       0       0       0         0

    3       1       1       0       1         0

    4       0       1       0       0         1

    5       1       0       1       0         1

Desired output is to have a comma separated list of "Flag Names" that are ON for each row. i.e.

   id  onFlags                             

------  ------------------------------------

    1  Flag_1,Flag_2,Flag_3,Flag_4,Flag_5  

    2                                      

    3  Flag_1,Flag_2,Flag_4,               

    4  Flag_2,Flag_5                       

    5  Flag_1,Flag_3,Flag_5   

It's not a generally required to print column names as a result of query. One easy solution is to fetch data in sql and then use some programming language to manipulate it. However, try running sql only to fetch it. I recommend to minimize the browser and try it on your own.

=============================================

SELECT

 id, CONCAT(flag1, flag2, flag3, flag4,flag5) AS onFlags

FROM

 (SELECT

   IF(Flag_1 = '1', 'Flag_1,', '') AS flag1,

   IF(Flag_2 = '1', 'Flag_2,', '') AS flag2,

   IF(Flag_3 = '1', 'Flag_3,', '') AS flag3,

   IF(Flag_4 = '1', 'Flag_4,', '') AS flag4,

   IF(Flag_5 = '1', 'Flag_5', '') AS flag5,

   id

 FROM

   Flags) AS a ;

Developing a Progressive Web App - MnLite

Introduction:

There is a lot of work that has been done in the web development in recent years but when it comes to experience on mobile it is defined by native apps. So the latest trending web technology i.e Progressive Web Apps, pushing heavily over the last few months to give a similar feel like native apps. It combines the ease of web development and gives App-like experience to the user. Icing on the cake is that it also works in offline and on slow internet connections. It uses web's newest features like service worker, App Shell, indexDB and others.

Technology Stack:

Frontend:

• React
• React-router
• react-swipeable-views
• Material-ui
• Node-fetch (to support IOS)

Build Tools:

• NodeJs
• Browserify
• Uglify-js

Web Features:

• Service worker
• Sw-toolbox

Service Workers:

It provides offline capabilities for the app, push notification, content caching and others. Service worker act as a proxy server between client and remote server. It is a script that runs in the background at browser independent of the app. It listens all the network requests that are in it's scope. The network requests can be intercepted and can be cached which helps it work in offline mode. It also supports push browser notifications. We worked out to run our app even without service worker for the browsers that do not support it yet. In such cases it executes plain ReactJS code in browser.

App Shell:

It is a minimal content of js and css which gets rendered until API’s responses are loaded. App shell can be a placeholder for the content that has to be loaded. With the help of service worker and its caching APIs the app shell page can be displayed offline. It removes the white screen for the user that comes before page loading and gives a better experience.

Implementing React and React Flux: 

From a developer's perspective if the code is written modular fashion it is easier to debug and maintain. In react we can divide the UI into components and reuse them. My advice is to make components as small as possible. Below is how we divided our screen into components.

Very less documentation on react-flux were found in starting days. React flux has three major components Dispatcher, Store, and the View (React components). API calling code is written in actions which invoke the dispatcher after getting response from server, which will update the store variables and the change will get reflected in react views for that variable. For first time user it becomes little difficult to catch how the UI will get updated when store variables gets changed in react flux. It is having a unidirectional flow which means if dispatcher is already handling some event and another request get triggered in meanwhile it won’t accept and will throw an error(‘can not dispatch elements in the middle of dispatch’) . However,to avoid this we can use waitFor().

Project Challenges:-

1. Moving to HTTPS:- Here at www.meritnation.com, we first implemented our Textbook Solutions feature with new methodology. And, in order to use service worker we needed to serve the requests in https. We created new https subdomains for serving images/js/css calls. Initially we planned to move our entire mobile site on https. However, looking at the increase in Connection Time for other pages, we wisely decided to move only relevant module to https. However, running half a site on https and rest on http has its own issues eg.

• Cookies set on https are not passed to http. We had to remove our cookie dependent code.
• Referer is not passed when switching from https to http. Here, meta referrer tag came to our rescue.

   <meta name="referrer" content="always">

• Service worker essentially works on https only. Though it allows us to use “localhost” domain during development. However, in order to test our new app on mobile device we required DNS change. As changing the IP of localhost is not advisable, we needed original SSL certificate setup.
• We were making API calls to our parent domain that runs on http. We configured our domain to serve on both http and https. To avoid CORS errors of cross origin access, we did below setting on local apache server.

  Header set Access-Control-Allow-Origin "http://localhost"

2. Server Side Rendering:- To render a page through service worker bundle.js has to imported. When a request comes to service worker it checks whether it is having defined route for that url or not. All API calls must be placed in componetDidMount method and, the same script has to be executed on both client and server side. To facilitate the server side rendering:-

• Use “match” to match the routes to a location without rendering
• RouterContext for rendering of route components

Client Side:

render(<Router history={history} routes={routes} />, mountNode)

Server Side:

match({ history, routes }, (error, redirectLocation, renderProps) => {

renderToString(<Router {...renderProps} />, mountNode)
})

3. Versioning System:- As we are saving the url responses on service worker, so the data served has to be updated when there is any backend or frontend change. We are using sw-toolbox for fetching the url responses and storing them in indexedDb in cacheFirst mode, so if same request comes again to service worker, it should get served from indexedDb.

• Version for APIs: We included version number in the API url.
• Version for bundle.js: Updating version number in bundle.js url is not sufficient as the file request with particular version number has been cached with server worker. In order to ensure that bundle.js gets updated in service worker, we need to set max-age header 0 for worker.js

<FilesMatch "\worker.js$">

<ifModule mod_headers.c>

Header set Cache-Control "max-age=0"

</ifModule>

</FilesMatch>

4. Filtering Urls in Service Worker:- Service worker intercepts all the requests that are falling in it’s scope. As we are registering worker on entire mobile domain, there are some modules whose requests should get filtered out so that they are not intercepted. We applied regex rules for particular requests that should go in service worker. Passing urls that are not  handled in react-router used to give Router Context error.

5. Implementing Swipe:- There was a requirement that data should be swipeable. To implement this we used react-swipeable-views library but there are certain issues that we faced while using it. :-

• If number of slides increases the swiping becomes very tedious.
• If the content in a slide has scroll width then its horizontal scroll won’t work. There is no facility to distinguish between swipe and scroll.
• On mozilla, swiping causes auto scroll of all the slides

          

6. Issue on Safari:- To get the webservice’s response, fetch Api is used which is not currently supported in safari. Problem was fixed by using node-fetch.

7. Storage Space Limit of IndexDB:- 1/3rd of the available space of the /home directory becomes a shared pool to be used by all apps. An individual app can use up to 20% of the shared pool.

Limit of IndexDb is ~ 0.2*(Available space/3)

You can confirm this using.

window.webkitStorageInfo.queryUsageAndQuota(webkitStorageInfo.TEMPORARY,

function(used, remaining) {

 console.log("Used quota: " + used + ", remaining quota: " + remaining);

}, function(e) {

 console.log('Error', e);

} );

8.Offline Google Analytics:- We did not want to loose our GA tracking of offline visits. We queued the visits and played them back once the network becomes reachable.

Reference:https://developers.google.com/web/showcase/2015/service-workers-iowa?hl=en#offline_google_analytics

9. Attempt at Caching Constructed Page in Service Worker:- In the initial phase of the project we tried to cache the complete constructed page in service worker. We were calling APIs in ComponentWillMount. And, the final page with all the DOM updations used to get cached in service worker. It has its own downsides e.g. It was not progressive any more as in it used to display white page unless all the API’s response have been received. Also, as we were running our entire frontend logic in ComponentWillMount, it used to get executed in service worker context. As service worker does not allow DOM access, It caused various “document not defined” errors.

Progressive Web Apps are a new era of web development. We are excited to learn and do more on it. Feel free to reach us in case any doubts.

Authors: Charanjeet Kaur, Prashant Singh

Web Development examples to begin with

So you are beginning to learn web development. Its a cool thing. and, I believe everyone must know coding and below are basic essential programs to begin your journey. If you are expertized in any of the coding language, you will find these niche. I am currently taking php training sessions for some of my colleagues. so, this list is like a note for me too :)

1. Implement basic CRUD operations. i.e. develop interfaces to Create, Read, Update and Delete data in a table say, Users.
2. Implement User Search. Give a username input box and a submit button. On submit, display all records with given user name.
3. Change above example such that it does not refresh webpage upon hitting submit button. ie use ajax and update table data.
4. Change above example such that username search is interactive i.e. ajax call is fired upon entering each character.
5. Implement an interface to display data sorted in descending order of User record entry time. Also, implement pagination with N number of rows per page.
6. Implement an interface that allows sorting each column in ascending/descending order. Sorting can be of two types
 i) sort the currently visible rows
 ii) sort the entire data set

Get going.

As a next step in backend, learn MVC frameworks e.g. Symfony, CakePHP, CodeIgnitor for PHP.
As a next step in frontend, learn MVC frameworks e.g Angular, Backbone, YUI, ReactJS

mysql - explaination of dealing with latest records of groups in a table

I will be covering three problems here

1. Fetch Latest record of each group
2. Fetch N Latest records of each group
3. Delete all records of a group except the Latest ones.


Say we have a table Products as

First, We wish to write a query that fetches the latest product shipped by each supplier. After scratching head for a while, I could find two ways to do it.


1. Write a Nested Query:

Fetch the results grouped by SupplierId and for each group, pass the ProductID that is maximum for that group. Query for this would be.

select *
from Products as a
where ProductId = (select MAX(ProductId)
                                from Products as b
                                where a.SupplierID = b.SupplierID)
group by SupplierID


Below image shows the execution for this query.

2) Self Join:

Another way is to perform a left join of the Products Table with itself. We will have to place two conditions on join. First will  be on SupplierID, as we want to find the latest product by each supplier and second would be on ProductID with the constrain that first table's productID is lesser than second. Doing so, the maximum ProuductID in table 1 will not be matched to any in table 2.

For example, if we have 3 products for a supplier as
-----------------------------------
|ProductID | Supplier ID|
-----------------------------------
|       1         |      1      |
|       2         |      1      |
|       3         |      1      |
-----------------------------------
If we perform a self join with the conditions that their Supplier Ids are equal and first table's ProductID is lesser than second , we will get
---------------------------------------------------------------------------
| a.ProductID | a.SupplierID | b.productID | b.supplierid |
---------------------------------------------------------------------------
|        1           |         1            |        2           |       1      |
|        1           |         1            |        3           |       1      |
|        2           |         1            |        3           |       1      |
|        3           |         1            |      NULL    |    NULL     |
---------------------------------------------------------------------------
Now, If we place where clause saying that b.productID should be NULL, we will get the desired result.


select a.*
from Products as a left join Products as b on (a.SupplierID=b.SupplierID
                                                                        and a.ProductID < b.ProductID)
where b.ProductID IS NULL


Below image shows the execution for this query.

Fetch N Latest Records

Similarly, in order to fetch N latest records we can write

select a.*
from Products as a left join Products as b on (a.SupplierID=b.SupplierID
                                                                        and a.ProductID < b.ProductID)
group by b.ProductID
having count(*) < 2

Below image shows the execution for this query.

Delete records except latest in each Group

Continuing the above logic, query for deletion would be

delete
from Products  as a left join Products as b on (a.SupplierID=b.SupplierID AND a.ProductID < b.ProductID)
where b.ProductID IS NOT NULL

How to change timezone of server Fedora/Ubuntu/Linux

 ln -sf /usr/share/zoneinfo/Asia/Calcutta /etc/localtime


In place of Asia/Calcutta, select the time zone you want to set.

Mysql - alter a table column to make it auto increment

Mysql : alter a table to make one of its column auto increment.


Query :




mysql> ALTER TABLE  WAREHOUSE MODIFY COLUMN WarehouseId INT(11) AUTO_INCREMENT;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key



i.e. in order to define a column as auto increment it must be set as primary key.

mysql> ALTER TABLE  WAREHOUSE MODIFY COLUMN WarehouseId INT(11) AUTO_INCREMENT PRIMARY KEY;

Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0

PHP - find biggest number in a array where array can also have nested arrays

PHP -Write a program to find biggest number in an array where array can also have nested arrays.

For example given an array like
Input : (1, 0, 6, 9, array(100, -1, 10, 7), 40, array(101, array(120, 240), 180), 200)

that can contain numbers and nested arrays containing numbers, we need to return the maximum number.

Output : 240


Program:


<?php

$inputArray = array(1, 0, 6, 9, array(100, -1, 10, 7), 40, array(101, array(120, 240), 180), 200);
echo findBiggest($inputArray);

function findBiggest($inputArray)
{
        static $biggest = 0;
        if(is_array($inputArray))
        {
                foreach($inputArray as $arr)
                {
                        findBiggest($arr);
                }
        }
        else
        {
                if($inputArray > $biggest)
                        $biggest = $inputArray;
        }
        return $biggest;

}

Weird comparison of a string in PHP

Weird comparison of a string in PHP

Say there is a dropdown named message as
<select name='message'>
<option value=0>Please select Message</option>
<option value='decoded'>Decoded</option>
<option value='processing'>Processing</option>
</select>


Now I want to validate the form and throw an error if user dint selects anything. What I wrote is.


if($message == 0)
{
echo "Error!!!";
echo "Message is not yet received";
}



Can you see anything wrong in this code?
What's happening is, for all selected values of message drop down, it throws an error!!!

it's quite surprising ... :-O


Any guesses on why is it happening...


Let me tell you...
Reason is that while comparing $message with 0, where 0 is an int, php converts string to int. that is it compares
(int) $message to 0.



To my full surprise, interger casting of any string yields 0. Which is why for all selected values, the comparison gets bypassed.


Solution to this is.

if($message == '0')
{
echo "Error!!!";
echo "Message is not yet received";
}