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

Recommended Answers

All 10 Replies

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?

Member Avatar for 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.

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?

Member Avatar for diafol

Good spot EF:

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

Thankyou for that useful trick :)

Member Avatar for 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.

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

Member Avatar for 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.

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.