Sql query to draw a grouped bar chart with Jpgraph

Question

Used Jpgraph to draw a grouped bar chart by taking Sql table values. Here is my table from which the graph should be drawn. (Not all data are displayed)

 ----------------------------------------
 |               student                |
 ----------------------------------------
 | user_ID                |  category   |
 ----------------------------------------
 |   MPhil/FT/2011/021    |  Full Time  |
 |   MPhil/PT/2013/011    |  Part Time  |
 |   MPhil/PT/2012/015    |  Part Time  |
 |   MPhil/FT/2012/024    |  Full Time  |
 |   MPhil/FT/2013/026    |  Full Time  |
 |   MPhil/PT/2011/030    |  Part Time  |
 ----------------------------------------

A grouped bar chart be drawn from this where the x-axis shows the year 2011, 2012, 2013 etc in the user_ID and for the category type 'Full Time' or 'Part Time'. For example, for no.of user_IDs which have year as 2011 two bars should be drawn separate for 'Full Time' count and 'Part Time' count. And then for year 2012 etc. How to write the SQL query for this? Can i use 'LIKE' to get the year? To take Full time and Part Time count to draw a simple bar chart, the SQL query is;

"SELECT count(*) AS num_rows, category FROM student WHERE category ='Full Time' or category= 'Part Time' GROUP BY category"

How to change this query to draw a grouped bar chart as explained above? How to get the year in user_ID and Full Time, Part time counts according the year? (There are more years, not only 2011,2012,2013 )

Dakshila Kamalsooriya Dakshila Kamalsooriya · Accepted Answer · 2013-11-21T00:38:31

Found the solution. Wrote two queries to get no. of students related to full time in each year and no of students related to part time in each year.

$sql = mysql_query("SELECT count(*) AS num_rows,LEFT(Right(user_ID,8),4) FROM student WHERE category ='Full Time' GROUP BY LEFT(Right(user_ID,8),4)") or die(mysql_error());
while($row = mysql_fetch_array($sql))
{
$data1[] = $row['num_rows'];
$leg[] = $row['LEFT(Right(user_ID,8),4)'];
}

$sql2 = mysql_query("SELECT count(*) AS num_rows,LEFT(Right(user_ID,8),4) FROM student WHERE category ='Part Time' GROUP BY LEFT(Right(user_ID,8),4)") or die(mysql_error());
while($row2 = mysql_fetch_array($sql2))
{
$data2[] = $row2['num_rows'];
$leg[] = $row2['LEFT(Right(user_ID,8),4)'];
}

So here is half of the code of grouped bar graph

$bplot1 = new BarPlot($data1);
$bplot1->SetFillColor("red"); 
$bplot2 = new BarPlot($data2);
$bplot2->SetFillColor("blue");

Sql query to draw a grouped bar chart with Jpgraph

1 Answers