Page 1 of 1

Convert .CSV to GD Graph

Posted: Mon Apr 07, 2008 3:54 am
by collamack
~pickle | Please use [ code=html ], [ code=php ], etc tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: :arrow: Posting Code in the Forums to learn how to do it too.


Dear Colleagues,
I'm a newbie and got problem :banghead: to write a proper code in PHP. i'm assigned to design a page to convert CSV file to GD Graph. but i stuck to store the data into mySQL(maybe got another way without store data in mySQL).
This is my coding:

Code: Select all

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<?php
$db = mysql_connect("localhost", "root", "") or die("Could not connect.");
if(!$db)  
    die("no db");
if(!mysql_select_db("cp_graph",$db))
    die("No database selected.");
?>
<title>Graph</title>
</head>
<body>
<?php 
if(isset($_POST['submit']))
    {
    ini_set("auto_detect_line_endings", 1);
    $current_row = 1;
    $filename = $_FILES["file"]["name"];
    $handle = fopen("$filename", "r");
 
    while ( ($data = fgetcsv($handle, 10000, ",") ) !== FALSE )
    {
    $number_of_fields = count($data);
    if ($current_row == 1)
    {
        for ($c=0; $c < $number_of_fields; $c++)
        {
            $header_array[$c] = $data[$c];
        }
    }
    else
    {
        for ($c=0; $c < $number_of_fields; $c++)
        {
            $data_array[$header_array[$c]] = $data[$c];
        }
 
        print_r($data_array);
    }
    $current_row++;
    }
 
    }
    
    fclose($handle);
    }
 
   else
 
   {
 
      print "<form action='index.php' method='post' enctype='multipart/form-data'>"; 
      print "Type file name to import:<br>"; 
      print "<input type='file' name='file' id='file' /><br>"; 
      print "<input type='submit' name='submit' value='submit'></form>"; 
   }
?>
</body>
</html>
The result would be:
Array ( [month] => may [a] => 3800 => 5700 [c] => 1500 [d] => 800 [e] => 1200 ) Array ( [month] => june [a] => 4500 => 3456 [c] => 2300 [d] => 2570 [e] => 7650 )

but the objective is to create a database which the field would be based on the header of the CSV file. for this example, the header should be, 'Month', 'a', 'b', 'c', 'd', & 'e'. So how to write mySQL code for this condition? Please help me.
i've tried to write:

Code: Select all

$create = 'CREATE TABLE `test` ('. ' `$header_array[$c]` VARCHAR(100) NOT NULL'. ' )'. ' ENGINE = myisam;';
mysql_query($create) or die(mysql_error());
but i dndt seem ok.Thank You!! :?: :?:


~pickle | Please use [ code=html ], [ code=php ], etc tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: :arrow: Posting Code in the Forums to learn how to do it too.

Re: Convert .CSV to GD Graph

Posted: Mon Apr 07, 2008 5:36 am
by Kadanis
First off there is a slight error in your SQL query.

Code: Select all

 
$create = 'CREATE TABLE `test` (`' . $header_array[$c] . '` VARCHAR(100) NOT NULL) ENGINE = myisam;';
 
You were breaking in and out of the string / variable sections in the wrong places. This would have caused MySQL to error on the query, I've run this changed query and it creates a table with 1 column using the value in $header_array[$c]

Hope that helps get you on the right track. Just a side note, but you are missing a primary key from the table creation so you might want to think about that too.

Re: Convert .CSV to GD Graph

Posted: Wed Apr 09, 2008 1:59 am
by collamack
I've got the solution to convert csv to mySQL. But, i still don't know how to generate a line graph from the mySQL.
my db got header.
department month1 month2 month3 month4 .........
so i want to design a page to generate a line graph where the x-axis in Month and y-axis is sales value.

Thank You

Re: Convert .CSV to GD Graph

Posted: Wed Apr 09, 2008 2:40 am
by onion2k
collamack wrote:so i want to design a page to generate a line graph where the x-axis in Month and y-axis is sales value.
That's nice. What's your question though?

Re: Convert .CSV to GD Graph

Posted: Wed Apr 09, 2008 3:00 am
by collamack
my question:

anyone got a code sample code to convert it to graph... :o

Re: Convert .CSV to GD Graph

Posted: Wed Apr 09, 2008 6:44 am
by onion2k
I doubt it. Why not post your code and let us help you figure out what the problem is?

Re: Convert .CSV to GD Graph

Posted: Mon Apr 14, 2008 3:26 am
by collamack
i've got a code. but i need to create a page to generate 2 graphs based on type of graph selected. here's my code

Code: Select all

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Daily Graph</title>
</head>
<body>
<form id="form1" name="form1" method="post" action="">
   <div align="center"><table width="50%" border="0">
    <tr>
<?php //mosCommonHTML::loadCalendar(); ?>
      <th scope="col">Date : 
      <input type="text" name="date" id="date" size="8" value="<?php echo date('d/m/Y')?>" onClick="return showCalendar('delivery_date', 'yyyy-mm-dd');"/></th>
      <th scope="col">Bag: <select name="bag" value="options">
        <option value="hbar">Horizontal Bar</option>
        <option value="vbar">Vertical Bar</option>
        <option value="line">Line Chart</option>
        <option value="3d">3D Chart</option>
        <option value="pie">Pie Chart</option>
        </SELECT></th>
      <th scope="col">Price: <select name="bag" value="options">
        <option value="line">Line Chart</option>
        <option value="hbar">Horizontal Bar</option>
        <option value="vbar">Vertical Bar</option>
        <option value="3d">3D Chart</option>
        <option value="pie">Pie Chart</option>
        </SELECT></th>
      <th scope="col"><input name="display" type="submit" class="button" value="Display" /></th>
    </tr>
  </table>
 </div>
</form>
<br/>
<?php
include ("../jpgraph.php");
include ("../jpgraph_line.php");
 
$ydata = array(11,3,23,12,5,1,9,13,5,7);
$y2data = array(354,200,265,99,111,91,198,225,293,251);
 
// Create the graph. These two calls are always required
$graph = new Graph(300,200,"auto"); 
$graph->img->SetMargin(40,40,20,40);
$graph->SetScale("textlin");
$graph->SetY2Scale("lin");
$graph->SetShadow();
 
// Create the linear plot
$lineplot=new LinePlot($ydata);
$lineplot2=new LinePlot($y2data);
 
// Add the plot to the graph
$graph->Add($lineplot);
$graph->AddY2($lineplot2);
$lineplot2->SetColor("orange");
$lineplot2->SetWeight(2);
$graph->y2axis->SetColor("orange");
 
$graph->title->Set("Example 5");
$graph->xaxis->title->Set("X-title");
$graph->yaxis->title->Set("Y-title");
 
$graph->title->SetFont(FF_FONT1,FS_BOLD);
$graph->yaxis->title->SetFont(FF_FONT1,FS_BOLD);
$graph->xaxis->title->SetFont(FF_FONT1,FS_BOLD);
 
$lineplot->SetColor("blue");
$lineplot->SetWeight(2);
 
$lineplot2->SetColor("orange");
$lineplot2->SetWeight(2);
 
$graph->yaxis->SetColor("blue");
 
// Display the graph
$graph->Stroke();
?>
 
</body>
</html>
 
but there some errors:
1) i dont know how to call from database since the example refer to fix data.
2) How to generate different graphs in a graph?
3) the error occurred
JpGraph Error: HTTP headers have already been sent.
Caused by output from file example5.php at line 10.
Explanation:
HTTP headers have already been sent back to the browser indicating the data as text before the library got a chance to send it's image HTTP header to this browser. This makes it impossible for the library to send back image data to the browser (since that would be interpretated as text by the browser and show up as junk text).

Most likely you have some text in your script before the call to Graph::Stroke(). If this texts gets sent back to the browser the browser will assume that all data is plain text. Look for any text, even spaces and newlines, that might have been sent back to the browser.

For example it is a common mistake to leave a blank line before the opening "<?php".

Re: Convert .CSV to GD Graph

Posted: Mon Apr 14, 2008 3:36 am
by onion2k
1. Not sure what the problem is with fetching the data from a database. You just need to fetch it like any other data.

2. Generating different graphs would be a matter of calling different methods in your GDGraph object. I'd recommend using a switch() block (look it up in the PHP manual before asking here if you don't know what one is).

3. The error is because you're trying to embed an image into the middle of a page of HTML. That won't work. You graph needs to be a separate script that you call with an html image tag, eg

<img src="http://www.domain.com/graph.php?graphTy ... 1=1&var2=2">

graph.php needs to be a script that only outputs the graph image, no text or html or anything. The _GET vars would be whatever data is needed to fetch the data and create the required graph.

Re: Convert .CSV to GD Graph

Posted: Mon Apr 14, 2008 4:02 am
by collamack
Thank you.
i know about switch function. but i dont know how to generate 2 different types of graph in 1 graph. anyone got example or link for me to get example.

Re: Convert .CSV to GD Graph

Posted: Tue Apr 15, 2008 12:36 am
by collamack
hai..i hv an idea how to generate the page. but right now, i'm stuck.

Code: Select all

<?php
define ('DBHOST','localhost');
define ('DBNAME','cp_graph'); 
define ('DBUSER','root');
define ('DBPASS','');
 
function dbQuery($sql) {        
    $conn = mysql_connect(DBHOST, DBUSER, DBPASS) OR DIE (mysql_error());   
    @mysql_select_db (DBNAME, $conn) OR DIE (mysql_error());    
    $result = mysql_query ($sql,$conn);
    return $result; 
}
 
function salesList()
{
$query="SELECT * FROM sales";
$result = dbQuery($query);
return $result;
}
        
include ("class/jpgraph.php");
include ("class/jpgraph_bar.php");
 
$result = salesList();
while ($row = mysql_fetch_array($result)){ 
 
$data1y=array($row['sales']);
original code is

Code: Select all

$data2y=array(8,2,11,7,14,4,1,2,3);
how to call db value?