[SOLVED] query question

[bschultz]

I have been asked by the local hospital to keep track of a walking contest.  Teams will walk each week, and will enter the results into a database.  I need to keep track of all the miles walked.  That part, I've done.

 

Now the hospital has asked me to create a table with all the teams results for each week.  Something like this:

 

 

   

Team Name

   

Week 1

   

Week 2

   

Week 3

   

Total

 

 

   

Team 1

   

12

   

14

   

11

   

37

 

 

   

Team 2

   

10

   

9

   

11

   

30

 

 

   

Team 3

   

15

   

7

   

7

   

29

 

 

   

Team 4

   

12

   

9

   

7

   

28

 

 

There will be (roughly) 50 teams (with completely random team names) walking...

 

The database is laid out like this:

 

row_number (integer, auto increment, primary key)

team_name (varchar 200)

week (int,2)

miles (decimal 10,2)

 

 

What's the best way to write a query to spit all that info out into an html table, sorted by the team with the most total miles walked?

[Ken2k7]

SELECT wc1.team_name AS team_name,
     (SELECT SUM(wc2.miles) FROM tablename wc2 WHERE wc2.team_name = wc1.team_name) AS total_miles
FROM tablename wc1
GROUP BY wc1.team_name
ORDER BY total_miles;

[bschultz]

Thanks for the help!

 

I do, though, have a question.  What is wc1 and wc2?  There is only one table in the database.

[Ken2k7]

They're just aliases. All you have to do in that SQL is replace tablename with the actual table name.

[bschultz]

I'll give it a whirl...thanks!

[roopurt18]

Have a look at creating a pivot table.  You can turn your data into a format that has columns:

teamname, week1, week2, week3, week4, ... weekN

 

Then you can build an HTML table as you normally would with a result set.

 

http://en.wikibooks.org/wiki/MySQL/Pivot_table

[bschultz]

WOW...is that hard to grasp!

 

I've tried, and came up with a combination of both query's

 

<?php
$qry="select *,
sum(miles*(1-abs(sign(week-1)))) as week1,
sum(miles*(1-abs(sign(week-2)))) as week2,

SELECT wc1.team_name AS team_name,
     (SELECT SUM(wc2.miles) FROM miles wc2 WHERE wc2.team_name = wc1.team_name) AS total_miles

FROM miles wc1
GROUP BY wc1.team_name
ORDER BY total_miles";


$result=mysql_query($qry);
?>

<table width="100%" border="1" cellspacing="1" cellpadding="1"> 
<tr> Go 100 For Health </tr>
<br>
<tr> 
  <td>Team Name</td>
  <td>Week 1</td>
  <td>Week 2</td>
  <td>Total Miles</td>

</tr>
<?php  
while ($row = mysql_fetch_array($result)) { 
echo "<TR>"; 
echo "<TD>".$row[team_name]." </TD>"; 
echo "<TD>".$row[week1]." </TD>"; 
echo "<TD>".$row[week2]." </TD>"; 
echo "<TD>".$row[total_miles]." </TD>"; 

echo "</TR>"; 
} 
echo "</TABLE>"; 
?>

 

This throws an error

 

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource

 

on this line:

 

while ($row = mysql_fetch_array($result)) {

 

 

Even removing the second part of the query (Ken2K7's part) threw the same error.

 

Any ideas?

 

Thanks!

[roopurt18]

<?php
$qry="select *,
sum(miles*(1-abs(sign(week-1)))) as week1,
sum(miles*(1-abs(sign(week-2)))) as week2

FROM miles wc1
GROUP BY wc1.team_name
ORDER BY total_miles";


$result=mysql_query($qry);
if( !$result ) {
  echo mysql_error() . '<br />';
}
?>

<table width="100%" border="1" cellspacing="1" cellpadding="1">
<tr> Go 100 For Health </tr>
<br>
<tr>
  <td>Team Name</td>
  <td>Week 1</td>
  <td>Week 2</td>
  <td>Total Miles</td>

</tr>
<?php  
while ($row = mysql_fetch_array($result)) { 
echo "<TR>"; 
echo "<TD>".$row[team_name]." </TD>"; 
echo "<TD>".$row[week1]." </TD>"; 
echo "<TD>".$row[week2]." </TD>"; 
echo "<TD>".$row[total_miles]." </TD>"; 

echo "</TR>"; 
} 
echo "</TABLE>"; 
?>

[bschultz]

two errors...

 

Unknown column 'total_miles' in 'order clause'

 

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource

 

This:

 

<?php
$qry="select *,
sum(miles*(1-abs(sign(week-1)))) as week1,
sum(miles*(1-abs(sign(week-2)))) as week2

FROM miles 
GROUP BY team_name";
?>

gives me the weekly miles...but no total miles.  How can I incorporate a total_miles into this?

[roopurt18]

<?php
$qry="select *,
sum(miles*(1-abs(sign(week-1)))) as week1,
sum(miles*(1-abs(sign(week-2)))) as week2,
(select sum( miles ) from miles b where a.team_name=b.team_name) as total_miles
FROM miles a
GROUP BY team_name";
?>

[bschultz]

That did it...THANK YOU!

[bschultz]

Alright...I've added all the weeks into the query, and it works.  Thanks again!

 

But, I realized that I forgot a step in the query.  I need to add a second table (members table)

 

and pull a mysql_num_rows of the rows that match the team_name (members.team_name AND miles.team_name) and then divide the total miles walked per team by the total number of walkers per team.

 

On this page (http://en.wikibooks.org/wiki/MySQL/Pivot_table) I didn't see anything to join two tables... how would I go about that?

 

Thanks!

[bschultz]

This is what I've come up with so far...and it's still not working.

 

<?php
$qry="select *,
sum(miles*(1-abs(sign(week-1)))) as week1,
sum(miles*(1-abs(sign(week-2)))) as week2,
sum(miles*(1-abs(sign(week-3)))) as week3,
sum(miles*(1-abs(sign(week-4)))) as week4,
sum(miles*(1-abs(sign(week-5)))) as week5,
sum(miles*(1-abs(sign(week-6)))) as week6,
sum(miles*(1-abs(sign(week-7)))) as week7,
sum(miles*(1-abs(sign(week-))) as week8,
sum(miles*(1-abs(sign(week-9)))) as week9,
sum(miles*(1-abs(sign(week-10)))) as week10,
sum(miles*(1-abs(sign(week-11)))) as week11,
sum(miles*(1-abs(sign(week-12)))) as week12,
sum(miles*(1-abs(sign(week-13)))) as week13,

(select sum( miles ) from miles b where a.team_name=b.team_name) as total_miles 

FROM miles a 

UNION 

(SELECT team_name,COUNT(*) FROM members WHERE miles.team_name=members.team_name) as number_walkers 

FROM members

GROUP BY total_miles DESC";
?>

[roopurt18]

In set mathematics, a UNION merely combines one or more sets into a single set.

Example: (a, b, c, d) UNION ( x, y, d, z ) => (a, b, c, d, x, y, d, z)

 

UNION performs the same operation in SQL.  It combines one or more result sets into a single result set.  Each result set must have the same number of columns in the same order with the same data types or it will not work.

 

Example:

Name (varchar)      Age (int)

Larry              23

George              57

Ralph              62

 

UNION

 

Name (varchar)      Age (int)

Sally              78

Betty              57

Susan              46

Tiphany            23

 

GIVES

Name (varchar)      Age (int)

Sally              78

Betty              57

Susan              46

Tiphany            23

Larry              23

George              57

Ralph              62

 

So a UNION is not what you want to use.  Nor do you want to use a JOIN.  Instead, how about another sub-query?

 

<?php
$qry="select *,
sum(miles*(1-abs(sign(week-1)))) as week1,
sum(miles*(1-abs(sign(week-2)))) as week2,
sum(miles*(1-abs(sign(week-3)))) as week3,
sum(miles*(1-abs(sign(week-4)))) as week4,
sum(miles*(1-abs(sign(week-5)))) as week5,
sum(miles*(1-abs(sign(week-6)))) as week6,
sum(miles*(1-abs(sign(week-7)))) as week7,
sum(miles*(1-abs(sign(week-))) as week8,
sum(miles*(1-abs(sign(week-9)))) as week9,
sum(miles*(1-abs(sign(week-10)))) as week10,
sum(miles*(1-abs(sign(week-11)))) as week11,
sum(miles*(1-abs(sign(week-12)))) as week12,
sum(miles*(1-abs(sign(week-13)))) as week13,
(select sum( miles ) from miles b where a.team_name=b.team_name) as total_miles,
(select count(*) from members c where a.team_name=c.team_name) as num_team_members,
total_miles / num_team_members as avg_miles_per_team_member
FROM miles a

GROUP BY total_miles DESC";
?>

[bschultz]

Roopurt 18...I REALLY appreciate the help.

 

That threw an error...no line number

 

Unknown column 'total_miles' in 'field list'

 

I follow the logic on the select (in fact, I had tried JOIN too with temp columns c and d) and that didn't work either...but what the heck is "field list"?

 

 

[roopurt18]

Let's take a look at the relevant portions of your query for the error:

select *,

# skipping weeks1 through weeks13

(select sum( miles ) from miles b where a.team_name=b.team_name) as total_miles,
(select count(*) from members c where a.team_name=c.team_name) as num_team_members,
total_miles / num_team_members as avg_miles_per_team_member

FROM miles a
GROUP BY total_miles DESC";

 

And the error:

Unknown column 'total_miles' in 'field list'

 

'field list' is the list of columns we have chosen to select.  The columns we are selecting are * (i.e. all of the table's columns), week1 through weekN, total_miles, num_team_members, and avg_miles_per_team_member.

 

avg_miles_per_team_member is what is giving us the trouble:

total_miles / num_team_members as avg_miles_per_team_member

 

However, total_miles and num_team_members are values calculated from sub-selects!  At that point in the query it does not look as if MySQL has processed the sub-selects and assigned them to the output columns we designated (total_miles, num_team_members).

 

Try rewriting:

(select sum( miles ) from miles b where a.team_name=b.team_name) as total_miles,
(select count(*) from members c where a.team_name=c.team_name) as num_team_members,
total_miles / num_team_members as avg_miles_per_team_member

 

To:

(@tm:=(select sum( miles ) from miles b where a.team_name=b.team_name)) as total_miles,
(@ntm:=(select count(*) from members c where a.team_name=c.team_name)) as num_team_members,
@tm / @ntm as avg_miles_per_team_member

The explanation for this is found at (take note of the user's comments at the bottom):

http://dev.mysql.com/doc/refman/5.0/en/user-variables.html

 

If that doesn't work, you can re-perform the sub-queries (and MySQL may optimize them out anyways):

(select sum( miles ) from miles b where a.team_name=b.team_name) as total_miles,
(select count(*) from members c where a.team_name=c.team_name) as num_team_members,
(select sum( miles ) from miles b where a.team_name=b.team_name) / (select count(*) from members c where a.team_name=c.team_name) as avg_miles_per_team_member

[roopurt18]

Here is one more possible rewrite if MySQL supports it:

select z.*, z.total_miles / z.num_team_members as avg_miles_per_team_member
from (select *,
sum(miles*(1-abs(sign(week-1)))) as week1,
sum(miles*(1-abs(sign(week-2)))) as week2,
sum(miles*(1-abs(sign(week-3)))) as week3,
sum(miles*(1-abs(sign(week-4)))) as week4,
sum(miles*(1-abs(sign(week-5)))) as week5,
sum(miles*(1-abs(sign(week-6)))) as week6,
sum(miles*(1-abs(sign(week-7)))) as week7,
sum(miles*(1-abs(sign(week-))) as week8,
sum(miles*(1-abs(sign(week-9)))) as week9,
sum(miles*(1-abs(sign(week-10)))) as week10,
sum(miles*(1-abs(sign(week-11)))) as week11,
sum(miles*(1-abs(sign(week-12)))) as week12,
sum(miles*(1-abs(sign(week-13)))) as week13,
(select sum( miles ) from miles b where a.team_name=b.team_name) as total_miles,
(select count(*) from members c where a.team_name=c.team_name) as num_team_members
FROM miles a
GROUP BY total_miles DESC
) as z

[bschultz]

(@tm:=(select sum( miles ) from miles b where a.team_name=b.team_name)) as total_miles,
(@ntm:=(select count(*) from members c where a.team_name=c.team_name)) as num_team_members,
@tm / @ntm as avg_miles_per_team_member

 

...didn't produce an error...but also didn't divide out the per walker average

 

(select sum( miles ) from miles b where a.team_name=b.team_name) as total_miles,
(select count(*) from members c where a.team_name=c.team_name) as num_team_members,
(select sum( miles ) from miles b where a.team_name=b.team_name) / (select count(*) from members c where a.team_name=c.team_name) as avg_miles_per_team_member

 

did divide out the average...but put it in the total miles column of the html table...and nothing in the html average column

 

select z.*, z.total_miles / z.num_team_members as avg_miles_per_team_member
from (select *,
   sum(miles*(1-abs(sign(week-1)))) as week1,
   sum(miles*(1-abs(sign(week-2)))) as week2,
   sum(miles*(1-abs(sign(week-3)))) as week3,
   sum(miles*(1-abs(sign(week-4)))) as week4,
   sum(miles*(1-abs(sign(week-5)))) as week5,
   sum(miles*(1-abs(sign(week-6)))) as week6,
   sum(miles*(1-abs(sign(week-7)))) as week7,
   sum(miles*(1-abs(sign(week-))) as week8,
   sum(miles*(1-abs(sign(week-9)))) as week9,
   sum(miles*(1-abs(sign(week-10)))) as week10,
   sum(miles*(1-abs(sign(week-11)))) as week11,
   sum(miles*(1-abs(sign(week-12)))) as week12,
   sum(miles*(1-abs(sign(week-13)))) as week13,
   (select sum( miles ) from miles b where a.team_name=b.team_name) as total_miles,
   (select count(*) from members c where a.team_name=c.team_name) as num_team_members
   FROM miles a
   GROUP BY total_miles DESC
) as z

 

did the same thing as the middle piece of code did.

 

I have to admit...this is WAY over my head.  I'm only able to copy and paste at this point.  I do appreciate the help...A LOT!

 

 

[bschultz]

I don't have a clue what I just did...but it's working!!!!!!

 

<?php
require_once('config.php');

	$link = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);
if(!$link) {
	die('Failed to connect to server: ' . mysql_error());
}

$db = mysql_select_db(DB_DATABASE);
if(!$db) {
	die("Unable to select database");
}


$qry="select *,
sum(miles*(1-abs(sign(week-1)))) as week1,
sum(miles*(1-abs(sign(week-2)))) as week2,
sum(miles*(1-abs(sign(week-3)))) as week3,
sum(miles*(1-abs(sign(week-4)))) as week4,
sum(miles*(1-abs(sign(week-5)))) as week5,
sum(miles*(1-abs(sign(week-6)))) as week6,
sum(miles*(1-abs(sign(week-7)))) as week7,
sum(miles*(1-abs(sign(week-))) as week8,
sum(miles*(1-abs(sign(week-9)))) as week9,
sum(miles*(1-abs(sign(week-10)))) as week10,
sum(miles*(1-abs(sign(week-11)))) as week11,
sum(miles*(1-abs(sign(week-12)))) as week12,
sum(miles*(1-abs(sign(week-13)))) as week13,

(select sum( miles ) from miles b where a.team_name=b.team_name) as total_miles,
(select count(*) from members c where a.team_name=c.team_name) as num_team_members,
(select sum( miles ) from miles b where a.team_name=b.team_name) / (select count(*) from members c where a.team_name=c.team_name) as avg_miles_per_team_member

FROM miles a
GROUP BY avg_miles_per_team_member DESC";

$result=mysql_query($qry);
if( !$result ) {
  echo mysql_error() . '<br />';
}
?>


<table width="100%" border="1" cellspacing="1" cellpadding="1">
<tr> Go 100 For Health </tr>
<br /><br />
<tr>
  <td><div align="center">Team Name</div></td>
  <td><div align="center">Wk<br /> 1</div></td>
  <td><div align="center">Wk<br /> 2</div></td>
    <td><div align="center">Wk<br /> 3</div></td>
  <td><div align="center">Wk<br /> 4</div></td>
    <td><div align="center">Wk<br /> 5</div></td>
  <td><div align="center">Wk<br /> 6</div></td>
    <td><div align="center">Wk<br /> 7</div></td>
  <td><div align="center">Wk<br /> 8</div></td>
    <td><div align="center">Wk<br /> 9</div></td>
  <td><div align="center">Wk<br /> 10</div></td>
    <td><div align="center">Wk<br /> 11</div></td>
  <td><div align="center">Wk<br /> 12</div></td>
  <td><div align="center">Wk<br /> 13</div></td>
    <td><div align="center">Total <br />Miles</div></td>
    <td><div align="center">Avg Per <br />Walker</div></td>
</tr>
<?php  
while ($row = mysql_fetch_array($result)) { 
echo "<TR>"; 
echo "<TD>".$row[team_name]." </TD>"; 
echo "<TD><div align='center'>".$row[week1]." </div></TD>"; 
echo "<TD><div align='center'>".$row[week2]." </div></TD>"; 
echo "<TD><div align='center'>".$row[week3]." </div></TD>"; 
echo "<TD><div align='center'>".$row[week4]." </div></TD>"; 
echo "<TD><div align='center'>".$row[week5]." </div></TD>"; 
echo "<TD><div align='center'>".$row[week6]." </div></TD>"; 
echo "<TD><div align='center'>".$row[week7]." </div></TD>"; 
echo "<TD><div align='center'>".$row[week8]." </div></TD>"; 
echo "<TD><div align='center'>".$row[week9]." </div></TD>"; 
echo "<TD><div align='center'>".$row[week10]." </div></TD>"; 
echo "<TD><div align='center'>".$row[week11]." </div></TD>"; 
echo "<TD><div align='center'>".$row[week12]." </div></TD>"; 
echo "<TD><div align='center'>".$row[week13]." </div></TD>"; 
echo "<TD><div align='center'>".$row[total_miles]." </div></TD>"; 
echo "<TD><div align='center'><strong>".round($row[avg_miles_per_team_member],3)." </strong></div></TD>";


echo "</TR>"; 
} 
echo "</TABLE>"; 
?>

 

roopurt18...thank you A LOT! 

 

I've learned alot about what is possible with MYSQL and PHP...still amazes me what these can do if you know what you're doing.  I'm just a lowly radio announcer who knows a little (and I stress, A LITTLE) about PHP and MYSQL.  Thank you for helping me with this!

[roopurt18]

No problem.