GD MYSQL PHP Line Chart

nathan100 · August 10, 2007

Hi,

I am having difficulty creating a line chart based on data selected from a combo box(s) to populate a table.

The chart should update as and when the form is updated.

<html>
<head>
<title>World Records</title>
</head>
<body>
<?php
$con = mysql_connect("host","user","pass");
if (!$con) {
 	die('Could not connect: ' . mysql_error());
}

mysql_select_db("database", $con);
?>

<form action="worldrecords.php?submit=yes" method="post">
<?php 
echo "<select name='event'>";

$res=mysql_query("select DISTINCT event from worldrecords");

if(mysql_num_rows($res)==0) {
	echo "there is no data in table..";
} else {
	while($row=mysql_fetch_assoc($res)) {

		echo "<option value='".$row['event']."'>".$row['event']."</option>";
	}
	echo '</select>';

	echo "<select name='gender'>";
	$res2=mysql_query("select DISTINCT gender from worldrecords");

	if(mysql_num_rows($res2)==0) {
		echo "there is no data in table..";
	} else {
		while($row2=mysql_fetch_assoc($res2)) {
			echo "<option value='".$row2['gender']."'>".$row2['gender']."</option>";
		}
	}
}
echo '</select>';
?>
	<input type="submit" name="submit" value="submit">
</form>
<?php
if($_GET['submit']) {	
	$event=$_REQUEST['event'];
	$gender=$_REQUEST['gender'];

	$mySql="SELECT * FROM worldrecords WHERE (event='".$event."') AND (gender='".$gender."') ORDER BY year, athlete";
	$result=mysql_query($mySql);

	echo "<h3>".$event." - ".$gender."</h3>";

	if(mysql_num_rows($result)==0) {
		echo "no records found..";
	} else {
?>
		<table border="1">
			<tr>
				<th>Year</th>
				<th>Time</th>
				<th>Venue</th>
				<th>Athlete</th>
				<th>Event</th>
				<th>Pool</th>
				<th>Gender</th>
			</tr>
<?php
		while($row2=mysql_fetch_assoc($result)) {
			echo "<tr>";
			echo "<td>" . $row2['year'] . "</td>";
			echo "<td>" . $row2['time'] . "</td>";
			echo "<td>" . $row2['venue'] . "</td>";
			echo "<td>" . $row2['athlete'] . "</td>";
			echo "<td>" . $row2['event'] . "</td>";
			echo "<td>" . $row2['pool'] . "</td>";
			echo "<td>" . $row2['gender'] . "</td>";
			echo "</tr>";
		}
?>





		</table>














<?php
	}
}
mysql_close($con);
?>



</body>
</html>

As you can see, i have left out the GD code, basically, i had a go at it - but it doesnt work and creates a prompt to save the page itself! So no point even putting it in.

Any help will be great. Thanks

Barand · August 10, 2007

The gd code should be in a separate file.

Except that it is created dynamically, a gd image is like any other image you want to place on a page (png, gif, jpg). It is in a file and you place it on the page with an HTML image tag

so with gd image

nathan100 · August 11, 2007

Ok: This is the code i added to create the chart... but it just opens and asks me to save/load/cancel.

I have also tried externally calling it: <img SRC="graph.php"> but this doesnt do anythign because the data that the chart is based upon could change depending on what is on the main PHP page (worldrecords.php)

Is this actually possible then???

<?php


header ("Content-type: image/jpg");


$x_gap=40; // The gap between each point in y axis 

$x_max=$x_gap*13; // Maximum width of the graph or horizontal axis
$y_max=250; // Maximum hight of the graph or vertical axis
// Above two variables will be used to create a canvas of the image//


$im = @ImageCreate ($x_max, $y_max)
or die ("Cannot Initialize new GD image stream");
$background_color = ImageColorAllocate ($im, 234, 234, 234);
$text_color = ImageColorAllocate ($im, 233, 14, 91);
$graph_color = ImageColorAllocate ($im,25,25,25);


$x1=0;
$y1=0;
$first_one="yes";
while($nt=mysql_fetch_array($result)){
$x2=$x1+$x_gap; // Shifting in X axis
$y2=$y_max-$nt[time]; // Coordinate of Y axis
ImageString($im,2,$x2,$y2,$nt[athlete],$graph_color); 
if($first_one=="no"){ // this is to prevent from starting $x1= and $y1=0
imageline ($im,$x1, $y1,$x2,$y2,$text_color); // Drawing the line between two points
}
$x1=$x2; // Storing the value for next draw
$y1=$y2;
$first_one="no"; // Now flag is set to allow the drawing
}

ImageJPEG ($im);

?>

Barand · August 11, 2007

should be correct mime type

header ("Content-type: image/jpeg");

and you need to do the query in this file to be able to process the data

nathan100 · August 12, 2007

Ok.

This is what i have now... just getting a box with a red cross at present.

<html> 
<head> 
<title>World Records</title> 
</head> 
<body> 
<?php 
    $con = mysql_connect("host","user","pass"); 
    if (!$con) { 
         die('Could not connect: ' . mysql_error()); 
    } 
     
    mysql_select_db("database", $con); 
?> 

<form action="worldrecords.php?submit=yes" method="post"> 
<?php  
    echo "<select name='event'>"; 

    $res=mysql_query("select DISTINCT event from worldrecords"); 

    if(mysql_num_rows($res)==0) { 
        echo "there is no data in table.."; 
    } else { 
        while($row=mysql_fetch_assoc($res)) { 
             
            echo "<option value='".$row['event']."'>".$row['event']."</option>"; 
        } 
        echo '</select>'; 
     
        echo "<select name='gender'>"; 
        $res2=mysql_query("select DISTINCT gender from worldrecords"); 
     
        if(mysql_num_rows($res2)==0) { 
            echo "there is no data in table.."; 
        } else { 
            while($row2=mysql_fetch_assoc($res2)) { 
                echo "<option value='".$row2['gender']."'>".$row2['gender']."</option>"; 
            } 
        } 
    } 
    echo '</select>'; 
?> 
        <input type="submit" name="submit" value="submit"> 
    </form> 
<?php 
    if($_GET['submit']) {     
        $event=$_REQUEST['event']; 
        $gender=$_REQUEST['gender']; 
         
        $mySql="SELECT * FROM worldrecords WHERE (event='".$event."') AND (gender='".$gender."') ORDER BY year, athlete"; 
        $result=mysql_query($mySql); 
         
        echo "<h3>".$event." - ".$gender."</h3>"; 
         
        if(mysql_num_rows($result)==0) { 
            echo "no records found.."; 
        } else { 
?> 
            <table border="1"> 
                <tr> 
                    <th>Year</th> 
                    <th>Time</th> 
                    <th>Venue</th> 
                    <th>Athlete</th> 
                    <th>Event</th> 
                    <th>Pool</th> 
                    <th>Gender</th> 
                </tr> 
<?php 
            while($row2=mysql_fetch_assoc($result)) { 
                echo "<tr>"; 
                echo "<td>" . $row2['year'] . "</td>"; 
                echo "<td>" . $row2['time'] . "</td>"; 
                echo "<td>" . $row2['venue'] . "</td>"; 
                echo "<td>" . $row2['athlete'] . "</td>"; 
                echo "<td>" . $row2['event'] . "</td>"; 
                echo "<td>" . $row2['pool'] . "</td>"; 
                echo "<td>" . $row2['gender'] . "</td>"; 
                echo "</tr>"; 
            } 
?> 





            </table> 







<IMG src="graph.php?event='.$event.'&gender='.$gender.'">







<?php 
        } 
    } 
    mysql_close($con); 
?> 



</body> 
</html>

And the graph page

graph.php

<?php 
    $con = mysql_connect("host","user","pass"); 
    if (!$con) { 
         die('Could not connect: ' . mysql_error()); 
    } 
     
    mysql_select_db("database", $con); 
?> 


<?php 
include "worldrecords.php";

$event=$_REQUEST['event'];  
gender=$_REQUEST['gender'];  
          
        $mySql="SELECT * FROM worldrecords WHERE (event='".$event."') AND (gender='".$gender."') ORDER BY year, athlete";  
        $result=mysql_query($mySql);  


header ("Content-type: image/jpeg"); 


$x_gap=40; // The gap between each point in y axis  

$x_max=$x_gap*13; // Maximum width of the graph or horizontal axis 
$y_max=250; // Maximum hight of the graph or vertical axis 
// Above two variables will be used to create a canvas of the image// 


$im = @ImageCreate ($x_max, $y_max) 
or die ("Cannot Initialize new GD image stream"); 
$background_color = ImageColorAllocate ($im, 234, 234, 234); 
$text_color = ImageColorAllocate ($im, 233, 14, 91); 
$graph_color = ImageColorAllocate ($im,25,25,25); 


$x1=0; 
$y1=0; 
$first_one="yes"; 
while($nt=mysql_fetch_array($result)){ 
$x2=$x1+$x_gap; // Shifting in X axis 
$y2=$y_max-$nt[time]; // Coordinate of Y axis 
ImageString($im,2,$x2,$y2,$nt[athlete],$graph_color);  
if($first_one=="no"){ // this is to prevent from starting $x1= and $y1=0 
imageline ($im,$x1, $y1,$x2,$y2,$text_color); // Drawing the line between two points 
} 
$x1=$x2; // Storing the value for next draw 
$y1=$y2; 
$first_one="no"; // Now flag is set to allow the drawing 
} 

ImageJPEG ($im); 

?>

Barand · August 12, 2007

gender=$_REQUEST['gender']; <----- missing $ at beginning of $gender

if you comment out the header line and run graph.php in your browser you should get any error reported

nathan100 · August 12, 2007

Hi,

Added in the $

I also commented out the header line in the graph.php ... but still just the box with cross in.

I think my host has disabled error messages for PHP... which isn't good!!

Hmm, Is this actually possible then?

Barand · August 12, 2007

try putting

error_reporting(E_ALL);

at top of script

nathan100 · August 12, 2007

No. Still just the little image square with red cross...

Im really stuck for ideas on this now.. tried everything.

Although never done this sort of thing before.

nathan100 · August 12, 2007

If i right click the image > save as:

http://www.mysite.co.uk/graph.php?event='.$event.'&gender='.$gender.'

this is what the picture url is.. is this correct??

just trying to give as much info to you as poss.

Barand · August 12, 2007

$event $gender should be values, not variable names

<IMG src="graph.php?event=<?php echo $event?>&gender=<?php echo $gender?>">

Also make sure <? has no whitespace before it, must be at start of file.

After imageJPEG($im), call imagedestroy($im)

nathan100 · August 12, 2007

Ooo.

Now were are getting somewhere!

The URL shows (for the picture):

http://www.mysite.co.uk/graph.php?event=4x50m Free&gender=M

So it is reading them in now.

But still box with cross... no white spaces that i can see.

Barand · August 12, 2007

OK, I set up a test db table and this code works (in the sense that it gave an image) Work needed on scaling the plot. You will also need your connection code replacing mine. (is it in worldrecords.php ?)

Placed on page with

<?php
$event = 'aaa';
$gender = 'F'; 
echo "<img src='graph.php?event=$event&gender=$gender'>";
?>

<?php 
#include "worldrecords.php";
mysql_connect('localhost');
mysql_select_db('test3');
$event=$_REQUEST['event'];  
$gender=$_REQUEST['gender'];  
          
        $mySql="SELECT * FROM worldrecords WHERE (event='".$event."') AND (gender='".$gender."') ORDER BY year, athlete";  
        $result=mysql_query($mySql) or die('Error');  


header ("Content-type: image/jpeg"); 


$x_gap=40; // The gap between each point in y axis  

$x_max=$x_gap*13; // Maximum width of the graph or horizontal axis 
$y_max=250; // Maximum hight of the graph or vertical axis 
// Above two variables will be used to create a canvas of the image// 


$im = @ImageCreate ($x_max, $y_max) 
or die ("Cannot Initialize new GD image stream"); 
$background_color = ImageColorAllocate ($im, 234, 234, 234); 
$text_color = ImageColorAllocate ($im, 233, 14, 91); 
$graph_color = ImageColorAllocate ($im,25,25,25); 


$x1=0; 
$y1=0; 
$first_one="yes"; 
while($nt=mysql_fetch_array($result)){ 
$x2=$x1+$x_gap; // Shifting in X axis 
$y2=$y_max-$nt[time]; // Coordinate of Y axis 
ImageString($im,2,$x2,$y2,$nt[athlete],$graph_color);  
if($first_one=="no"){ // this is to prevent from starting $x1= and $y1=0 
imageline ($im,$x1, $y1,$x2,$y2,$text_color); // Drawing the line between two points 
} 
$x1=$x2; // Storing the value for next draw 
$y1=$y2; 
$first_one="no"; // Now flag is set to allow the drawing 
} 

ImageJPEG ($im); 
imagedestroy($im);
?>

nathan100 · August 12, 2007

Thankyou for that.

Well, I get a grey box as an output, but with no lines/chart on it...

Is this what you mean by scaling? ... how is that done??

Barand · August 12, 2007

If there are no lines etc it sounds like it isn't finding data for your event/gender combination that is being passed in the querystring

nathan100 · August 12, 2007

iv used the hardcoding like you had on your example... hmmmmm.

Barand · August 12, 2007

With my values or yours?

nathan100 · August 12, 2007

Actually. in the Graph.php page - is it using the connection string to the DB??

Its using the SQL ($result) but cant see where it actually uses the connection string

nathan100 · August 12, 2007

Correction....

It does work!! Thankyou soooo much.

Reason i couldn't see a line is because the values were so small. For example, 100m is in seconds, so therefore you cant really see it.... however, other events are in minutes.

Is there a way to scale this??

Barand · August 12, 2007

If plot area is 200 pix high and max value is 10 seconds then you have 20 px for each second, so y value is

image_height - (bottom_margin + seconds * pix_per_second)

Sign In

GD MYSQL PHP Line Chart

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information