• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 397
  • Last Modified:

PHP with Access Database problem

Hi,

Using PHP I'm trying to take certain portions of data from this access database:
(below is a snippett, it's actually much larger)

I need the *LAST* entry for each day, for the last Seven Days where the ID is Number 1 and 3, problem is I cant get it to sort through and pick the right pieces, as the date will obviously always change.

ID      LogDate             Interval      Value
1      01/04/2006 07:00:00      15      1
1      01/04/2006 07:15:00      15      1
1      01/04/2006 14:15:00      15      1
1      01/04/2006 14:30:00      15      1
1      01/04/2006 14:45:00      15      1
1      01/04/2006 18:30:00      15      1
1      01/04/2006 18:45:00      15      1
1      01/04/2006 19:00:00      15      1
1      01/04/2006 19:15:00      15      1
1      01/04/2006 19:30:00      15      1 <-----
1      02/04/2006 06:45:00      15      11
1      02/04/2006 07:00:00      15      1
1      02/04/2006 07:15:00      15      1
1      02/04/2006 18:30:00      15      1
1      02/04/2006 18:45:00      15      1
1      02/04/2006 19:00:00      15      1 <-----
2      01/04/2006 07:00:00      15      1.748889
2      01/04/2006 11:30:00      15      7806.003
2      01/04/2006 11:45:00      15      8568.824
2      01/04/2006 16:15:00      15      20532.98
2      01/04/2006 16:30:00      15      20773.9
2      01/04/2006 16:45:00      15      2
2      02/04/2006 18:45:00      15      13344.91
2      02/04/2006 19:00:00      15      13345.07
2      03/04/2006 06:45:00      15      0.1027778
2      03/04/2006 07:00:00      15      17.215
3      01/04/2006 06:45:00      15      0
3      01/04/2006 07:00:00      15      1.167034E-04
3      01/04/2006 07:15:00      15      5.836651E-04
3      01/04/2006 12:45:00      15      0.7098396
3      01/04/2006 13:00:00      15      0.7700542
3      01/04/2006 15:00:00      15      1.190712
3      01/04/2006 15:15:00      15      1.22265
3      01/04/2006 15:30:00      15      1.254473
3      01/04/2006 15:45:00      15      1.299548 <----

So far, I've got the following code working, but not doing quite doing all that I need, right now it outputs all of one ID:

-----------------------------------------------------

<?php
// Connect to The Database and File Operations
$db = "new.mdb";

// Handling information... MS Access!
$conn = new COM("ADODB.Connection");
$conn->Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=$db");

// Grab the necessary items (this is currently also a test)
$sql = "SELECT   ID, LogDate, Value
        FROM     tblWerte
       WHERE    ID=2
        ORDER BY ID";

// SELECT MAX(orderID) AS lastID FROM $db_table

$rs = $conn->Execute($sql);


// HTML output code here
?>
<body bgcolor="#F0F0F0">
 <tr>
    <td><font size="6"><strong><font color="#0000FF" font face="Verdana, Arial, Helvetica, sans-serif">Test Output from Solar Panel </font></strong></font><font face="Verdana, Arial, Helvetica, sans-serif"><strong><br>
      Table used: tblMonatsWerte (Months Worth)</strong></font></td>
</tr>
<P></P>
<table width="40%" border="1" cellpadding="0" cellspacing="0" bordercolor="#000000">
<tr>
<font size="2" face="Verdana, Arial, Helvetica, sans-serif">
<th>ID</th>
<th>LogDate</th>
<th>Value</th>
            
            
            
</tr>
<?php while (!$rs->EOF): ?>
<tr>
<td><div align="center"><font size="2" face="Verdana, Arial, Helvetica, sans-serif"><?= $rs->Fields['ID']->Value ?></td>
<td><div align="center"><font size="2" face="Verdana, Arial, Helvetica, sans-serif"><?= $rs->Fields['LogDate']->Value ?></td>
<td><div align="center"><font size="2" face="Verdana, Arial, Helvetica, sans-serif"><?= $rs->Fields['Value']->Value ?></td>
                        
</tr>
<?php $rs->MoveNext() ?>
<?php endwhile ?>
---------------------------------------------------------


Anyone able to help me with the next step - getting the code to do what I need?
Thanks,

- M
0
nikez2k4
Asked:
nikez2k4
  • 25
  • 20
1 Solution
 
Richard QuadlingSenior Software DeverloperCommented:
SELECT TOP 7
 MAX(LogDate),
 Interval,
 Value
FROM
 tblWerte
GROUP BY
 LogDate
ORDER BY
 LogDate

I think should do the trick?
0
 
Richard QuadlingSenior Software DeverloperCommented:
Ah. No. You're using a datatime field. That will only get the top 7 entries. Also, I think you are very limited on the functions you can use with MS Access.

No CONVERT, no DATESERIAL (that I can use anyway).

Hmm.

Having to trawl through the ENTIRE table is a pain.

But you can...

<?php
$a_last7days = array();
$s_SQL = 'SELECT LogDate, Interval, Value FROM tblWerte ORDER BY LogDate DESC';
$conn = new COM("ADODB.Connection");
$conn->Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=$db");
$rs = $conn->Execute($sql);

// No construct the array of the last 7 days. Remember that we are getting the source data in reverse order, so this will / should be a short trawl.
while(count($a_last7days) !== 7)
 {
 $s_date = date('Y/m/d', strtotime($rs->Fields['LogDate']));
 if (!isset($a_last7days[$s_date]))
  {
  $a_last7days[$s_date] = $rs->Fields; // Store all the fields for the most recent log for the date in $s_date.
  }
 $rs->MoveNext();
 }

// To see if the data is OK.
print_r($a_last7days);
?>

NOTE: I don't use ADO, so I am assuming that the execute method ALSO moves the first row into the Fields array. If not, I suspect you will need something like $rs->First() or similar.


0
 
nikez2k4Author Commented:
Thanks veyr much for the replies. I couldn't get that code to run but I could see what you were suggesting.  

Although I want two separate pieces (info from ID=1 and ID=3) I guess I can run those queries separately, the problem I've got is finding the *last* entry for the day.  As each day the last entry could be different time I somehow need to convert the date to string value and compare it but cant get it to work.

The format for that date (written to LogDate) is:
eg. "16/03/2006 11:15:00" - which is this: date('d/m/Y G:i:s'......    Problem is, I cant get the query to recognise the last entry for the day on that ID.

-
//$yesterday = date('d/m/Y G:i:s', strtotime('-24 hours'));
$yesterday = date('d/m/Y', strtotime('-24 hours'));

$sql = "SELECT   ID, LogDate, Value
       FROM     tblWerte
       WHERE    ID =2
       ORDER BY LogDate";


- I tried to add   " AND date= .... "   in the WHERE part of the query, but it's not playing.

At the moment I'd happily settle for the yesterday's last entry and worry about 7 days worth some other time, but would be curious to know how to do it.
I wonder, if I could get "yesterday" query to work, could I repeat that by setting the day by -1 an extra 6 days to get the last 7 days?
Thanks again,

- M


0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Richard QuadlingSenior Software DeverloperCommented:
No!!!!! 1 query in reverse order for ALL the transactions which means the most recent transactions come first. Then, per-row, extracting the datatime field. Converting it to JUST the date (dropping the time). If the date doesn't exist in my set of dates add it. Keep going until I have 7 days. Only the last transaction for the day will be used. I don't care WHEN the transactions occur in the day, only the last one will be used.

Just rereading the question. You will need to include a WHERE clause which says WHERE ID=1 or ID=3

Also, do you want 7 days for EACH id or 7 days with ANY id ; i.e. it may by 6 days for id1 and 1 day for id3?
0
 
nikez2k4Author Commented:
Hi.

It's last entry for each day for ID2 and ID3..
Thanks

- M
0
 
Richard QuadlingSenior Software DeverloperCommented:
Right ...

So try this ...

<?php
$a_last7days = array ( 1 => array (), 3 => array () );
$s_SQL = 'SELECT LogDate, Interval, Value FROM tblWerte WHERE id = 1 or id = 3 ORDER BY LogDate DESC';
$conn = new COM ( "ADODB.Connection" );
$conn->Open ( "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=$db" );
$rs = $conn->Execute ( $sql );

// No construct the array of the last 7 days. Remember that we are getting the source data in reverse order, so this will / should be a short trawl.
while ( ( count ( $a_last7days[1] ) !== 7 ) && count ( $a_last7days[3] !== 7 ) )
      {
       $s_date = date ( 'Y/m/d', strtotime ( $rs->Fields['LogDate'] ) );
       $i_id = $rs_Fields['id'];
       if ( !isset ( $a_last7days[$i_id][$s_date]) && ( count ( $a_last7days[$i_id] ) < 7 ) )
            {
            $a_last7days[$i_id][$s_date] = $rs->Fields; // Store all the fields for the most recent log for the date in $s_date.
            }
      $rs->MoveNext();
      }

// To see if the data is OK.
print_r ( $a_last7days );
?>

This should now give you the last transacation for 7 days for each id. A total of upto 14 transactions.

If you don't what DO you get? Do you get any errors? The above code SHOULD run (based upon your initial code).
0
 
nikez2k4Author Commented:
Hi,

I get an error with this:

(LINE 10) $rs = $conn->Execute ( $sql );

Notice: Undefined variable: SQL in D:\test.php on line 10

Fatal error: Uncaught exception 'com_exception' with message '<b>Source:</b> Provider<br/><b>Description:</b> Type mismatch.' in D:\test.php:10 Stack trace: #0 D:\test.php(10): com->Execute(NULL) #1 {main} thrown in D:\test.php on line 10

---
On the offchance it was a typo, and it should have been $s_SQL instead, I get...

Fatal error: Uncaught exception 'com_exception' with message '<b>Source:</b> Unknown<br/><b>Description:</b> Unknown' in D:\test.php:10 Stack trace: #0 D:\test.php(10): com->Execute('SELECT LogDate...') #1 {main} thrown in D:\test.php on line 10
--

It's a pain there's more than one entry per day, eh!

- M




0
 
Richard QuadlingSenior Software DeverloperCommented:
Oops.

$s_sql not $sql


Can you use YOUR com code ...

// Connect to The Database and File Operations
$db = "new.mdb";

// Handling information... MS Access!
$conn = new COM("ADODB.Connection");
$conn->Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=$db");

Oh.

I missed the $db line.

Try adding just that first
0
 
nikez2k4Author Commented:
Thx.

Had already added in the $db var + COM stuff - still just complains about "$rs = $conn->Execute ( $s_SQL );"
0
 
Richard QuadlingSenior Software DeverloperCommented:
Can you cut and paste the SQL you are using?

Can you try executing the sql inside Access manually or via MSQuery or some other tool/
0
 
Richard QuadlingSenior Software DeverloperCommented:
OOps.

$s_SQL = 'SELECT ID, LogDate, Interval, Value FROM tblWerte WHERE ID=1 OR ID=3 ORDER BY LogDate DESC';

Forgot to select the ID too.

Maybe adding a ; after DESC will help. Shouldn't make a difference though.

0
 
nikez2k4Author Commented:
Ahh. Some tit had changed the field name from "Interval" to "LogInterval", wow that's annoying - so that's why the query execution was complaining.  
I'll make a note to kill the guy at a later date :-)

Anyway... Now I'm left with repeated lines of this:
Notice: Undefined variable: rs_Fields in D:\test.php on line 20
Line 20:       $i_id = $rs_Fields['ID'];

And at the end of the page:
-
Fatal error: Uncaught exception 'com_exception' with message '<b>Source:</b> ADODB.Field<br/><b>Description:</b> Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.' in D:\test.php:19 Stack trace: #0 D:\test.php(19): strtotime(NULL) #1 {main} thrown in D:\test.php on line 19
-

Line19:       $s_date = date ( 'Y/m/d', strtotime ( $rs->Fields['LogDate'] ) );

Thanks for your help so far :-)


0
 
Richard QuadlingSenior Software DeverloperCommented:
I don;t know ANYTHING about ADO, so you will need to use whatever bounds checking you already use. Personally I would just use ODBC as this doesn't require any additional COM layer (which is slow as each command in PHP has to be translated in real time to an appopriate com function and lots of layers of code, ....

Did you include the ID in the SQL SELECT statement?

It also looks like you need a different way to determine the eof to the way I am using.

As there is no ID, you are not adding anything to the array so they never reach 7 each and you read all the rows.
0
 
nikez2k4Author Commented:
ALso noticed I think the date format in that string is wrong, it should be 'd/m/Y' but doesnt make any difference to the over all problem.
The fact the field contains times is a pain.

Here's the query I'm running:
-
$s_SQL = 'SELECT ID, LogDate, LogInterval, Value
   FROM tblWerte
   WHERE ID = 1 OR ID = 3
   ORDER BY LogDate DESC';
-
0
 
Richard QuadlingSenior Software DeverloperCommented:
$i_id = $rs_Fields['id'];

becomes

$i_id = $rs->Fields['id'];

COME ON!!! TYPOS!!! DEAL WITH THEM!!!!!! <cheesy_grin />

Turn on error_reporting(E_ALL);

Errors are your first line of debugging.
0
 
Richard QuadlingSenior Software DeverloperCommented:
$i_id = $rs->Fields['ID'];

may be as I don't know if case is important in your COM.
0
 
nikez2k4Author Commented:
Hehe Yeah, typos must die!
I tried the case sensitivity thing, didnt make a difference, I did put the 'typo' correction in and I get this:

Notice: Undefined variable: rs_Fields in D:\test.php on line 20

Fatal error: Uncaught exception 'com_exception' with message 'Unable to lookup `': Unknown name. ' in D:\test.php:20 Stack trace: #0 D:\test.php(20): unknown() #1 {main} thrown in D:\test.php on line 20

- Line 20 is .. *drumroll* $i_id = $rs->$rs_Fields['ID'];

Feels close!

0
 
Richard QuadlingSenior Software DeverloperCommented:
TYPO!!!!!!!!!!

Come on!!!!!!!


$i_id = $rs->$rs_Fields['ID'];

becomes


$i_id = $rs->Fields['ID'];


0
 
Richard QuadlingSenior Software DeverloperCommented:
"Undefined variable: rs_Fields in D:\test.php on line 20"

There is nothing called $rs_Fields.
0
 
nikez2k4Author Commented:
> There is nothing called $rs_Fields.

Yeah, our replies are quite close together :-)

Replaced typo with "$i_id = $rs->Fields['ID'];" and get lots of these:

Warning: Illegal offset type in D:\inetpub\wwwroot\solar\index4.php on line 25
Warning: Illegal offset type in D:\inetpub\wwwroot\solar\index4.php on line 27

Possibly safe to ignore these as they are a warning *shrug* but it's referring to:

25        if ( !isset ( $a_last7days[$i_id][$s_date]) && ( count ( $a_last7days[$i_id] ) < 7 ) )
26          {
27          $a_last7days[$i_id][$s_date] = $rs->Fields; // Store all the fields for the most recent log for the date in $s_date.

and at the end:

Fatal error: Uncaught exception 'com_exception' with message '<b>Source:</b> ADODB.Field<br/><b>Description:</b> Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.' in D:\test.php:19 Stack trace: #0 D:\test.php(19): strtotime(NULL) #1 {main} thrown in D:\test.php on line 19

It takes a few seconds to run, so it's clearly doing something in the background before it bails! :-)

0
 
nikez2k4Author Commented:
Oh, line 19 is:

      $s_date = date ( 'd/m/Y', strtotime ( $rs->Fields['LogDate'] ) );
0
 
Richard QuadlingSenior Software DeverloperCommented:
Can you add ...

echo 'Got an ID and date of ';
var_dump($i_id);
var_dump($s_date);
echo '<br />';

after the assignment of $i_id and $s_date.

CANNOT IGNORE ERRORS, WARNINGS OR NOTICES!!!! Why do you think they exist?

If you car said "NO PETROL"! you would just say "Hey nice little warning", get in the car and hope to get to work on time!!!!!
0
 
nikez2k4Author Commented:
Hehe. Well, some PHP warnings aren't bad news, these obviously are!

After adding the output code, I get lots of these (snippet blow)
-
Warning: Illegal offset type in D:\test.php on line 32
Got an ID and date of object(variant)#6610 (0) { } string(10) "01/01/1970"

Warning: Illegal offset type in D:\test.php on line 30

Warning: Illegal offset type in D:\test.php on line 30

Warning: Illegal offset type in D:\test.php on line 32
Got an ID and date of object(variant)#6611 (0) { } string(10) "01/01/1970"

Warning: Illegal offset type in D:\test.php on line 30

Warning: Illegal offset type in D:\test.php on line 30

Warning: Illegal offset type in D:\test.php on line 32
Got an ID and date of object(variant)#6612 (0) { } string(10) "03/10/2006"

Warning: Illegal offset type in D:\test.php on line 30

Warning: Illegal offset type in D:\test.php on line 30

Warning: Illegal offset type in D:\test.php on line 32
Got an ID and date of object(variant)#6613 (0) { } string(10) "03/10/2006"
-

Line 30 and 32 are as per 25 and 27 in the previous post...
0
 
Richard QuadlingSenior Software DeverloperCommented:
OK. Try removing the 2 var_dumps and use ...

var_dump($rs->Fields);

Let's see what comes back from the $rs.

You do realise that using ODBC would give you NONE of these problems!!!!! And it would be faster and more portable!!!!
0
 
nikez2k4Author Commented:
Snippet:
-
Warning: Illegal offset type in D:\test.php on line 29

Warning: Illegal offset type in D:\test.php on line 29

Warning: Illegal offset type in D:\test.php on line 31
Got an ID and date of object(variant)#3280 (0) { }
-

ODBC - Yeah, been thinking about that? Do you know a way? I've never used it before (with PHP)

0
 
Richard QuadlingSenior Software DeverloperCommented:
Yes.

But with the COM problem I cannot see how you get data out of the com. The principle I've provided will work! IF you can get the data out of the COM.
0
 
nikez2k4Author Commented:
Happy to try another/better alternative if you can help? I originally used the ADO because it was straight forward for grabbing all the data out of it - but then it started to get complicated :-)
0
 
Richard QuadlingSenior Software DeverloperCommented:
The most important part of the ODBC is knowing what the DSN (DataSoureName) string is. I construct mine on the fly specifically for each database server as I don't want to have one in the system for everyone to see.

odbc_connect("DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=$db", "", "");

is probably all you would need.

So.

Try this ...

<?php
// DB, SQL and local storage.
$db = "new.mdb";
$s_SQL = 'SELECT ID, LogDate, Interval, Value FROM tblWerte WHERE ID=1 OR ID=3 ORDER BY LogDate DESC';
$a_last7days = array(1 => array (), 3 => array () );

// Connect to the database and run the query.
$r_conn = odbc_pconnect("DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=$db", '', '' ); // Persistent connection to save time reconnecting.
$r_results = odbc_exec($r_conn, $s_SQL );

/*
Trawl the data whilst ...
1 - We have NOT reached the end of the file AND
2 - We have NOT got 7 days for id=1 AND
3 - We have NOT got 7 days for id=3
*/
while(False !== ($a_row = odbc_fetch_array($r_results)) && (count($a_last7days[1])!== 7) && count($a_last7days[3] !== 7))
      {
      $i_id = $a_row['ID']; // Get the id from the row - looks easier to read and is redundant.
      $s_logDate = date('Ymd', strtotime($a_row['LogDate'])); // Get the DATE only part of the transaction date.
      
        // For the id is the date missing and do we have less than 7 days.
      if(!isset($a_last7days[$i_id][$s_logDate]) && (count($a_last7days[$i_id]) < 7))
            {
                // Remember the original data for this date.
            $a_last7days[$i_id][$s_logDate] = $a_row;
            }
      }
// Free the results - clean up!!!
odbc_free_result($r_results);

// Let's see the results.
echo '<pre>' . var_export($a_last7days, True) . '</pre>';
?>
0
 
nikez2k4Author Commented:
Hi.  I'd just started reading about Odbc stuff.  Yeah, looks like it could be better.  I changed the $db var for the current DB.
and this is what I get on the screen:
-
Warning: odbc_exec() [function.odbc-exec]: SQL error: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1., SQL state 07001 in SQLExecDirect in D:\index5.php on line 13

Warning: odbc_fetch_array(): supplied argument is not a valid ODBC result resource in D:\test5.php on line 21

Warning: odbc_free_result(): supplied argument is not a valid ODBC result resource in D:\test5.php on line 34

array (
  1 =>
  array (
  ),
  3 =>
  array (
  ),
)

----
Line 13:
$r_results = odbc_exec($r_conn, $s_SQL );
 
Line 21:
while(False !== ($a_row = odbc_fetch_array($r_results)) && (count($a_last7days[1])!== 7) && count($a_last7days[3] !== 7))
 
Line 34:
odbc_free_result($r_results);
----




0
 
nikez2k4Author Commented:
Ah! Typo! Fixed it and get:
-
array (
  1 =>
  array (
    20060516 =>
    array (
      'KanalID' => '1',
      'LogDatumZeit' => '2006-05-16 09:00:00',
      'LogInterval' => '15',
      'Wert' => '1.0',
    ),
    20060515 =>
    array (
      'KanalID' => '1',
      'LogDatumZeit' => '2006-05-15 20:45:00',
      'LogInterval' => '15',
      'Wert' => '1.0',
    ),
    20060514 =>
    array (
      'KanalID' => '1',
      'LogDatumZeit' => '2006-05-14 20:45:00',
      'LogInterval' => '15',
      'Wert' => '1.0',
    ),
    20060513 =>
    array (
      'KanalID' => '1',
      'LogDatumZeit' => '2006-05-13 20:15:00',
      'LogInterval' => '15',
      'Wert' => '11.0',
    ),
    20060512 =>
    array (
      'KanalID' => '1',
      'LogDatumZeit' => '2006-05-12 20:30:00',
      'LogInterval' => '15',
      'Wert' => '11.0',
    ),
    20060511 =>
    array (
      'KanalID' => '1',
      'LogDatumZeit' => '2006-05-11 20:45:00',
      'LogInterval' => '15',
      'Wert' => '1.0',
    ),
    20060510 =>
    array (
      'KanalID' => '1',
      'LogDatumZeit' => '2006-05-10 20:45:00',
      'LogInterval' => '15',
      'Wert' => '1.0',
    ),
  ),
  3 =>
  array (
    20060516 =>
    array (
      'KanalID' => '3',
      'LogDatumZeit' => '2006-05-16 09:00:00',
      'LogInterval' => '15',
      'Wert' => '0.1622059',
    ),
    20060515 =>
    array (
      'KanalID' => '3',
      'LogDatumZeit' => '2006-05-15 20:45:00',
      'LogInterval' => '15',
      'Wert' => '0.4221842',
    ),
    20060514 =>
    array (
      'KanalID' => '3',
      'LogDatumZeit' => '2006-05-14 20:45:00',
      'LogInterval' => '15',
      'Wert' => '0.5580518',
    ),
    20060513 =>
    array (
      'KanalID' => '3',
      'LogDatumZeit' => '2006-05-13 20:15:00',
      'LogInterval' => '15',
      'Wert' => '0.2715577',
    ),
    20060512 =>
    array (
      'KanalID' => '3',
      'LogDatumZeit' => '2006-05-12 20:30:00',
      'LogInterval' => '15',
      'Wert' => '1.733723',
    ),
    20060511 =>
    array (
      'KanalID' => '3',
      'LogDatumZeit' => '2006-05-11 20:45:00',
      'LogInterval' => '15',
      'Wert' => '2.183499',
    ),
    20060510 =>
    array (
      'KanalID' => '3',
      'LogDatumZeit' => '2006-05-10 20:45:00',
      'LogInterval' => '15',
      'Wert' => '2.174336',
    ),
  ),
)

-
0
 
nikez2k4Author Commented:
The fields are different cos I've just been given a german version of hte DB, and I tried it on both - and pasted the German one in by accident, but yeah - Awesome! :-)
0
 
nikez2k4Author Commented:
Thanks loads for this.  Really great!  - Last question! - Is there any way I can fomat the output?
Thanks again :-)
0
 
Richard QuadlingSenior Software DeverloperCommented:
Ok. 1 comment.

The above code looks for the last reading for 7 days where a reading takes place. If a day is missed it is excluded from the list and it would be 8 days ago. sort of thing.
0
 
Richard QuadlingSenior Software DeverloperCommented:
How do you want to output the data?


XML/CSV/HTML?
0
 
nikez2k4Author Commented:
HTML, please :-)
0
 
Richard QuadlingSenior Software DeverloperCommented:
Build the table you want to see and I'll show you how to populate it. What order do you want? Do you want id1 next to id2? underneath? how?
0
 
nikez2k4Author Commented:
I guess something similar to what I had at the top:

For each ID, something like this:
-
<table width="40%" border="0" cellspacing="0" cellpadding="1">
  <tr>
    <td width="56%">Date (last entry at end of the day)</td>
    <td width="44%">Value;</td>
  </tr>
  <tr>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
  </tr>
</table>

Be interested to know the CSV way too, if you dont mind! :-)
0
 
Richard QuadlingSenior Software DeverloperCommented:
Loops and echos would do, but that's the dull bit! Surely you can do that!!!???
0
 
Richard QuadlingSenior Software DeverloperCommented:
Maybe ...

<?php
// Get the time into the data. Notice the leading & to the arrays!!!!!
$a_days = array();
foreach($a_last7days as $i_id => &$a_7days_data)
      {
      foreach($a_7days_data as $s_date => &$a_day_data)
            {
            $a_days[$i_id][] = $s_date;
            $a_day_data['time'] = date('H:i:s', strtotime($a_day_data['LogDatumZeit'];
            }
      }

echo <<< END_TABLE
<table border="1">
      <caption>Last 7 days final readings</caption>
      <thead>
            <tr>
                  <th rowspan="2">Date</th>
                  <th colspan="3">ID : 1</th>
                  <th colspan="3">ID : 3</th>
            </tr>
            <tr>
                  <th>Time</th>
                  <th>LogInterval</th>
                  <th>Wert</th>
            </tr>
      </thead>
      <tbody>
            <tr>
                  <td>{$a_last7days[1][$a_days[1][0]]['time']}</td>
                  <td>{$a_last7days[1][$a_days[1][0]]['LogInterval']}</td>
                  <td>{$a_last7days[1][$a_days[1][0]]['Wert']}</td>
                  <td>{$a_last7days[3][$a_days[3][0]]['time']}</td>
                  <td>{$a_last7days[3][$a_days[3][0]]['LogInterval']}</td>
                  <td>{$a_last7days[3][$a_days[3][0]]['Wert']}</td>
            </tr>
            <tr>
                  <td>{$a_last7days[1][$a_days[1][1]]['time']}</td>
                  <td>{$a_last7days[1][$a_days[1][1]]['LogInterval']}</td>
                  <td>{$a_last7days[1][$a_days[1][1]]['Wert']}</td>
                  <td>{$a_last7days[3][$a_days[3][1]]['time']}</td>
                  <td>{$a_last7days[3][$a_days[3][1]]['LogInterval']}</td>
                  <td>{$a_last7days[3][$a_days[3][1]]['Wert']}</td>
            </tr>            
            <tr>
                  <td>{$a_last7days[1][$a_days[1][2]]['time']}</td>
                  <td>{$a_last7days[1][$a_days[1][2]]['LogInterval']}</td>
                  <td>{$a_last7days[1][$a_days[1][2]]['Wert']}</td>
                  <td>{$a_last7days[3][$a_days[3][2]]['time']}</td>
                  <td>{$a_last7days[3][$a_days[3][2]]['LogInterval']}</td>
                  <td>{$a_last7days[3][$a_days[3][2]]['Wert']}</td>
            </tr>
            <tr>
                  <td>{$a_last7days[1][$a_days[1][3]]['time']}</td>
                  <td>{$a_last7days[1][$a_days[1][3]]['LogInterval']}</td>
                  <td>{$a_last7days[1][$a_days[1][3]]['Wert']}</td>
                  <td>{$a_last7days[3][$a_days[3][3]]['time']}</td>
                  <td>{$a_last7days[3][$a_days[3][3]]['LogInterval']}</td>
                  <td>{$a_last7days[3][$a_days[3][3]]['Wert']}</td>
            </tr>
            <tr>
                  <td>{$a_last7days[1][$a_days[1][4]]['time']}</td>
                  <td>{$a_last7days[1][$a_days[1][4]]['LogInterval']}</td>
                  <td>{$a_last7days[1][$a_days[1][4]]['Wert']}</td>
                  <td>{$a_last7days[3][$a_days[3][4]]['time']}</td>
                  <td>{$a_last7days[3][$a_days[3][4]]['LogInterval']}</td>
                  <td>{$a_last7days[3][$a_days[3][4]]['Wert']}</td>
            </tr>
            <tr>
                  <td>{$a_last7days[1][$a_days[1][5]]['time']}</td>
                  <td>{$a_last7days[1][$a_days[1][5]]['LogInterval']}</td>
                  <td>{$a_last7days[1][$a_days[1][5]]['Wert']}</td>
                  <td>{$a_last7days[3][$a_days[3][5]]['time']}</td>
                  <td>{$a_last7days[3][$a_days[3][5]]['LogInterval']}</td>
                  <td>{$a_last7days[3][$a_days[3][5]]['Wert']}</td>
            </tr>
            <tr>
                  <td>{$a_last7days[1][$a_days[1][6]]['time']}</td>
                  <td>{$a_last7days[1][$a_days[1][6]]['LogInterval']}</td>
                  <td>{$a_last7days[1][$a_days[1][6]]['Wert']}</td>
                  <td>{$a_last7days[3][$a_days[3][6]]['time']}</td>
                  <td>{$a_last7days[3][$a_days[3][6]]['LogInterval']}</td>
                  <td>{$a_last7days[3][$a_days[3][6]]['Wert']}</td>
            </tr>
      </tbody>
</table>
END_TABLE;

?>
0
 
Richard QuadlingSenior Software DeverloperCommented:
<?php
// Get the time into the data. Notice the leading & to the arrays!!!!!
$a_days = array();
foreach($a_last7days as $i_id => &$a_7days_data)
     {
     foreach($a_7days_data as $s_date => &$a_day_data)
          {
          $a_days[$i_id][] = $s_date;
          $a_day_data['time'] = date('H:i:s', strtotime($a_day_data['LogDatumZeit'];
          }
     }

echo <<< END_TABLE
<table border="1">
      <caption>Last 7 days final readings</caption>
      <thead>
            <tr>
                  <th rowspan="2">Date</th>
                  <th colspan="3">ID : 1</th>
                  <th colspan="3">ID : 3</th>
            </tr>
            <tr>
                  <th>Time</th>
                  <th>LogInterval</th>
                  <th>Wert</th>
                  <th>Time</th>
                  <th>LogInterval</th>
                  <th>Wert</th>
            </tr>
      </thead>
      <tbody>
            <tr>
                  <td>{a_days[1][0]}</td>
                  <td>{$a_last7days[1][$a_days[1][0]]['time']}</td>
                  <td>{$a_last7days[1][$a_days[1][0]]['LogInterval']}</td>
                  <td>{$a_last7days[1][$a_days[1][0]]['Wert']}</td>
                  <td>{$a_last7days[3][$a_days[3][0]]['time']}</td>
                  <td>{$a_last7days[3][$a_days[3][0]]['LogInterval']}</td>
                  <td>{$a_last7days[3][$a_days[3][0]]['Wert']}</td>
            </tr>
            <tr>
                  <td>{a_days[1][1]}</td>
                  <td>{$a_last7days[1][$a_days[1][1]]['time']}</td>
                  <td>{$a_last7days[1][$a_days[1][1]]['LogInterval']}</td>
                  <td>{$a_last7days[1][$a_days[1][1]]['Wert']}</td>
                  <td>{$a_last7days[3][$a_days[3][1]]['time']}</td>
                  <td>{$a_last7days[3][$a_days[3][1]]['LogInterval']}</td>
                  <td>{$a_last7days[3][$a_days[3][1]]['Wert']}</td>
            </tr>            
            <tr>
                  <td>{a_days[1][2]}</td>
                  <td>{$a_last7days[1][$a_days[1][2]]['time']}</td>
                  <td>{$a_last7days[1][$a_days[1][2]]['LogInterval']}</td>
                  <td>{$a_last7days[1][$a_days[1][2]]['Wert']}</td>
                  <td>{$a_last7days[3][$a_days[3][2]]['time']}</td>
                  <td>{$a_last7days[3][$a_days[3][2]]['LogInterval']}</td>
                  <td>{$a_last7days[3][$a_days[3][2]]['Wert']}</td>
            </tr>
            <tr>
                  <td>{a_days[1][3]}</td>
                  <td>{$a_last7days[1][$a_days[1][3]]['time']}</td>
                  <td>{$a_last7days[1][$a_days[1][3]]['LogInterval']}</td>
                  <td>{$a_last7days[1][$a_days[1][3]]['Wert']}</td>
                  <td>{$a_last7days[3][$a_days[3][3]]['time']}</td>
                  <td>{$a_last7days[3][$a_days[3][3]]['LogInterval']}</td>
                  <td>{$a_last7days[3][$a_days[3][3]]['Wert']}</td>
            </tr>
            <tr>
                  <td>{a_days[1][4]}</td>
                  <td>{$a_last7days[1][$a_days[1][4]]['time']}</td>
                  <td>{$a_last7days[1][$a_days[1][4]]['LogInterval']}</td>
                  <td>{$a_last7days[1][$a_days[1][4]]['Wert']}</td>
                  <td>{$a_last7days[3][$a_days[3][4]]['time']}</td>
                  <td>{$a_last7days[3][$a_days[3][4]]['LogInterval']}</td>
                  <td>{$a_last7days[3][$a_days[3][4]]['Wert']}</td>
            </tr>
            <tr>
                  <td>{a_days[1][5]}</td>
                  <td>{$a_last7days[1][$a_days[1][5]]['time']}</td>
                  <td>{$a_last7days[1][$a_days[1][5]]['LogInterval']}</td>
                  <td>{$a_last7days[1][$a_days[1][5]]['Wert']}</td>
                  <td>{$a_last7days[3][$a_days[3][5]]['time']}</td>
                  <td>{$a_last7days[3][$a_days[3][5]]['LogInterval']}</td>
                  <td>{$a_last7days[3][$a_days[3][5]]['Wert']}</td>
            </tr>
            <tr>
                  <td>{a_days[1][6]}</td>
                  <td>{$a_last7days[1][$a_days[1][6]]['time']}</td>
                  <td>{$a_last7days[1][$a_days[1][6]]['LogInterval']}</td>
                  <td>{$a_last7days[1][$a_days[1][6]]['Wert']}</td>
                  <td>{$a_last7days[3][$a_days[3][6]]['time']}</td>
                  <td>{$a_last7days[3][$a_days[3][6]]['LogInterval']}</td>
                  <td>{$a_last7days[3][$a_days[3][6]]['Wert']}</td>
            </tr>
      </tbody>
</table>
END_TABLE;

?>

SOrry. Missed a bit.

Save this as a plain HTML and view it.

Looks OK.

0
 
nikez2k4Author Commented:
Thx. That's less dull ;-)

Had to add the & to <td>{a_days[1][6]}</td> and extra ))'s  $a_day_data['time'] = date('H:i:s', strtotime($a_day_data['LogDatumZeit']));
Thanks a lot for your help :-)
0
 
Richard QuadlingSenior Software DeverloperCommented:
$ even?

)):-( no ones perfect
0
 
Richard QuadlingSenior Software DeverloperCommented:
500 easy points!

COUGH BLOODY COUGH!!!
0
 
nikez2k4Author Commented:
Yeah, defo :-)
Will see if I can figure out how to do that - as I'd already given you the points but - thanks, very grateful!
0
 
Richard QuadlingSenior Software DeverloperCommented:
I'm getting something else? Can't wait? What is it???
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 25
  • 20
Tackle projects and never again get stuck behind a technical roadblock.
Join Now