Jump to content


Photo

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


  • Please log in to reply
28 replies to this topic

#1 Moron

Moron
  • Members
  • PipPipPip
  • Advanced Member
  • 368 posts

Posted 17 August 2006 - 05:02 PM

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:

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>";
}


It's formatted perfectly, but it's pulling the entire date range going back to 2001.



#2 akrytus

akrytus
  • Members
  • PipPipPip
  • Advanced Member
  • 70 posts

Posted 17 August 2006 - 05:05 PM

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?

#3 Moron

Moron
  • Members
  • PipPipPip
  • Advanced Member
  • 368 posts

Posted 17 August 2006 - 05:07 PM

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?


True, but I don't want to restrict anything in the query. Is there a way to restrict it within the "while" statement?



#4 akrytus

akrytus
  • Members
  • PipPipPip
  • Advanced Member
  • 70 posts

Posted 17 August 2006 - 05:20 PM

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!


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>";
  }
}

Let me know if it works!

#5 Ifa

Ifa
  • Members
  • PipPipPip
  • Advanced Member
  • 88 posts
  • LocationRauma, Finland

Posted 17 August 2006 - 05:22 PM

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>";
}

That might work too
I'm just a guy who likes to code for fun...

#6 trq

trq
  • Staff Alumni
  • Advanced Member
  • 31,041 posts

Posted 17 August 2006 - 05:36 PM

True, but I don't want to restrict anything in the query.


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?

#7 akrytus

akrytus
  • Members
  • PipPipPip
  • Advanced Member
  • 70 posts

Posted 17 August 2006 - 06:02 PM

Ahh, I agree!  Very well put.

#8 Moron

Moron
  • Members
  • PipPipPip
  • Advanced Member
  • 368 posts

Posted 17 August 2006 - 06:59 PM

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!


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>";
  }
}

Let me know if it works!


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.

#9 akrytus

akrytus
  • Members
  • PipPipPip
  • Advanced Member
  • 70 posts

Posted 17 August 2006 - 07:00 PM

What are your errors, what is the output?

#10 Moron

Moron
  • Members
  • PipPipPip
  • Advanced Member
  • 368 posts

Posted 17 August 2006 - 07:47 PM

What are your errors, what is the output?


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.



#11 appeland

appeland
  • Members
  • PipPipPip
  • Advanced Member
  • 30 posts

Posted 17 August 2006 - 09:07 PM

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


#12 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,025 posts

Posted 18 August 2006 - 12:00 AM

SELECT * FROM mytablename
WHERE LYR = '2006' AND Lmo > '06'
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#13 Moron

Moron
  • Members
  • PipPipPip
  • Advanced Member
  • 368 posts

Posted 18 August 2006 - 01:22 PM

Look at this adaption of my code, posted by Akrytus:

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>";
  }
}


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.


#14 craygo

craygo
  • Staff Alumni
  • Advanced Member
  • 1,973 posts
  • LocationRhode Island

Posted 18 August 2006 - 01:29 PM

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

$day=$RESULT['Lmo']."/".$RESULT['Lda']."/".$RESULT['Lda'];
  $day_timestamp=strtotime($day);

shouldn't it be
  $day=$RESULT['LYR']."/".$RESULT['Lmo']."/".$RESULT['Lda'];
  $day_timestamp=strtotime($day);

Ray



#15 Ifa

Ifa
  • Members
  • PipPipPip
  • Advanced Member
  • 88 posts
  • LocationRauma, Finland

Posted 18 August 2006 - 01:31 PM

So mine didn't work? :(
I'm just a guy who likes to code for fun...

#16 Moron

Moron
  • Members
  • PipPipPip
  • Advanced Member
  • 368 posts

Posted 18 August 2006 - 01:47 PM

So mine didn't work? :(


Sorry, but yours threw an error.

:-[

#17 Moron

Moron
  • Members
  • PipPipPip
  • Advanced Member
  • 368 posts

Posted 18 August 2006 - 01:49 PM

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

$day=$RESULT['Lmo']."/".$RESULT['Lda']."/".$RESULT['Lda'];
  $day_timestamp=strtotime($day);

shouldn't it be
  $day=$RESULT['LYR']."/".$RESULT['Lmo']."/".$RESULT['Lda'];
  $day_timestamp=strtotime($day);

Ray



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.


#18 AndyB

AndyB
  • Staff Alumni
  • Advanced Member
  • 5,465 posts
  • LocationToronto

Posted 18 August 2006 - 01:50 PM

but I just can't seem to crack the whole "July 1 and forward" problem.


Did you try the solution Barand posted in this thread?
Legend has it that reading the manual never killed anyone.
My site

#19 Ifa

Ifa
  • Members
  • PipPipPip
  • Advanced Member
  • 88 posts
  • LocationRauma, Finland

Posted 18 August 2006 - 01:59 PM

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>";
}
:)
I'm just a guy who likes to code for fun...

#20 Moron

Moron
  • Members
  • PipPipPip
  • Advanced Member
  • 368 posts

Posted 18 August 2006 - 02:00 PM

but I just can't seem to crack the whole "July 1 and forward" problem.


Did you try the solution Barand posted in this thread?


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, Mo > 06 won't work because the fiscal year will also run from 01 through 06 of 2007.






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users