How to loop through result in a column and return in a table?

[Dragazarth]

Im new to php so I decided to make site to test what i have learnt so far, nothing fancy just a test of php skills..

So I created a MySQL database (also new to), with a schema called items, a table called things, with 10 columns, idthings being the identifyer and then the other columns where just tf2 classes.
I wanted users to sign up (done that) and imput what masks they have for an event, therre is one mask for each class (in other words ten different masks, but you can get mutiples of each mask. so i created an imput page so the data would be put into a database with idthing equal to their name and then an integer depending on how many of the masks/hats they had.

Now ive got a group that distributes the hats evenly through the group, with everyone getting one hat.

SO i did this:

$resultgroup = mysql_query("SELECT SUM(*) FROM '$class'.things");

Where class is supposed to cycle through the 9 different classes and so $resultgroup would be an array of 9 numbers each number the sum of how many of each hat/masks that all the users had.
I can't work out how to make $class cycle through the 9 different columns which are named after each class.

Im rather confused myself as to what im trying to do!

Hopefully this post isnt so long that everybody gets bored and stop reading! (if youve made it this far well done!
This is part of my page but not the whole thing , i think it is pretty self contained though

<?php

echo "<h3>What is required:</h3>";

$con = mysql_connect("localhost","tf2","password");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

  

mysql_select_db("items", $con);
$needed=mysql_query("SELECT COUNT(idthing) FROM things");
$class='...................';
$resultgroup = mysql_query("SELECT SUM(*) FROM \"$class\".things");



echo "
<tr>
<td>Scout?</td> //possibly i could use a loop here to just cycle through each of the 
<td>Soldier?</td>                                //classes names                           
<td>Pyro?</td>
<td>Demoman?</td>
<td>Heavy?</td>
<td>Engineer?</td>
<td>Medic?</td>
<td>Sniper?</td>
<td>Spy?</td>
</tr>";

while($col = mysql_fetch_array($resultgroup))
	{ 
	echo "<td>" . $col[$class] . "</td>";
	}
echo "</tr>

THanks in advance Dragazarth

[diafol]

Sorry, I'm totally befuddled as to what you need. Even more confused wrt table and how it's used.

[Dragazarth]

okay ill try to clear it up...

each user signs up and puts in the number of type of of hat/mask that they have (there are 10 choose from

If all the users are added together i need to get all the different hats for each user, so ten for each user 1 in each catogory.

I want to display in the table the amount of users minus the number of that type of hat that i have, so if some users have 1 of that hat but others dont we can see in the table that theres 4 more of that type needed

If your still with me I need that to be shown for each of the 9 different classes which is where the loop is meant to help

Hopefully this makes some type of sense

[diafol]

Ok -
1: count no. of users using COUNT() and store as a php variable.
2. sum each individual field and take out the number of users.

I hope that's right.

//$num_users is number of users obviously.

$rs = mysql_query("SELECT SUM(field1), SUM(field2), SUM(field3), SUM(field4) ... FROM table");
$fieldnames = array("hat1","hat2","mask2"...);
$i = 0;
$row = mysql_fetch_array($rs);

foreach($row AS $rowitem){
 $rowitem = $rowitem - $num_users;
 echo "<td>{$fieldnames[$i]}? $rowitem</td>";
 $i++;
} 

//NOT TESTED OR ANY DB ERROR HANDLING!

[Dragazarth]

Sorry for not responding instantly..

If I show you what my SQL table looks like maybe it'll make more sense..

IDthings  |    scout     |    Soldier    |    demo    |   //and some more
________________________________________________________...
User1     |     2        |       0        |     1
me        |     0        |       0        |     1
you       |     1        |       1        |     0

So its something like that (hopfully indents will stay) but with a few more "classes" at the top (the tf2 classes)

Since I have 3 users I need 3 of each hat (the numbers represent the amount of hats/mask for each different type of class aka number of different hats)

So I want to

mysql_query("SELECT COUNT(idthing) FROM table");

and then make a table in html that Looks like this:

NEEDED:
Scout     |    Soldier    |    demo    |   //and some more
________________________________________...
0         |       2       |     1

But I want it so it automatically cycles through each class with a php/sql loop/code of some description

Hopefully this is clearer!

[diafol]

That's what I tried to provide - did you try it?

[Dragazarth]

sorry i misread a line last post and I was confused...

Anyhow I tried what you did but it ended up giving me a whole lot of -4

i tried this:

$con = mysql_connect("localhost","tf2","password");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

  
mysql_select_db("items", $con);

$num_users = mysql_query("SELECT COUNT(idthing) FROM things");
$rs = mysql_query("SELECT SUM('scout'), SUM('soldier'), SUM('pyro'), SUM('demoman'), SUM('heavy'), SUM('engineer'), SUM('medic'), SUM('sniper'), SUM('spy') FROM things");
$fieldnames = array("scout","soldier","pyro","demoman","heavy","engineer","medic","sniper","spy");
$i = 0;
$row = mysql_fetch_array($rs);

foreach($row AS $rowitem)
	{
	$rowitem = $rowitem - $num_users;
	echo "<td>{$fieldnames[$i]}? $rowitem</td>";
	$i++;
	}

and it gave me this

scout? -4soldier? -4pyro? -4demoman? -4heavy? -4engineer? -4medic? -4sniper? -4spy? -4? -4? -4? -4? -4? -4? -4? -4? -4? -4

im not sure where the -4s are coming from!

Any suggestions? Or did i put something strange in, or is it my DB?

thanks

[diafol]

mysql_query("SELECT SUM('scout'), SUM('soldier'), SUM('pyro'), SUM('demoman'), SUM('heavy'), SUM('engineer'), SUM('medic'), SUM('sniper'), SUM('spy') FROM things");

try:

mysql_query("SELECT SUM(scout), SUM(soldier), SUM(pyro) ...);