PHP code for using historic data

[tobyspond]

Hi Paul,

You are correct. Here is a sample.

<?php
$dbhost ='localhost';
$dbuser ='username';
$dbpassword ='*********';
$database ='databasename';

//the name of the SQL table holding the dayfile
$table_name="dayfile";

?>

Kerry

[PaulMy]

Hi and thanks very much Kerry, I am making progress but .

I have created a dbconfig.php from your example and entered my information (copy and paste from the ImportCumulusFile to make sure I had entered it correctly). Then running http://www.komokaweather.com/mysql/annualexample.php I get

Warning: include(php_include/dbconfig.php) [function.include]: failed to open stream: No such file or directory in /home/content/96/5379896/html/mysql/annualexample.php on line 8

Warning: include() [function.include]: Failed opening 'php_include/dbconfig.php' for inclusion (include_path='.:/usr/local/php5/lib/php') in /home/content/96/5379896/html/mysql/annualexample.php on line 8

Warning: mysql_connect() [function.mysql-connect]: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) in /home/content/96/5379896/html/mysql/annualexample.php on line 11
Connection Error: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

Line 8 is include "php_include/dbconfig.php";
Line 11 is $db = mysql_connect($dbhost, $dbuser, $dbpassword) or die("Connection Error: " . mysql_error());
and the information entered in dbconfig.php for $dbhost, $dbuser and $dbpassword is the same as in the working ImportCumulusFile.php.

The dbconfig.php and the annualexample.php are both in my http://www.komokaweather.com/mysql/ folder. I have also tried the tempexample.php which is also in the same folder.

I have manualy uploaded the dayfile.txt again today with ImportCumulusFile.php and also Sep13Log.txt and I can get to these through my GoDaddy account.

Once I have an example working then hopefully I can understand and learn. Any suggestion on what I may have overlooked to have your annualexample.php and tempexample.php work?

Paul

[mcrossley]

Paul

If your dbconfig.php script is in the same folder as main script you are including it from, then the include statement could look like...

Code: Select all

include ('dbconfig.php');

Which will search the include path first, then the current folder.

or you could try...

Code: Select all

include ('./dbconfig.php');

which 'should' override the search path and only look in the current folder - I think! But I'm no PHP expert.

[PaulMy]

Ah yes those dreaded paths... will I error get to understand them and I changed the "include" line to more like in my other php scripts, changed from dayfile and monthly to Dayfile and Monthly and... now SUCCESS
http://www.komokaweather.com/mysql/annualexample.php and http://www.komokaweather.com/mysql/tempexample.php
Thanks Mark and Kerry, now the fun begins...

I should be able to update the dayfile every day with Toolbox as that is the same file name all the time, but will have to try to understand how to do that with the changing name monthly log files. For now I can update manually with ImportCumulusFile.php or should the monthly files be appended?

Paul

[tobyspond]

Hi Paul,

Glad you got it working.

I change the monthly name every month in the toolbox.

I also select overwrite because I found it appended the entire monthly file to the existing one; not just the new records.

Kerry

[mcrossley]

Paul, glad its work now - have fun!

I'm not bothering with the monthly files at the moment. I find that most meaningful stuff can be extracted from the dayfile data. Otherwise I'd create a script to do the import, which could work out the file name (you have to be careful at month end, on the 1st of the month you want to import the previous months file) and call the script from toolbox or Cumulus.

I change the monthly name every month in the toolbox.

I also select overwrite because I found it appended the entire monthly file to the existing one; not just the new records.

Oh err! That shouldn't happen, the monthly table has a primary key on the LogDateTime which should prevent duplicate rows.

[tobyspond]

Hi Mark,

I seem to remember it happening with the day file as well. Although I'll admit, I have not tested it recently and maybe it is on my end. I'm away from home and can't test it - maybe next week.

Just to clarify - This issue only occurs with the uploaded file, not when the file gets included in the database table.

Kerry

[PaulMy]

Hi Kerry,
Before I get too far along and then have to redo, what is your naming of the monthly files in your MySQL? In my MySQL database under Table I have "Dayfile", "Monthly" which was my first upload attempt but likely will be removed; and a new "Month200810" which is the first of my planned monthly uploads. Is there any preference for simplicity later on?

Paul

[tobyspond]

Hi Paul,

I use Aug13.txt naming convention. The toolbox uploads the monthly file and then I have teh script add the data to one monthly file in MYSQL. You can call that file what you like. You can also use individual month tables but that may complicate queries.

Kerry

[PaulMy]

I am trying to learn a bit more by using previously provided sample and would like to sort my data by month rather than year but have not found the way to do that http://www.komokaweather.com/mysql/monthlyfrom2008.php

This is what I have in my PHP file:

// the actual query for the grid data the query displays data for all years grouped by month.

$q1 = "SELECT LogDate, Monthname(LogDate) as Month, Year(LogDate) as Year, Day(LogDate) as Day, Min(MinTemp) as MinTemp, max(MaxTemp) as MaxTemp, round(Avg(AvgTemp),1) as AvgTemp FROM Dayfile group by Month, Year order by LogDate";
$result1 = mysql_query($q1)or die('Error: ' . mysql_error());
?>

<!--how to display the query results in tables -->

<table>
<tr>
<th>Month</th>
<th>Year</th>
<th> Max Temp</th>
<th> Min Temp</th>
<th> Avg Temp</th>
</tr>
<?php
while($row=mysql_fetch_object($result1)){
?>
<tr >
<th><?php echo $row->Month; ?></th>
<th><?php echo $row->Year; ?></th>
<td><?php echo $row->MaxTemp; ?></td>
<td><?php echo $row->MinTemp; ?></td>
<td><?php echo $row->AvgTemp; ?></td>
</tr>
<?php
}
?>
</table>

But it sorts by year. I have tried to reverse Year and Month in "group by Month, Year order by LogDate" but without success.

Any suggestions?
Paul

[tobyspond]

Hi Paul,

Replace order by logdate with order by month(logdate), year(logdate) ASC

Kerry

[PaulMy]

Thanks Kerry, that guidance has overcome my initial obstacle. Now on to more daring things...

Paul