Checking for holes?

brianb

Hello,

I have a script which pulls data from a particular site once a day. What I want to do though is check for any days that the script might not have run by checking for holes in my MySQL database dates.

For instance say I have entries that look like so...

date = 2003-10-28
date = 2003-10-29
date = 2003-10-31

...so 2003-10-30 would be missing...

What sort of script could I write to check for missing dates in a sequence?

Any ideas?

Coding examples are much appreciated!

Thanks!

WinterDragon


$dates = array("2003-10-28", "2003-10-29", "2003-10-31");

$currentDay = 0;
for($i=0;$i<sizeof($dates);$i++) {
list($year, $month, $day) = explode("-", $dates[$i]);
if($currentDay == 0) {
$currentDay = $day;
}
$nextDay = $currentDay + 1;
$difference = $nextDay - $currentDay;
if($difference > 1) {
echo "Missing...";
}
}

actually this more then likely won't work, I was like writing with the intention and thought of how it would work, and after I started, my brain began to stop, and suddenly I couldn't finish it correctly, so maybe this could at least start you off... shrugs.

HalfaBee

How about

$sql = 'SELECT DAY_OF_YEAR(date_field) from table_name where #put your date range here #';
mysql_query( $sql );

while( list($row[])=mysql_fetch_row($result) );
$size=count($row);
$i=$row[0];
for( $j=0;$j<$size;$j++ ) {
if( $i != $row[$j+$start] )
echo 'missing day number $i';
}
[/code]

HalfaBee

brianb

Ok...

Here's what I've got...not really working right...any ideas why?

<?

$sql = "SELECT ID, DAYOFYEAR(date) AS thedate FROM Dates WHERE date > '2002-01-01'";
$res = mysql_query($sql);

while (list($row[]) = mysql_fetch_row($res)) {

$size = count($row);
$i = $row[thedate];

echo "$size<br>$i";

for ($j=0; $j<$size;$j++) {

if ($i != $row[$j+$start])
echo "This date is missing: $i<br><br>";
}
}

?>

HalfaBee

I assume you did the mysql connection part.
This will only work in a year, not over 2001-12-31 as dayofyear() will return 1 on 2002-1-1

use mysql_fetch_assoc() if you use field names.

HalfaBee

brianb

Ok...

I understand the date part, but my dates start on the first day of 2002 so that shouldn't be an issue.

I changed the loop to grab from mysql_fetch_assoc()...

Now, the script is outputing evey single record in the database...

Example: I have 6600 entries in the database. What the script is outputing is:
1
2
3
4
5
6
7...

All the way to 6600. Any ideas on how to make this work? I have a feeling we're getting close.

Thanks for the help!

HalfaBee

Change this

while (list($id[],$row[]) = mysql_fetch_assoc($res))

I will have a look tonight, I really mucked up.

HalfaBee

Try this

$sql = "SELECT ID, DAYOFYEAR(date) AS thedate FROM Dates WHERE date > '2002-01-01'";
$res = mysql_query($sql);

while (list($id[],$row[]) = mysql_fetch_row($res)) ;

for( $i=1;$i<365;$i++ ) $vals[]=$i;

$diff = array_diff( $vals , $row );
echo "<pre>";
print_r( $diff );

HalfaBee

brianb

With that exact code in place, here's the output I get:

<pre>Array
(
)

Interesting...

HalfaBee

Probably no holes. 🙂