Jump to content

averaging fields


AV1611

Recommended Posts

[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
Share on other sites

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
Share on other sites

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
Share on other sites

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
Share on other sites

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
Share on other sites

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
Share on other sites

[!--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
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • 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.