Date Range selection help

[TexasOutdoors]

I have a script that stores data in this format.

INSERT INTO ercotload VALUES (34466,'2004-07-08 00:13:00');

I have the below:

<form action="<? echo $_SERVER['PHP_SELF'] ?>" method="POST"> 
start date: <input type="text" name="start_date"> (YYYY-MM-DD)<br> 
end date: <input type="text" name="end_date"> (YYYY-MM-DD)<br> 
<input type="submit" name="submit" value="submit"> 
</form> 

<? 
$SQL_host = "localhost";
$SQL_user = "db_user";
$SQL_pass = "password";
$SQL_db   = "db_name";

$link = mysql_connect("$SQL_host", "$SQL_user", "$SQL_pass");
if (!$link) {
   die("Could not connect: " . mysql_error());
}
if (isset($_POST['submit'])) 
{ 
    $sql = "SELECT * FROM ercotload WHERE datetime BETWEEN '" . $_POST['start_date'] . "' AND '" . $_POST['end_date'] . "'"; 
    echo $sql; 
} 
mysql_close($link);
?> 

but when I click submit, it just returns the sql statement with no data. Am I doing something wrong here. I'm trying to learn php/mysql.

I'd like for it to display the data similar to this: http://www.txohosting.com/load/data.php

Thanks in advance.

[tsinka]

Hi,

you forgot to select the db and to execute the query,like

<?  

$SQL_host = "localhost"; 
$SQL_user = "db_user"; 
$SQL_pass = "password"; 
$SQL_db   = "db_name"; 

$link = mysql_connect("$SQL_host", "$SQL_user", "$SQL_pass") or die("Could not connect: " . mysql_error()); 
mysql_select_db("$SQL_db",$link) or die ("Could not select db: ".mysql_error());

if (isset($_POST['submit']))  

{  

    $sql = "SELECT id FROM ercotload WHERE datetime BETWEEN '" . $_POST['start_date'] . "' AND '" . $_POST['end_date'] . "'";  

$res = mysql_query($sql,$link);

while ($row = mysql_fetch_assoc($res)) {
    echo "ID: ".$row['id']."<br>\n";
}
}  

?>  

Thomas

[TexasOutdoors]

Thanks for the reply. It works, somewhat. lol and i'm sure it's my error.

Ya don't happen to know how to add the date along side the value do ya in a table below?

Got it testing here: http://www.txohosting.com/load/date.php

Or is it possible to do it like this below?

<table border=1 cellspacing=2 cellpadding=3 bordercolor=RED>
<tr>
<td><b>ERCOTLOAD</b></td>
<td><b>DATETIME</b></td>
</tr>
";

while ($x < $num_rows)
{
// Changing Background color for each alternate row
if (($x%2)==0) { $bgcolor="#B3FAFF"; } else { $bgcolor="#ff9000"; }
// Retreiving data and putting it in local variables for each row
$ercotload=mysql_result($sql,$x,"ercotload");
$datetime=mysql_result($sql,$x,"datetime");
echo "
<tr bgcolor=$bgcolor height=30>
<td>$ercotload</td>
<td>$datetime</td>
</tr>
";
$x++;
} // end while
echo "</table>";

that code came from another page, but similar.

[tsinka]

Hi,

I'm not sure if I understood you completely, especially

"Ya don't happen to know how to add the date along side the value do ya in a table below?"

But if you chose to use the same variable names I used in my last post then $sql contains just the query and $res the result set, so changing e.g.

$ercotload=mysql_result($sql,$x,"ercotload");
to
$ercotload=mysql_result($res,$x,"ercotload");

might solve the problem.

Or

<?PHP
while ($row = mysql_fetch_assoc($res)) 
{ 
  // Changing Background color for each alternate row 
  if (($x%2)==0) { 
    $bgcolor="#B3FAFF"; 
  } else { 
    $bgcolor="#ff9000"; 
  } 
  // Retreiving data and putting it in local variables for each row 
  $ercotload=$row["ercotload"]; 
  $datetime=$row["datetime"]; 
  echo <<< EOHTML 
  <tr bgcolor="$bgcolor" height="30"> 
    <td>$ercotload</td> 
    <td>$datetime</td> 
  </tr> 
EOHTML;

$x++; 

} // end while 
?>

Assuming that there are columns in the table named eroctload and datetime.

If you used $res earlier in the code you might need to put a reset($res) in front of the while loop.

If that didn't help you please post an example of how the result should look like.

Thomas

[TexasOutdoors]

Thanks Thomas for walking along side me here.

Lemme give a background. I'm grabbing Texas energy data and storing it in sql, example here: http://www.txohosting.com/load/data.php

Then using that to graph here: http://www.txohosting.com/load/index.php

I'd like to return this data, date selectable, similar to the format found at http://www.txohosting.com/load/data.php

Eventually, I'd like to use the data with the date selectable that you helped me with earleir, to then graph that data similar to the other page. I'd like to be able to view a graph of a particular day.

Thanks again.

BTW, the page ya helped me with is here: http://www.txohosting.com/load/date.php and works fine, but the date along with the value would help greatly.

[tsinka]

Hi,

can you post the source of date.php and the layout of that table (because you use datetime as field name which seems to be a reserved word) ?

Thanks,
Thomas

[TexasOutdoors]

Out put here: http://www.txohosting.com/load/data.php

Source:

<?php


$db = mysql_connect("localhost", "user", "pass") or die("Could not connect");
mysql_select_db("ercot") or die("Could not select database");

$thisday = date("Y.m.d"); 

$result = mysql_query("SELECT ercotload,datetime FROM ercotload WHERE datetime between '$thisday 00:00:00' AND '$thisday 23:59:59';") or die("Query failed");

while ($row = mysql_fetch_array($result)) 
{ 
    $res = $row[0];
    $td = $row[1];
    echo "$td = <font color=red>$res</font><br> ";
}


?>

I guess I could edit that query with the info you gave me earlier and use the date select. That particular query only grabs the data for the current day as it is.

The output of what you helped me with is here:

<form action="<? echo $_SERVER['PHP_SELF'] ?>" method="POST"> 
start date: <input type="text" name="start_date"> (YYY-MM-DD)<br> 
end   date:  

<input type="text" name="end_date"> (YYY-MM-DD)<br> 
<input type="submit" name="submit" value="submit"><input name="" type="reset"> 
</form> 

<?   

$SQL_host = "localhost";  

$SQL_user = "db_user";  

$SQL_pass = "password";  

$SQL_db   = "sql_db";  

$link = mysql_connect("$SQL_host", "$SQL_user", "$SQL_pass") or die("Could not connect: " . mysql_error());  

mysql_select_db("$SQL_db",$link) or die ("Could not select db: ".mysql_error()); 

if (isset($_POST['submit']))   

{   

    $sql = "SELECT ercotload FROM ercotload WHERE datetime BETWEEN '" . $_POST['start_date'] . "' AND '" . $_POST['end_date'] . "'";   

$res = mysql_query($sql,$link); 

while ($row = mysql_fetch_assoc($res)) { 
    echo "LOAD MW: ".$row['ercotload']."<br>\n"; 
} 
}   

?>

Thanks again, and I hate to bother ya.

[tsinka]

Hi,

I tried the date.php script again. If someone selects the same date as start AND end date (just to choose exactly one day) nothing is returned, so this one might do the job including the output of the Date:

<?PHP
  $startDate = isset($_POST['start_date']) ? $_POST['start_date'] : "";
  $endDate = isset($_POST['end_date']) ? $_POST['end_date'] : "";
?>
<form action="<? echo $_SERVER['PHP_SELF'] ?>" method="POST"> 
start date: <input type="text" name="start_date" value ="<?PHP echo $startDate; ?>"> (YYY-MM-DD)<br> 
end   date:   

<input type="text" name="end_date" value="<?PHP echo $endDate; ?>"> (YYY-MM-DD)<br> 
<input type="submit" name="submit" value="submit">
<input type="reset"> 
</form> 

<?    

$SQL_host = "localhost";   

$SQL_user = "db_user";   

$SQL_pass = "password";   

$SQL_db   = "sql_db";   

$link = mysql_connect("$SQL_host", "$SQL_user", "$SQL_pass") or die("Could not connect: " . mysql_error());   

mysql_select_db("$SQL_db",$link) or die ("Could not select db: ".mysql_error());  

if (isset($_POST['submit']))    

{    

    $sql = "SELECT ercotload,datetime FROM ercotload WHERE datetime BETWEEN '".trim($startDate)." 00:00:00' AND '".trim($endDate)." 23:59:59'";    

$res = mysql_query($sql,$link);  

while ($row = mysql_fetch_assoc($res)) {  
    echo "LOAD MW: ".$row['ercotload']." - Date: ".$row['datetime']."<br>\n";  
}  
}    

?> 

Additionally, you might want to check if the submitted dates are valid dates and that the end date is greater than or equal to the start date.

Thomas

P.S.: You don't bother me 🙂

[TexasOutdoors]

Thanks again. That seems to do as needed. Now, I have to see if there's a way to graph the data it grabs with that query. You can view it here: http://www.txohosting.com/load/archive.php

I tried using that query in the graph form, but it showed the garbled letters instead of the image. Jpgraph is a learning experience for me.

I have got to learn PHP/Mysql better. It's so powerful after seeing all this.

Familiar with jpgraph?? lol

[tsinka]

Just a little bit,

it's been a while that I had something to do with jpgraph (an older version).

Post the code you have so far. The garbled letters might be the binary code of the image.

Thomas

[TexasOutdoors]

Yea, I believe you're right. The url to the page is: http://www.txohosting.com/load/date_graph.php

What I did was add the form to a graphed page with a query, and then used the query we used on the other page. Not sure how all this pieces together. Man, not many people would help like you have. Thanks again!! If you're ever in Texas, I owe ya a few beers!!

Below is the code:

<form action="<? echo $_SERVER['PHP_SELF'] ?>" method="POST"> 
start date: <input type="text" name="start_date"> (YYY-MM-DD)<br> 
end   date:   

<input type="text" name="end_date"> (YYY-MM-DD)<br> 
<input type="submit" name="submit" value="submit"><input name="" type="reset"> 
</form> 
<?php
// A medium complex example of JpGraph
// Note: You can create a graph in far fewwr lines of code if you are
// willing to go with the defaults. This is an illustrative example of
// some of the capabilities of JpGraph.
?>

<?
include ("../load/include/jpgraph.php");
include ("../load/include/jpgraph_line.php");
include ("../load/include/jpgraph_bar.php");

$month=array("Jan","Feb","Mar","Apr","Maj","Jun","Jul","Aug","Sep","Okt","Nov","Dec");

// Create some datapoints

// for($i=0; $i<$steps; ++$i) {

$databary = "";


$db = mysql_connect("localhost", "db_user", "password") or die("Could not connect");
mysql_select_db("db_sql") or die("Could not select database");

$result = mysql_query("SELECT ercotload FROM ercotload WHERE datetime BETWEEN '" . $_POST['start_date'] . "' AND '" . $_POST['end_date'] . "'") or die("Query failed");
$ii = 0;

global $ercotload;

while ($row = mysql_fetch_array($result)) {
    $ercotload[$ii] = $row[0];
    $datetime[$ii] = $row[1];
    $ii++;
}


$datay = "";

 for ($i=0; $i<480; ++$i) {
    $exnow = explode(" ", $datetime[$i]);
    $exnow = explode(":", $exnow[1]);
    $time = $exnow[0].":".$exnow[1];
    $databarx[] = $time;
    $datay[$i]=$ercotload[$i];
}





// New graph with a background image and drop shadow
$graph = new Graph(850,800,"auto", -1);

// $graph->SetBackgroundImage("tiger_bkg.png",3);
$graph->SetShadow();

// Use text X-scale so we can text labels on the X-axis
$graph->SetScale("textlin");

// Y2-axis is linear
$graph->SetY2Scale("lin");

// Color the two Y-axis to make them easier to associate
// to the corresponding plot (we keep the axis black though)
$graph->yaxis->SetColor("black","red");
$graph->y2axis->SetColor("black","orange");

$today = date("F j, Y, g:i a");  

// Set title and subtitle
$graph->title->SetFont(FF_VERDANA,FS_NORMAL,28);
$graph->title->Set("ERCOT Load");

$graph->subtitle->Set("$today");

// Use built in font (don't need TTF support)
$graph->title->SetFont(FF_FONT1,FS_BOLD);

// Make the margin around the plot a little bit bigger then default
$graph->img->SetMargin(40,140,40,80);    

// Slightly adjust the legend from it's default position in the
// top right corner to middle right side
$graph->legend->Pos(0.03,0.5,"right","center");

// Display every 60:th tickmark
$graph->xaxis->SetTextTickInterval(30);

// Label every 1:nd tick mark
$graph->xaxis->SetTextLabelInterval(2);

// Setup the labels
$graph->xaxis->SetTickLabels($databarx);
$graph->xaxis->SetLabelAngle(90);

//$graph->xaxis->SetFont(FF_ARIAL,FS_NORMAL,11);
//$graph->xaxis->SetTickLabels($labels);
//$graph->xaxis->SetLabelAngle(45); 

// Create a red line plot
$p1 = new LinePlot($datay);
$p1->SetColor("red");
$p1->SetLegend("MW-Load");

// Create the bar plot
$b1 = new BarPlot($databary);
$b1->SetLegend("Temperature");
$b1->SetFillColor("orange");
// $b1->SetColor("green");
$b1->SetAbsWidth(8);

// Drop shadow on bars adjust the default values a little bit
// $b1->SetShadow("steelblue",2,2);

// The order the plots are added determines who's ontop
$graph->Add($p1);
$graph->AddY2($b1);

// Finally output the  image
$graph->Stroke();

?>

[tsinka]

Hi,

the problem is that Stroke sends some headers to set the Content type. But since you print at least the form PHP can't send the headers. There must not be any output prior to sending headers.

So just to test avoid echoing anything before using the jpGraph function.

If you want to combine jpGraph and HTML I'd suggest to write a little PHP script that contains the image creation functions, like loadimg.php. You might save the submitted POST vars or the array containing the data of the database in the session and use that data in the loadimg.php script. You can then do something like

<img src='loadimg.php'> or something like that.

But just to test your code you can suppress any output if $_POST['submit'] is set just to see the picture.

Make sure that the script starts with <?PHP and ends with ?> without any empty lines (or the form) outside the PHP block

Thomas

[TexasOutdoors]

Thanks again. With the help of a friend on MSN earlier, we got most of it figured out. I created a page with just the form, and used the action to point towards the graph. I then placed the query used on the initial page on the graph page.

You can view here: http://www.txohosting.com/load/archive_data.php

It works well, it's just a matter of configuring jpgraph, which I am definitely not familiar with. Dang lines tend to run off the graph for some reason. The initial page was set up for increments of a minute, where the data I grab is every 5 minutes. This may be causing some issues.

Thanks again for all your help!!