Moron Posted August 17, 2006 Share Posted August 17, 2006 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 Link to comment Share on other sites More sharing options...
akrytus Posted August 17, 2006 Share Posted August 17, 2006 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 Link to comment Share on other sites More sharing options...
Moron Posted August 17, 2006 Author Share Posted August 17, 2006 [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? Quote Link to comment Share on other sites More sharing options...
akrytus Posted August 17, 2006 Share Posted August 17, 2006 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 Link to comment Share on other sites More sharing options...
Ifa Posted August 17, 2006 Share Posted August 17, 2006 [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]That might work too Quote Link to comment Share on other sites More sharing options...
trq Posted August 17, 2006 Share Posted August 17, 2006 [quote]True, but I don't want to restrict anything in the query.[/quote]Why? This was the same with your SUM problem. Its MUCH more efficient to let MySql worry about it. Why retrieve data you dont need? Quote Link to comment Share on other sites More sharing options...
akrytus Posted August 17, 2006 Share Posted August 17, 2006 Ahh, I agree! Very well put. Quote Link to comment Share on other sites More sharing options...
Moron Posted August 17, 2006 Author Share Posted August 17, 2006 [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 Link to comment Share on other sites More sharing options...
akrytus Posted August 17, 2006 Share Posted August 17, 2006 What are your errors, what is the output? Quote Link to comment Share on other sites More sharing options...
Moron Posted August 17, 2006 Author Share Posted August 17, 2006 [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 Link to comment Share on other sites More sharing options...
appeland Posted August 17, 2006 Share Posted August 17, 2006 Hello,very interesting thread indeed, do you have a date /time stamp column in you table ?What is is called, how is it populated and what's the type of it in mysql ?#Andi Quote Link to comment Share on other sites More sharing options...
Barand Posted August 18, 2006 Share Posted August 18, 2006 SELECT * FROM mytablenameWHERE LYR = '2006' AND Lmo > '06' Quote Link to comment Share on other sites More sharing options...
Moron Posted August 18, 2006 Author Share Posted August 18, 2006 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 Link to comment Share on other sites More sharing options...
craygo Posted August 18, 2006 Share Posted August 18, 2006 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 Link to comment Share on other sites More sharing options...
Ifa Posted August 18, 2006 Share Posted August 18, 2006 So mine didn't work? :( Quote Link to comment Share on other sites More sharing options...
Moron Posted August 18, 2006 Author Share Posted August 18, 2006 [quote author=Ifa link=topic=104649.msg417949#msg417949 date=1155907867]So mine didn't work? :([/quote]Sorry, but yours threw an error. :-[ Quote Link to comment Share on other sites More sharing options...
Moron Posted August 18, 2006 Author Share Posted August 18, 2006 [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 Link to comment Share on other sites More sharing options...
AndyB Posted August 18, 2006 Share Posted August 18, 2006 [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 Link to comment Share on other sites More sharing options...
Ifa Posted August 18, 2006 Share Posted August 18, 2006 [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 Link to comment Share on other sites More sharing options...
Moron Posted August 18, 2006 Author Share Posted August 18, 2006 [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 Link to comment Share on other sites More sharing options...
Moron Posted August 18, 2006 Author Share Posted August 18, 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 Link to comment Share on other sites More sharing options...
Ifa Posted August 18, 2006 Share Posted August 18, 2006 Is the $RESULT['LYR'] two or four numbers? ie 06 or 2006? Quote Link to comment Share on other sites More sharing options...
craygo Posted August 18, 2006 Share Posted August 18, 2006 OK here is the table structure i recreated[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]And here is the code i used[code]<?$TotalGenHours = 0;$sql = "SELECT * FROM testtable"; $RESULTDS = mysql_query($sql) or die (mysql_error()); while ($RESULT = mysql_fetch_assoc($RESULTDS)) { $day=$RESULT['LYR']."/".$RESULT['Lmo']."/".$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]Works fine for meRay Quote Link to comment Share on other sites More sharing options...
Moron Posted August 18, 2006 Author Share Posted August 18, 2006 [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 Link to comment Share on other sites More sharing options...
Moron Posted August 18, 2006 Author Share Posted August 18, 2006 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 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.