[SOLVED] Help with time/query

[Drewser33]

HI,

 

What I am trying to do:

 

At the end of all of this I would like to have an average of each question selected on the form broken up by the time frame as explained below.

 

I have a form that allows a user to select a start and end date.  I would like to break that up into weeks automatically - so if the user selected a start date of 01/01/2009 and end date of 1/31/09, the result would be

 

$startdate = 01/01/2009

$nextdate = 01/07/2009

 

Run a query using those dates,

 

Then

$startdate = 01/07/2009

$nextdate = 01/14/2009

 

Re-Run the query using the new dates, but still keeping the first queries results. up to the end date.

 

The form also allows the user to select the questions that they would like see the results of.  I have named the check boxes the ID's of question.  So I can use the $_GET against a query of the ID's in the Question table to find which questions should be included.

 

Tables and Data:

tblcall contains the dateopened to compare the start/next/end date variables to.

 

tblquestions holds the questions

 

tblresults holds CallID = tblcall.ID, QID=tblquestion.ID, and the result for each.

 

So

tblquestion.ID = 1, tblquestion = a

tblquestion.ID = 2, tblquestion = b

tblquestion.ID = 3, tblquestion = c

But User only Selects 1 and 2 from the form:

tblcall.ID = 1, tblcall.DateOpened = 1/2/09

tblresult.CallID = 1, tblresult.QID = 1, Result = 3

tblresult.CallID = 1, tblresult.QID = 2, Result = 2

tblresult.CallID = 1, tblresult.QID = 3, Result = 1

tblcall.ID = 2, tblcall.DateOpened = 1/5/09

tblresult.CallID = 2, tblresult.QID = 1, Result = 8

tblresult.CallID = 2, tblresult.QID = 2, Result = 6

tblresult.CallID = 2, tblresult.QID = 3, Result = 4

tblcall.ID = 3, tblcall.DateOpened = 1/12/09

tblresult.CallID = 3, tblresult.QID = 1, Result = 10

tblresult.CallID = 3, tblresult.QID = 2, Result = 5

tblresult.CallID = 3, tblresult.QID = 3, Result = 15

tblcall.ID = 4, tblcall.DateOpened = 1/13/09

tblresult.CallID = 4, tblresult.QID = 1, Result = 0

tblresult.CallID = 4, tblresult.QID = 2, Result = 6

tblresult.CallID = 4, tblresult.QID = 3, Result = 9

 

So the output:

Week1: 1/1/09 - 1/7/09

Question 1: Avg = ((8 + 3)/2) = 5.5

Questoin 2: Avg = ((6 + 2)/2) = 4

Week2: 1/7/09 - 1/14/09

Question 1: Avg = ((10 + 0/)1) = 10 - a zero value for an answer means it was unanswered and should not count in the average calculation.

Questoin 2: Avg = ((5 + 6)/2) = 5.5

 

Can I accomplish what I am looking for?

THANKS IN ADVANCE!!

 

[Gighalen]

You will have to generate a timestamp from the first date that the client has selected. You will then have to generate the timestamp for the ending date. Finally, you will have to find the number of seconds in a week and execute whatever so long as the current timestamp is > than the ending timestamp. At the end of every session you will simply have to add the number of seconds to the timestamp, and have a cron job run a script to see if the current timestamp in the database is greater or less than the current TIME on the sever, and if not then it will execute the script as mentioned earlier.

 

I hope this helps.

[Drewser33]

Thanks, but that sounds a bit out of my league.  I am attempting do it with whiles, and possibly arrays, but I have hard time with arrays too.

 

Any ideas on either of those, which I understand better, that would be very helpful.

 

Thanks again.

[Drewser33]

After trying some different things, this may be a "newbie" way to do it, but it works great:

 

	$start = strtotime($datefrom);
	for($cursor=$start;$cursor<=$enddate; $cursor = strtotime('+1 week',$cursor))
	{
		if($x == 0)
		{
			$startyear = date('Y',$start);
			$startmonth = date('m',$start);
			$startday = date('d',$start);	
			$startdate1  = mktime(0, 0, 0, date($startmonth), $startday, $startyear);
			$startdate = date('Y-m-d H:i:s',$startdate1);
			$nextdate1 = mktime(0, 0, 0, $startmonth, date($startday)+7, $startyear);
			$nextdate = date('Y-m-d H:i:s',$nextdate1);
		}else
		{	
			$startdate1 = $cursor;
			$startdate = date('Y-m-d H:i:s',$startdate1);
			$nextdate1 = strtotime('+1 week',$startdate1);
			$nextdate = date('Y-m-d H:i:s',$nextdate1);			
			//echo date('Y-m-d H:i:s',)$nextdate);
		}
		$ecol = $ecol + 1;
		mysql_select_db('testwork');
		$query38 = "SELECT ID FROM tblproactivequestions WHERE Type = 1";
		$result38 = mysql_query($query38);
		$displaystart = date('Y-m-d',$startdate1);
		$displayend = date('Y-m-d',$nextdate1);
		$erow = 1;
		while($row38 = mysql_fetch_array($result38)) 
		{
		 	$qid = $row38['ID'];
			if(isset($_GET[$qid]))
			{
				$erow = ($erow + 1);
				$qid = $row38['ID'];
			}else
			{
				$qid = 0;
				$erow = $erow;
			}	
			$query60 = "SELECT SUM(tblproactiveresults.Result) AS SumOfResult
									FROM tblproactivequestions INNER JOIN (tblproactiveresults INNER JOIN tblproactivecall ON tblproactiveresults.CallID = tblproactivecall.ID) ON tblproactivequestions.ID = tblproactiveresults.QID
									WHERE tblproactivecall.DateOpened > '$startdate' AND tblproactivecall.DateOpened < '$nextdate' AND tblproactiveresults.QID='$qid' AND
									tblproactiveresults.Result > 0";	
			$result60 = mysql_query($query60);	
			$query61 = "SELECT Count(tblproactiveresults.Result) AS CountOfResult
									FROM tblproactivequestions INNER JOIN (tblproactiveresults INNER JOIN tblproactivecall ON tblproactiveresults.CallID = tblproactivecall.ID) ON tblproactivequestions.ID = tblproactiveresults.QID
									WHERE tblproactivecall.DateOpened > '$startdate' AND tblproactivecall.DateOpened < '$nextdate' AND tblproactiveresults.QID='$qid' AND
									tblproactiveresults.Result > 0";	
			$result61 = mysql_query($query61);
			$row61 = mysql_fetch_array($result61);
			while($row60 = mysql_fetch_array($result60))
			{
				$count61 = $row61['CountOfResult'];
				$result = $row60['SumOfResult'];
				if($count61 <> 0)
				{		
					$avg = round(($result / $count61),2);							
				}
			}				
		}	
		$x = 1;
	}		
}