Jump to content

Archived

This topic is now archived and is closed to further replies.

Moron

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

Recommended Posts

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.

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
[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?

Share this post


Link to post
Share on other sites
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!

Share this post


Link to post
Share on other sites
[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

Share this post


Link to post
Share on other sites
[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?

Share this post


Link to post
Share on other sites
[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.

Share this post


Link to post
Share on other sites
[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.

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
So mine didn't work? :(

Share this post


Link to post
Share on other sites
[quote author=Ifa link=topic=104649.msg417949#msg417949 date=1155907867]
So mine didn't work? :(
[/quote]

Sorry, but yours threw an error.

:-[

Share this post


Link to post
Share on other sites
[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.

Share this post


Link to post
Share on other sites
[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?

Share this post


Link to post
Share on other sites
[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]
:)

Share this post


Link to post
Share on other sites
[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.

Share this post


Link to post
Share on other sites
[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.

:-[

Share this post


Link to post
Share on other sites
Is the $RESULT['LYR'] two or four numbers? ie 06 or 2006?

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
[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).

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.