hyster Posted September 23, 2015 Share Posted September 23, 2015 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 Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted September 24, 2015 Share Posted September 24, 2015 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 Quote Link to comment Share on other sites More sharing options...
Solution Jacques1 Posted September 24, 2015 Solution Share Posted September 24, 2015 (edited) 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 Quote Link to comment Share on other sites More sharing options...
hyster Posted September 25, 2015 Author Share Posted September 25, 2015 (edited) 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 Quote Link to comment Share on other sites More sharing options...
Jacques1 Posted September 25, 2015 Share Posted September 25, 2015 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); Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.