Page 1 of 3

calculations with php

Posted: Wed Jan 14, 2009 6:20 am
by buzzby247
let me outline my project.
a ratings form that will store the score given (rating from 1 to 5 as 0 is not considered an integer for use with calculations).
i would like to be able to show the average score for a 5 or 7 day period as well as a 28 day or 30 day period. maybe taking the results from a particular date to a particular date might be more helpful. so the query could look to all dates between whatever i specify and get the results for it. (one date, many entries)
making the form and storing the integers i can do. its getting the data out and utilising some sort of calculation to get the desired result i am having a lil problem with. can anyone help me with this?

Re: calculations with php

Posted: Wed Jan 14, 2009 6:32 am
by jaoudestudios
You will need something like this, depending on your database structure.

Code: Select all

 
SELECT count(*) FROM table WHERE DATE > DATE_SUB(NOW(), INTERVAL 5 DAY);
 

Re: calculations with php

Posted: Wed Jan 14, 2009 6:42 am
by buzzby247
so as long as i have a field called DATE then i can use this code to get the desired results i need? even when i change the interval to 30 to get the months values?

Re: calculations with php

Posted: Wed Jan 14, 2009 6:48 am
by jaoudestudios
Yep, have a date column and store the mysql timestamp (not php), it will look something like this 2008-10-14 16:29:41.

Instead of 30 days you can specific 1month, or 1 year.

Code: Select all

 
SELECT count(*) FROM table WHERE DATE > DATE_SUB(NOW(), INTERVAL 1 MONTH);
SELECT count(*) FROM table WHERE DATE > DATE_SUB(NOW(), INTERVAL 1 YEAR);
 
This is going back in time from now by a certain amount of days/months/years. If you want it between 2 dates that will require a bit more work but not much more. I suggest get it working like above, as you might have to modify it slightly depending on your table and filters. Then once you have that working, we can build on it and make it customisable (i.e between 2 dates)

Re: calculations with php

Posted: Wed Jan 14, 2009 6:51 am
by buzzby247
great..cheers for that. certainly given me the starting blocks to get it working. i will be getting back to you about this if thats ok.
once again, cheers mate

Re: calculations with php

Posted: Thu Jan 15, 2009 5:28 am
by buzzby247
hi there

in my table i have 3 fields: id, rank, dateSubmitted.

id is autoincrement. rank is the result i want to work with and dateSubmitted is the timpestamp which comes in this format, 2009-01-15 10:57:54

you have this in your post:

SELECT count(*) FROM table WHERE DATE > DATE_SUB(NOW(), INTERVAL 5 DAY);

would mine then be
SELECT avg(rank) FROM rankresults WHERE DATE > DATE_SUB(NOW(), INTERVAL 5 DAY);

Re: calculations with php

Posted: Thu Jan 15, 2009 5:37 am
by jaoudestudios
Yep, is your column date in capitals as I think it is case sensitive. You should put your columns lower case. That way when you write sql queries, the sql parts are in capitals (i.e. SELECT) but your columns are lower case or camel case.

Try it and see :wink:

But it will only return 1 result? is that what you are expecting?

Re: calculations with php

Posted: Thu Jan 15, 2009 5:47 am
by buzzby247
this is what i am working with

Code: Select all

 
$query = "INSERT INTO rankresults (rank, dateSubmitted) VALUES ('$rank',NOW())";
mysql_query($query);
$rankresult=mysql_query("SELECT avg(rank) FROM rankresults WHERE dateSubmitted > DATE_SUB(NOW(), INTERVAL 2 DAY");
mysql_close();
 
then i want to chuck the result out here

Code: Select all

 
<table>
  <tr>
    <td width="260">average rank over the last # days</td>
    <td width="140"><?php echo "$rankresult"; ?></td>
  </tr>
</table>
 
i tried this but its not working. any tips please?

yes this is the result i want. i want to output an average for that particular period. that way i will be able to guage what happens from week to week and then also from month to month

Re: calculations with php

Posted: Thu Jan 15, 2009 5:55 am
by jaoudestudios
Change your query to...

Code: Select all

SELECT avg(rank) as avgRank FROM rankresults WHERE dateSubmitted > DATE_SUB(NOW(), INTERVAL 2 DAY
And echo this...

Code: Select all

echo $rankresult['avgRank'];
Btw did you not get an error before?

Re: calculations with php

Posted: Thu Jan 15, 2009 6:35 am
by buzzby247
no i have not got an error and also there are no results being shown at all. this is what i am using.

Code: Select all

 
<table width="400" border="0" cellspacing="2" cellpadding="2">
  <tr>
    <td width="260">average usability over the last 7 days</td>
    <td width="140"><?php echo $usabilityresult['avgUsability']; ?></td>
  </tr>
  <tr>
    <td>average usability over the last 28 days</td>
    <td><?php echo $rankresult['avgUsability']; ?></td>
  </tr>
</table>
 
this is the back code am using

Code: Select all

$query = "INSERT INTO usabilityresults (usability, dateSubmitted) VALUES ('$usability',NOW())";
mysql_query($query);
$usabilityresult=mysql_query("SELECT avg(usability) as avgUsability FROM usabilityresults WHERE dateSubmitted > DATE_SUB(NOW(), INTERVAL 2 DAY");
mysql_close();
is it something to do with the mysql_close or is that fine. i dont get any error messages at all....actually i dont see anything apart from the forms themselves

Re: calculations with php

Posted: Thu Jan 15, 2009 6:39 am
by jaoudestudios
Remove the close at the moment and let php do its own garbage collection.

Is the data definitely appearing in the database?

Re: calculations with php

Posted: Thu Jan 15, 2009 7:02 am
by buzzby247
nothing is showin in the page even tho its defo in the database. i have looked at both tables and results are there.
do i need to add in any hidden fields or something? like the 'id'. am not sure why its not showing at all

Re: calculations with php

Posted: Thu Jan 15, 2009 8:37 am
by jaoudestudios
Can you post your database schema and data (database full dump).

Re: calculations with php

Posted: Thu Jan 15, 2009 8:59 am
by buzzby247
thats the sql dump

Code: Select all

 
-- Table structure for table `usabilityresults`
 
 
DROP TABLE IF EXISTS `usabilityresults`;
CREATE TABLE IF NOT EXISTS `usabilityresults` (
  `id` int(11) NOT NULL auto_increment,
  `usability` int(11) default NULL,
  `dateSubmitted` varchar(20) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;
 
 
-- Dumping data for table `usabilityresults`
 
 
INSERT INTO `usabilityresults` (`id`, `usability`, `dateSubmitted`) VALUES
(1, 2, '2009-01-15 10:57:52'),
(2, 5, '2009-01-15 10:59:22'),
(3, 2, '2009-01-15 10:59:27'),
(4, 3, '2009-01-15 10:59:32'),
(5, 4, '2009-01-15 10:59:40');
this is the php code

Code: Select all

$query = "INSERT INTO usabilityresults (usability, dateSubmitted) VALUES ('$usability',NOW())";
mysql_query($query) or die(mysql_error());
$usabilityresult = mysql_query("SELECT avg(usability) as avgUsability FROM usabilityresults WHERE dateSubmitted > DATE_SUB(NOW(), INTERVAL 2 DAY");
this is the front end bit

Code: Select all

<table width="400" border="0" cellspacing="2" cellpadding="2">
  <tr>
    <td width="260">average usability over the last 7 days</td>
    <td width="140"><?php echo $usabilityresult['avgUsability']; ?></td>
  </tr>
</table>
bottom line is the radio button submission results store in the form but do not come out

Re: calculations with php

Posted: Thu Jan 15, 2009 11:12 am
by buzzby247
the query works well now. the only problem i am having is to get the data out of the table. this is the code i am using but that part seems not to be working. is there something i am doing wrong?

Code: Select all

<table width="400" border="0" cellspacing="2" cellpadding="2">
  <tr>
    <td width="260">average usability over the last 7 days</td>
    <td width="140"><?php echo $usabilityRow['avgUsability']; ?></td>
  </tr>
  <tr>
    <td>average usability over the last 28 days</td>
    <td><?php echo $usabilityRow['avgUsability']; ?></td>
  </tr>
</table>
the code that preceeds the one above is this:

Code: Select all

$query = ("INSERT INTO usabilityresults (usability, dateSubmitted) VALUES ('$usability',NOW())");
mysql_query($query) or die(mysql_error());
$usabilityQuery = "SELECT avg(usability) as avgUsability FROM usabilityresults WHERE dateSubmitted > DATE_SUB(NOW(), INTERVAL 2 DAY)";
$usabilityresult=mysql_query($usabilityQuery);  
if (!$usabilityresult) {
    die("Query error!  Query is $usabilityQuery<br>Error is " . mysql_error());
}
$usabilityRow = mysql_fetch_array($usabilityresult);