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
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...
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);