Moron

[quote author=AndyB link=topic=105053.msg419447#msg419447 date=1156170199] [quote author=Moron link=topic=105053.msg419446#msg419446 date=1156170043] It still pulls all records going back to 2001.[/quote] I'd echo the value of $lastjuly in your code to make sure it really is the same as 07-01-2001 would be. [/quote] You were right, Andy. Adding this to the query in the WHERE statement did the trick: [quote]and LH.[LYR] >= '$last'  and LH.[Lda] >= '01' and LH.[Lmo] >= '07' [/quote] Thanks again!

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.

[quote author=ToonMariner link=topic=105053.msg419438#msg419438 date=1156168997] 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) [/quote] 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?

[quote author=thorpe link=topic=105053.msg419412#msg419412 date=1156167392] As Ive said many times before you should be trying to do this in your query. [/quote] 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.

[quote author=ToonMariner link=topic=105053.msg419399#msg419399 date=1156166278] 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]; [/quote] Thanks, but the formatting isn't the problem. The problem seems to be that it's seeing the date of a [b]specific[/b] record on March 28, 2001. Therefore, since that specific record is [b]not[/b] 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.

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: [quote] while ($RESULT = mssql_fetch_assoc($RESULTDS))   { $lastjuly = strtotime("July 1 $last"); $todaysdate = date(Ymd); $todaystring = strtotime($todaysdate); [b]$recdate = "$RESULT[Lmo]$RESULT[Lda]$RESULT[LYR]";[/b] [b]$recdatestring = strtotime($recdate);[/b] 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>"; } }[/quote] Note the lines I put in bold. If I echo $recdate, it gives me 03282001 and pulls ZERO records. ????????????????????????????????????????????????????????????????? Anybody?

[quote author=craygo link=topic=104649.msg417998#msg417998 date=1155911724] That is just showing you what my table is structured like. Has nothing to do with the code. DO NOT INCLUDE IT ON YOUR PAGE!! Well I keep asking how the fields are structured and I get no answer so I set them as varchar for worst case senario. Ray [/quote] I see. Thanks. I first tried it with only the second section and it tossed a syntax error. I'll take another crack at it.

Should this part be inside php tags? [quote author=craygo link=topic=104649.msg417987#msg417987 date=1155910125] [code]CREATE TABLE `testtable` (   `Leave Code` varchar(100) NOT NULL default '',   `Hours` varchar(100) NOT NULL default '',   `LYR` varchar(100) NOT NULL default '',   `Lmo` varchar(100) NOT NULL default '',   `Lda` varchar(100) NOT NULL default '',   `id` int(11) unsigned NOT NULL auto_increment,   PRIMARY KEY  (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ; -- -- Dumping data for table `testtable` -- INSERT INTO `testtable` VALUES ('222', '4', '2006', '01', '01', 1); INSERT INTO `testtable` VALUES ('254', '9', '2006', '02', '02', 2); INSERT INTO `testtable` VALUES ('254', '6', '2006', '07', '01', 3); INSERT INTO `testtable` VALUES ('258', '5', '2006', '08', '01', 4); INSERT INTO `testtable` VALUES ('257', '8', '2006', '09', '01', 5);[/code] [/quote]

[quote author=Ifa link=topic=104649.msg417986#msg417986 date=1155909932] Is the $RESULT['LYR'] two or four numbers? ie 06 or 2006? [/quote] It's four (2006).

[quote author=Ifa link=topic=104649.msg417980#msg417980 date=1155909591] [code=php:0]while ($RESULT = mssql_fetch_assoc($RESULTDS))  { if($RESULT['LYR'].".".$RESULT['Lmo'].".".$RESULT['Lda'] < "2006.7.1") continue; 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>"; } [/code] :) [/quote] Thanks. This doesn't error but it returns no records whatsoever. :-[

[quote author=AndyB link=topic=104649.msg417973#msg417973 date=1155909044] [quote]but I just can't seem to crack the whole "July 1 and forward" problem.[/quote] Did you try the solution Barand posted in this thread? [/quote] Yes, but there were two things I encountered: 1. I couldn't seem to integrate it into the current SELECT statement. 2. Since this is a fiscal year matter, [i]Mo > 06[/i] won't work because the fiscal year will also run from 01 through 06 of 2007.

[quote author=craygo link=topic=104649.msg417946#msg417946 date=1155907774] how is your table structured?? Specifically the field that holds the date. All these guys trying to help but if your date field is a string you are going to have issues. also this does not look right [code]$day=$RESULT['Lmo']."/".$RESULT['Lda']."/".$RESULT['Lda'];   $day_timestamp=strtotime($day);[/code] shouldn't it be [code]  $day=$RESULT['LYR']."/".$RESULT['Lmo']."/".$RESULT['Lda'];   $day_timestamp=strtotime($day);[/code] Ray [/quote] I had tried this and it worked: $combineddate="$RESULT[Lmo]$RESULT[Lda]$RESULT[LYR]"; I could also convert it into a UNIX timestamp, do the math, you name it, but I just can't seem to crack the whole "July 1 and forward" problem.

[quote author=Ifa link=topic=104649.msg417949#msg417949 date=1155907867] So mine didn't work? :( [/quote] Sorry, but yours threw an error. :-[

Look at this adaption of my code, posted by Akrytus: [quote]while ($RESULT = mssql_fetch_assoc($RESULTDS))  {   $day=$RESULT['Lmo']."/".$RESULT['Lda']."/".$RESULT['Lda'];   $day_timestamp=strtotime($day);   if ($day_timestamp>1151726400){     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>";   } } [/quote] I had already tried this, with combining the Month/Day/Year, then converting it into a UNIX timestamp and doing the math, but it STILL keeps giving me all records. Now THIS: if ($RESULT['LYR'] >= 2006)  { ...will give me just records from 2006, but I can't seem to get the July 1 and forward thing to work. Very frustrating.

[quote author=akrytus link=topic=104649.msg417581#msg417581 date=1155841229] What are your errors, what is the output? [/quote] That's just it; it doesn't error at all but the records pulled are all the way back to 2001 when the database was started.

[quote author=akrytus link=topic=104649.msg417540#msg417540 date=1155835225] You have to combine all variables to make 1 and convert it into a unix time stamp.  Then compare to the unix time stamp of 07/01/2006 which I already figured out and put in the code for you.  Then you can compare the date from the dbase to the unix timestamp.  That should do it! [code] while ($RESULT = mssql_fetch_assoc($RESULTDS))  {   $day=$RESULT['Lmo']."/".$RESULT['Lda']."/".$RESULT['Lda'];   $day_timestamp=strtotime($day);   if ($day_timestamp>1151726400){     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>";   } } [/code] Let me know if it works! [/quote] Thanks, but it still pulls records all the way back to 2001. I had tried the UNIX timestamp idea but couldn't get it to work.

[quote author=akrytus link=topic=104649.msg417534#msg417534 date=1155834349] If this was posted before, perhaps I am missing something, but where exactly are you filtering out dates you dont want.  It looks to me as if you are echoing all data from the database? [/quote] True, but I don't want to restrict anything in the query. Is there a way to restrict it within the "while" statement?

Sorry to keep picking everyone's brains here but this has been driving me crazy for three days now. I have records pulling from a database, but I want it to pull ONLY records that are AFTER last July 1 (07/01/2006). The code is: [quote] while ($RESULT = mssql_fetch_assoc($RESULTDS))  { 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>"; } [/quote] It's formatted perfectly, but it's pulling the entire date range going back to 2001.

[quote author=GingerRobot link=topic=104630.msg417425#msg417425 date=1155826271] Oh i see, sorry. I guess the string is not explicit enough. You could try last 01 July or something, but again, that might be too ambiguous. You could try: [code] <?php $month = date('m'); if($month >= 6){ $year = date('Y'); }else{ $year = date('Y') -1; } $lastjuly = strtotime("01 July $year"); echo $lastjuly; echo date("m/d/Y", $lastjuly); ?> [/code] [/quote] Yep, that works. It seems to be a derivation of the "fiscal year" code you posted. Outstanding! And thanks. :)

[quote author=GingerRobot link=topic=104630.msg417415#msg417415 date=1155825752] because you are echoing both the timestamp and the the formatted version? [/quote] Yes, but that's deliberate. Since I'm a total newbie, I tend to echo WAY too many things to see what I'm doing.  :D The problem is that the formatted version should be giving me 07/01/2006, since that was the [i]last July 1[/i] that happened. Instead, it's giving me 06/29/2001. See what I mean? Thanks!

This code: [quote]<?php $lastjuly = strtotime("Last July 1"); echo $lastjuly; echo date("m/d/Y", $lastjuly); ?> .....returns the UNIX timestamp and 06/29/2001    :o Anybody know why? Now, if I change it to read "Last Monday," it returns 08/14/2006, so it works, but what's wrong with my syntax?

[quote author=GingerRobot link=topic=104506.msg416875#msg416875 date=1155743789] [code] <?php $total_hours = 0;//assign 0 to it otherwise php5 will give a warning while ($RESULT = mssql_fetch_assoc($RESULTDS))  { echo "<tr align=center>"; echo "<td>"; //echo $combineddate; echo $RESULT['Lmo'];  echo "/"; echo $RESULT['Lda']; echo "/"; echo $RESULT['LYR']; echo "</td>"; echo "<td>"; echo $RESULT['Leave Code']; echo "</td>"; echo "<td>"; $total_hours = $total_hours + $RESULT['Hours']; echo $RESULT['Hours'];    echo "</td>"; echo "</tr>"; } //printf ("%01.2f", $TotalHours); print("</table>"); echo "Total hours: $total_hours"; ?> [/code] [/quote] Thank you, thank you, thank you! Ginger, you ROCK! :)

[quote author=thorpe link=topic=104506.msg416857#msg416857 date=1155742401] Explain exactly what it is you want to get the sum of. Your not really helping much here. A simple example would be... [code] SELECT SUM(hours) AS totalhours FROM tbl [/code] [/quote] I see. This part of the code... [quote]echo "<td>"; echo $RESULT['Hours'];  echo "</td>";[/quote] .... pulls the hours of leave taken for each record. What I want to do is add the hours from each record displayed. I've tried doing it like in your example above but I can't seem to properly integrate that statement into my query. If there is a way to do this outside of the query? If so, it would be my preferred method.

[quote author=thorpe link=topic=104506.msg416848#msg416848 date=1155741464] Can we see your sql statement Moron? [/quote] Here you go: [quote]$RESULTDS=mssql_query("SELECT DISTINCT LH.[Employee Number], LH.[Lmo], LH.[Lda], LH.[LYR], LH.[Hours], LH.[Leave Code], M2.[HRYRAT], M2.[EMPNO], M2.[MANLAP], M2.[MANLAC], M2.[MANLTC], M2.[MSKLAB], M2.[MSKLTC], M2.[MSKLAB], M2.[MSKLTC], M2.[NAMEMI], M2.[NAMEL], M2.[NAMEF] FROM LEAVHST LH INNER JOIN MASTERL2 M2 ON LH.[Employee Number]=M2.EMPNO WHERE M2.[EMPNO] = '".$_POST['employeenumber']."'    ORDER BY LH.[LYR] desc, LH.[Lmo] desc, LH.[Lda] desc"); $RESULT=mssql_fetch_assoc($RESULTDS);[/quote]

while ($RESULT = mssql_fetch_assoc($RESULTDS))  { echo "<tr align=center>"; echo "<td>"; //echo $combineddate; echo $RESULT['Lmo'];  echo "/"; echo $RESULT['Lda']; echo "/"; echo $RESULT['LYR']; echo "</td>"; echo "<td>"; echo $RESULT['Leave Code']; echo "</td>"; echo "<td>"; echo $RESULT['Hours']; echo "</td>"; echo "</tr>"; } //printf ("%01.2f", $TotalHours); print("</table>"); ?> This produces the total hours of leave taken over a period of time. What would be the right way to add these hours? When I try the usual SUM statement, it gives me the figure for one record only. I've also tried to include the SUM statement within the WHILE statement and that pretty much hoses the entire thing. Anybody?