Jump to content

averaging fields

AV1611

By AV1611
in MySQL Help

Recommended Posts

obsidian Member

obsidian

Posted
Posted
[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?

296938[/snapback]

 

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!

Link to comment
https://forums.phpfreaks.com/topic/2533-averaging-fields/#findComment-8399
Share on other sites
AV1611 Advanced Member

AV1611

Posted
  • Author
Posted

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!

296969[/snapback]

 

Link to comment
https://forums.phpfreaks.com/topic/2533-averaging-fields/#findComment-8402
Share on other sites
obsidian Member

obsidian

Posted
Posted
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....

296993[/snapback]

 

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!

Link to comment
https://forums.phpfreaks.com/topic/2533-averaging-fields/#findComment-8405
Share on other sites
AV1611 Advanced Member

AV1611

Posted
  • Author
Posted

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?

296938[/snapback]

 

Link to comment
https://forums.phpfreaks.com/topic/2533-averaging-fields/#findComment-8414
Share on other sites
obsidian Member

obsidian

Posted
Posted

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!

Link to comment
https://forums.phpfreaks.com/topic/2533-averaging-fields/#findComment-8415
Share on other sites
AV1611 Advanced Member

AV1611

Posted
  • Author
Posted

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>- -

</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>+ +

</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>

Link to comment
https://forums.phpfreaks.com/topic/2533-averaging-fields/#findComment-8418
Share on other sites
ryanlwh Newbie

ryanlwh

Posted
Posted

[!--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--]

Link to comment
https://forums.phpfreaks.com/topic/2533-averaging-fields/#findComment-8419
Share on other sites

Archived

This topic is now archived and is closed to further replies.

Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.