Posts

Creating HTML Tables from SQL

Image
If you are like me you have probably come across this scenario You have wasted your precious time developing a super nice and complex select query to fetch your data from an RDBMS, but you have been doing this in a database client and your SQL is working pretty well. Cute, now you need your application like PHP to run this SQL and produce and HTML table that you should echo to your user. Great! Just when you thought the codding job was done now you need to start referencing php syntax aswell and this code takes you all morning to write since you keep confusing SQL syntax and PHP syntax 😠😠 Well you can get on the internet of things and use a php code like this one here  by David Walsh, or if you are crazy like me you can make your sql write the HTML code for you so you just simply echo the result to your browser. Lets begin: 1. The Data Set: we first of all need to get the data set that we want to convert into an HTML table. This is simple enough we just run our...
Post a Comment
Read more

SQL Pivot Tables

Image
So i am a data analyst and my job in what i would like to call data mining exercises entails cross referencing large data sets that have little to no structured relationships. So i find myself writing huge complex select queries that at some point have to pivot the data around just to prepare it to be cross referenced with another complex query. Lucky for me its not much of a hustle when working with Oracle and Microsoft SQL server. The moment we start talking about MySQL, boy oh boy... that's a totally different story. There are workarounds to achieve this. One famous solution on the internet goes like this: Assuming we have a table that keeps track of student test scores like the one shown in the figure to the left. Lets also assume that we want to pivot this data by year. Thus our rows will have the student name and subject, our columns would be the respective year and the data cells would contain the average score.  The solution is to create computed column...
Post a Comment
Read more

Flexible Data Insertion Procedure With JSON Datatype

Image
For a long time now I have been laughing at these guys who are all hyped up about No-SQL databases. I have always thought that why bother handling data objects that you cannot determine their structure in the first place? That sort of thing should be left for application servers to handle the JSON and XML tangled mess. We should just leave SQL for 'Structured Relational Data', but with time many famous RDBMS' like Oracle, MySQL and Microsoft SQL Server started to introduce support for JSON data objects and I thought to myself '"OMG! Not these guys too  ". It seemed like everyone was going this direction, so afraid of being left in the stone age, I packed my travel bag  (*just an expression, totaly just tried out various things on my pc*)  and embarked on a quest to find the ultimate application for these pesky JSON rodents. So what is the advantage of non-tabulated data objects like JSON... Well the most obvious one is that they are strings so you can pas...
Post a Comment
Read more