PHP + MySQL help

Aridhol

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.

JHunz

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?

Sharp101

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.

JHunz

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.

Aridhol

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.

Aridhol

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.

admanb

Try this:

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

Does that get what you want?

FightTest

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.

Aridhol

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.