count in a while loop to last td in row

[hyster]

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

[mac_gyver]

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

[mac_gyver]

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

[hyster]

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.

[hyster]

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

[seandisanti]

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

[seandisanti]

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)

[mac_gyver]

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.

[mac_gyver]

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.

[hyster]

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>