calculations with php
Moderator: General Moderators
calculations with php
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?
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?
- jaoudestudios
- DevNet Resident
- Posts: 1483
- Joined: Wed Jun 18, 2008 8:32 am
- Location: Surrey
Re: calculations with php
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
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?
- jaoudestudios
- DevNet Resident
- Posts: 1483
- Joined: Wed Jun 18, 2008 8:32 am
- Location: Surrey
Re: calculations with php
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.
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)
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);
Re: calculations with php
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
once again, cheers mate
Re: calculations with php
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);
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);
- jaoudestudios
- DevNet Resident
- Posts: 1483
- Joined: Wed Jun 18, 2008 8:32 am
- Location: Surrey
Re: calculations with php
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?
Try it and see
But it will only return 1 result? is that what you are expecting?
Re: calculations with php
this is what i am working with
then i want to chuck the result out here
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
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();
Code: Select all
<table>
<tr>
<td width="260">average rank over the last # days</td>
<td width="140"><?php echo "$rankresult"; ?></td>
</tr>
</table>
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
- jaoudestudios
- DevNet Resident
- Posts: 1483
- Joined: Wed Jun 18, 2008 8:32 am
- Location: Surrey
Re: calculations with php
Change your query to...
And echo this...
Btw did you not get an error before?
Code: Select all
SELECT avg(rank) as avgRank FROM rankresults WHERE dateSubmitted > DATE_SUB(NOW(), INTERVAL 2 DAYCode: Select all
echo $rankresult['avgRank'];Re: calculations with php
no i have not got an error and also there are no results being shown at all. this is what i am using.
this is the back code am using
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
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>
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();- jaoudestudios
- DevNet Resident
- Posts: 1483
- Joined: Wed Jun 18, 2008 8:32 am
- Location: Surrey
Re: calculations with php
Remove the close at the moment and let php do its own garbage collection.
Is the data definitely appearing in the database?
Is the data definitely appearing in the database?
Re: calculations with php
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
do i need to add in any hidden fields or something? like the 'id'. am not sure why its not showing at all
- jaoudestudios
- DevNet Resident
- Posts: 1483
- Joined: Wed Jun 18, 2008 8:32 am
- Location: Surrey
Re: calculations with php
Can you post your database schema and data (database full dump).
Re: calculations with php
thats the sql dump
this is the php code
this is the front end bit
bottom line is the radio button submission results store in the form but do not come out
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');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");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>Re: calculations with php
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?
the code that preceeds the one above is this:
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>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);
Last edited by buzzby247 on Fri Jan 16, 2009 4:10 am, edited 1 time in total.