0

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

3
Contributors
10
Replies
11
Views
4 Years
Discussion Span
Last Post by JayJ
Featured Replies
  • You could run this query first to get how many tables you'd need: SELECT COUNT(DISTINCT group) FROM my_table; Read More

  • 1
    diafol 3,547   4 Years Ago

    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; … Read More

  • 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? Read More

  • 1
    diafol 3,547   4 Years Ago

    Good spot EF: $tables .= "<table><tr><td>" . str_replace(",","</td><td>", $data['teams']) . "</td></tr></table>"; Read More

  • 2
    diafol 3,547   4 Years Ago

    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. Read More

1

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

SELECT COUNT(DISTINCT group) FROM my_table;
0

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?

1

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.

Edited by diafol

2

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?

1

Good spot EF:

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

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.

0

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

2

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.

0

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

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.