help with missing rows query

Mucello

I have a database with hourly weather observations. The user is allowed to pull them for any date range and my scripts will display the data in a nice format. No big deal. But sometimes, there is missing data... in other words, sometimes there may be an hour or two where there were no observations recorded. What I want to do is somehow have my output show that there was a missing row of data.

Here is a simplified example of what my output does now with a missing row:
2004-05-01 02:00
2004-05-01 03:00
2004-05-01 05:00
2004-05-01 06:00
Notice the 04:00 row is missing. What I want to do is have my scripts instead show this:

2004-05-01 02:00
2004-05-01 03:00
data unavailable
2004-05-01 05:00
2004-05-01 06:00

So how can I pull this off? Can I do it in the MySQL query? If so, how?

Thanks,
Mark

Nixon

Could you possibly post your code, I think you will be using the while statement so... you could try


if (empty($row['weather'])) { print "There is no data"; }

Where $row being your array and weather being whatever the column is on your database.

Hope this helps.

Mucello

I appreciate the reply, and I see where you're trying to go with that, but it won't work. I don't think my solution will be as simple as checking for an empty variable.

I am indeed using while loops... I loop through each row that is returned from the query using mysql_fetch_array(). But the question still remains-- how does my script know when a row of data is missing from the database? If it isn't a part of the array that is returned, then empty() isn't going to tell me.

laserlight

But the question still remains-- how does my script know when a row of data is missing from the database?

How do you know when a row of data is missing from the database?

Mucello

Originally posted by laserlight
How do you know when a row of data is missing from the database?

I know because there are 24 hours in a day and should therefore be 24 rows per day (one observation per hour).

devinemke

I don't think my solution will be as simple as checking for an empty variable.

in fact, it is that simple. here's a very simplified example (i don't know your exact data structure)

$result = mysql_query ('SELECT id, date_time FROM table');
while ($row = mysql_fetch_assoc ($result)) {$array[$row['id']] = $row['date_time'];}

foreach ($array as $key => $value)
{
	if ($value) {echo $key . ' : ' . $value . '<br>';}
	else {echo 'data unavailable<br>';}
}

laserlight

devinemke, the problem with that is that the row doesnt even exist in the table.

I know because there are 24 hours in a day and should therefore be 24 rows per day (one observation per hour).

That's one way of looking at it.
Unfortunately, that is also one way that is difficult to translate into an algorithm, since it makes use of human pattern recognition, which can be a bit of an overkill to implement (artificial neural networks, anyone?).

If the observations are guaranteed to be at: yyyy-mm-dd HH:MM
for unique values of "HH", then a solution would be to check that "HH" is in running order.
This cant be (easily) done on the database with your current database setup, so you'll have to use PHP.
To obtain the value of HH, you can either use regular expressions, or better still, take advantage of the fixed length of the date string and get the substring corresponding to HH

After that, just implement the check by means of a counter.
You'll have to be careful about when the counter starts and ends though, since that is dependent on the range specified.

Mucello

laserlight-- that makes sense and that was the direction I was leaning towards. I was hoping I would be able to pull it off in MySQL but it looks like PHP is where I'll have to do it. The HH:MM is indeed known so I can work with that. It'll be dirty but I'll get it eventually.

Thanks,
Mark

Nixon

How about something along the lines of:


while ($i<=24)

{

//query

$i=0;

$var=substr($mysql_time, -4);

if ($i<10) {

$testvar="0";
$testvar.=$i;
$testvar.=":00";

}

else

{

$testvar=$i;
$testvar.=":00";

}

if ($var==$testvar) { echo($mysql_data); } else { print "Data Unavailable"; }

$i++

}

}

Not sure how that will work just done it off the top of my head 🙂 - I'm sure you can improvise it.

Mucello

Nixon-- that's the direction I'm going. Your while loop however has me doing a query for each loop iteration but other than that looks good. Oh and also, it doesn't really account for more than 24 hours skipped... I'm getting close to a solution though🙂

After I figure out exactly how I'm going to do this, I'll post the relevant code snippets here in case someone else needs it for reference in the future.
-Mark