[PHP/MySQL]Live Score Board

MySQL database
#11

not the way i want … but it is displaying the results its just a formatting issue at this point and im not sure how to get it to format the way i want. you can see the DEMO

[php]<?php
$con=mysqli_connect(“host”,“user”,“pass”,“db”);
// Check connection
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$query = ‘SELECT
cp.Soldiername,
cp.Kills,
cp.Deaths,
cp.Score,
s.squadName,
t.teamName
FROM
tbl_currentplayers AS cp
LEFT JOIN tbl_teams AS t ON t.teamID = cp.TeamID
LEFT JOIN tbl_squads AS s ON s.squadID = cp.SquadID
ORDER BY
t.teamID ASC,
s.squadName ASC’;

$result = mysqli_query($con,$query);
$num_rows = $result->num_rows;
if($num_rows > 0) {
echo “<table class=“onlinePlayers”>

Player Name Team Squad K/D Stats ”;
while($row = mysqli_fetch_assoc($result)){
echo “”;
echo “” . $row[‘Soldiername’] . “”;
echo “” . $row[‘teamName’] . “”;
echo “” . $row[‘squadName’] . “”;
echo “” . $row[‘Kills’] . “/” . $row[‘Deaths’] . “”;
echo “<a href=“https://i-stats.net/index.php?action=pcheck&player=” . $row[‘Soldiername’] . “&game=BF3&sub=Check+Player” target=_blank>iStats | <a href=“http://metabans.com/search/” . $row[‘Soldiername’] . “” target=_blank>MetaBans”;
echo “”;
}
}else {
echo “<table class=“onlinePlayers”>”;
echo “<td colspan=“5”>No Players Online” ;
}
echo “<td colspan=“5”><a class=“button” href=“http://battlelog.battlefield.com/bf3/servers/show/pc/8c863f97-1369-4c4b-bf17-c369bd2adf88/Kryptic-Killers-24-7-Metro-1000tix-PBBans-PBScreens/” target=”_blank">Play Now!" ;
echo “”;
mysqli_close($con);
?> [/php]
#12

Well, the results of a query is simply an array of data. If you have the array in hand (after a query) and you
know there is data inside it, you can simple loop thru the data and make sure it breaks as needed. Just keep
a pointer to the current team and current squad. When they change, display the title for the next one. Only
display the team name and squad name once for each change… Loosely, something like:

    Teams: $current_team="xyz"; $current_squad="xyz"; while(loop thru the results...) { If $row[$team]!=$current_team { $current_team=$row[$team]; // set current team to new-current team echo "
  • " . $row["team"] . "
      • "; // close previous UL and start a new one... } If $row[$squad]!=$current_squad { $current_squad=$row[$squad]; // set current squad to new-squad team echo "
    • " . $row["squad"] . "
      • "; } }

    This is just a loose explanation, not tested code… You need to just know when to break the UL’s. Since you
    want them broken on teams, the above will work. The titles need to be the first ones after the new UL’s and
    you have to nest the UL’s for the squad members. Hope that makes sense. Once you get further, show us
    what you end up with. If you can’t get it to work, we can help further…

    #13

    i guess i am getting way ahead of my abilities, cause i am even more confused now. I realize that you are trying to help by giving the “idea” to use and letting me learn by doing but for some reason i am still lost. i really wish i could show you an example of what i am wanting to do so that you could help me understand better. … or perhaps i am over complicating all of this?? lets do this … what would the best practice way be to display the data that i want to display in an easy to read organized manner that fits in the area that it currently is in?

    #14

    draw.io

    I think in order for us to help, you need to know and be able to communicate what you want. Pictures work. The link above is what we use for diagraming layouts.

    #15

    Jay, you mentioned you have only one table and then you made two tables. We should start there. How many
    teams do you have? If only the two that you showed, then there is no need for two tables. If there are many
    such as hundreds, then you would do better with two tables. In which case, use the join to get the names from
    the indexed table of squads. Either way, you end up with a table of data that contains the team name, squad
    name and members of the squads. Loosely in the form of TEAM, SQUAD, SOLDIERS and that basically means
    that you just need to loop thru the results as I showed you and break where needed. There are many other
    ways to handle this. I found a nice recursive version, but, it keeps running queries for the members. The way
    I showed you should work. Here is a version combined with the code you posted. Without access to the DB,
    not sure if it will work, but, test it and post the results…

    First, think of the bullet code. Here is how it looks for the display using my own samples:

      team name 1
        squad name 1
      • Soldiername1
      • Soldiername2
        squad name 2
      • Soldiername3
      • Soldiername4
      team name 2
        squad name 3
      • Soldiername5
      • Soldiername6
    So, as you see, there are only two break points. First is teams and next squads. This makes sense, right? Your code would have to check for each and display it as needed. Using the correct
      ,
    • ,
      • and
    tags where they are needed. Here is a sample that might work for you. It is an improved version of the sample I posted earlier. Try it and let us know if it works or not for you... [php] <?php $result = mysqli_query($con,$query); $num_rows = $result->num_rows; if($num_rows > 0) { $current_team="xyz"; $current_squad="xyz"; while(loop thru the results...) { If($row[$team]!=$current_team) { if($current_team!="xyz") echo ""; // close the previous team group $current_team=$row[$team]; // set current team to new-current team echo "
      " . $row["team"]; // show team as top level... } If $row[$squad]!=$current_squad { if($current_squad!="xyz") echo "
    "; // close the previous squad group $current_squad=$row[$squad]; // set current squad to new-squad team echo "
      " . $row["squad"]; // show squad as sub-level... } echo "
    • " . $row["Soldiername"] . "
      • "; // show Soldiername as lowest-level... } echo "
    "; // close the entire group... } else { echo "No players online at this time!
    "; } ?> [/php] Note that the "xyz" is just a name of a team that does not exist. It is used to start the loop with to know where the code should end and display the correct ending of a group of data. This makes the end ( ) tag work. The above is not tested as no DB connection. If it does not work for you, I can create a test array to use, but it should work. It should create the needed bullet list for you. Let us know how it works for you...
    #16

    here is the diagram that i am trying to achieve…

    http://prntscr.com/a4zuyn

    The database is populated by a 3rd party program running on the game server that i cannot edit, which means that i cannot populate the 2 tables that i made with any of the data from the game server. so i am limited in the data that i can use. That is why i made 2 tables one with the correlation between the teamID and teamName and the other for the correlation between the squadID and squadName

    #17

    If you can read the data from their database, then you can create your own version of the data. But, if you can
    read their data and just want to display it, you do not need to. In that case, just use their data…

    Your diagram shows a table, not a bullet list. To create this table, it would be very simple and the easiest way is
    to just do two queries and create two nested tables. This is simple enough to do. Similar to the bullet list only
    two queries, one for each team and just a straightforward table display with headings and data cells. Is that what
    you need help with now?

    #18

    yes that has been the request from the get-go … ive never wanted to do bullet list i even stated in my replies that i wanted a table and that i was only useing the bullet list to help describe what i was looking for as an example. however i do appreciate all the suggestions as it helps me learn new and better ways of doing things, so in the future if i ever need to do a dynamic bullet list i now know how to.

    #19

    This is actually where those really irritating drawing with asterisks comes in handy, as that is kind of what you are doing.

    As Ernie said, it is just a matter of knowing when to change the tags in a loop.

    #20

    Jay, To create a table instead of bullet lists, you just have to lay out the structure first. Anyway, the layout you
    posted shows the layout. But, you should do this in HTML with just text and get it to look like you want it to be.
    If you need table help, post us your questions. Just get it to look like you want it and then, we can help you get
    the data into it. The basic way I showed you for bullet’s should work with a few changes. Some of the

      's
      will become 's and some will become 's. Not too complicated. Well, give it a try and we can help when
      you get stuck. And, sorry that I was confused on what you wanted… I was just following your example…
    #21

    OK i created the table with fake data laid out like the diagram…

    [php]

    Scoreboard Table 
      <table id="left-overall-header" width="100%" border="0">
    <tr>
      <td>&nbsp;</td>
      <td><div align="center"> 
          <!-- Start Left Side Team -->
          <table id="left-team" width="100%" border="1">
            <tr>
              <th><div align="center">US</div></th>
            </tr>
          </table>
          <!-- End Left Side Team --> 
        </div></td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td colspan="3"><div align="center"> 
          <!-- Start Left Side Data Headers -->
          <table id="left-DataHeaders" width="100%" border="1">
            <tr>
              <th><div align="center">Player</div></th>
              <th><div align="center">Squad</div></th>
              <th><div align="center">K/D</div></th>
              <th><div align="center">Score</div></th>
            </tr>
          </table>
          <!-- End Left Side Data Headers --> 
        </div></td>
    </tr>
  </table>
  <br />
  
  <!-- Start Left Side Data -->
  
  <table id="left-data" width="100%" border="1">
    <tr>
      <td><div align="center"><a href="#">Adam</a></div></td>
      <td><div align="center">Alpha</div></td>
      <td><div align="center">25/2</div></td>
      <td><div align="center">1100</div></td>
    </tr>
    <tr>
      <td><div align="center"><a href="#">Adam</a></div></td>
      <td><div align="center">Alpha</div></td>
      <td><div align="center">25/2</div></td>
      <td><div align="center">1100</div></td>
    </tr>
    <tr>
      <td><div align="center"><a href="#">Adam</a></div></td>
      <td><div align="center">Alpha</div></td>
      <td><div align="center">25/2</div></td>
      <td><div align="center">1100</div></td>
    </tr>
    <tr>
      <td><div align="center"><a href="#">Adam</a></div></td>
      <td><div align="center">Alpha</div></td>
      <td><div align="center">25/2</div></td>
      <td><div align="center">1100</div></td>
    </tr>
    <tr>
      <td><div align="center"><a href="#">Adam</a></div></td>
      <td><div align="center">Alpha</div></td>
      <td><div align="center">25/2</div></td>
      <td><div align="center">1100</div></td>
    </tr>
  </table>
  
  <!-- End Left Side Data --></td>
<!-- End Left Side --> 
<!-- Start Break between left/right -->
<td></td>
<!-- End Break between left/right -->
<td><!-- Start Right Side -->
  
  <table id="Right-overall-header" width="100%" border="0">
    <tr>
      <td>&nbsp;</td>
      <td><div align="center"> 
          <!-- Start Right Side Team -->
          <table id="right-team" width="100%" border="1">
            <tr>
              <th><div align="center">RU</div></th>
            </tr>
          </table>
          <!-- End Right Side Team --> 
        </div></td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td colspan="3"><div align="center"> 
          <!-- Start Right Side Data Headers -->
          <table id="Right-DataHeaders" width="100%" border="1">
            <tr>
              <th><div align="center">Player</div></th>
              <th><div align="center">Squad</div></th>
              <th><div align="center">K/D</div></th>
              <th><div align="center">Score</div></th>
              <th><div align="center">Stats</div></th>
            </tr>
          </table>
          <!-- End Right Side Data Headers --> 
        </div></td>
    </tr>
  </table>
  <br />
  
  <!-- Start Right Side Data -->
  
  <table id="right-data" width="100%" border="1">
    <tr>
      <td><div align="center"><a href="#">John</a></div></td>
      <td><div align="center">Alpha</div></td>
      <td><div align="center">25/2</div></td>
      <td><div align="center">1100</div></td>
    </tr>
    <tr>
      <td><div align="center"><a href="#">John</a></div></td>
      <td><div align="center">Alpha</div></td>
      <td><div align="center">25/2</div></td>
      <td><div align="center">1100</div></td>
    </tr>
    <tr>
      <td><div align="center"><a href="#">John</a></div></td>
      <td><div align="center">Alpha</div></td>
      <td><div align="center">25/2</div></td>
      <td><div align="center">1100</div></td>
    </tr>
    <tr>
      <td><div align="center"><a href="#">John</a></div></td>
      <td><div align="center">Alpha</div></td>
      <td><div align="center">25/2</div></td>
      <td><div align="center">1100</div></td>
    </tr>
    <tr>
      <td><div align="center"><a href="#">John</a></div></td>
      <td><div align="center">Alpha</div></td>
      <td><div align="center">25/2</div></td>
      <td><div align="center">1100</div></td>
    </tr>
  </table>
  
  <!-- End right Side Data --></td>
<!-- End right Side -->
    [/php]

    so using my code or some variation of my code how would i get it to display properly?

    [php]<?php
    $con=mysqli_connect(“host”,“user”,“pass”,“dbname”);
    // Check connection
    if (mysqli_connect_errno())
    {
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
    }

    $query = ‘SELECT
    cp.Soldiername,
    cp.Kills,
    cp.Deaths,
    cp.Score,
    s.squadName,
    t.teamName
    FROM
    tbl_currentplayers AS cp
    LEFT JOIN tbl_teams AS t ON t.teamID = cp.TeamID
    LEFT JOIN tbl_squads AS s ON s.squadID = cp.SquadID
    ORDER BY
    t.teamID ASC,
    s.squadName ASC’;

    $result = mysqli_query($con,$query);
    $num_rows = $result->num_rows;
    if($num_rows > 0) {
    echo “<table class=“onlinePlayers”>

    <TH colspan=“2”>US<TH colspan=“2”>RU Squad K/D Squad K/D ”;
    while($row = mysqli_fetch_array($result, MYSQLI_ASSOC);){
    echo “”;
    echo “<a href=“https://i-stats.net/index.php?action=pcheck&player=” . $row[‘Soldiername’] . “&game=BF3&sub=Check+Player” target=_blank>” . $row[‘Soldiername’] . “”;
    echo “” . $row[‘teamName’] . “”;
    echo “” . $row[‘squadName’] . “”;
    echo “” . $row[‘Kills’] . “/” . $row[‘Deaths’] . “”;
    echo “”;
    }
    }else {
    echo “<table class=“onlinePlayers”>”;
    echo “<td colspan=“5”>No Players Online” ;
    }
    echo “<td colspan=“5”><a class=“button” href=”#" target="_blank">Play Now!" ;
    echo “”;
    mysqli_close($con);
    ?> [/php]
    #22

    Well, the simplest way is to create two queries. One for the US and one for RU. Just use the where clause and
    select just the data for US and RU instead of lumping them all together. Then, in your posted code, you would
    replace lines 48-77 with a loop to display the US names. You would loop thru the soldiers in that list using your
    WHILE clause and display a line for each row. (

    data… etc… ) And, then do the same
    for the RU table. Since you already have it laid out, the table I mean, then all you need is to replace the rows in
    each of the two tables for with the live data.

    Now, you can do it with just one query if you wish, and create two WHILE loops to create the rows, but, you
    would need to check for the team (US/RU) inside the loop. Either way would work. Perhaps it might be easier
    to use just one query and the same code for each table.

    Give it a try and let us know if it works or fails. And, post the code either way in case we can help you improve
    it at all…

    #23

    ok here is my attempt at joining the table design with the php/mysql code … WARNING its ugly and probably not the most efficient way of doing it LOL. It works but the table design gets thrown off as you can see HERE … I really suck at tables and getting them adjusted right, how can i fix the layout so that the headers and data cells line up?

    [php]

    ”;
    echo “”;
    echo “”;
    echo “”;
    echo “”;
    echo “”;
    } ?>
     
    US
    		 
    Player
    Squad
    K/D
    Score

    <?php $con=mysqli_connect("host","user","pass","dbn"); if (mysqli_connect_errno()) { echo "Failed to connect to MySQL: " . mysqli_connect_error(); }

    $query = ‘SELECT
    cp.Soldiername,
    cp.Kills,
    cp.Deaths,
    s.squadName,
    t.teamName,
    cp.Score
    FROM
    tbl_currentplayers_test AS cp
    LEFT JOIN tbl_teams AS t ON t.teamID = cp.TeamID
    LEFT JOIN tbl_squads AS s ON s.squadID = cp.SquadID
    WHERE
    cp.TeamID = 1
    ORDER BY
    t.teamID ASC,
    s.squadID ASC’;

    $query2 = 'SELECT

    cp.Soldiername,
    cp.Kills,
    cp.Deaths,
    s.squadName,
    t.teamName,
    cp.Score
    FROM
    tbl_currentplayers_test AS cp
    LEFT JOIN tbl_teams AS t ON t.teamID = cp.TeamID
    LEFT JOIN tbl_squads AS s ON s.squadID = cp.SquadID
    WHERE
    cp.TeamID = 2
    ORDER BY
    t.teamID ASC,
    s.squadID ASC’;

    $result = mysqli_query($con,$query);
    while($row = mysqli_fetch_assoc($result)){
    echo “

    ”;
    echo “”;
    echo “”;
    echo “”;
    echo “”;
    echo “”;
    } ?>
    <div align=“center”><a href=”#">" . $row[‘Soldiername’] . “<div align=“center”>” . $row[‘squadName’] . “<div align=“center”>” . $row[‘Kills’] . “/” . $row[‘Deaths’] . “<div align=“center”>” . $row[‘Score’] . “
        <!-- End Left Side Data --></td>
  <!-- End Left Side --> 
  <!-- Start Break between left/right -->
  <td></td>
  <!-- End Break between left/right -->
  <td><!-- Start Right Side -->
    
    <table id="Right-overall-header" width="100%" border="0">
      <tr>
        <td>&nbsp;</td>
        <td><div align="center"> 
            <!-- Start Right Side Team -->
            <table id="right-team" width="100%" border="1">
              <tr>
                <th><div align="center">RU</div></th>
              </tr>
            </table>
            <!-- End Right Side Team --> 
          </div></td>
        <td>&nbsp;</td>
     </tr>
     <tr>
       <td colspan="3"><div align="center"> 
           <!-- Start Right Side Data Headers -->
           <table id="Right-DataHeaders" width="100%" border="1">
             <tr>
               <th><div align="center">Player</div></th>
               <th><div align="center">Squad</div></th>
               <th><div align="center">K/D</div></th>
               <th><div align="center">Score</div></th>
               <th><div align="center">Stats</div></th>
             </tr>
           </table>
           <!-- End Right Side Data Headers --> 
         </div></td>
     </tr>
   </table>
   <br />
   
   <!-- Start Right Side Data -->
   
   <table id="right-data" width="100%" border="1">
   <?php
     $result2 = mysqli_query($con,$query2);

    while($row = mysqli_fetch_assoc($result2)){
    echo “
    <div align=“center”><a href=”#">" . $row[‘Soldiername’] . “<div align=“center”>” . $row[‘squadName’] . “<div align=“center”>” . $row[‘Kills’] . “/” . $row[‘Deaths’] . “<div align=“center”>” . $row[‘Score’] . “
       <!-- End right Side Data --></td>
 <!-- End right Side -->
    <?php mysqli_close($con); ?> [/php]
    #24

    I am not seeing the issue, the table looks like you want it to. Other that the cells not being populated, what is the issue with how it looks?

    #25

    LOL, you are getting there! But, there is no ALIGN tag inside of DIV’s. You need to use STYLE instead.
    You should use it like this…

    ...
    #26

    i added some false data to the table just to populate it …

    i will make the div changes now

    EDIT::
    [member=43746]ErnieAlex[/member]
    Ok the div’s are fixed as you stated

    [member=72272]astonecipher[/member]
    if you look at the tables the RU side is wider than the US side and the actual data is mis-aligned with the headers

    #27

    Oh, also, when you have a row in a table and you want it to be the width of the table and cover all of the cells,
    you can do it like this:

    US ( To span across four cols... )

    But, also, why did you place all those nested tables into the code? Silly… You only need one table and two
    rows. Then, add the rows of data… Loosely, something like this:

    US
    Player Squad K/D Score
    Ernie KillerSquad 3234/3113 1000234
    So much neater than yours... You will have to code the last row with your queries and of course add in your styling for how it looks... Styling will make them stand out however you want them. Just a sample...
    #28

    i nested the tables because it achieved the layout that i wanted … i wanted a border around US but i also wanted the cell US is located in to be slightly smaller than the data header table to give it that stacked look… Is there a better way to achieve that look? Im trying to not get it looking generic … i like the step down look, it makes it more appealing to the eye (IMHO)

    #29

    It is ALWAYS best to make it look like you want it too… If you used CSS and DIV’s instead of a table, you
    can make it very fancy looking… Lots of samples around…

    But, using DIV’s with CSS, you can create some really nice displays. Here is a sample. Just put it into a new
    file and look what it does. Not much code. You could turn your square design into a fancy one with little work.

    [php]

    SECTION ONE
    SECTION TWO
    [/php] Just an idea....
    #30

    well its starting to look like i have bitten alot more off than my skill is able to chew on so i think i am going to leave it as it is. I doubt that i am going to be able to get it todo what i want without hiring someone to do it for me so i guess i will have to revisit it once i have learned alot more. Thank you all for your help. i really appreciate all the time and effort that you guys put into these forums.

    Sponsor our Newsletter | Privacy Policy | Terms of Service