Vanilla Forums has been nominated for a second time in the CMS Critic "Critic's Choice" awards, and we need your vote! Read more here, and then do the thing (please).
Our new Indie Games subforum is now open for business in G&T. Go and check it out, you might land a code for a free game. If you're developing an indie game and want to post about it, follow these directions. If you don't, he'll break your legs! Hahaha! Seriously though.
Our rules have been updated and given their own forum. Go and look at them! They are nice, and there may be new ones that you didn't know about! Hooray for rules! Hooray for The System! Hooray for Conforming!

PHP + MySQL help

AridholAridhol Registered User regular
edited April 2009 in Help / Advice Forum
first off, I hope this is ok in H&A and yes this is for a work project.
I am *just* attempting to learn php and MySQl (2 weeks in) so to say I'm a beginner is an understatement.
I have the following: (all the database connection stuff has actual values in my file.)
<?php


$dbname='databasename';

$dbc = mysql_connect('hostname', 'username', 'password') OR DIE ('Unable to connect to database! Please try again later.');
mysql_select_db($dbname);

;
$query = "SELECT * FROM teams ORDER BY team ASC";
$result = mysql_query($query) or die(mysql_error());


while ($row = mysql_fetch_assoc($result)) {
$team = $row['team'];
$rep = $row['rep'];
$modems = $row['modems'];


echo '<table border="1" cellpadding="2" cellspacing="0" bordercolor="#000000" bgcolor="#7CFC00">';
echo '<tr>';
echo '<td width="90">' . $team . '</td>';
echo '<td width="90">' . $rep . '</td>';
echo '<td width="15">' . $modems . '</td>';
echo '</tr>';
echo '</table>';

}

?>



So this pulls all of the records in my table and sorts them by team, great! except I want to sort by the sum of the $modems for each team. E.g. if team 1 has their 4 members with 2 $modems each that'd be 8, which would be more than say team 2 which only has 6. I want to then make team 1 be at the top, team 2 next etc...

I hope that is semi-clear. The table I have has 3 fields 'team' 'rep' 'modems' and I basically want to generate a page that displays the top team totals (of modems) at the top.

Appreciate any help or explanation or tutorials that could point me in the right direction.

Aridhol on

Posts

  • JHunzJHunz Registered User regular
    edited April 2009
    If I understand correctly what you want, you're looking for one member of each team displayed in each row, in order of number of modems sold by the whole team. This is off the top of my head without a database to try it in, so the syntax might be slightly off:

    select team, rep, modems from team
    order by sum(modems) DESC, rep
    group by team

    The key here is the sum function, which is adding together the modems column from certain rows. It knows which rows to add together because of the group by statement, which tells it to add together only modems for the same team.

    As a sidenote, the table is listing a rep, his team number, and his sales. Why is the table called team? :p

    bunny.gif Gamertag: JHunz. R.I.P. Mygamercard.net bunny.gif
  • Sharp101Sharp101 TorontoRegistered User regular
    edited April 2009
    If I'm also understanding correctly, I think you're missing

    select team, rep, sum(modems) from team
    order by sum(modems) DESC, rep
    group by team

    Otherwise it would just return the non-added modem values.

    Also, it looks like Rep isn't needed if your just getting a sum of modems sold... so you could use this

    select team, sum(modems) from team
    order by sum(modems) DESC
    group by team

    I would also look into using more Raw SQL in your PHP. I don't remember if it's hard to do or not, but it will help you in learning SQL.

    When I worked in python/sql, I always had a query window open to create/test my queries before using them in my scripts.

  • JHunzJHunz Registered User regular
    edited April 2009
    I assumed that he still wanted to show each rep and how many modems they sold, but just sort the results based on the team sales. If not, then you would be correct.

    bunny.gif Gamertag: JHunz. R.I.P. Mygamercard.net bunny.gif
  • AridholAridhol Registered User regular
    edited April 2009
    JHunz wrote: »
    I assumed that he still wanted to show each rep and how many modems they sold, but just sort the results based on the team sales. If not, then you would be correct.

    This is correct.

    I'm not sure why I named the table teams....

    Appreciate the help and I will try this out.

  • AridholAridhol Registered User regular
    edited April 2009
    Throwing

    select team,rep,modems FROM teams ORDER BY modems DESC

    into the SQL window in mysqladmin returns a list of all the teams,reps,modems sorted by most modems. If i add on

    select team,rep,couriers FROM teams ORDER BY couriers DESC, rep GROUP BY team

    it gives me an error.
    MySQL said: Documentation
    #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY team
    
    LIMIT 0, 30' at line 1
    


    if I can get this to order by the most modems and then group the reps into their teams I'd be done :)


    edit
    wheee

    SELECT SUM(couriers),team
    FROM teams
    GROUP BY team
    ORDER BY SUM(couriers) DESC

    this works sorta. Doesn't show the reps but sorts the teams.

  • admanbadmanb the bored genie Seattle, WARegistered User regular
    edited April 2009
    Try this:

    SELECT SUM(couriers), MIN(rep), team
    FROM teams
    GROUP BY team
    ORDER BY SUM(couriers) DESC

    Does that get what you want?

    twitter, github, resume/portfolio, if you like to play or host boardgames online, check out handtracker
  • FightTestFightTest Registered User regular
    edited April 2009
    The problem is once you introduce a GROUP BY you're collapsing other rows into whatever you grouped by, so there's no way to get both a group sum and individual reps from a single query.

    This won't show the individual contribution of the reps but it will show the team, who's on the team, and the team's total sorted by the most modems:

    SELECT team, GROUP_CONCAT(rep ORDER BY rep ASC SEPARATOR ', ') as reps, sum(modems) FROM teams GROUP BY team ORDER BY sum(modems) DESC;


    Or you could just break down and use two queries and get everything you want.

    MOBA DOTA.
  • AridholAridhol Registered User regular
    edited April 2009
    Thanks to everyone that replied. Everything is working perfectly now. I even managed to make a page where I can read the current modem sales, store that and then add it to whatever number is entered in the form and then store that total. wooo!

    Once again thanks everyone.

Sign In or Register to comment.