mattichu Posted March 5, 2012 Share Posted March 5, 2012 Hi! i have a table: Day Username Rate Hours Shift Cost -------------------------------------------------------------------- MON Matt 8.5 2 £17 MON Imogen 6 2 £12 MON Matt 8.5 1 £8.5 MON Imogen 6 1 £6 -------------------------------------------------------------------- Im trying to output a table that shows the total cost for each user. like: User Rate Hours Wage ---------------------------------------- Matt 8.5 3 £25.5 Imogen 6 3 £18 ---------------------------------------- How would i go about doing this?? many thanks! x Quote Link to comment https://forums.phpfreaks.com/topic/258344-sql-sum-with-array/ Share on other sites More sharing options...
Maq Posted March 5, 2012 Share Posted March 5, 2012 Moving to MySQL to get the query first. Not sure what you mean by "with array" but you have to SUM() "hours" and "Shift Cost" and GROUP BY() "Username". '£' may give you some trouble. Try it yourself and post what you come up with. Quote Link to comment https://forums.phpfreaks.com/topic/258344-sql-sum-with-array/#findComment-1324274 Share on other sites More sharing options...
The Little Guy Posted March 5, 2012 Share Posted March 5, 2012 select User, sum(Rate) as Rate, sum(Hours) as Hours from My_Table group by User Quote Link to comment https://forums.phpfreaks.com/topic/258344-sql-sum-with-array/#findComment-1324275 Share on other sites More sharing options...
mattichu Posted March 5, 2012 Author Share Posted March 5, 2012 fab ive got that to work in php myadmin but how do i output it as an HTML table? x Quote Link to comment https://forums.phpfreaks.com/topic/258344-sql-sum-with-array/#findComment-1324279 Share on other sites More sharing options...
mattichu Posted March 5, 2012 Author Share Posted March 5, 2012 SOLVED <? $username="****"; $password="*****"; $database="******"; mysql_connect('localhost',$username,$password); @mysql_select_db($database) or die( "Unable to select database"); $query="select username,rop, sum(totalcost) as totalcost, sum(totalhours) as totalhours from hours group by username"; $result = mysql_query($query) or die(mysql_error()); // Print out result while($row = mysql_fetch_array($result)){ ?> <html> <table><td><?php echo $row['username']; ?></td><td><?php echo $row['rop'] ; ?></td><td><?php echo $row['totalhours'] ; ?></td><td><?php echo $row['totalcost'] ; ?></td> </table> </html> <? } ?> many thanks guys! Quote Link to comment https://forums.phpfreaks.com/topic/258344-sql-sum-with-array/#findComment-1324284 Share on other sites More sharing options...
Maq Posted March 5, 2012 Share Posted March 5, 2012 That's the general idea, some suggestions: - Use complete tags (<?php). - Better formatting. It will help you in the long run. - Instead of using or die (which I think is OK for debugging) properly handle errors - http://www.phpfreaks.com/blog/or-die-must-die Quote Link to comment https://forums.phpfreaks.com/topic/258344-sql-sum-with-array/#findComment-1324288 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.