retrieving results from database to generate a graph.

furrls · December 12, 2010

Hello people, currently i ran into some problem.

Currently, i have a database called responses which have the fields of ID, Student_id, question_id, Answer.

I had stored my results into the database which appeared to be 1 1 1 Agree

2 1 2 Disagree

3 3 4 Unsatisfied.

So any recommendation on how should i do about in generating the result into a graph whereby the graph will show how many students choose "AGree" on that particular question THANKS

litebearer · December 12, 2010

http://articles.techrepublic.com.com/5100-10878_11-5475046.html

http://www.qualitycodes.com/tutorial.php?articleid=20&title=How-to-create-bar-graph-in-PHP-with-dynamic-scaling

http://www.builderau.com.au/program/php/soa/Using-GD-functions-in-PHP-to-create-a-bar-chart/0,339028448,339200806,00.htm

furrls · December 12, 2010

if (isset($_POST['Submit'])) {
$question = $_POST['question'];


$SQL = "SELECT student_id, question_id, COUNT(answer) from responses WHERE question_id='".$question."'";
$result = mysql_query($SQL)or die("ERROR: $query. ".mysql_error());
$total = 0;
while($row = mysql_fetch_assoc($result)){
//echo "There are ". $row['COUNT(answer)'] ." for ".$row['question_id']."	";
//echo "<br />";
$total = $row['COUNT(answer)'];



}
echo "There are ".$total." of students taking question ".$question.".<br/>";
unset($SQL);
unset($result);
unset($row);
unset($total);

$SQL = "SELECT question_id, answer, COUNT(student_id) AS TOTAL FROM responses WHERE question_id = '".$question."' GROUP BY answer";
$result = mysql_query($SQL) or die("ERROR: $query. ".mysql_error());
$total = 0;
while($row = mysql_fetch_assoc($result)){

$total = $row['TOTAL'];
echo "There are ".$total." answered ".$row['answer']." in question ".$question.".<br/>";
}


   
}

?>
<html>
<form action="test.php" method="POST">
<tr>
<td>
<?php
$sql_result=mysql_query("select question_id, question from stu_satisfaction_tblquestions order by question_id asc");
    echo "<select name='question'>"; 
    echo "<option size =30 selected>Select</option>";
    if(mysql_num_rows($sql_result)) 
    { 
     while($row = mysql_fetch_assoc($sql_result)) 
     { 
     echo "<option value=$row[question_id]>$row[question]</option>"; 
     } 

    } 
    else {
     echo "<option>No Names Present</option>";  
    } 
    ?>
</td>
</tr>
<tr>
<td><input type="submit" name="Submit" value="Select" />
</tr>
</form>
</html>

currently, i created a dropdown box to view each question specifically. Now i can get to view the total number of students that did the survey and how many student chose the different options in each question. So how i'm going to generate a graph from here.

BlueSkyIS · December 12, 2010

http://articles.techrepublic.com.com/5100-10878_11-5475046.html

http://www.qualitycodes.com/tutorial.php?articleid=20&title=How-to-create-bar-graph-in-PHP-with-dynamic-scaling

http://www.builderau.com.au/program/php/soa/Using-GD-functions-in-PHP-to-create-a-bar-chart/0,339028448,339200806,00.htm

litebearer · December 12, 2010

This first piece of code may help (roughly hammered together from the links I posted above) .

The second can be run as a simple test file to see how the graph could/would appear (see link for example)

<?PHP
/*
presuming a db table as such
ID|Student_id|question_id|Answer
(note: Answer value should be 1,2 or 3)
*/

/* connect to db */
include('db.php');

/* determine what question you are graphing */
$what_question = some_question_id

/* create query for each different answer */
/* NOTE I may be wrong as to the EXACT and PROPER syntax */
/* Hopefully Pika will corect my errors */
$query_agree = "SELECT count(Answer) as Agree where question_id = '$what_question' AND Answer = 1";
$result1 = mysql_query($query_agree);
$row1 = mysql_fetch_array($result1);
$tot_agree = $row1['Agree'];

$query_disagree = "SELECT count(Answer) as Disagree where question_id = '$what_question' AND Answer = 2";
$result2 = mysql_query($query_disagree);
$row2 = mysql_fetch_array($result2);
$tot_disagree = $row1['Disagree'];

$query_unsatisfied = "SELECT count(Answer) as Unsatisfied where question_id = '$what_question' AND Answer = 3";
$result3 = mysql_query($query_unsatisfied);
$row3 = mysql_fetch_array($result3);
$tot_unsatisfied = $row1['Unsatisfied'];

/* create and display the graph */
$values=array(
	"Agree" => $tot_agree,
	"Disagree" => $tot_disagree,
	"Unsatisfied" => $tot_unsatisfied
);


$img_width=450;
$img_height=300; 
$margins=20;


# ---- Find the size of graph by substracting the size of borders
$graph_width=$img_width - $margins * 2;
$graph_height=$img_height - $margins * 2; 
$img=imagecreate($img_width,$img_height);


$bar_width=20;
$total_bars=count($values);
$gap= ($graph_width- $total_bars * $bar_width ) / ($total_bars +1);


# -------  Define Colors ----------------
$bar_color=imagecolorallocate($img,0,64,128);
$background_color=imagecolorallocate($img,240,240,255);
$border_color=imagecolorallocate($img,200,200,200);
$line_color=imagecolorallocate($img,220,220,220);

# ------ Create the border around the graph ------

imagefilledrectangle($img,1,1,$img_width-2,$img_height-2,$border_color);
imagefilledrectangle($img,$margins,$margins,$img_width-1-$margins,$img_height-1-$margins,$background_color);


# ------- Max value is required to adjust the scale	-------
$max_value=max($values);
$ratio= $graph_height/$max_value;


# -------- Create scale and draw horizontal lines  --------
$horizontal_lines=20;
$horizontal_gap=$graph_height/$horizontal_lines;

for($i=1;$i<=$horizontal_lines;$i++){
	$y=$img_height - $margins - $horizontal_gap * $i ;
	imageline($img,$margins,$y,$img_width-$margins,$y,$line_color);
	$v=intval($horizontal_gap * $i /$ratio);
	imagestring($img,0,5,$y-5,$v,$bar_color);

}


# ----------- Draw the bars here ------
for($i=0;$i< $total_bars; $i++){ 
	# ------ Extract key and value pair from the current pointer position
	list($key,$value)=each($values); 
	$x1= $margins + $gap + $i * ($gap+$bar_width) ;
	$x2= $x1 + $bar_width; 
	$y1=$margins +$graph_height- intval($value * $ratio) ;
	$y2=$img_height-$margins;
	imagestring($img,0,$x1+3,$y1-10,$value,$bar_color);
	imagestring($img,0,$x1+3,$img_height-15,$key,$bar_color);		
	imagefilledrectangle($img,$x1,$y1,$x2,$y2,$bar_color);
}
header("Content-type:image/png");
imagepng($img);

?>

2nd piece

<?PHP
$tot_agree = 7;
$tot_disagree = 9;
$tot_unsatisfied = 2;
/* create and display the graph */
$values=array(
	"Agree" => $tot_agree,
	"Disagree" => $tot_disagree,
	"Unsatisfied" => $tot_unsatisfied
);


$img_width=450;
$img_height=300; 
$margins=20;


# ---- Find the size of graph by substracting the size of borders
$graph_width=$img_width - $margins * 2;
$graph_height=$img_height - $margins * 2; 
$img=imagecreate($img_width,$img_height);


$bar_width=20;
$total_bars=count($values);
$gap= ($graph_width- $total_bars * $bar_width ) / ($total_bars +1);


# -------  Define Colors ----------------
$bar_color=imagecolorallocate($img,0,64,128);
$background_color=imagecolorallocate($img,240,240,255);
$border_color=imagecolorallocate($img,200,200,200);
$line_color=imagecolorallocate($img,220,220,220);

# ------ Create the border around the graph ------

imagefilledrectangle($img,1,1,$img_width-2,$img_height-2,$border_color);
imagefilledrectangle($img,$margins,$margins,$img_width-1-$margins,$img_height-1-$margins,$background_color);


# ------- Max value is required to adjust the scale	-------
$max_value=max($values);
$ratio= $graph_height/$max_value;


# -------- Create scale and draw horizontal lines  --------
$horizontal_lines=20;
$horizontal_gap=$graph_height/$horizontal_lines;

for($i=1;$i<=$horizontal_lines;$i++){
	$y=$img_height - $margins - $horizontal_gap * $i ;
	imageline($img,$margins,$y,$img_width-$margins,$y,$line_color);
	$v=intval($horizontal_gap * $i /$ratio);
	imagestring($img,0,5,$y-5,$v,$bar_color);

}


# ----------- Draw the bars here ------
for($i=0;$i< $total_bars; $i++){ 
	# ------ Extract key and value pair from the current pointer position
	list($key,$value)=each($values); 
	$x1= $margins + $gap + $i * ($gap+$bar_width) ;
	$x2= $x1 + $bar_width; 
	$y1=$margins +$graph_height- intval($value * $ratio) ;
	$y2=$img_height-$margins;
	imagestring($img,0,$x1+3,$y1-10,$value,$bar_color);
	imagestring($img,0,$x1+3,$img_height-15,$key,$bar_color);		
	imagefilledrectangle($img,$x1,$y1,$x2,$y2,$bar_color);
}
header("Content-type:image/png");
imagepng($img);

?>

http://nstoia.com/test/graph.php

furrls · December 13, 2010

hey thanks for the codes, but how do i make it dynamic? because the answers set are different in my case. cos i'm doing a survey.

For example. Strongly agree disagree

satistified unsatisfied not applicable.

These are the different type of answers set for some of my question, so how do i change the code in such way that it is dynamic when appearing as the different option on the y-axis of the graph ?

Sign In

retrieving results from database to generate a graph.

Recommended Posts

furrls

Link to comment

Share on other sites

litebearer

Link to comment

Share on other sites

furrls

Link to comment

Share on other sites

BlueSkyIS

Link to comment

Share on other sites

litebearer

Link to comment

Share on other sites

furrls

Link to comment

Share on other sites

Archived

Browse

Activity

Important Information