Loop help

[JayJ]

I have 3 tables that I wish to produce but I just cannot get my head around the logic for producing them. I'll try and explain the best I can.

I have one table and the contents are as follows

team |  group

A    | 1
B    | 1
C    | 1
D    | 2
E    | 2
F    | 3

Basically what I want to do is have 3 tables based on "position" - so splitting those up with 1 in to 1 table, those with 2 in to 2 etc:

e.g.

Table 1:     Table 2:       Table 3:
A             D               F
B             E
C

I know how I can do it roughly if I do seperate queries "where group='1'" but what I'm trying to do is look at a table to see how many groups I have (at this stage 3, but eventually there may be 4) and spit out that number of tables.

From there I want to print Table 1, then Table 2, then Table 3 (and if there's a 4th group, Table 4) with each of the teams listed below their respective Group

I assume that I must use some sort of loop, but I just cannot get my head around how to do that

Any advice?

JJ

[EvolutionFallen]

You could run this query first to get how many tables you'd need:

SELECT COUNT(DISTINCT group) FROM my_table;

[JayJ]

Thanks Evolution

So now that I have say 3 tables, how can I then populate table 1 with all the data where group = 1, table 2 group 2, table 3 group 3.

The long way that I can think is just by hardcoding them and using if + else based on th numcount to show the tables. Must be an easier way though?

[diafol]

use GROUP_CONCAT:

SELECT `group`, GROUP_CONCAT(team SEPARATOR ',') AS teams ORDER BY `group`

This will give data as:

1 A,B,C
2 D,E
3 F

You can replace the separator (,) in the teams field into rows

$tables = "";
while(...){
    $tables .= "<table><tr><td> . str_replace(",","</td><td>", $data['teams']) . "</td></tr></table>";
}
echo $tables;

That's my favourite.

[EvolutionFallen]

Nice, diafol! I didn't know about group_concat() (or maybe I forgot it, who knows...).
I think you're missing a " after the <td>, no?

[diafol]

Good spot EF:

$tables .= "<table><tr><td>" . str_replace(",","</td><td>", $data['teams']) . "</td></tr></table>";

[JayJ]

Thankyou for that useful trick :)

[diafol]

Mark thread solved (link button below) if it is.

BTW - the separator ',' - may be better to use something else as ',' may form part of a team name, if you have real names that is as opposed to just letters.

[JayJ]

I plan to use | as the seperator

Now to figure out how to do the loop for each table. I assume that the best way is using an if statement or a while statement?

e.g.

$whichteam = mysql_query("SELECT * FROM team WHERE team_name = '.$teamgroup.'");

Would that work in a loop at all? Not sure where I should be starting with this bit to loop each group

Thanks for the help so far - that's why I've been a long-term lurker here :)

[diafol]

I already provided you with the loop structure:

$result = mysql_query("SELECT `group`, GROUP_CONCAT(team SEPARATOR '|') AS teams ORDER BY `group`");
$tables = "";
if($result){
    while($data = mysql_fetch_assoc($result)){
        $tables .= "<table><tr><td>" . str_replace("|","</td><td>", $data['teams']) . "</td></tr></table>";
    }
}
echo $tables;

Try that. For future work avoid mysql_*, use mysqli_* or PDO.

[JayJ]

Thankyou! I think I have it heading the right way now :)