Retrieving past records. Need Help Please

[mparab]

Hello,

I have a problem with a timesheet application. I have a drop down list box of dates. I would like to be able to pick a date out of the list, click a button and have that information for that date displayed. The page already displays the last two weeks of current information. The only way I can think of doing this is with a JavaScript. Does anyone else have any ideas.

Thanks in advance,

M

[sarah]

Just take the variable that you get from the dropdown and use that date in your query instead of the date that you are using for the past 2 weeks query.

I guess it depends on what format the values in the dropdown are and how you are storing the dates (seconds, Y-m-d,etc).

[mparab]

That's where the problem lies. Can I post some code for you to look at?

[sarah]

If it is short...sure. If it is long, you might want to just describe it.

[mparab]

Here's my code, thanks:

function entryform($uname)
{

//$uname=$GET['name'];
//$USER[uname]=$uname;
//$uname='mparab';
$start='2003-01-01';
$end='2003-12-31';
$today=date("Y-m-d");
//$self = $_POST['PHP_SELF'];

$db = @mysql_connect("localhost", "root")
or die("Failed to connect to database!");
mysql_select_db("nti_admin", $db)
or die("Failed to connect to database!");

$result=mysql_query("SELECT * FROM employee WHERE uname='$uname'",$db);

$act=mysql_query("SELECT * FROM activities",$db);

$pro=mysql_query("SELECT * FROM projects",$db);

$stat=mysql_query("SELECT * FROM status",$db);

$query=mysql_query("SELECT DISTINCT *, DATE_FORMAT( date, '%a, %Y-%c-%d' ) AS DAY FROM timesheet WHERE uname='$uname' AND date BETWEEN (NOW( ) - INTERVAL 60 DAY) AND NOW( ) + INTERVAL 14 DAY ORDER BY date DESC",$db);

$sql=mysql_query("SELECT * FROM timesheet WHERE (uname='$uname') AND (date BETWEEN '$start' AND '$end') ",$db);

$week=mysql_query("SELECT DISTINCT week_ends.weekends FROM week_ends, timesheet WHERE timesheet.date >= '$start' AND weekends < NOW( ) + INTERVAL 28 DAY",$db);

$thisweek=mysql_query("SELECT DISTINCT week_ends.weekends FROM week_ends, timesheet WHERE timesheet.date >= '2003-01-01' AND weekends < NOW( ) + INTERVAL 28 DAY AND weekends BETWEEN NOW( ) AND ( NOW( ) + INTERVAL 7 DAY )",$db);

if ($myrow=mysql_fetch_array($result))
{
print"<form name=input method=post action='submit.php'>";
print"<table width=25% align=left cellspacing=0>";
print"<tr>";
print"<td colspan=2><img src=images/NTLogo.png width=100 height=75></td>";
print"</tr>";
print"<tr>";
print"<table>";
print"<tr>";
print"<td width=32%>";
print"</td>";
print"<td width=68%></td>";
print"</tr>";
print"<td>";
print"<table align=left border=0 cellspacing=2 cellpadding=2>";
print" <tr>";
print" <th>Timesheet For:</th>";
print" <td>$myrow[first_name] $myrow[last_name]</td>";
print" </tr>";
print"<tr>";
print"<th>Week Ending:</th>";
print"<td><select name=week>";
if ($weeks=mysql_fetch_array($week)AND $this=mysql_fetch_array($thisweek))
{
do{
if ($weeks[weekends]==$this[weekends])
{print"<option value=$weeks[weekends] selected='$this[weekends]'>$weeks[weekends]";}
else{print"<option value='$weeks[weekends]'>$weeks[weekends]";}

}
while($weeks=mysql_fetch_array($week));
}
print"</td>";
print" </tr>";
print"</table>";
print"</td>";
print"<td>";

[sarah]

Whew...that's an eyefull. There aren't the same number of } as there are { Is that just because you only posted part of the code?

I can't see what the exact problem is. Since I don't have a database with your tables etc, I can't really play around with it which is how I resolve most of my bugs.

• Does the function work to print the results for the 2 week query?
• Is it printing the dropdown menu correctly?
• If it isn't printing the dropdown, what is it printing?

You might want to try taking it apart and testing each part of it individually.

[mparab]

Yeah, sorry. Should have told you that it was only partial code. I just want to know how to change a sql statement. I guess the easiest thing to do is to create a java function. Anyway better get to it, thanks.

M

[sarah]

You can make the sql statement a variable just like anything else...

if(whatever){
   $SQLresult="SELECT * FROM timesheet WHERE (uname='$uname') AND (date BETWEEN '$start' AND '$end')";
}else{
   $SQLresult="SELECT * FROM timesheet WHERE (uname='$uname') AND date >'$start'";
}
//
$result=mysql_query("$SQLresult",$db);

Or, you can just change the $start and $end variables to the new dates.