sql distanct and group by in table

[hyster]

 I have a database with 2 columns, player - file

PLAYER    -  FILE
hyster - ussr-Object263.png 
hyster - ussr-Object268.png 
merc   - germany-E-100.png 
merc   - germany-JagdPz_E100.png 

what I want to do is create a horizontal table like this

<table>
<tr>
            <td>hyster</td><td> ussr-Object263.png 
                                ussr-Object268.png </td>
  </tr>
  <tr> 
<td>merc </td><td> germany-E-100.png germany-JagdPz_E100.png</td> 
</tr> 
</table>

the file part is going to be an image,

I can do it vertical by running 2 query's but I will be getting up to 30 names so to many for a vertical system.

 

its the method im stuck on so any pointers in the way to do this please ??

 

all the code I have so far.

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "test";
 
 // Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
 } 

$sql = "SELECT * FROM players GROUP BY player,file";
$result = $conn->query($sql);


if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        echo '' . $row["player"]. '<IMG src="test_files/' . $row["file"]. '"><br>';
    }
} else {
    echo "0 results";
}
 $conn->close();
?>

[ginerjm]

Your use of the terms 'horizontal table' and vertical table confuse me. What I see is that you want to place all images belonging to a 'player' in one row next to the players name/id/?. That is apparent and easily doable with one query.

 

Your use of group by in your query is probably not right. IMO you simply need an order by since you are not using any summary operators in your query that you need to use a group by clause.

 

The tricky part of your output is going to be building the row but not finishing it until you encounter a record with a new player value. At that point you close the last td element and the row and begin a new row, output the player and then begin the td element for the file and any ensuing files in the following records. Simply requires that you retain a 'last_player' value and check it with each new record.

[Barand]

An alternative approach is

SELECT player
    , GROUP_CONCAT(file) as files
FROM players
GROUP BY player

which wiil give you

+----------+--------------------------------------------+
| player   |  files                                     |
+----------+--------------------------------------------+
| hyster   | ussr-Object263.png,ussr-Object268.png      |
| merc     | germany-E-100.png,germany-JagdPz_E100.png  |
+----------+--------------------------------------------+

You can then explode the files column in the results on the comma

[hyster]

thanks barand, exactly what im after

 

im now stuck on looping the $file, if I put a foreach loop in it only loops for the player column

 

I can not figure out how to replace $ar[1] so it auto loops the file portion

while($row = $result->fetch_assoc()) {
    
   $str=$row["file"];
   $ar=explode(",",$str);

        echo  $row["player"]. '<IMG src="test_files/' . $ar[1]. '"><br>';

    }
}

[jcbones]

You could use a foreach:

 

foreach($ar as $file) {
 echo '<img src="test_files/' . $file . '" /><br />';
}

[hyster]

thanks guys, got it sorted