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

User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

Re: calculations with php

Post by jaoudestudios »

SELECT avg(usability) as avgUsability FROM usabilityresults WHERE dateSubmitted > DATE_SUB(NOW(), INTERVAL 2 DAY
you are missing a bracket on the end, it should be...

Code: Select all

SELECT avg(usability) as avgUsability FROM usabilityresults WHERE dateSubmitted > DATE_SUB(NOW(), INTERVAL 2 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 »

I setup your DB at this end and ran the query with the bracket at the end...it works :)
buzzby247
Forum Newbie
Posts: 23
Joined: Wed Jan 14, 2009 6:19 am

Re: calculations with php

Post by buzzby247 »

the query works but i am still not gettin the results on the form page where i have this code:

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 form is blank in that area.

the code does submit perfectly tho with no errors
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

Re: calculations with php

Post by jaoudestudios »

$usabilityRow = mysql_fetch_array($usabilityresult);
Try this instead...

Code: Select all

$usabilityRow = mysql_fetch_assoc($usabilityresult);
buzzby247
Forum Newbie
Posts: 23
Joined: Wed Jan 14, 2009 6:19 am

Re: calculations with php

Post by buzzby247 »

i have use these and yet nothing has happened:
mysql_fetch_array($usabilityresult)
mysql_fetch_assoc($usabilityresult)
mysql_fetch_row($usabilityresult)
mysql_result($usabilityresult)

the field still remains blank
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

Re: calculations with php

Post by jaoudestudios »

How hard would it be to zip up the code including a DB dump and post it here? then I can run it
buzzby247
Forum Newbie
Posts: 23
Joined: Wed Jan 14, 2009 6:19 am

Re: calculations with php

Post by buzzby247 »

well i can email it to you if thats cool. code and sql dump. (i think i sent the dump already but its no biggi to include it again)
buzzby247
Forum Newbie
Posts: 23
Joined: Wed Jan 14, 2009 6:19 am

Re: calculations with php

Post by buzzby247 »

actually here is the cdoe. fill in what you need to run it then see what happens

Code: Select all

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
<SCRIPT LANGUAGE="JavaScript">
function redirect () { setTimeout("go_now()",18000); }
function go_now ()   { window.location.href = "resultstrial.php"; }
</script></head>
<?php
if(isset($_POST['SubmitFirst']))
{
$dbhost = 'localhost';
$dbuser = '#';
$dbpass = '#';
$dbname = '#';
$connection = mysql_connect($dbhost, $dbpass, $dbuser) or die(mysql_error());
$db_selected = mysql_select_db('formresults', $connection);
 
 
mysql_select_db($dbname) or die(mysql_error());
 
 
if(!get_magic_quotes_gpc()) {
    $rank=addslashes($_POST['rank']);
}else {
    $rank = $_POST['rank'];
}
 
$query = ("INSERT INTO rankresults (rank, dateSubmitted) VALUES ('$rank',NOW())");
mysql_query($query) or die(mysql_error());
$rankQuery = "SELECT avg(rank) as avgRank FROM rankresults WHERE dateSubmitted > DATE_SUB(NOW(), INTERVAL 2 DAY)";
$rankresult=mysql_query($rankQuery);  
if (!$rankresult) {
    die("Query error!  Query is $rankQuery<br>Error is " . mysql_error());
}
$rankRow = mysql_fetch_array($rankresult); 
 
 
 
echo "<table width='100%' cellspacing='0' cellpadding='4'><tr><td>";
echo "<span style='background-color:white; padding: 2px; color: #234e5f;'>";
echo "<strong>YOUR RANK VALUE BEEN SUBMITTED</strong><br><br>";
echo "</span>";
echo "</td></tr></table>"; 
echo "<body onLoad=redirect() text=#000000 link=#00FFFF vlink=#C0C0C0>"; 
echo "</body>";
}
 
elseif(isset($_POST['SubmitSecond']))
{
$dbhost = 'localhost';
$dbuser = 'hamleyboy';
$dbpass = 'hamleyboy';
$dbname = 'formresults';
$connection = mysql_connect($dbhost, $dbpass, $dbuser) or die(mysql_error());
$db_selected = mysql_select_db('formresults', $connection);
 
 
mysql_select_db($dbname) or die(mysql_error());
 
 
if(!get_magic_quotes_gpc()) {
    $usability=addslashes($_POST['usability']);
}else {
    $usability = $_POST['usability'];
}
 
$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_result($usabilityresult); 
echo "<table width='100%' cellspacing='0' cellpadding='4'><tr><td>";
echo "<span style='background-color:white; padding: 2px; color: #234e5f;'>";
echo "<strong>YOUR USABILITY VALUE HAS BEEN SUBMITTED</strong><br><br>";
echo "</span>";
echo "</td></tr></table>"; 
echo "<body onLoad=redirect() text=#000000 link=#00FFFF vlink=#C0C0C0>"; 
echo "</body>";
}
else
{
?>
<body>
<form id="form2" name="form2" method="post" action="resultstrial.php">
<table width="227" border="0" cellspacing="2" cellpadding="2">
  <tr>
    <td colspan="2">rate this form aswell. useability</td>
  </tr>
  <tr>
    <td width="106"> can't use it at all</td>
    <td width="107">
<input type="radio" name="usability" id="very poor" value="1" /></td>
  </tr>
  <tr>
    <td>difficult to use</td>
    <td><input type="radio" name="usability" id="poor" value="2" /></td>
  </tr>
  <tr>
    <td>just about understand</td>
    <td><input name="usability" type="radio" id="fair" value="3" checked="checked" /></td>
  </tr>
  <tr>
    <td>can use with a bit of help</td>
    <td><input type="radio" name="usability" id="good" value="4" /></td>
  </tr>
  <tr>
    <td>very user friendly</td>
    <td><input type="radio" name="usability" id="very good" value="5" /></td>
  </tr>
  <tr>
    <td colspan="2"><label>
      <input type="submit" name="SubmitSecond" id="SubmitSecond" value="Submit" />
    </label></td>
    </tr>
</table>
</form>
<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>
<hr style="border-top: 1px solid #cccccc;"/>
<br/><br/>
<form id="form1" name="form1" method="post" action="resultstrial.php">
<table width="227" border="0" cellspacing="2" cellpadding="2">
  <tr>
    <td colspan="2">rate this form. rank</td>
  </tr>
  <tr>
    <td width="106"> very poor</td>
    <td width="107">
<input type="radio" name="rank" id="very poor" value="1" /></td>
  </tr>
  <tr>
    <td>poor</td>
    <td><input type="radio" name="rank" id="poor" value="2" /></td>
  </tr>
  <tr>
    <td>fair</td>
    <td><input name="rank" type="radio" id="fair" value="3" checked="checked" /></td>
  </tr>
  <tr>
    <td>good</td>
    <td><input type="radio" name="rank" id="good" value="4" /></td>
  </tr>
  <tr>
    <td>very good</td>
    <td><input type="radio" name="rank" id="very good" value="5" /></td>
  </tr>
  <tr>
    <td colspan="2"><input type="submit" name="SubmitFirst" id="SubmitFirst" value="Submit" /></td>
    </tr>
</table>
</form>
<table width="400" border="0" cellspacing="2" cellpadding="2">
  <tr>
    <td width="260">average rank over the last 7 days</td>
    <td width="140"><?php echo $rankRow['avgRank']; ?></td>
  </tr>
  <tr>
    <td>average rank over the last 28 days</td>
    <td><?php echo $rankRow['avgRank']; ?></td>
  </tr>
</table>
<hr style="border-top: 1px solid #cccccc;"/>
<br/><br/>
</body>
<?php
}
?>
</html>
this is the sql code

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');
 
i know am using tables but for now am not concerned about that. i just want the code to work. afterwhich i can div it up to standards
buzzby247
Forum Newbie
Posts: 23
Joined: Wed Jan 14, 2009 6:19 am

Re: calculations with php

Post by buzzby247 »

here is the code for the rank results table:

Code: Select all

-- Table structure for table `rankresults`
--
 
DROP TABLE IF EXISTS `rankresults`;
CREATE TABLE IF NOT EXISTS `rankresults` (
  `id` int(11) NOT NULL auto_increment,
  `rank` int(11) default NULL,
  `dateSubmitted` varchar(20) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;
 
--
-- Dumping data for table `rankresults`
--
 
INSERT INTO `rankresults` (`id`, `rank`, `dateSubmitted`) VALUES
(1, 2, '2009-01-15 10:57:54'),
(2, 5, '2009-01-15 10:59:24'),
(3, 1, '2009-01-15 10:59:30'),
(4, 3, '2009-01-15 10:59:35'),
(5, 5, '2009-01-15 10:59:38'),
(6, 5, '2009-01-15 16:20:19'),
(7, 5, '2009-01-16 10:16:23');
 
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

Re: calculations with php

Post by jaoudestudios »

Ok I got it working, it is a cobble. You have your database connection and query in a conditional statement, so when you echo the result the previous code to calculate this result has not run.
buzzby247
Forum Newbie
Posts: 23
Joined: Wed Jan 14, 2009 6:19 am

Re: calculations with php

Post by buzzby247 »

can you send me the correct code please so i can see where i went wrong?
much appreciated.
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

Re: calculations with php

Post by jaoudestudios »

Well, sure, but I dont think it is correctly in terms of exactly what you want, so you will need to modify it. But hopefully give you an idea.

Code: Select all

 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
<SCRIPT LANGUAGE="JavaScript">
function redirect () { setTimeout("go_now()",18000); }
function go_now ()   { window.location.href = "resultstrial.php"; }
</script></head>
<?php
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = '';
$dbname = '';
$connection = mysql_connect($dbhost, $dbuser, $dbpass) or die(mysql_error());
$db_selected = mysql_select_db('temp', $connection);
 
$rankQuery = "SELECT avg(usability) as avgUsability FROM usabilityresults WHERE dateSubmitted > DATE_SUB(NOW(), INTERVAL 20 DAY)";
$rankresult=mysql_query($rankQuery);  
$usabilityRow = mysql_fetch_array($rankresult);
 
if (!$rankresult) {
    die("Query error!  Query is $rankQuery<br>Error is " . mysql_error());
}
 
if(isset($_POST['SubmitFirst']))
{
 
if(!get_magic_quotes_gpc()) {
    $rank=addslashes($_POST['rank']);
}else {
    $rank = $_POST['rank'];
}
 
$query = ("INSERT INTO rankresults (rank, dateSubmitted) VALUES ('$rank',NOW())");
mysql_query($query) or die(mysql_error());
 
 
 
 
echo "<table width='100%' cellspacing='0' cellpadding='4'><tr><td>";
echo "<span style='background-color:white; padding: 2px; color: #234e5f;'>";
echo "<strong>YOUR RANK VALUE BEEN SUBMITTED</strong><br><br>";
echo "</span>";
echo "</td></tr></table>";
echo "<body onLoad=redirect() text=#000000 link=#00FFFF vlink=#C0C0C0>";
echo "</body>";
}
 
elseif(isset($_POST['SubmitSecond']))
{
$dbhost = 'localhost';
$dbuser = 'hamleyboy';
$dbpass = 'hamleyboy';
$dbname = 'formresults';
$connection = mysql_connect($dbhost, $dbpass, $dbuser) or die(mysql_error());
$db_selected = mysql_select_db('formresults', $connection);
 
 
mysql_select_db($dbname) or die(mysql_error());
 
 
if(!get_magic_quotes_gpc()) {
    $usability=addslashes($_POST['usability']);
}else {
    $usability = $_POST['usability'];
}
 
$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_assoc($usabilityresult);
echo "<table width='100%' cellspacing='0' cellpadding='4'><tr><td>";
echo "<span style='background-color:white; padding: 2px; color: #234e5f;'>";
echo "<strong>YOUR USABILITY VALUE HAS BEEN SUBMITTED</strong><br><br>";
echo "</span>";
echo "</td></tr></table>";
echo "<body onLoad=redirect() text=#000000 link=#00FFFF vlink=#C0C0C0>";
echo "</body>";
}
else
{
?>
<body>
<form id="form2" name="form2" method="post" action="resultstrial.php">
<table width="227" border="0" cellspacing="2" cellpadding="2">
  <tr>
    <td colspan="2">rate this form aswell. useability</td>
  </tr>
  <tr>
    <td width="106"> can't use it at all</td>
    <td width="107">
<input type="radio" name="usability" id="very poor" value="1" /></td>
  </tr>
  <tr>
    <td>difficult to use</td>
    <td><input type="radio" name="usability" id="poor" value="2" /></td>
  </tr>
  <tr>
    <td>just about understand</td>
    <td><input name="usability" type="radio" id="fair" value="3" checked="checked" /></td>
  </tr>
  <tr>
    <td>can use with a bit of help</td>
    <td><input type="radio" name="usability" id="good" value="4" /></td>
  </tr>
  <tr>
    <td>very user friendly</td>
    <td><input type="radio" name="usability" id="very good" value="5" /></td>
  </tr>
  <tr>
    <td colspan="2"><label>
      <input type="submit" name="SubmitSecond" id="SubmitSecond" value="Submit" />
    </label></td>
    </tr>
</table>
</form>
<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>
<hr style="border-top: 1px solid #cccccc;"/>
<br/><br/>
<form id="form1" name="form1" method="post" action="resultstrial.php">
<table width="227" border="0" cellspacing="2" cellpadding="2">
  <tr>
    <td colspan="2">rate this form. rank</td>
  </tr>
  <tr>
    <td width="106"> very poor</td>
    <td width="107">
<input type="radio" name="rank" id="very poor" value="1" /></td>
  </tr>
  <tr>
    <td>poor</td>
    <td><input type="radio" name="rank" id="poor" value="2" /></td>
  </tr>
  <tr>
    <td>fair</td>
    <td><input name="rank" type="radio" id="fair" value="3" checked="checked" /></td>
  </tr>
  <tr>
    <td>good</td>
    <td><input type="radio" name="rank" id="good" value="4" /></td>
  </tr>
  <tr>
    <td>very good</td>
    <td><input type="radio" name="rank" id="very good" value="5" /></td>
  </tr>
  <tr>
    <td colspan="2"><input type="submit" name="SubmitFirst" id="SubmitFirst" value="Submit" /></td>
    </tr>
</table>
</form>
<table width="400" border="0" cellspacing="2" cellpadding="2">
  <tr>
    <td width="260">average rank over the last 7 days</td>
    <td width="140"><?php echo $rankRow['avgRank']; ?></td>
  </tr>
  <tr>
    <td>average rank over the last 28 days</td>
    <td><?php echo $rankRow['avgRank']; ?></td>
  </tr>
</table>
<hr style="border-top: 1px solid #cccccc;"/>
<br/><br/>
</body>
<?php
}
?>
</html>
 
buzzby247
Forum Newbie
Posts: 23
Joined: Wed Jan 14, 2009 6:19 am

Re: calculations with php

Post by buzzby247 »

the code above. is thats what is working? did you have to move code in order for the php callout to show in the table below the form?
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

Re: calculations with php

Post by jaoudestudios »

$usabilityRow['avgUsability']
This was not being calculated, so I moved the calculation part (database connection & query) out of the conditional statement so that when it displayed that line on the page it contained a value. Otherwise it will be empty and display nothing.
buzzby247
Forum Newbie
Posts: 23
Joined: Wed Jan 14, 2009 6:19 am

Re: calculations with php

Post by buzzby247 »

ahh..i have seen what you have done. you moved the if statement below the SELECT so that it runs regardless of submission or not. i have done the same for the rank results since you started it with the usability results. i have tidied up the code a little. cheers for that. its working properly.

so now that bit is done i would like some help if you can with regards to stipulating date parameters so the 7 day and 28 day results can kick in. i am not sure how to go about that. any pointers?
Post Reply