Moron Posted August 29, 2006 Share Posted August 29, 2006 [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']."' and [b]LH.[LYR] >= '$last'[/b] and [b]LH.[Lda] >= '01'[/b] and [b]LH.[Lmo] >= '07'[/b] ORDER BY LH.[LYR] desc, LH.[Lmo] desc, LH.[Lda] desc"); $RESULT=mssql_fetch_assoc($RESULTDS);[/quote]Okay, as of right now, this is pulling exactly what I want it to pull, records on or after July 1, 2006, the start of our fiscal year (note that [b]$last[/b] determines the last year in which July 1 occurred, but I didn't see the need to include the code here).Anyway, it works now, but come January, the month will NOT be ">= 07" anymore. I tried this:[code]if ($last == $curr_year) { $monlast = 07;}else{$monlast = 12;}[/code]I can echo it and it works fine, BUT, if I put [b]LH.[Lmo] >= '$monlast'[/b] in the query, the entire query crashes.Can anyone tell me a better way of going about this -OR- tell me why $monlast crashes the code? Quote Link to comment Share on other sites More sharing options...
radar Posted August 29, 2006 Share Posted August 29, 2006 How your actual query and everything... like yes this is your mysql part of the query but where is the php.. and give like 3 lines before and 3 lines after -- might help a little bit more ... Quote Link to comment Share on other sites More sharing options...
craygo Posted August 29, 2006 Share Posted August 29, 2006 you should seperate the query statement from your sql. That way you can echo out the sql statement and see if it is returning what you want.[code]$sql = "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']."' and LH.[LYR] >= '$last' and LH.[Lda] >= '01' and LH.[Lmo] >= '07' ORDER BY LH.[LYR] desc, LH.[Lmo] desc, LH.[Lda] desc";$RESULTDS=mssql_query($sql) or die ('There is an error with your query'); $RESULT=mssql_fetch_assoc($RESULTDS);[/code]Now you can comment out your query with // then add an echo to give you your sql[code]$sql = "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']."' and LH.[LYR] >= '$last' and LH.[Lda] >= '01' and LH.[Lmo] >= '07' ORDER BY LH.[LYR] desc, LH.[Lmo] desc, LH.[Lda] desc";echo $sql;// $RESULTDS=mssql_query($sql) or die ('There is an error with your query'); // $RESULT=mssql_fetch_assoc($RESULTDS);[/code]See if it is returning what you want.Ray Quote Link to comment Share on other sites More sharing options...
Moron Posted August 29, 2006 Author Share Posted August 29, 2006 Thanks, guys. Before the query (it's MS SQL, by the way), is...[code]//Define year of current fiscal yearif(($curr_month ==6 && $curr_day >= 1) || $curr_month >6){$last = $curr_year;}else{$last = $curr_year - 1;}//Define month to start withif ($last == $curr_year) { $monlast = 07;}else{$monlast = 12;}//Define day to break onif ($last >= $curr_year){$daypoint = 01;}else{$daypoint = 31;}[/code]The query works perfectly, BUT... it won't when January comes around with the month hardcoded as ">= 07" because January, being month 01, won't fit that criteria. I basically need a way to make the query fiscal year-sensitive.Any help is greatly appreciated! Quote Link to comment Share on other sites More sharing options...
craygo Posted August 29, 2006 Share Posted August 29, 2006 I work with this also. Does your fiscal year start july 1st??If your date is august 3, 2006 will it be fiscal year 2007 or 2006??Where is $curr_year, $curr_month, $curr_day coming from?? Is it just todays date??Is what you are trying to do is find out if a particular date is in the current fiscal year??Ray Quote Link to comment Share on other sites More sharing options...
Moron Posted August 29, 2006 Author Share Posted August 29, 2006 [quote author=craygo link=topic=106085.msg424051#msg424051 date=1156864364]I work with this also. Does your fiscal year start july 1st??If your date is august 3, 2006 will it be fiscal year 2007 or 2006??Where is $curr_year, $curr_month, $curr_day coming from?? Is it just todays date??Is what you are trying to do is find out if a particular date is in the current fiscal year??Ray[/quote]Yes, our fiscal year starts July 1. We're in fiscal year 2007 right now.[code]if(($curr_month ==6 && $curr_day >= 1) || $curr_month >6){$last = $curr_year;}else{$last = $curr_year - 1;}$curr_month = date('m');$curr_day = date('d');$curr_year = date('Y');[/code]On the last question, no, I want to pull records from the previous July 1 and forward. The query is doing this now, but it isn't "fiscal year sensitive." It's saying that if [Lmo] (Leave Month) is >= 07, etc... This hard wiring won't work after we hit January (01). Quote Link to comment Share on other sites More sharing options...
hitman6003 Posted August 29, 2006 Share Posted August 29, 2006 So, just to be sure...you are storing the date for leave in three seperate columns...Lya, Lda, and Lmo?If so, then you should be able to do something like:[code]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'] . "' AND ((LH.[LYR] = '$last' AND LH.[Lmo] >= '07') OR (LH.[LYR] = '" . $last + 1 . "' AND LH.[Lmo] < '07'))ORDER BY LH.[LYR] desc, LH.[Lmo] desc, LH.[Lda] desc[/code] Quote Link to comment Share on other sites More sharing options...
Moron Posted August 29, 2006 Author Share Posted August 29, 2006 Hitman6003,Thanks a ton, bit it keeps giving me this:[quote][b]Warning: mssql_query() [function.mssql-query]: message: Line 1: Incorrect syntax near '1'. (severity 15) in E:\User\Inetpub\wwwroot\PHPLeaveTime\leaveprocess.php on line 285Warning: mssql_query() [function.mssql-query]: message: Unclosed quotation mark before the character string ')) ORDER BY LH.[LYR] desc, LH.[Lmo] desc, LH.[Lda] desc'. (severity 15) in E:\User\Inetpub\wwwroot\PHPLeaveTime\leaveprocess.php on line 285Warning: mssql_query() [function.mssql-query]: Query failed in E:\User\Inetpub\wwwroot\PHPLeaveTime\leaveprocess.php on line 285Warning: mssql_fetch_assoc(): supplied argument is not a valid MS SQL-result resource in E:\User\Inetpub\wwwroot\PHPLeaveTime\leaveprocess.php on line 286[/b][/quote] Quote Link to comment Share on other sites More sharing options...
hitman6003 Posted August 29, 2006 Share Posted August 29, 2006 Seems to not escape the quotes correctly to add the 1 to $last.[code]$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'] . "' AND ((LH.[LYR] = '$last' AND LH.[Lmo] >= '07') OR (LH.[LYR] = '" . $last + 1 . "' AND LH.[Lmo] < '07'))ORDER BY LH.[LYR] desc, LH.[Lmo] desc, LH.[Lda] desc";$result = mssql_query($query);[/code] Quote Link to comment Share on other sites More sharing options...
Moron Posted August 29, 2006 Author Share Posted August 29, 2006 I also tried replacing just this part:[code]((LH.[LYR] = '$last' AND LH.[Lmo] >= '07') OR (LH.[LYR] = '" . $last + 1 . $last + 1 . "' AND LH.[Lmo] < '07'))[/code]No go. It gives four major sql errors. Thanks for the help, though. My syntax is probably just hosed somewhere. Quote Link to comment Share on other sites More sharing options...
Moron Posted August 29, 2006 Author Share Posted August 29, 2006 Did you mean to put this twice in a row?[code] $last + 1 . $last + 1 .[/code] Quote Link to comment Share on other sites More sharing options...
Moron Posted August 29, 2006 Author Share Posted August 29, 2006 Hey, check it out. This returns the right results:[code](LH.[LYR] = '$last' AND LH.[Lmo] >= '07') OR (LH.[LYR] = '$last' + 1 AND LH.[Lmo] < '07') [/code]But does anyone see a problem with it? Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.