Jump to content


Photo

AAAARRRGGHHH!!!!!!!!!!!!!!!


  • Please log in to reply
11 replies to this topic

#1 Moron

Moron
  • Members
  • PipPipPip
  • Advanced Member
  • 368 posts

Posted 21 August 2006 - 01:15 PM

I'm as sick of posting this as you folks are of reading it. Trust me on this! But will someone PLEASE PLEASE PLEASE tell me what is happening here??????? A week of my life on this is ENOUGH!!!!!!

My code:

while ($RESULT = mssql_fetch_assoc($RESULTDS))   {

$lastjuly = strtotime("July 1 $last");
$todaysdate = date(Ymd);
$todaystring = strtotime($todaysdate);
$recdate = "$RESULT[Lmo]$RESULT[Lda]$RESULT[LYR]";
$recdatestring = strtotime($recdate);

if ($recdatestring >= $lastjuly)  {


echo "<tr align=center>";
echo "<td>";


echo $RESULT['Lmo'];
echo "/";
echo $RESULT['Lda'];
echo "/";
echo $RESULT['LYR'];
echo "</td>";

echo "<td>";
echo $RESULT['Leave Code'];
echo "</td>";

echo "<td>";
$TotalGenHours = $TotalGenHours + $RESULT['Hours'];
echo $RESULT['Hours'];
echo "</td>";

echo "</tr>";
}
}


Note the lines I put in bold. If I echo $recdate, it gives me 03282001 and pulls ZERO records.
?????????????????????????????????????????????????????????????????

Anybody?



#2 ToonMariner

ToonMariner
  • Members
  • PipPipPip
  • Advanced Member
  • 3,342 posts
  • LocationNewcastle upon Tyne, UK

Posted 21 August 2006 - 01:17 PM

that line is saying recdate = monthdayyear as pulled from the database (so it will look like 082106 or similar).

If it is not working try.

$recdate = $RESULT[Lmo] . $RESULT[Lda] . $RESULT[LYR];
follow me on twitter @PHPsycho

#3 Moron

Moron
  • Members
  • PipPipPip
  • Advanced Member
  • 368 posts

Posted 21 August 2006 - 01:34 PM

that line is saying recdate = monthdayyear as pulled from the database (so it will look like 082106 or similar).

If it is not working try.

$recdate = $RESULT[Lmo] . $RESULT[Lda] . $RESULT[LYR];


Thanks, but the formatting isn't the problem. The problem seems to be that it's seeing the date of a specific record on March 28, 2001. Therefore, since that specific record is not after July 1, 2006, then no records are pulled. What I'm trying to tell it (with GREAT DIFFICULTY) is....

Go through ALL the records. *IF* a record is *ON OR AFTER* July 1, 2006, then display it.


#4 trq

trq
  • Staff Alumni
  • Advanced Member
  • 31,041 posts

Posted 21 August 2006 - 01:36 PM

As Ive said many times before you should be trying to do this in your query.

#5 ToonMariner

ToonMariner
  • Members
  • PipPipPip
  • Advanced Member
  • 3,342 posts
  • LocationNewcastle upon Tyne, UK

Posted 21 August 2006 - 01:37 PM

Then ist something to do with your query....
follow me on twitter @PHPsycho

#6 Moron

Moron
  • Members
  • PipPipPip
  • Advanced Member
  • 368 posts

Posted 21 August 2006 - 01:48 PM

As Ive said many times before you should be trying to do this in your query.


Here's the part I don't understand, though; The query is pulling exactly what I want it to pull, which is all records. In other words, I don't want to miss anything. I can make it pull by calendar year, as in all records for that employee number for a specific year and so on, but I want to pull records after July 1.


#7 ToonMariner

ToonMariner
  • Members
  • PipPipPip
  • Advanced Member
  • 3,342 posts
  • LocationNewcastle upon Tyne, UK

Posted 21 August 2006 - 02:03 PM

the state that in the query under the where clause.

Which ever field holds the date use 'WHERE `date` > 'july 1st' (obviously change that so the formats match otherwise it'll fail)
follow me on twitter @PHPsycho

#8 Moron

Moron
  • Members
  • PipPipPip
  • Advanced Member
  • 368 posts

Posted 21 August 2006 - 02:11 PM

the state that in the query under the where clause.

Which ever field holds the date use 'WHERE `date` > 'july 1st' (obviously change that so the formats match otherwise it'll fail)


But this isn't a one-time deal. July 1 is the start of our fiscal year. Therefore, I have to use variables that define the previous July 1. Is there a way to plug a variable into a query?



#9 AndyB

AndyB
  • Staff Alumni
  • Advanced Member
  • 5,465 posts
  • LocationToronto

Posted 21 August 2006 - 02:12 PM

Some test code - and the results it produces showing that the strtotime argument you're using isn't working:

$recdate = "03282001";
echo strtotime($recdate); // outputs -1
echo "<br>";

$recdate = "03-28-2001"; // test date
echo strtotime($recdate); // outputs 1285128000
echo "<br>";

$last = "2001";
$recdate = "07-01-". $last;
$lastjuly = strtotime($recdate); 
echo $lastjuly; // outputs 1340424000

That should point the way to success ... which is to generate the record date from the database using:

$recdate2 = $RESULT['Lmo']. "-". $RESULT['Lda']. "-". $RESULT['LYR']; // what is this record's date?


I do realise that you're working with a legacy database where the 'date' is stored in that funny manner, but (and I think I've said it before) fixing the database to use a single real date field would have avoided all the aggravation you're having trying to work around the fundamentally flawed date storage structure.  And I can't believe it woud take very long to fix the database - add new field named 'real_date', date format, then loop thru' your database generating dates in yyyy-mm-dd format by concatenating three field values and update each row with a real date.
Legend has it that reading the manual never killed anyone.
My site

#10 Moron

Moron
  • Members
  • PipPipPip
  • Advanced Member
  • 368 posts

Posted 21 August 2006 - 02:20 PM

Thanks a lot, Andy. Based on what you posted, I tried this:

$recdate2 = $RESULT['Lmo']. "-". $RESULT['Lda']. "-". $RESULT['LYR'];
$recdate2string = strtotime($recdate2);

if ($recdate2string >= $lastjuly)  {

...etc....

It still pulls all records going back to 2001.

I think you're right, though; I'm going to look at simply adding a consolidated date field in the database. I guess nobody considered this back in 2001.



#11 AndyB

AndyB
  • Staff Alumni
  • Advanced Member
  • 5,465 posts
  • LocationToronto

Posted 21 August 2006 - 02:23 PM


It still pulls all records going back to 2001.


I'd echo the value of $lastjuly in your code to make sure it really is the same as 07-01-2001 would be.
Legend has it that reading the manual never killed anyone.
My site

#12 Moron

Moron
  • Members
  • PipPipPip
  • Advanced Member
  • 368 posts

Posted 21 August 2006 - 04:08 PM


It still pulls all records going back to 2001.


I'd echo the value of $lastjuly in your code to make sure it really is the same as 07-01-2001 would be.


You were right, Andy. Adding this to the query in the WHERE statement did the trick:

and LH.[LYR] >= '$last'  and LH.[Lda] >= '01' and LH.[Lmo] >= '07'


Thanks again!





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users