1.11M Members

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

JJ

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

``````SELECT COUNT(DISTINCT group) FROM my_table;
``````

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?

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.

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?

Good spot EF:

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

Thankyou for that useful trick :)

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.

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 :)

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.

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

Question Answered as of 1 Year Ago by diafol and EvolutionFallen
You