AV1611 Posted September 19, 2005 Share Posted September 19, 2005 [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? Quote Link to comment https://forums.phpfreaks.com/topic/2533-averaging-fields/ Share on other sites More sharing options...
obsidian Posted September 19, 2005 Share Posted September 19, 2005 [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! Quote Link to comment https://forums.phpfreaks.com/topic/2533-averaging-fields/#findComment-8399 Share on other sites More sharing options...
AV1611 Posted September 19, 2005 Author Share Posted September 19, 2005 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] Quote Link to comment https://forums.phpfreaks.com/topic/2533-averaging-fields/#findComment-8402 Share on other sites More sharing options...
obsidian Posted September 19, 2005 Share Posted September 19, 2005 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 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! Quote Link to comment https://forums.phpfreaks.com/topic/2533-averaging-fields/#findComment-8405 Share on other sites More sharing options...
AV1611 Posted September 19, 2005 Author Share Posted September 19, 2005 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] Quote Link to comment https://forums.phpfreaks.com/topic/2533-averaging-fields/#findComment-8414 Share on other sites More sharing options...
obsidian Posted September 19, 2005 Share Posted September 19, 2005 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! Quote Link to comment https://forums.phpfreaks.com/topic/2533-averaging-fields/#findComment-8415 Share on other sites More sharing options...
AV1611 Posted September 19, 2005 Author Share Posted September 19, 2005 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> Quote Link to comment https://forums.phpfreaks.com/topic/2533-averaging-fields/#findComment-8418 Share on other sites More sharing options...
ryanlwh Posted September 19, 2005 Share Posted September 19, 2005 [!--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--] Quote Link to comment https://forums.phpfreaks.com/topic/2533-averaging-fields/#findComment-8419 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.