hyster Posted October 8, 2015 Share Posted October 8, 2015 (edited) I have some code that works other the last TD in the table is not displaying. the last td in the row does a count of occurrences in the row of $row["combination_exists"]' I can not work out where to put the $count for the last row - TD. thanks $sql = "SELECT tank_list.name_i18n, tank_list.tank_id, tank_list.name, tank_list.short_name_i18n, player_list.account_id, player_list.nickname, player_list.clan, 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 = '$tier' AND player_list.clan = '$clanname' ORDER BY tank_list.type ASC, tank_list.name ASC, tank_list.nation ASC, player_list.nickname ASC "; $result = $conn->query($sql); $lastval = 0; $count = 0; if (@$result->num_rows > 0) { // output data of each row while($row = $result->fetch_assoc()) { // 1st td - 1st row if ($lastval == "0"){ $lastval = $row["name_i18n"]; echo "<tr><td width='167'>".$row["short_name_i18n"]."</td>"; } // rest of td 1st td if ($row["name_i18n"] != $lastval){ //all other rows $lastval = $row["name_i18n"]; //works for all but last row - last td if ($row["name_i18n"] != $lastval){ $lastval = $row["name_i18n"]; //echo "<td>". $count ."</td></tr>"; $count = 0; }else{ echo "<td>". $count ."</td></tr>"; $count = 0; } echo "</tr><td width='167'>".$row["short_name_i18n"]."</td>"; } // status of tank -- fills out a table row if ($row["name_i18n"] != $lastval){ $lastval = $row["name_i18n"]; echo "<td><img src='./img/".$row["combination_exists"].".png' height='20' width='20' title=".$row["nickname"]."></td>"; if ($row["combination_exists"] == '1'){ $count++; } }else{ echo "<td><img src='./img/".$row["combination_exists"].".png' height='20' width='20' title=".$row["nickname"]."></td>"; if ($row["combination_exists"] == '1'){ $count++; } } } } else { echo "Please selct again, no players found matching your search"; } if (@$row["combination_exists"] == '1'){ @$count++; } $conn->close(); ?> </table> Edited October 8, 2015 by Ch0cu3r Corrected topic title Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted October 8, 2015 Share Posted October 8, 2015 (edited) you may want to revisit the last post in your previous thread where that sql query was developed - 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. ^^^ doing what was shown in the code in that last post to store the data into an array will greatly simplify the code that outputs the table. you will just need to use a foreach loop to loop over the main array. the key is the tank name, the value is an array of data for the row. then just loop over the data for each row with another foreach loop. edit: to get the sum of the not null values, you can use php's array_sum() on the array of data. you should also never use the @ error suppressor in your code. if you were getting errors from those statements, you would want to know so that you could correct the problem that was causing them. Edited October 8, 2015 by mac_gyver Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted October 8, 2015 Share Posted October 8, 2015 starting with the line that runs your sql query, the following is all that you need (untested) to produce the result - $result = $conn->query($sql); // run the query $rows = $result->fetch_all(MYSQLI_ASSOC); $data = array(); foreach ($rows as $row) { $data[$row['short_name_i18n']][$row['nickname']] = ($row['combination_exists'] == 1); } if(empty($data)){ echo "Please select again, no players found matching your search"; } else { foreach($data as $tank_name=>$arr){ echo "<tr><td width='167'>".$tank_name."</td>"; // tank name as row label foreach($arr as $nickname=>$element){ // output the data for each cell - you apparently have two images 0.png and 1.png echo "<td><img src='./img/{$element}.png' height='20' width='20' title='$nickname'></td>"; } echo "<td>". array_sum($arr) ."</td></tr>"; } } ?> </table> another recommendation - only SELECT the columns in your sql statement that you are using the data from. based on what you have shown in this thread, the select part of the query only needs to be - SELECT tank_list.short_name_i18n, player_list.nickname, garage_list.tank_id IS NOT NULL AS combination_exists Quote Link to comment Share on other sites More sharing options...
hyster Posted October 8, 2015 Author Share Posted October 8, 2015 massive thanks mac_giver (along with barand I owe u a few beers) im in way over my head with what im doing atm (I didn't see the last post on the previous post or I would have tried that). I can only learn buy messing with code till I get it doing what I need, I can not follow most tutorials as I find them bloated and I lose the tree's in the forest. the SQL query is more than what I need as I was experimenting with adding additional data. I did have 2 images but was told it made it harder to read so I just used the 1.png. as for the @ I only use them as a short cut, I would fix the errors once I had the code working, I know its bad practice but to me its the easiest way. Quote Link to comment Share on other sites More sharing options...
hyster Posted October 10, 2015 Author Share Posted October 10, 2015 (edited) sorry but I have been fiddling again and got stuck with the styling using vars for colours for the CSS:/ the data matches tank_list :: name - type so I thought it be as simple as adding $row['type'] to create the div id for the css in the foreach but its only echoing the last entry not looping as I thought it would. I did as suggested and created the whole table from the single query this time if(empty($data)){ echo "Please select again, no players found matching your search"; } else { $firstTank = reset($data); $players = array_keys($firstTank); $array = array_keys($firstTank); $count = count($array); echo "<tr><th></th>"; for ($i = 0; $i < $count; $i++) { echo "<th><img src='./img/name/vrt".$array[$i].".png' title=".$array[$i]."></th>"; //echo "<td>".$array[$i] . "\n</td>"; } echo "</tr></thead></div><tbody>"; foreach($data as $tank_name=>$arr){ echo "<tr><td width='167'><div id='".$row['type']."'>".$tank_name . "</div><div id='right'>" . array_sum($arr)."</div></td>"; // tank name as row label foreach($arr as $nickname=>$element){ if ($element == 1){ echo "<td><img src='./img/{$element}.png' height='20' width='20' title='$nickname'></td>"; }else{ echo "<td></td>"; } } //echo "<td>". array_sum($arr) ."</td></tr>"; } } Edited October 10, 2015 by hyster Quote Link to comment Share on other sites More sharing options...
seandisanti Posted October 10, 2015 Share Posted October 10, 2015 (edited) a few things... 1) you're hardcoding 'right' as the div id for each pass through the loop, so using the same id for multiple div's. That's a bad idea, and can result in unpredictable behavior at best 2) Inline styling, even when automated, should be avoided. That goes exponentially for every duplicated style you're adding 3) reverse your single and double quote usage habits and you'll save yourself a ton of headaches. always use double quotes for parameter and attribute values in HTML, and use single quotes for php strings, and concatenate your variables in. That way you avoid using the literal string '$nickname' as the title for example. echo '<td><img src="./img/' . $element . '.png' height="20" width="20" title="' . $nickname . '"></td>'; //and seriously move that styling into a stylesheet or <style> block outside of your loop Edited October 10, 2015 by seandisanti Quote Link to comment Share on other sites More sharing options...
seandisanti Posted October 10, 2015 Share Posted October 10, 2015 also don't add comments on same line as code except for comedic effect because it make the line grossly long and makes code tougher to read, but comments easier to miss (talking about what i did in example to be funny, nothing you did) Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted October 10, 2015 Share Posted October 10, 2015 your output is probably only showing one line because the html is broken. when you moved the array_sum($arr) from the end of the row to the cell with the tank_name, you commented out the </tr> tag. if you are trying to style a set of items, you would use a css class selector, not an id selector. Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted October 10, 2015 Share Posted October 10, 2015 That way you avoid using the literal string '$nickname' as the title for example. except that php variables are parsed and replaced with their value when used inside of an overall double-quoted string or when using heredoc string syntax, resulting in a minimum of syntax/context changes to use php variables as part of a string. concatenation results in far more errors, both php syntax and html syntax (the OP currently is missing quotes around the title=... attribute value in the table header section) for those who don't clearly yet know which of the syntax is there for the php and which is there for the html/css/javascript markup. Quote Link to comment Share on other sites More sharing options...
Solution hyster Posted October 10, 2015 Author Solution Share Posted October 10, 2015 changed css to use class's css is in a block at the top, (will be moved to a css file when im done) fixed the ' and " (I hope) $row['type'] holds 1 of 5 values which im going to use to change the bgcolor final </tr> I missed putting back in as I was experimenting having it at the start and end of the row :$ $row['type'] still holding the last row value and not the row value (still too dumb to work out what im missing) applying for a bank loan to pay for the beer I owe mac_gyver <div style=' height:800px; overflow:auto;'> <table class='hovertable' cellspacing'0' cellpadding='0' border='1' > <thead> <?php $sql = "SELECT tank_list.name_i18n, tank_list.tank_id, tank_list.name, tank_list.type, tank_list.nation, tank_list.short_name_i18n, player_list.account_id, player_list.nickname, player_list.clan, 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 = '$tier' AND player_list.clan = '$clanname' ORDER BY FIELD(tank_list.type, 'lighttank', 'mediumTank', 'heavyTank', 'AT-SPG', 'SPG'), name ASC, tank_list.name ASC, tank_list.nation ASC, player_list.nickname ASC "; $result = $conn->query($sql); $rows = $result->fetch_all(MYSQLI_ASSOC); $data = array(); foreach ($rows as $row) { $data[$row['short_name_i18n']][$row['nickname']] = ($row['combination_exists'] == 1); } if(empty($data)){ echo "Please select again, no players found matching your search"; } else { $firstTank = reset($data); $players = array_keys($firstTank); $array = array_keys($firstTank); $count = count($array); echo '<tr><th width="170" ></th>'; for ($i = 0; $i < $count; $i++) { echo '<th><img src="./img/name/vrt'.$array[$i].'.png" title="'.$array[$i].'"></th>'; //echo "<td>".$array[$i] . "\n</td>"; } echo '</tr></thead></div><tbody>'; foreach($data as $tank_name=>$arr){ echo '<tr><td><div class="'.$row['type'].'">'.$tank_name. '</div><div class="right">' . array_sum($arr).'</div></td>'; // tank name as row label foreach($arr as $nickname=>$element){ // output the data for each cell if ($element == 1){ echo '<td><img src="./img/' . $element . '.png" height="20" width="20" title="' . $nickname . '"></td>'; }else{ echo "<td></td>"; } } echo '</tr>'; // may use this later //echo '<td><div class="right">'. array_sum($arr) .'</div></td></tr>'; } } ?> </tbody> </table></div> 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.