Jump to content

retrieving results from database to generate a graph.

furrls

By furrls
in PHP Coding Help

 Share

Recommended Posts

furrls Newbie

furrls

Posted
Posted

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 :D THANKS

litebearer Member

litebearer

Posted
Posted

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 Newbie

furrls

Posted
  • Author
Posted 

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 Newbie

BlueSkyIS

Posted
Posted
litebearer Member

litebearer

Posted
Posted

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 Newbie

furrls

Posted
  • Author
Posted

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 ? :D

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.