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
- Read and follow assignments in Chapter 8.
- Final Project 5: A SQL Web Dashboard. Create a PHP webpage that displays two data charts using SQL and JQuery GVChart:
- a bar chart of total sales by vintage year.
- a pie chart of total sales by salesperson.
- Final Exam
Jay,
When is this due?
Seth
The Final exam and project 6 and all projects are due May 23 at midnight
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.
@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({ …
I got the bar chart and pie chart to show up now.
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
@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.
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?
@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.
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
@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.
Thanks. Once I got the feel for how the charts were formed the coding wasn’t very difficult.