Okay, I give up. What's the answer?

[Moron]

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.

[akrytus]

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?

[Moron]

[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?

[akrytus]

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!

[Ifa]

[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

[trq]

[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?

[akrytus]

Ahh, I agree!  Very well put.

[Moron]

[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.

[akrytus]

What are your errors, what is the output?

[Moron]

[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.

[appeland]

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

[Barand]

SELECT * FROM mytablename
WHERE LYR = '2006' AND Lmo > '06'

[Moron]

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.

[craygo]

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

[Ifa]

So mine didn't work? :(

[Moron]

[quote author=Ifa link=topic=104649.msg417949#msg417949 date=1155907867]
So mine didn't work? :(
[/quote]

Sorry, but yours threw an error.

:-[

[Moron]

[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.

[AndyB]

[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?

[Ifa]

[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]
:)

[Moron]

[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.

[Moron]

[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.

:-[

[Ifa]

Is the $RESULT['LYR'] two or four numbers? ie 06 or 2006?

[craygo]

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 me

Ray

[Moron]

[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).

[Moron]

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]