while statement - unexpected results

[sunshine66]

Hi,
I'm running an sql query to print out the list of results in table format. Additional variables are determined by manipulating the data from the query result. Some of my variables are showing up properly (i.e. $ItemNum and $Descrip), but the ones dealing with $Date do not ($Day and $DayNum).

In addition to the actual $Date (yyyy-mm-dd), I need to show
The problem is that $Day and $DayNum result in the same values regardless if $Date has changed. I'm so confused how to solve this problem!

Here is the php code:

<?php

$query = "SELECT * FROM Timesheets WHERE EquipmentCode!='' ORDER BY JobCode,PhaseNum,CostCode,EquipmentCode";
$result = mysql_query($query);

while($line = mysql_fetch_array($result))
{
$Date = $line["Date"];
$EquipCode = $line["EquipmentCode"];
$Employee = $line["EmployeeName"];

// split out item num from equipment code
list($Model,$ItemNum) = split("#",$EquipCode);

// convert date to day # and day of week
$Day = date("D", $Date);
$DayNum = date("w", $line["Date"]); $DayNum = $DayNum+1;
// why is this returning the same values for date for all line items? and not the result i want!!!


$tableinfo .= "<tr bgcolor=$color>
<td nowrap class=\"small\">$EquipCode</td>
<td nowrap class=\"small\">$DayNum-$Day$Employee$ItemNum</td>
<td nowrap class=\"small\">$Date ($Day)</td>		
</tr>\n";

}
?>

code continues to print results which look like
Code Description Date (Day)
H#109 4-WedA.LES109 2008-02-02 (Wed)

L#63 4-WedA.LES63 2008-01-28 (Wed)

#65 4-WedC.ROD65 2008-01-30 (Wed)

M#91 4-WedC.ROD91 2008-01-28 (Wed)

What I need is for the $Day and $DayNum values to correlate to the respective date, so that:
2008-02-02 Day = Sat; DayNum=7
2008-01-30 Day = Wed; DayNum=4
2008-01-28 Day = Sun; DayNum=1

I'm sure this is an easy fix, but everything I try produces the same (incorrect) result.

Help appreciated!

[laserlight]

Read the PHP Manual for [man]date/man. Its optional second parameter is a

By the way, kindly post your well indented PHP code in php bbcode tags.

[sunshine66]

duh... i have read the php manual for date() and while() and can't find any references to solve this problem

i've spent several hours trying to figure this out - i'm sure it is a 1 minute fix

the problem is not with converting the dates, it is the results of the while statement - i've tried moving the date() stuff to outside the loop but that doesn't help

what is the php bbcode?

[laserlight]

the problem is not with converting the dates, it is the results of the while statement

What brings you to that conclusion?

what is the php bbcode?

[php]your php code[/php]

[laserlight]

Oh dear, I just realised that my earlier post was truncated during editing. What is meant to say was:

Read the PHP Manual for date(). Its optional second parameter is a Unix timestamp, but you are passing a date string. You should use [man]strtotime/man or [man]mktime[/man].

[sunshine66]

Again, the problem is not with the date() formating, it is a problem with formatting all the $Date variables as the same $Date variable, or taking the last date formatted and assigning to all instances

if i do not have these variables in a while statement, they display correctly

for example,

$Date = $line["Date"]; // returns the value 2008-02-02
$Day = date("D", $Date); // returns the value Sat
$DayNum = date("w", $line["Date"]); $DayNum = $DayNum+1; //returns the value of 7

[laserlight]

Again, the problem is not with the date() formating, it is a problem with formatting all the $Date variables as the same $Date variable, or taking the last date formatted and assigning to all instances

I am telling you point blank that if $Date contains a string like '2008-02-02', using it as the second argument to date() is wrong.

if i do not have these variables in a while statement, they display correctly

That is strange. Let's try:

<?php
$line["Date"] = '2008-02-02';
$Date = $line["Date"]; // returns the value 2008-02-02
$Day = date("D", $Date); // returns the value Sat
$DayNum = date("w", $line["Date"]); $DayNum = $DayNum+1; //returns the value of 7

echo $Date . '<br />' . $Day . '<br />' . $DayNum;
?>

What is your output?

[sunshine66]

the result is :

2008-02-02
Wed
4

so, to extrapolate, if i could remove the while statement, the results would be something like:

$Date = $line["Date"] // value is '2008-02-02';
$Day = date("D", $Date);
$DayNum = date("w", $line["Date"]); $DayNum = $DayNum+1;

the output would be 2008-02-02 Sat 7

$Date = $line["Date"] // value is '2008-01-30';
$Day = date("D", $Date);
$DayNum = date("w", $line["Date"]); $DayNum = $DayNum+1;

the output would be 2008-01-30 Wed 4

$Date = $line["Date"] // value is '2008-01-28';
$Day = date("D", $Date);
$DayNum = date("w", $line["Date"]); $DayNum = $DayNum+1;

the output would be 2008-01-30 Mon 2

within the while statement the code looks like this

while($line = mysql_fetch_array($result))
{
$Date = $line["Date"];
// convert date to day # and day of week
$Day = date("D", $Date);
$DayNum = date("w", $line["Date"]); $DayNum = $DayNum+1;

$Date ($Day) $DayNum;
}

print "$Date ($Day) $DayNum";
?>

the output looks like :

2008-02-02 (Wed) 4
2008-01-28 (Wed) 4
2008-01-30 (Wed) 4

When I would expect it to be:
2008-02-02 (Sat) 7
2008-01-28 (Mon) 2
2008-01-30 (Wed) 4

[laserlight]

It has nothing to do with your while statement. Your while statement looks fine. The problem is with your use of date().

Maybe this will convince you:

<?php
$query = "SELECT * FROM Timesheets WHERE EquipmentCode!=''
          ORDER BY JobCode,PhaseNum,CostCode,EquipmentCode";
$result = mysql_query($query);

$tableinfo = '';
while ($line = mysql_fetch_array($result))
{
    $Date = $line["Date"];
    $EquipCode = $line["EquipmentCode"];
    $Employee = $line["EmployeeName"];

// split out item num from equipment code
list($Model,$ItemNum) = split("#",$EquipCode);

// convert date to day # and day of week
$Timestamp = strtotime($Date);
$Day = date("D", $Timestamp);
$DayNum = date("w", $Timestamp) + 1;

$tableinfo .= "<tr bgcolor=$color>
<td nowrap class=\"small\">$EquipCode</td>
<td nowrap class=\"small\">$DayNum-$Day$Employee$ItemNum</td>
<td nowrap class=\"small\">$Date ($Day)</td>
</tr>\n";
}
?>

EDIT:
Wait a minute. It looks like you are printing the values outside of your loop. So your while loop is not wrong, but your printing is wrong.

I suggest that you post the smallest and simplest script that demonstrates the problem.

[sunshine66]

i get the problem!

i fixed it with

$Datestamp = strtotime($Date);
$Day = date("D", $Datestamp); 
$DayNum = date("w", $Datestamp); $DayNum = $DayNum+1;

Sorry i'm still learning php
thanks for your help!

[laserlight]

Yep, you hit on the right solution even without looking at my example 🙂

However, note that:

$DayNum = date("w", $Datestamp); $DayNum = $DayNum+1;

is better written as:

$DayNum = date("w", $Datestamp) + 1;

[sunshine66]

thanks for the tip
i was wondering how to do that!

[laserlight]

No problem. Remember to mark this thread as resolved using the thread tools.