Need some ideas from a loop expert

[skeetley]

Here is what I am trying to accomplish. Now what I have works, but I have 100 stations and there has be a better way to do this. What I am doing is displaying the first record for each Station, and then no more from that station.

$result = mysql_query ("SELECT * FROM blah blah");

$Station1 = "Station1";
$Station2 = "Station2";
$Station3 = "Station3";
.
.
.


while ($row = mysql_fetch_assoc($result))
{
    extract ($row); 
    $array[$Station_ID] =  blah, blah

if (($Station_ID == $Station1) OR ($Station_ID==$Station2) OR ($Station_ID==$Station3) OR (....) )
  {
  echo "blah blah"; 

  if (($Station_ID==$Station1)) {$Station1="donotshowanymore";}
  if (($Station_ID==$Station2)) {$Station2="donotshowanmore";}
  if (($Station_ID==$Station3)) {$Station3="donotshowanymore";}
  .
  .
  .

  }
}

[nemonoman]

Sounds like your building logic into the loop that might better be placed in the SQL.

Sounds like you are looking for 1 record for each workstation, right?

So your SQL is PROBABLY going to be better served by a query that just gets 1 record per workstation:

SELECT *
FROM blah
GROUP BY workstationid

Question is: what do you want that 1 record to be? the most recent workstation record based on timestamp?

SELECT blah.*, max(timestamp)
FROM blah
GROUP BY workstationid

Anyway, worth thinking about:

[skeetley]

Thanks for the help. I can't get get just one record for the station because some stations do not report all the time. So I need to get a one day interval of station data and then look through it for the most recent record.

My SQL gets the one day interval, sorts by date and time, and then I am trying to use this loop as way to just print the latest record for each station.

Make sense?

[nemonoman]

If all you want is the most recent record, something like my method will work, as it would produce the most recent timestamped record for each workstation.

[skeetley]

Oh sorry, I overlooked the max part in your SQL statement because I didn't know it was a command.

My SQL is :
$result = mysql_query ("SELECT * FROM Actual_Hourly_2004, Stations WHERE (Actual_Hourly_2004.FMFP_ID=Stations.FMFP_ID) AND (Date_Valid > current_date - interval 2 day) AND (Stations.Forecast LIKE 'y') ORDER by 'Date_Valid', 'Time_Valid' DESC,'Grp','Pos'");

Technically I don't need to sort by Date_Valid because the interval of one day only return that day's records.

So how do I set this up?

$result = mysql_query ("SELECT *, max(Time_Valid) FROM Actual_Hourly_2004, Stations WHERE (Actual_Hourly_2004.FMFP_ID=Stations.FMFP_ID) AND (Date_Valid > current_date - interval 2 day) AND (Stations.Forecast LIKE 'y') ORDER by 'Date_Valid', 'Time_Valid' DESC,'Grp','Pos'");

edit - I put 2 days in there because I am just testing and have no data from today in the db. That command above returns no records though.

[nemonoman]

Start with this:
$result = mysql_query ("SELECT *, max(Time_Valid)
FROM Actual_Hourly_2004, Stations WHERE (Actual_Hourly_2004.FMFP_ID=Stations.FMFP_ID)
AND (Date_Valid > current_date() - interval 2 day)
AND Stations.Forecast='y' GROUP BY Stations.FMFP_ID
ORDER by 'Date_Valid', 'Time_Valid' DESC,'Grp','Pos'");

Not knowing your precise data schema, I'm making a best guess.

I've also made a few minor edits to your statemen, because my guess is that it's throwing an error you haven't bothered to catch.

[skeetley]

nemonoman, that works and thanks for cleaning up my SQL statement before I got into trouble.

The only thing that doesn't seem to be working right is which time it returns. It is returning one record for each station great and in the siplay order I want, but the times are wrong. There doesn't appear to be an reasoning to them either. Most are the min(Time_Valid) of 00:00:00, but the odd one is 09:00:00 or 07:00:00 even though all of them that I have checked have a complete record set for today.

edit - How is this for weird, I changed max(Time_Valid) to min(Time_Valid) in the SQL statement just to see what would happen and nothing changed in the output.

[nemonoman]

Sorry: you've got a data problem now, not a logic problem. Make sure you're looking at the column you mean to: I would guess what you want is max(somedatefield), not max(someTimefield). Also you can try max(concat(somedatefield,sometimefield))

[skeetley]

Well the 1 day interval takes care of my max(somedatefield), so then I just need max(my time field) Time_Valid is of Type Time in the db.

I wish this would work. It would save me a tonne of work. That max(Time_Valid) is doing something because it only returns one row. It is just not the right row. I wonder where my "data" problem is.

[nemonoman]

So sorry, I suggest again that you try:

max(concat(somedatefield,sometimefield))

If you think about it, you'll understand:

if (as it sounds) you've got some gizmo that updates timefield, and if timefield is always updated by the gizmo at the same time on different days, than max(time) and min(time) will always be the same and neither will be very helpful to your search. What you want is the maximum date/time record. This would be better managed via a timestamp field which would store all the info in 1 field like today this moment is something like 20040318122400

[skeetley]

What I have is at database that is updated hourly, but it is appended, not overwritten. So for each hour of each day there is a record for each station. So there should be a min and max for each date and time field. Like for today for Station1 there would be records for 00:00:00, 01:00:00, ....., 15:00:00. So I was hoping max(Time_valid) would just show the record of 15:00:00 but it doesn't.

Replacing max(Time_Valid) with max(concat(Date_Valid,Time_Valid)) doesn't change anything because the date is the same for all records returned due to the interval deal so the only thing that is different is the Time_Valid field and it doesn't seem to want to max that either way.

Thanks for continuing to try and help me.

[nemonoman]

If you're getting max(time) and min(time) the same, something's wrong with your query or your data. Check out the raw data.

[skeetley]

The raw data is all good. I have been checking it in phpmyadmin to verify everything.

My query is:

$result = mysql_query ("SELECT *, max(Time_Valid) FROM Actual_Hourly_2004, Stations WHERE (Actual_Hourly_2004.FMFP_ID=Stations.FMFP_ID) AND (`Date_Valid` > current_date() - interval 1 day) AND Stations.Forecast='y' GROUP BY Stations.FMFP_ID ORDER by 'Grp','Pos'"); 

This should be saying select all the fields from both the Stations and Actual_Hourly_2004 tables. I want the data only from today and only the most recent hour that the data was for each FMFP_ID. The Station has to have forecast=y. Put them in order of Grp, Pos for display purposes.

[skeetley]

Maybe this will help you to help me.

This query returns all the records for each hour for each station for today:

$result = mysql_query ("SELECT * 
FROM `Actual_Hourly_2004` 
WHERE 1 AND `Date_Valid` = '2004-03-18' "); 

This query returns nothing:

$result = mysql_query ("SELECT *, max(Time_Valid) 
FROM `Actual_Hourly_2004` 
WHERE 1 AND `Date_Valid` = '2004-03-18' "); 

[skeetley]

Any ideas nemonoman or anyone else?

[nemonoman]

$result = mysql_query ("SELECT FMFP_ID, max(Time_Valid)
FROM Actual_Hourly_2004
WHERE Date_Valid = '2004-03-18'
GROUP BY FMFP_ID");