SQL: Week 8 May 16, 2011

Topics

Chapter 8 of the textbook covers a variety of techniques that help optimize your use of SQL.   Some of the techniques are available in different ways in tools such as MySQL Workbench.

Now that we understand how to interact with SQL from webpages, our final project should be lots of fun  – creating a web dashboard using SQL and JQuery GVChart, a simple Javascript plugin developed by Janusz Kamieński that dynamically turns HTML tables into Google Data Charts.

I found that viewing the source code of the Demo Page of GVChart was a great resource for understanding how to structure the HTML tables and include the Javascripts in the webpage head section.

Assignments – Due May 23, 2011 at midnight

  1. Read and follow assignments in Chapter 8.
  2. Final Project 5: A SQL Web Dashboard.   Create a PHP webpage that displays two data charts using SQL and JQuery GVChart:
    1. a bar chart of total sales by vintage year.
    2. a pie chart of total sales by salesperson.
  3. Final Exam

12 comments for “SQL: Week 8 May 16, 2011

  1. seth schwebs
    May 16, 2011 at 6:48 pm

    Jay,

    When is this due?

    Seth

  2. May 16, 2011 at 8:13 pm

    The Final exam and project 6 and all projects are due May 23 at midnight

  3. Pla
    May 17, 2011 at 8:43 pm

    It looks like when I populate the table using SQL in PHP, the JQuery GVChart does not display the pie chart or any graph. If I hard code the data like in the demo, it works. Any idea what I am doing wrong? I will send you my chart.php file.

    • May 17, 2011 at 11:49 pm

      @Pla – I suggest you run the page without the graphs enabled so that you can see the tables and their HTML structure. The HTML structure should be identical to the examples on the example page at http://www.ivellios.toron.pl/technikalia/demos/gvChart/

      View the source of the example page to see the exact table structures. Also make sure the ID of the table is identical to the selector identified in the JQuery function.

      For example, a table with id=”graphme” should have the JQuery call as ..

      jQuery(‘#graphme’).gvChart({ …

  4. Pla
    May 18, 2011 at 1:12 pm

    I got the bar chart and pie chart to show up now.

  5. Francesca Rohr
    May 18, 2011 at 9:52 pm

    Hi Jay,

    I have downloaded the plugin. I have also created my webpage with the sql queries showing up as tables (Week8_Query1.php). I know I need to insert the javascript into my html code including a few extra lines referencing it, but I can’t open the javascript files to see what’s in them. Do I need to upload them to the server first? I can’t find any reference to how this step is accomplished.

    Thanks,
    Francesca

    • May 18, 2011 at 10:46 pm

      @Francesca – the best resource is to view the source code of the example page at http://www.ivellios.toron.pl/technikalia/demos/gvChart/
      This will provide insight on how the table structures should look and how to load the javascripts into the page (in the head) and how to create the activation code on the page to turn the tables into charts.
      There are 3 scripts to include, one is hosted at google.com, the 2 others need to be uploaded to your site.
      The activation code needs to be run for both charts – the jquery selector in the parentheses needs to reference the id attribute of the table it is graphing. I explained this in a comment above in response to Pla’s question.

      Bye the way, I will be presenting this tutorial in my Advanced Dreamweaver class tomorrow (Thursday May 19) at 6 in Maggini room 2803, you are all invited to sit in.

  6. May 19, 2011 at 8:22 pm

    Jay,
    The bar chart is very confusing. I have the charts appearing on my page but I had to hard code the tables to get them to work. The pie chart looks OK (hard coded) but how do you get the bar chart to display with different colors unless you use different tags for the vintage years? The color bars end up squished together at the top! Please see what I have at

    http://student.santarosa.edu/~anarbuto/sql/project6/index.php

    where all of the bars are the same color. Coding the tables is tricky and the information on the demo site very slight. Can we just use hard coded tables for this assignment?

    • May 19, 2011 at 9:41 pm

      @Anna – The gvCharts bar chart, which is based on Google Data Visualization (http://code.google.com/apis/chart/), charts each series in the same color, but can chart multiple data series in the same chart, each of which would be assigned a different color. You can specify your own colors for each data series, review the configuration options here:
      http://code.google.com/apis/chart/interactive/docs/gallery/barchart.html

      Since this is an SQL class, it isn’t appropriate to hard-code the tables, you need to learn the technique of embedding the data results from the SQL queries into the HTML tables. This is explained on pages 500-501 in the textbook, although the example in the book needs to be modified so that the table structure matches the structure that the gvChart is looking for. The charts are really just the icing on the cake to make the project more interesting visually and to provide insight into the power of web-based SQL combined with JQuery capabilities in the browser.

  7. Francesca Rohr
    May 19, 2011 at 9:31 pm

    Hi Jay,

    Okay, so I’m not an html person but I’ve looked at the source code for my page and the example page. Here’s the problem, as far as I can see it – where does the php code with the sql query go – is it in tbody, thead, or before both of them after table? I could hard code it like the others but I’m not going to. I couldn’t come to your class tonight.
    Thanks,
    Francesca

  8. May 19, 2011 at 9:44 pm

    @Francesca – Pages 500-501 in the textbook provide a good code example for how to embed SQL query results into an HTML table. You would need to modify somewhat for the gvChart structure, but the book should give you a good overview of the concept.

  9. May 20, 2011 at 9:31 am

    Thanks. Once I got the feel for how the charts were formed the coding wasn’t very difficult.

Leave a Reply