PHP or SQL, i dont no where to start

[hyster]

im trying to create a html table from 2 SQL tables.

the result will be like this page http://bad.x10.mx/cw/view.php but with the columns and rows reversed

 

the tables are

GARAGE -> nickname - tank_id(array of values)

TANK_LIST -> tank_id(single entry)

 

GARAGE - tank_id only holds X amount of tanks not all in TANK_LIST - tank_id

 

creating the table I don't no how to display a given tank in the correct position in the players column as the GARAGE don't hold every tank,

would it be possible to do it purely in SQL or do I have to use php or both :/

 

atm the only way I can think of doing this is a query for each td cell but since there are 451 TANK_LIST rows and id be searching for 30 players that's a massive amount of query's.

 

I hope I made sense as im confusing myself just trying to explain what im after

[mac_gyver]

your database query would retrieve the data you want, in the order that you want it. you would store the data from the query into an array, then extract the heading information from the array of data. then just loop over the data and output it the way you want it. see the following post for an example of doing this - http://forums.phpfreaks.com/topic/298003-data-display-in-wrong-column/?do=findComment&comment=1520525

[Jacques1]

This can be done with a single query if you fix your data model. Fields in an SQL table are not supposed to hold “arrays”, comma-separated values or anything like that. One field is for one value.

 

If you need a many-to-many relationship between players and tanks, you create a third table which assigns player IDs to tank IDs. So your three tables should look like this:

player
- player_id
- name 
- ...

tanks
- tank_id
- name
- ...

player_tanks
- player_id
- tank_id
- ...

Now your data actually becomes accessible to your database system. To get a full matrix of all player/tank combinations, you use a CROSS JOIN between tanks and players (the cartesian product of both tables). Then you do a LEFT JOIN between the resulting table and player_tanks to figure out which combinations are actually in use.

 

The tables and test data:

CREATE TABLE players (
  player_id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE tanks (
  tank_id INT AUTO_INCREMENT PRIMARY KEY,
  tank_name VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE player_tanks (
  player_id INT NOT NULL,
  tank_id INT NOT NULL,
  PRIMARY KEY (player_id, tank_id),
  FOREIGN KEY (player_id) REFERENCES players (player_id),
  FOREIGN KEY (tank_id) REFERENCES tanks (tank_id)
);

-- test data

INSERT INTO players (username)
VALUES
  ('Player A'),
  ('Player B'),
  ('Player C')
;

INSERT INTO tanks (tank_name)
VALUES
  ('Tank A'),
  ('Tank B'),
  ('Tank C')
;

INSERT INTO player_tanks (player_id, tank_id)
VALUES
  (1, 2),
  (1, 3),
  (2, 1)
;

The query:

SELECT
  tanks.tank_id,
  tanks.tank_name,
  players.player_id,
  players.username,
  player_tanks.player_id IS NOT NULL AS combination_exists
FROM
  players
  CROSS JOIN tanks
  LEFT JOIN player_tanks ON players.player_id = player_tanks.player_id AND tanks.tank_id = player_tanks.tank_id
ORDER BY
  tanks.tank_name ASC, players.username ASC
;

Turning this into an HTML table is pretty straightforward: You iterate over the SQL rows, emit a table cell for each one, and whenever you encounter a new tank, you start a new HTML row.

Edited September 24, 2015 by Jacques1

[hyster]

thanks jacques1, im nearly there. I re-designed the database as u said.

I made the 1st row from another query using distinct to get the players name for the columns.

 

do I have to create a new query to populate the 1st td stating what the tank is ??

I cant work out how to start a new row when I new tank is outputted instead of a record row

<table border="1">
<?php

// populate 1st header row
echo "<tr><td>Tank List</td>";
$sql1 = "SELECT DISTINCT nickname FROM player_list  ";
$result1 = $conn->query($sql1);
if ($result1->num_rows > 0) {
    // output data of each row
    while($row1 = $result1->fetch_assoc()) {

        echo  "<td>".$row1["nickname"]."</td>";
    }
}
echo "</tr>";


//populate data rows
$sql = "SELECT 
    tank_list.name_i18n, 
    tank_list.tank_id, 
    tank_list.name, 
    player_list.account_id, 
    player_list.nickname, 
    garage_list.tank_id IS NOT NULL AS combination_exists 
FROM 
    player_list 
    CROSS JOIN tank_list 
    LEFT JOIN garage_list ON player_list.account_id = garage_list.account_id AND tank_list.tank_id =        garage_list.tank_id where tank_list.level='10' 
ORDER BY 
  tank_list.name ASC, 
  tank_list.nation ASC, 
  player_list.nickname ASC ";


$result = $conn->query($sql);
if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {

        echo  "<tr><td>".$row["name_i18n"]."</td><td> ".$row["combination_exists"]."</td></tr>";
    }
} else {
    echo "0 results";
}
 $conn->close();
 ?> 
</table>

//current  output

<TABLE border="1"> 
  <TBODY>
  <TR>
    <TD>Tank list</TD>
    <TD>beasthr</TD>
    <TD>Georgieboii</TD>
    <TD>Fluffy_Bad_Squirrel</TD>
    <TD>Angry_Hamster</TD>
    <TD>Olddevil333</TD>
    <TD>Hyster</TD></TR>
  <TR>
    </TR>
  <TR>
    </TR>
  <TR>
    <TD>Bat.-Châtillon 25 t</TD>
    <TD> 1</TD></TR>
  <TR>
    <TD>Bat.-Châtillon 25 t</TD>
    <TD> 1</TD></TR>
  <TR>
    <TD>Bat.-Châtillon 25 t</TD>
    <TD> 0</TD></TR>
  <TR>
    <TD>Bat.-Châtillon 25 t</TD>
    <TD> 0</TD></TR>
  <TR>
    <TR>
    <TD>Bat.-Châtillon 25 t</TD>
    <TD> 0</TD></TR>
  <TR>
    <TD>Bat.-Châtillon 25 t</TD>
    <TD> 0</TD></TR>

  <TR>
    <TD>FV215b</TD>
    <TD> 0</TD></TR>
  <TR>
    <TD>FV215b</TD>
    <TD> 0</TD></TR>
  <TR>
    <TD>FV215b</TD>
    <TD> 0</TD></TR>
  <TR>
    <TD>FV215b</TD>
    <TD> 1</TD></TR>
  <TR>
    <TD>FV215b</TD>
    <TD> 0</TD></TR>
  <TR>
    <TD>FV215b</TD>
    <TD> 0</TD></TR>
  <TR>
    </TR>
  <TR>
    <TD>FV215b (183)</TD>
    <TD> 0</TD></TR>
  <TR>
    <TD>FV215b (183)</TD>
    <TD> 0</TD></TR>
  <TR>
    <TD>FV215b (183)</TD>
    <TD> 0</TD></TR>
  <TR>
    <TD>FV215b (183)</TD>
    <TD> 0</TD></TR>
  <TR>
    <TD>FV215b (183)</TD>
    <TD> 1</TD></TR>
  <TR>
    <TD>FV215b (183)</TD>
    <TD> 0</TD></TR>
  <TR>
    </TR>
  <TR>
    <TD>E 100</TD>
    <TD> 1</TD></TR>
  <TR>
    <TD>E 100</TD>
    <TD> 0</TD></TR>
  <TR>
    <TD>E 100</TD>
    <TD> 0</TD></TR>
  <TR>
    <TD>E 100</TD>
    <TD> 0</TD></TR>
  <TR>
    <TD>E 100</TD>
    <TD> 1</TD></TR>
  <TR>
    <TD>E 100</TD>
    <TD> 0</TD></TR>
  <TR>
    </TR>
  <TR>
    </TR></TBODY></TABLE>

Edited September 25, 2015 by hyster

[Jacques1]

You only need a single query to get all information. The one I posted contains a complete matrix of all players, all tanks and whether a particular player has a particular tank.

 

To make life easier, you may want to put the result into a two-dimensional array before doing the output, as suggested by mac_gyver.

$tankPlayersQuery = $databaseConnection->query('
    SELECT
      tanks.tank_id,
      tanks.tank_name,
      players.player_id,
      players.username,
      player_tanks.player_id IS NOT NULL AS combination_exists
    FROM
      players
      CROSS JOIN tanks
      LEFT JOIN player_tanks ON players.player_id = player_tanks.player_id AND tanks.tank_id = player_tanks.tank_id
    ORDER BY
      tanks.tank_name ASC, players.username ASC
');
$tankPlayersResult = $tankPlayersQuery->fetchAll();

$tankPlayers = [];
foreach ($tankPlayersResult as $row)
{
    $tankPlayers[$row['tank_name']][$row['username']] = ($row['combination_exists'] == 1);
}

To get all players for the table heading, you just have to pick a row from the two-dimensional array and get its keys:

$firstTank = reset($tankPlayers);
$players = array_keys($firstTank);

var_dump($players);