## Using FusionCharts with RoR > Plotting data from a database
In this section, we'll show you how to use FusionCharts and Ruby to plot charts from data contained in a database. We'll create a pie chart to show "Production by Factory" using dataXML method. For the sake of ease, we'll use an MySQL Database. You can, however, use any database with FusionCharts including MS SQL, Oracle, Access etc. Database configuration will be available here Download Package >> RoR >> config >> database.yml. In the production version, we have used database named as fusioncharts. Before you go further with this page, we recommend you to please see the previous section "Basic Examples" as we start off from concepts explained in that page.
All code discussed here is present in Controller : Download Package > Code > RoR > app > controllers > dbexample_controller.rb. Rhtml : Download Package > Code > RoR > app > views > dbexample folder.
Database Structure
Llet's quickly have a look at the database structure.
The database contains just 2 tables: Factory_Master: To store the name and id of each factory Factory_Output: To store the number of units produced by each factory for a given date. For demonstration, we've fed some dummy data in the database. Let's now shift our attention to the RoR that will interact with the database, fetch data and then render a chart.
Building the Ruby for dataXML Method
Controller: dbexample_controller.rb Action: basicdbexample class DbExampleController < ApplicationController def basicdbexample #In this example, we show how to connect FusionCharts to a database. #For the sake of ease, we've used an MySQL . It just contains two tables, which are linked to each other. animateChart = params['animate'] #Set default value of 1 if animateChart="" animateChart = "1" end #Database Objects - Initialization oRs='' oRs2='' strQuery='' #strXML will be used to store the entire XML document generated strXML='' #Create the recordset to retrieve data #Generate the chart element strXML = "<chart caption='Factory Output report' subCaption='By Quantity' pieSliceDepth='30' showBorder='1' formatNumberScale='0' numberSuffix=' Units animation='" + animateChart + "'>" #Iterate through each factory oRs = Factorymaster.find(:all) oRs.each do |recordset| #Now create second recordset to get details for this factory @oRs2 = Factoryoutput.find(:all,:conditions=>["FactoryId=?",recordset.FactoryId.to_s]) recordcount = @oRs2.length count = 0 quantity = 0 while count < recordcount quantity = quantity + @oRs2[count][:Quantity].to_i count = count + 1 end #Generate <set label='..' value='..'/> factoryid = "" @oRs2.each do |recordset2| if factoryid != recordset2.FactoryId strXML = strXML + "<set label='" + recordset.FactoryName + "' value='" + quantity.to_s + "' />" end factoryid = recordset2.FactoryId end end #Close recordset #Finally, close <chart> element strXML = strXML + "</chart>" #Create the chart - Pie 3D Chart with data from strXML @chart=renderChart("/FusionCharts/Pie3D.swf", "", strXML, "FactorySum", 600, 300, false, false) end end View: <HTML> <HEAD> <TITLE> FusionCharts - Database Example </TITLE> <SCRIPT LANGUAGE="Javascript" SRC="/FusionCharts/FusionCharts.js"></SCRIPT> </HEAD> <BODY> <%= @chart%> </BODY> </HTML>
The following actions are taking place in this code: We first include FusionCharts.js JavaScript class Thereafter, we generate the XML data document by iterating through the recordset and store it in strXML variable. Finally, we render the chart using renderChart() method and pass strXML as dataXML. When you now run the code, you'll get an output as under: