Solved

mySQL-->Line Chart-->PNG image

Posted on 2002-04-08
52
632 Views
Last Modified: 2008-02-01
Hi

I looked for information on the web and found nothing really helpful for me.  I got a mySQL database with numbers and dates.  the numbers are y axis, dates are x axis.  i want to do is, a php page will download the data and build a png image line chart, or something like that will look nice.  its a weight vs date graph is what i am looking at.  (weight in kilograms).

this should be created dynamically, as an add button will have user add a weight to database and also posts current date.  i havent started that yet, but i cant find anything on graphs thats really useful. some are too advanced for me.  thanks.
0
Comment
Question by:jwnrb
  • 31
  • 15
  • 5
  • +1
52 Comments
 

Author Comment

by:jwnrb
ID: 6926022
Well I designed a primitive method of storing data into database.  And a way to download data to a table.  Now I need to get that data into a chart.

0
 
LVL 4

Expert Comment

by:lokeshv
ID: 6927033
check this library, its really good and easy to use

http://www.aditus.nu/jpgraph/


You just get all ur data from MySQL DB and pass to the library as an array.

Few Examples are check them out.


Hope this will help.

Lk
0
 

Author Comment

by:jwnrb
ID: 6927064
Ah yes...I heard about that. Actually I had downloaded it and tried to make use of it.  It would love to be able to use it, however it yells at me about GD lib 2.0 requirements.  I tried other libs and got TrueType not installed w/ gdlib, etc.  I found a quite basic one, which uses parameters to get the data (DrawPlot->("blah",5); or so...

any suggestions in getting jpgraph to work is appreciated!

thnx
0
 
LVL 4

Expert Comment

by:lokeshv
ID: 6927395
just comple ur PHP with GD(--with-gd=path/to/gd/library)...

this is what u have to do ..to start working...


LK
0
 

Author Comment

by:jwnrb
ID: 6928065
well this is on a server i have no control over.  i am friendly with the admins though.  its on my school account.

dunno if this will help, but hope it does.

http://rockhopper.monmouth.edu/~s0338596/test.php
0
 
LVL 4

Expert Comment

by:lokeshv
ID: 6928408
yeah it does....

PHP is compiled with GD on your server.....so just download the jpgraph library edit he config file and u can start using it...


post here if still get in some problem...


Lk:-)
0
 

Author Comment

by:jwnrb
ID: 6928614
where is a config file?

this is what i got:

~/public_html/jpgraph $ tree
.
|-- COPYING
|-- README
`-- src
    |-- Changelog
    |-- Examples
    |   |-- backgroundex01.php
    |   |-- backgroundex02.php
    |   |-- backgroundex03.php
    |   |-- bar_csimex1.php
    |   |-- bar_csimex1.png
    |   |-- bar_csimex2.php
    |   |-- bar_csimex2.png
    |   |-- bargradex1.php
    |   |-- bargradex2.php
    |   |-- bargradex3.php
    |   |-- bargradex4.php
    |   |-- bargradex5.php
    |   |-- bargradex6.php
    |   |-- bartutex12.php
    |   |-- centeredlineex01.php
    |   |-- centeredlineex02.php
    |   |-- centeredlineex03.php
    |   |-- example1.1.php
    |   |-- example1.2.php
    |   |-- example1.php
    |   |-- example10.php
    |   |-- example13.php
    |   |-- example14.php
    |   |-- example15.php
    |   |-- example16.1.php
    |   |-- example16.2.php
    |   |-- example16.3.php
    |   |-- example16.php
    |   |-- example17.php
    |   |-- example18.php
    |   |-- example19.php
    |   |-- example2.1.php
    |   |-- example2.5.php
    |   |-- example2.6.php
    |   |-- example2.php
    |   |-- example20.php
    |   |-- example21.php
    |   |-- example22.php
    |   |-- example23.php
    |   |-- example24.php
    |   |-- example3.1.php
    |   |-- example3.2.php
    |   |-- example3.php
    |   |-- example4.php
    |   |-- example5.php
    |   |-- example6.php
    |   |-- example7.php
    |   |-- example8.php
    |   |-- example9.php
    |   |-- exampleex9.php
    |   |-- filledline01.php
    |   |-- filledlineex01.php
    |   |-- ganttex30.php
    |   |-- impulsex1.php
    |   |-- impulsex2.php
    |   |-- impulsex3.php
    |   |-- impulsex4.php
    |   |-- linebarex1.php
    |   |-- linebarex2.php
    |   |-- linebarex3.php
    |   |-- linegrace.php
    |   |-- linegraceex.php
    |   |-- negbarvalueex01.php
    |   |-- nullvalueex01.php
    |   |-- pie3d_csimex1.php
    |   |-- pie3d_csimex1.png
    |   |-- pie3dex1.php
    |   |-- pie_csimex1.php
    |   |-- pie_csimex1.png
    |   |-- pieex1.php
    |   |-- pieex2.php
    |   |-- pieex3.php
    |   |-- pieex4.php
    |   |-- pieex5.php
    |   |-- pieex6.php
    |   |-- rotateex1.php
    |   |-- scatterex1.php
    |   |-- scatterex2.php
    |   |-- scatterlinkex1.php
    |   |-- scatterlinkex2.php
    |   |-- show-example.php
    |   |-- show-image.php
    |   |-- show-source.php
    |   |-- spiderex1.php
    |   |-- spiderex2.php
    |   |-- spiderex3.php
    |   |-- spiderex4.php
    |   |-- spiderex5.php
    |   |-- spiderex6.1.php
    |   |-- spiderex6.php
    |   |-- spiderex7.php
    |   |-- spiderlogex1.php
    |   |-- spiderlogex2.php
    |   |-- staticbandbarex1.php
    |   |-- staticbandbarex2.php
    |   |-- staticbandbarex3.php
    |   |-- staticbandbarex4.php
    |   |-- staticbandbarex5.php
    |   |-- staticbandbarex6.php
    |   |-- staticbandbarex7.php
    |   |-- staticlinebarex1.php
    |   |-- testsuit_jpgraph.php
    |   |-- text-example1.php
    |   |-- text-example2.php
    |   |-- tiger_bkg.gif
    |   |-- tiger_bkg.jpg
    |   `-- tiger_bkg.png
    |-- Todo
    |-- jpgraph.php
    |-- jpgraph_bar.php
    |-- jpgraph_canvas.php
    |-- jpgraph_dir.php
    |-- jpgraph_error.php
    |-- jpgraph_gantt.php
    |-- jpgraph_line.php
    |-- jpgraph_log.php
    |-- jpgraph_pie.php
    |-- jpgraph_pie3d.php
    |-- jpgraph_scatter.php
    `-- jpgraph_spider.php

2 directories, 123 files
0
 

Author Comment

by:jwnrb
ID: 6928618

Warning: Failed opening 'jpgraph_dir.php' for inclusion (include_path='.:/usr/share/php') in /export/home/us/s0338596/public_html/jpgraph/src/jpgraph.php on line 15

Fatal error: imagecolorresolvealpha(): requires GD 2.0 or later in /export/home/us/s0338596/public_html/jpgraph/src/jpgraph.php on line 2889

is what i got when i ran an example
0
 

Author Comment

by:jwnrb
ID: 6928625
~/public_html/jpgraph/src $ ls -l | grep jpgraph_dir
-rwxr-xr-x    1 s0338596 student      1202 Mar  1 05:34 jpgraph_dir.php
0
 
LVL 4

Expert Comment

by:lokeshv
ID: 6928909
seems you are using the older ersion og GD ...

GD2.0 is required for the latest verison of jpgraph(1.6.1)

try to download the old version of jpgraph...

if you dont find it anywhr ..post here and i will upload for you on myserver.



Lk
0
 

Author Comment

by:jwnrb
ID: 6928963
which version? the only version i see is 1.5.  is there an archive i am missing?

thnx

0
 
LVL 4

Expert Comment

by:lokeshv
ID: 6930835
check which GD version is required for version 1.5

version 1.2 needs GD version 1.6...so if u get verison 1.2

that wld work fine for you..


Regards

Lk
0
 
LVL 4

Expert Comment

by:lokeshv
ID: 6930836
or if you can give me access on that server i will fex that for you..


Lk
0
 

Author Comment

by:jwnrb
ID: 6931639
if you access my server?  well the only way is my passwd which is a breach in my contract to use the server.  you can tell me how to fix it though ;-) !

Umm, do you have earlier versions that would work with my GD version? I think i am 1.6.  

I think the only downloadable version there is, is the latest version.  I dont have root access on my server so i cant put 2.0 on it anyways.
0
 

Author Comment

by:jwnrb
ID: 6931641
but still odd how i get this:

Warning: Failed opening 'jpgraph_dir.php' for inclusion (include_path='.:/usr/share/php') in /export/home/us/s0338596/public_html/jpgraph/src/jpgraph.php
on line 15
0
 
LVL 4

Expert Comment

by:lokeshv
ID: 6932954
i have the version 1.2 which works fine with GD1.6

Lk
0
 

Author Comment

by:jwnrb
ID: 6933049
can you email me a gzipped tar of it?

or put it on a server for me to download?

that would be great.

thnx!
0
 
LVL 4

Expert Comment

by:lokeshv
ID: 6933271
yes i can ..

give me ur email address ..

Lk
0
 

Author Comment

by:jwnrb
ID: 6933885
weinrj@cybernex.net
0
 
LVL 4

Expert Comment

by:lokeshv
ID: 6934218
ok i have uploaded that on my server ..you can download it from there ...


URL is

http://www.asmlabs.com/jpgraph/jpgraph.tar.gz



Hope this will solve ur problem..


Lk :o)
0
 

Author Comment

by:jwnrb
ID: 6934245
great!!! thanks!! the examples work!

Now back to the main query I had. How do i get the mySQL data to the graph to plot?

Thanks....

0
 
LVL 4

Accepted Solution

by:
lokeshv earned 100 total points
ID: 6934276
you want everything in 100 points ??



just grab the ur mysql data in a array and pass it to the libray as points on X -axis and Y -axis..


check this in eample/test2.php
$datay = array(12,6,10,5);
$datax = array("Rep1","Rep2","Rep3","Rep4");

so just replace the arrays..

Lk:o)
0
 

Author Comment

by:jwnrb
ID: 6934306
well 100 points seemed fair enough....the question never asked how to get jpGraph to work, it was how to plot a line chart to a png image.

;-)

i guess i can up an extra 50 points if you help me get this to work a bit more.

in my table called weight, one column is called weight which holds tinyint numbers, and one column is called date, which holds text of date, such as 11/4/2002.  the date datatype wasnt working out to well for me, so i just used text instead.  

so to plot dates on the bottom, x axis, and the weights, y axis, and extract that data from the weight table in hte jon database.  ( i know some minor mySQl stuff such as insert into and getting data for printing, just not in the sesne of plotting! :-)

(eventually i like to add a data limitor, where i can have a start date and end date box where the user can limit what the chart and table displays.)...but thats for much later on my mission of learning php/mySQL.

thanks for everything so far!
0
 

Author Comment

by:jwnrb
ID: 6934448
well i havent tried passing data to the array yet, but i used the example into a chart whre it does get data from db but it doesnt work out too well.

http://rockhopper.monmouth.edu/~s0338596/weight.php
0
 

Author Comment

by:jwnrb
ID: 6934453
but test5.php works fine in that same path.,
0
 

Author Comment

by:jwnrb
ID: 6935284
okay...i got the image to be inline with the chart/table...now i need to get the array!

this is getting fun!

0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 4

Expert Comment

by:lokeshv
ID: 6935860
ok give me ur database structure and i will post the code here..

Lk
0
 

Author Comment

by:jwnrb
ID: 6935881
#
# Table structure for table `weight`
#

CREATE TABLE weight (
  weight tinyint(4) NOT NULL default '0',
  date text NOT NULL
) TYPE=MyISAM COMMENT='weight in kg with date';


$today = date("j/n/Y");  # formats 31/12/2001 format for dates!
#echo $today;

$db = mysql_connect("localhost", "s0338596", "*****");
mysql_select_db("jon",$db);


$query = "INSERT INTO weight (weight, date) VALUES ('$weight', '$today')";


i hope that is the information requested.

===================

  <?php

                $db = mysql_connect("localhost", "s0338596", "***");
                mysql_select_db("jon",$db);
                $result = mysql_query("select * from weight");
                $index = 0;


                if($inf = mysql_fetch_array($result)) {
                        do {
                             if ( $index % 2 == 0 )
                                echo "<tr bgcolor=#dddddd>";
                             else
                                echo "<tr bgcolor=#cccccc>";
                             echo "<td width=80>";
                             echo $inf["weight"];
                             echo "</td>";
                             echo "<td width=125>";
                             echo $inf["date"];
                             echo "</td>";
                             echo "</tr>";
                             $index++;
                        } while ($inf = mysql_fetch_array($result));
                }

?>


that is most of the code. i did.  

thnx,
0
 
LVL 40

Expert Comment

by:RQuadling
ID: 6936896
You could do bar charts WITHOUT any special libraries!

And there is a JavaScript library for charts from Netscape.

Richard Quadling.
0
 
LVL 40

Expert Comment

by:RQuadling
ID: 6936899
0
 

Author Comment

by:jwnrb
ID: 6937011
Rich,

That is a very interesting idea indeed!  I didnt even think I could do that In JavaScript! So its same to assume that mySQL data can be used in it?  However, for the application I am using this for, a line-chart is best.  Though a bar-graph has similar properties, I think for the purpose of this, a line is best.  I will keep note of the netscape version.  (THe line thing I am doing is a personal project)  At work, I do a lot of javascripting, asp, etc., and the things I do, must work with the Netscape browser, so that can be a quite powerful tool, as I am developing some items where the raw data should be graphed, and it looks like a bar graph would be best.

Many thanks!

0
 
LVL 40

Expert Comment

by:RQuadling
ID: 6937020
No problem.

To do line charts, you really do need a server side app and/or graph library.

Regards,

Richard.
0
 

Author Comment

by:jwnrb
ID: 6937240
Yea i got the php thing to work, except i cant figure out how to get the data from database into the array they need.

i got server side stuff running greatly.  the graph u showed me would work great on my non server side stuff i develop!

Thanks!
0
 

Author Comment

by:jwnrb
ID: 6937762
Yea i got the php thing to work, except i cant figure out how to get the data from database into the array they need.

i got server side stuff running greatly.  the graph u showed me would work great on my non server side stuff i develop!

Thanks!
0
 

Author Comment

by:jwnrb
ID: 6938861
well i think this should work, but it didnt/  but how do i get the other field as the x-axis?

thnx/

jw/

<?php
include_once("dbconnect.inc");
include("phpgraph/jpgraph.php");
include("phpgraph/jpgraph_line.php");

$SQL = "SELECT * FROM weight";
$RESULT = mysql_query($SQL);

if ( $myrow=mysql_fetch_array( $RESULT ) ) {
   do {
      $ydata[] = $myrow["weight"];
   } while ( $myrow=mysql_fetch_array( $RESULT ) );
}



$graph = new Graph(600,400);
$graph->SetScale("textlin");
$graph->title->Set("Jonathan Weinraub's Weight Chart");
$graph->xaxis->title->Set("Date");
$graph->yaxis->title->Set("Weight (kg)");

$lineplot = new LinePlot($ydata);
$lineplot->mark->SetType(MARK_DIAMOND);

$graph->Add($lineplot);
$graph->Stroke();

?>
0
 
LVL 4

Expert Comment

by:lokeshv
ID: 6939042
do it like this..

for($i=0;$i<mysql_num_rows($RESULT);$i++){
          $row=mysql_fetch_array($RESULT);
          $ydata[$i]=$row[weight];
          $temp=explode("-",$row[date]);//if ur date format is yyyy-mm-dd
          $xdata[$i]=$temp[2];
}

now u have $ydata and $xdata


so just plot the line plot


$lineplot = new LinePlot($ydata,$xdata);


Hope this will solve ur problem..

Lk


0
 

Author Comment

by:jwnrb
ID: 6939048
well my date column is a text field, bc i rather have dates in dd-mm-yyyy.
0
 

Author Comment

by:jwnrb
ID: 6939073
Warning: Supplied argument is not a valid MySQL result resource in /export/home/us/s0338596/public_html/wchart.php on line 9
JpGraph Error: Min and Max Y value for graph is both 0!
0
 

Author Comment

by:jwnrb
ID: 6939076
now it seems that chart is drawn. except there is no data.

what i get is, the y-axis gets the range of weights. the x-axis shows how many items there are, not the actual titles.

also, nothing gets plotted.

jw
0
 
LVL 4

Expert Comment

by:lokeshv
ID: 6939553
can u post ur code here...

and check u have data in ur table or not ..


Lk
0
 

Author Comment

by:jwnrb
ID: 6939578
<?php
include("phpgraph/jpgraph.php");
include("phpgraph/jpgraph_line.php");

$db = mysql_connect(localhost,s0338596,***);
mysql_select_db(jon,$db);

$SQL = "SELECT * FROM weight";
$RESULT = mysql_query($SQL);

for ( $i = 0; $i < mysql_num_rows($RESULT); $i++ ){
   $row=mysql_fetch_array($RESULT);
   $ydata[$i] = $row[weight];
   $temp=explode("-",$row[date]);
   $xdata[$i]=$temp[2];
}



$graph = new Graph(600,400);
$graph->SetScale("textlin");
$graph->title->Set("Jonathan's Weight Chart");
$graph->xaxis->title->Set("Date");
$graph->yaxis->title->Set("Weight (kg)");

$lineplot = new LinePlot($ydata,$xdata);
$lineplot->mark->SetType(MARK_DIAMOND);

$graph->Add($lineplot);
$graph->Stroke();

?>
0
 

Author Comment

by:jwnrb
ID: 6939579
this code proves data in chart.

  <?php

                $db = mysql_connect("localhost", "s0338596", "***");
                mysql_select_db("jon",$db);
                $result = mysql_query("select * from weight");
                $index = 0;


                if($inf = mysql_fetch_array($result)) {
                        do {
                             if ( $index % 2 == 0 )
                                echo "<tr bgcolor=#dddddd>";
                             else
                                echo "<tr bgcolor=#cccccc>";
                             echo "<td width=80>";
                             echo $inf["weight"];
                             echo "</td>";
                             echo "<td width=125>";
                             echo $inf["date"];
                             echo "</td>";
                             echo "</tr>";
                             $index++;
                        } while ($inf = mysql_fetch_array($result));
                }

?>
0
 
LVL 4

Expert Comment

by:lokeshv
ID: 6940090
save ur data as SQL and send that to me...i will fix and post the code here for u.


email id lokesh@messihalabs.com


Lk
0
 

Author Comment

by:jwnrb
ID: 6940123
how can i save data as sql?
0
 

Author Comment

by:jwnrb
ID: 6943771
ok...got it to work today.
thanks for all ur help!
0
 
LVL 4

Expert Comment

by:lokeshv
ID: 6943791
Thnaks for A

Lk
0
 

Author Comment

by:jwnrb
ID: 6943799
no prob
u helped me get what i needed to get done
~jw
0
 

Expert Comment

by:m-a-s
ID: 7885151
jwnrb:

if( condition )
{
   do
   {
      actions
   }
   while( condition )
}

can be (is usually) written as

while( condition )
{
   actions
}

RQuadling:

JavaScript can draw only bar charts or, maximum, dot charts. No scew lines :( But that JavaScript graph library is really great, thank you!
0
 
LVL 40

Expert Comment

by:RQuadling
ID: 7890766
NP.

Glad to be of use.

Have you got a site with this all working?
0
 

Author Comment

by:jwnrb
ID: 7891490
http://rockhopper.monmouth.edu/~s0338596/weight.php

is how i am using it for fun.

just to see how i got the opra winfrey weight loss scheme.

i am surprises to see this thread active since it was closed a year ago!

0
 
LVL 40

Expert Comment

by:RQuadling
ID: 7891709
That's m-a-s's fault!

But an answer then can always be looked at again.

What about the code for this?
0
 

Author Comment

by:jwnrb
ID: 7891910
the javascript is neat and havent had a chance to use it yet.  at work we still use asp though at home i use php.  my office is going to be using php i understand soon but still on IIS servers. i rather use apache :-)
0

Featured Post

Easy Project Management (No User Manual Required)

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction HTML checkboxes provide the perfect way for a web developer to receive client input when the client's options might be none, one or many.  But the PHP code for processing the checkboxes can be confusing at first.  What if a checkbox is…
Consider the following scenario: You are working on a website and make something great - something that lets the server work with information submitted by your users. This could be anything, from a simple guestbook to a e-Money solution. But what…
The viewer will learn how to dynamically set the form action using jQuery.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now