Jump to content

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


Moron

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.

Link to comment
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?

Link to comment
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!
Link to comment
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
Link to comment
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.
Link to comment
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.

Link to comment
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.
Link to comment
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

Link to comment
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.
Link to comment
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]
:)
Link to comment
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.

Link to comment
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.

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

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.