Page 2 of 3

Re: calculations with php

Posted: Thu Jan 15, 2009 1:03 pm
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)

Re: calculations with php

Posted: Thu Jan 15, 2009 1:12 pm
by jaoudestudios
I setup your DB at this end and ran the query with the bracket at the end...it works :)

Re: calculations with php

Posted: Fri Jan 16, 2009 4:00 am
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

Re: calculations with php

Posted: Fri Jan 16, 2009 4:29 am
by jaoudestudios
$usabilityRow = mysql_fetch_array($usabilityresult);
Try this instead...

Code: Select all

$usabilityRow = mysql_fetch_assoc($usabilityresult);

Re: calculations with php

Posted: Fri Jan 16, 2009 4:33 am
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

Re: calculations with php

Posted: Fri Jan 16, 2009 4:42 am
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

Re: calculations with php

Posted: Fri Jan 16, 2009 4:53 am
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)

Re: calculations with php

Posted: Fri Jan 16, 2009 4:56 am
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

Re: calculations with php

Posted: Fri Jan 16, 2009 5:20 am
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');
 

Re: calculations with php

Posted: Fri Jan 16, 2009 5:29 am
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.

Re: calculations with php

Posted: Fri Jan 16, 2009 5:41 am
by buzzby247
can you send me the correct code please so i can see where i went wrong?
much appreciated.

Re: calculations with php

Posted: Fri Jan 16, 2009 5:46 am
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>
 

Re: calculations with php

Posted: Fri Jan 16, 2009 5:50 am
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?

Re: calculations with php

Posted: Fri Jan 16, 2009 5:55 am
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.

Re: calculations with php

Posted: Fri Jan 16, 2009 6:07 am
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?