calculations with php

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

buzzby247
Forum Newbie
Posts: 23
Joined: Wed Jan 14, 2009 6:19 am

calculations with php

Post 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?
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

Re: calculations with php

Post 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);
 
buzzby247
Forum Newbie
Posts: 23
Joined: Wed Jan 14, 2009 6:19 am

Re: calculations with php

Post 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?
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

Re: calculations with php

Post 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)
buzzby247
Forum Newbie
Posts: 23
Joined: Wed Jan 14, 2009 6:19 am

Re: calculations with php

Post 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
buzzby247
Forum Newbie
Posts: 23
Joined: Wed Jan 14, 2009 6:19 am

Re: calculations with php

Post 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);
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

Re: calculations with php

Post 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?
buzzby247
Forum Newbie
Posts: 23
Joined: Wed Jan 14, 2009 6:19 am

Re: calculations with php

Post 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
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

Re: calculations with php

Post 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?
buzzby247
Forum Newbie
Posts: 23
Joined: Wed Jan 14, 2009 6:19 am

Re: calculations with php

Post 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
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

Re: calculations with php

Post by jaoudestudios »

Remove the close at the moment and let php do its own garbage collection.

Is the data definitely appearing in the database?
buzzby247
Forum Newbie
Posts: 23
Joined: Wed Jan 14, 2009 6:19 am

Re: calculations with php

Post 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
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

Re: calculations with php

Post by jaoudestudios »

Can you post your database schema and data (database full dump).
buzzby247
Forum Newbie
Posts: 23
Joined: Wed Jan 14, 2009 6:19 am

Re: calculations with php

Post 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
buzzby247
Forum Newbie
Posts: 23
Joined: Wed Jan 14, 2009 6:19 am

Re: calculations with php

Post 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);
Last edited by buzzby247 on Fri Jan 16, 2009 4:10 am, edited 1 time in total.
Post Reply