Jump to content


Photo

averaging fields


  • Please log in to reply
7 replies to this topic

#1 AV1611

AV1611
  • Members
  • PipPipPip
  • Advanced Member
  • 997 posts

Posted 19 September 2005 - 11:39 AM

[FIELD1] [FIELD2]
JOE 3
JOE 4
JOE 4
JOE 3
JOE 4



I Want to make a query that displays the average number for records where field1=joe

Or, do I need to use PHP to do the averaging?


#2 obsidian

obsidian
  • Staff Alumni
  • Advanced Member
  • 3,202 posts
  • LocationSeattle, WA

Posted 19 September 2005 - 01:38 PM

[FIELD1]  [FIELD2]
JOE              3
JOE              4
JOE              4
JOE              3
JOE              4
I Want to make a query that displays the average number for records where field1=joe

Or, do I need to use PHP to do the averaging?

View Post


the sql AVG() function should do the trick:
[!--PHP-Head--][div class=\'phptop\']PHP[/div][div class=\'phpmain\'][!--PHP-EHead--]
[span style=\"color:#0000BB\"]<?php
[/span][span style=\"color:#FF8000\"]// for specific user
[/span][span style=\"color:#0000BB\"]$sql [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#0000BB\"]mysql_query[/span][span style=\"color:#007700\"]([/span][span style=\"color:#DD0000\"]\"SELECT AVG(field2) AS avg WHERE field1 = \'joe\'\"[/span][span style=\"color:#007700\"]);
echo [/span][span style=\"color:#0000BB\"]mysql_result[/span][span style=\"color:#007700\"]([/span][span style=\"color:#0000BB\"]$sql[/span][span style=\"color:#007700\"], [/span][span style=\"color:#0000BB\"]0[/span][span style=\"color:#007700\"], [/span][span style=\"color:#DD0000\"]\'avg\'[/span][span style=\"color:#007700\"]);

[/span][span style=\"color:#FF8000\"]// for getting averages for all users:
[/span][span style=\"color:#0000BB\"]$sql [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#0000BB\"]mysql_query[/span][span style=\"color:#007700\"]([/span][span style=\"color:#DD0000\"]\"SELECT field1, AVG(field2) AS avg GROUP BY field1\"[/span][span style=\"color:#007700\"]);
while ([/span][span style=\"color:#0000BB\"]$row [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#0000BB\"]mysql_fetch_array[/span][span style=\"color:#007700\"]([/span][span style=\"color:#0000BB\"]$sql[/span][span style=\"color:#007700\"])) {
  echo [/span][span style=\"color:#DD0000\"]\"$row[field1] - $row[avg]<br />\n\"[/span][span style=\"color:#007700\"];
}
[/span][span style=\"color:#0000BB\"]?>
[/span]
[/span][!--PHP-Foot--][/div][!--PHP-EFoot--]

hope this helps!
You can't win, you can't lose, you can't break even... you can't even get out of the game.

<?php
while (count($life->getQuestions()) > 0)
{   $life->study(); } ?>
  LINKS: PHP: Manual MySQL: Manual PostgreSQL: Manual (X)HTML: Validate It! CSS: A List Apart | IE bug fixes | Zen Garden | Validate It! JavaScript: Reference Cards RegEx: Everything RegEx

#3 AV1611

AV1611
  • Members
  • PipPipPip
  • Advanced Member
  • 997 posts

Posted 19 September 2005 - 03:07 PM

You have a habit of helping me a log... Thanks...

Using the second solution, can the results be echoed into a table?...

whenever I try to echo any query into a table, it only gives me the first result, then the table closes, and I don't get any more... If I do it without the table, it works fine...

I know, that's kind of a dumb question....




the sql AVG() function should do the trick:
[!--PHP-Head--][div class=\'phptop\']PHP[/div][div class=\'phpmain\'][!--PHP-EHead--]
[span style=\\\"color:#0000BB\\\"]<?php
[/span][span style=\\\"color:#FF8000\\\"]// for specific user
[/span][span style=\\\"color:#0000BB\\\"]$sql [/span][span style=\\\"color:#007700\\\"]= [/span][span style=\\\"color:#0000BB\\\"]mysql_query[/span][span style=\\\"color:#007700\\\"]([/span][span style=\\\"color:#DD0000\\\"]\\\"SELECT AVG(field2) AS avg WHERE field1 = \'joe\'\\\"[/span][span style=\\\"color:#007700\\\"]);
echo [/span][span style=\\\"color:#0000BB\\\"]mysql_result[/span][span style=\\\"color:#007700\\\"]([/span][span style=\\\"color:#0000BB\\\"]$sql[/span][span style=\\\"color:#007700\\\"], [/span][span style=\\\"color:#0000BB\\\"]0[/span][span style=\\\"color:#007700\\\"], [/span][span style=\\\"color:#DD0000\\\"]\'avg\'[/span][span style=\\\"color:#007700\\\"]);

[/span][span style=\\\"color:#FF8000\\\"]// for getting averages for all users:
[/span][span style=\\\"color:#0000BB\\\"]$sql [/span][span style=\\\"color:#007700\\\"]= [/span][span style=\\\"color:#0000BB\\\"]mysql_query[/span][span style=\\\"color:#007700\\\"]([/span][span style=\\\"color:#DD0000\\\"]\\\"SELECT field1, AVG(field2) AS avg GROUP BY field1\\\"[/span][span style=\\\"color:#007700\\\"]);
while ([/span][span style=\\\"color:#0000BB\\\"]$row [/span][span style=\\\"color:#007700\\\"]= [/span][span style=\\\"color:#0000BB\\\"]mysql_fetch_array[/span][span style=\\\"color:#007700\\\"]([/span][span style=\\\"color:#0000BB\\\"]$sql[/span][span style=\\\"color:#007700\\\"])) {
[/span][span style=\\\"color:#0000BB\\\"]  echo [/span][span style=\\\"color:#DD0000\\\"]\\\"$row[field1] - $row[avg]<br />\n\\\"[/span][span style=\\\"color:#007700\\\"];
}
[/span][span style=\\\"color:#0000BB\\\"]?>[/span]
[/span][!--PHP-Foot--][/div][!--PHP-EFoot--]

hope this helps!

View Post



#4 obsidian

obsidian
  • Staff Alumni
  • Advanced Member
  • 3,202 posts
  • LocationSeattle, WA

Posted 19 September 2005 - 04:48 PM

You have a habit of helping me a log... Thanks...

Using the second solution, can the results be echoed into a table?...

whenever I try to echo any query into a table, it only gives me the first result, then the table closes, and I don't get any more... If I do it without the table, it works fine...

I know, that's kind of  a dumb question....

View Post


i've always believed that there's no such thing as a dumb question... some just take a little more thinking to answer than others :D

here's how i'd echo in a table:
[!--PHP-Head--][div class=\'phptop\']PHP[/div][div class=\'phpmain\'][!--PHP-EHead--]
[span style=\"color:#0000BB\"]<?php
$sql [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#0000BB\"]mysql_query[/span][span style=\"color:#007700\"]([/span][span style=\"color:#DD0000\"]\"SELECT field1, AVG(field2) AS avg GROUP BY field1\"[/span][span style=\"color:#007700\"]);
echo [/span][span style=\"color:#DD0000\"]\"<table>\n\"[/span][span style=\"color:#007700\"];
while ([/span][span style=\"color:#0000BB\"]$row [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#0000BB\"]mysql_fetch_array[/span][span style=\"color:#007700\"]([/span][span style=\"color:#0000BB\"]$sql[/span][span style=\"color:#007700\"])) {
  echo [/span][span style=\"color:#DD0000\"]\"<tr><td>$row[field1]</td><td>$row[avg]</td></tr>\n\"[/span][span style=\"color:#007700\"];
}
echo [/span][span style=\"color:#DD0000\"]\"</table>\n\"[/span][span style=\"color:#007700\"];
[/span][span style=\"color:#0000BB\"]?>
[/span]
[/span][!--PHP-Foot--][/div][!--PHP-EFoot--]

hope this works for you!
You can't win, you can't lose, you can't break even... you can't even get out of the game.

<?php
while (count($life->getQuestions()) > 0)
{   $life->study(); } ?>
  LINKS: PHP: Manual MySQL: Manual PostgreSQL: Manual (X)HTML: Validate It! CSS: A List Apart | IE bug fixes | Zen Garden | Validate It! JavaScript: Reference Cards RegEx: Everything RegEx

#5 AV1611

AV1611
  • Members
  • PipPipPip
  • Advanced Member
  • 997 posts

Posted 19 September 2005 - 06:49 PM

A little off topic, but part of this thread... then I can mark solved...
<whining>
My boss is quite unreasonable :x
</whining>
OK,

Your table works great...

I know how to add table color ,

but... here is what he wants... every other row to be a different color, like the old color bar paper <showing my age>

What I don't understand is query results are printed a row at a time and are recursive... I don't know how I would make the second row a different color with a query result... I know how to do it in a normal table...

I hope I make sense...

Thanks...





[FIELD1]  [FIELD2]
JOE              3
JOE              4
JOE              4
JOE              3
JOE              4
I Want to make a query that displays the average number for records where field1=joe

Or, do I need to use PHP to do the averaging?

View Post



#6 obsidian

obsidian
  • Staff Alumni
  • Advanced Member
  • 3,202 posts
  • LocationSeattle, WA

Posted 19 September 2005 - 06:57 PM

no problem... take a look at this one, and see if you can grasp how the recurring code changes the color each time:
[!--PHP-Head--][div class=\'phptop\']PHP[/div][div class=\'phpmain\'][!--PHP-EHead--]
[span style=\"color:#0000BB\"]<?php
$sql [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#0000BB\"]mysql_query[/span][span style=\"color:#007700\"]([/span][span style=\"color:#DD0000\"]\"SELECT field1, AVG(field2) AS avg GROUP BY field1\"[/span][span style=\"color:#007700\"]); [/span][span style=\"color:#FF8000\"]// run query
[/span][span style=\"color:#0000BB\"]$color [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#DD0000\"]\'#ffffff\'[/span][span style=\"color:#007700\"]; [/span][span style=\"color:#FF8000\"]// set default color (white in this case)
[/span][span style=\"color:#007700\"]echo [/span][span style=\"color:#DD0000\"]\"<table>\n\"[/span][span style=\"color:#007700\"];
while ([/span][span style=\"color:#0000BB\"]$row [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#0000BB\"]mysql_fetch_array[/span][span style=\"color:#007700\"]([/span][span style=\"color:#0000BB\"]$sql[/span][span style=\"color:#007700\"])) {
  [/span][span style=\"color:#0000BB\"]$color [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#0000BB\"]$color [/span][span style=\"color:#007700\"]== [/span][span style=\"color:#DD0000\"]\'#ffffff\' [/span][span style=\"color:#007700\"]? [/span][span style=\"color:#DD0000\"]\'#f2f3f4\' [/span][span style=\"color:#007700\"]: [/span][span style=\"color:#DD0000\"]\'#ffffff\'[/span][span style=\"color:#007700\"]; [/span][span style=\"color:#FF8000\"]// ternary operator switching between white and pale blue for each row
  [/span][span style=\"color:#007700\"]echo [/span][span style=\"color:#DD0000\"]\"<tr style=\'background-color: $color;\'><td>$row[field1]</td><td>$row[avg]</td></tr>\n\"[/span][span style=\"color:#007700\"];
}
echo [/span][span style=\"color:#DD0000\"]\"</table>\n\"[/span][span style=\"color:#007700\"];
[/span][span style=\"color:#0000BB\"]?>
[/span]
[/span][!--PHP-Foot--][/div][!--PHP-EFoot--]

good luck!
You can't win, you can't lose, you can't break even... you can't even get out of the game.

<?php
while (count($life->getQuestions()) > 0)
{   $life->study(); } ?>
  LINKS: PHP: Manual MySQL: Manual PostgreSQL: Manual (X)HTML: Validate It! CSS: A List Apart | IE bug fixes | Zen Garden | Validate It! JavaScript: Reference Cards RegEx: Everything RegEx

#7 AV1611

AV1611
  • Members
  • PipPipPip
  • Advanced Member
  • 997 posts

Posted 19 September 2005 - 09:59 PM

[quote=obsidian,Sep 19 2005, 02:57 PM]
no problem... take a look at this one, and see if you can grasp how the recurring
the colors don't alternate...


here is my code...


<snip>


<form METHOD="POST" action="surveyinsert.php">

<?php

<snip login stuff here>

$result = mysql_query
("SELECT * FROM SURVEY");

$color = '#ffffff';

echo '<table cellspacing="3" BORDERCOLORLIGHT = "#5877B6" BORDERCOLORDARK = "#011233" align="center" border="5" width = "90%">';


// these label the table fields on the form
echo '<tr align="left" ><td width = "25%" align = "center" valign="middle" >
Employee Name</td>
<td width = "5%" align = "center" valign="middle" >
ID</td>
<td width = "14%" align = "center" valign="middle" >
<h1>-&nbsp;-
</td><td width = "14%" align = "center" valign="middle" >
<h1>-
</td><td width = "14%" align = "center" valign="middle" >
Meets Expectations
</td><td width = "14%" align = "center" valign="middle" >
<h2>+
</td><td width = "14%" align = "center" valign="middle" >
<h2>+&nbsp;+
</td></tr>';

while ($row = mysql_fetch_array($result))
{
$color = $color == '#ffffff' ? '#f2f3f4' : '#ffffff';
echo '<tr style="background-color: $color;"><td>';
echo $row['NAME'];
echo '</td><td>';
echo $row['ID'];
echo '</td><td align = "center" ><input TYPE="RADIO" name="RATING" value="1"></td>
<td align = "center" ><input TYPE="RADIO" name="RATING" value="2"></td>
<td align = "center" ><input TYPE="RADIO" name="RATING" value="3"></td>
<td align = "center" ><input TYPE="RADIO" name="RATING" value="4"></td>
<td align = "center" ><input TYPE="RADIO" name="RATING" value="5"></td>
</tr>';
}

echo "</table>";
?>

<br>
<center><input type="submit" name="submit" value="submit"></center>
</form>

#8 ryanlwh

ryanlwh
  • Staff Alumni
  • Advanced Member
  • 511 posts

Posted 19 September 2005 - 10:50 PM

[!--PHP-Head--][div class=\'phptop\']PHP[/div][div class=\'phpmain\'][!--PHP-EHead--]
echo \'<tr style=\"background-color: $color;\"><td>\';[/span][!--PHP-Foot--][/div][!--PHP-EFoot--]

Remember, PHP won't parse variables in a single-quoted string. Use double-quotes or concatenation.


[!--PHP-Head--][div class=\'phptop\']PHP[/div][div class=\'phpmain\'][!--PHP-EHead--]
echo \"<tr style=\\"background-color: $color;\\"><td>\";

//or

echo \'<tr style=\"background-color: \'.$color.\';\"><td>\';
[/span][!--PHP-Foot--][/div][!--PHP-EFoot--]


Here's another method to do this.

[!--PHP-Head--][div class=\'phptop\']PHP[/div][div class=\'phpmain\'][!--PHP-EHead--]
$color = array(\'#f2f3f4\' , \'#ffffff\');

$i=0;
while($row = mysql_fetch_array($result))
{
  echo \'<tr style=\"background-color: \'.$color[$i%2].\';\"><td>\';

  //blah blah blah

  $i++;
}
[/span][!--PHP-Foot--][/div][!--PHP-EFoot--]
Please use EDIT * 100...
Please use
or [php] * 1000...

PLEASE READ THE POSTED SOLUTIONS CAREFULLY * 1000000...




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users