Jamie Belcher Jamie Belcher - 6 months ago 13
PHP Question

Adding and subtracting a value within a table using either JavaScript or PHP

So I have a leaderboard set up at work which is put together using php, a lot of MySQL and some pretty looking CSS.
You can see in the table that I have a column labelled 'Flukes' - I'd like to add a tiny + and - button on either side of the value which will increase or decrease the value, and somehow I'd like to add this for each person. I've been trying to find some nice JavaScript to create this but I don't seem to know how to add this for each individual person

League Table

Below is a snippet from the code, the rest above is just all the MySQL needed to pull this information out.

<table>
<thead>
<tr>
<th>Name</th>
<th>Score</th>
<th>Game Count</th>
<th>Flukes</th>
</tr>
</thead>
<tbody>
<? foreach($aPlayers as $aPlayer)
{
if ($aPlayer['lGameCount'] > 0 && $aPlayer['lGameCount'] < 4)
{ ?>
<tr class="yellow" style="text-align:center;">
<td><?=$aPlayer['PlayerName']?></td>
<td><?=round($aPlayer['lScore'])?></td>
<td><?=$aPlayer['lGameCount']?></td>
<td><?=$aPlayer['lFlukes']?></td>
</tr>
<? }
elseif ($aPlayer['lGameCount'] > 3)
{ ?>
<tr class="green" style="text-align:center;">
<td><?=$aPlayer['PlayerName']?></td>
<td><?=round($aPlayer['lScore'])?></td>
<td><?=$aPlayer['lGameCount']?></td>
<td><?=$aPlayer['lFlukes']?></td>
</tr>
<? }
else
{ ?>
<tr class="red" style="text-align:center;">
<td><?=$aPlayer['PlayerName']?></td>
<td><?=round($aPlayer['lScore'])?></td>
<td><?=$aPlayer['lGameCount']?></td>
<td><?=$aPlayer['lFlukes']?></td>
</tr>
<? }
} ?>
</tbody>
</table>

Answer

Here is a JQuery-based Solution you might want to try. We simulated an Array representing the Result of MySQL Data like so:

PHP

        <?php

            $aPlayers = [
                [
                    "PlayerName"    => "Johnny",
                    "lScore"        => "3113",
                    "lGameCount"    => "41",
                    "lFlukes"       => "42",
                ],
                [
                    "PlayerName"    => "Phil",
                    "lScore"        => "3022",
                    "lGameCount"    => "13",
                    "lFlukes"       => "40",
                ],
                [
                    "PlayerName"    => "Jammizle",
                    "lScore"        => "2994",
                    "lGameCount"    => "62",
                    "lFlukes"       => "100",
                ],
                [
                    "PlayerName"    => "Gary",
                    "lScore"        => "2989",
                    "lGameCount"    => "10",
                    "lFlukes"       => "0",
                ],
                [
                    "PlayerName"    => "Johnathan",
                    "lScore"        => "2985",
                    "lGameCount"    => "9",
                    "lFlukes"       => "5",
                ],
                [
                    "PlayerName"    => "Kevin",
                    "lScore"        => "2970",
                    "lGameCount"    => "17",
                    "lFlukes"       => "5",
                ],
                [
                    "PlayerName"    => "Jason",
                    "lScore"        => "2882",
                    "lGameCount"    => "11",
                    "lFlukes"       => "62",
                ],
                [
                    "PlayerName"    => "Stewart",
                    "lScore"        => "2882",
                    "lGameCount"    => "81",
                    "lFlukes"       => "56",
                ],
                [
                    "PlayerName"    => "Chris",
                    "lScore"        => "3015",
                    "lGameCount"    => "1",
                    "lFlukes"       => "75",
                ],
                [
                    "PlayerName"    => "Nick",
                    "lScore"        => "2985",
                    "lGameCount"    => "1",
                    "lFlukes"       => "98",
                ],
            ];
        ?>

We then changed the Structure of your Table to have classes so that we can work with the classes in JQuery and we also added a little CSS to help us visualize the Process like so:

HTML

        <style type="text/css">
            .increase-flukes,
            .decrease-flukes{
                cursor:pointer;     /* <== ADD A POINTER CURSOR TO INDICATE YOU CAN CLICK TO INCREASE OR DECREASE FLUKES*/
                font-weight:bold;
                font-size: 18px;
                display:inline-block;
                min-width:20px;
                text-align: center;
                color:red;
            }
            td.flukes-cell{
                text-align: left;
            }
        </style>



        <table>
            <thead>
            <tr>
                <th>Name</th>
                <th>Score</th>
                <th>Game Count</th>
                <th>Flukes</th>
            </tr>
            </thead>
            <tbody>
            <? foreach($aPlayers as $aPlayer)
            {
                if ($aPlayer['lGameCount'] > 0 && $aPlayer['lGameCount'] < 4)
                { ?>
                    <tr class="yellow" style="text-align:center;">
                        <td><?php echo $aPlayer['PlayerName'];?></td>
                        <td><?php echo round($aPlayer['lScore']);?></td>
                        <td><?php echo $aPlayer['lGameCount'];?></td>
                        <td class="flukes-cell">
                            <span class="increase-flukes" data-pid="<?php echo $aPlayer['PlayerID'];?>">+</span>
                            <span class="flukes" id="fluke-<?php echo $aPlayer['PlayerID'];?> " data-pid="<?php echo $aPlayer['PlayerID'];?>"><?php echo $aPlayer['lFlukes'];?></span>
                            <span class="decrease-flukes" data-pid="<?php echo $aPlayer['PlayerID'];?>">-</span>
                        </td>
                    </tr>
                <?  }
                elseif ($aPlayer['lGameCount'] > 3)
                { ?>
                    <tr class="green" style="text-align:center;">
                        <td><?php echo $aPlayer['PlayerName'];?></td>
                        <td><?php echo round($aPlayer['lScore']);?></td>
                        <td><?php echo $aPlayer['lGameCount'];?></td>
                        <td class="flukes-cell">
                            <span class="increase-flukes" data-pid="<?php echo $aPlayer['PlayerID'];?>">+</span>
                            <span class="flukes" id="fluke-<?php echo $aPlayer['PlayerID'];?> " data-pid="<?php echo $aPlayer['PlayerID'];?>"><?php echo $aPlayer['lFlukes'];?></span>
                            <span class="decrease-flukes" data-pid="<?php echo $aPlayer['PlayerID'];?>">-</span>
                        </td>
                    </tr>
                <?  }
                else
                { ?>
                    <tr class="red" style="text-align:center;">
                        <td><?php echo $aPlayer['PlayerName'];?></td>
                        <td><?php echo round($aPlayer['lScore']);?></td>
                        <td><?php echo $aPlayer['lGameCount'];?></td>
                        <td class="flukes-cell">
                            <span class="increase-flukes" data-pid="<?php echo $aPlayer['PlayerID'];?>">+</span>
                            <span class="flukes" id="fluke-<?php echo $aPlayer['PlayerID'];?> " data-pid="<?php echo $aPlayer['PlayerID'];?>"><?php echo $aPlayer['lFlukes'];?></span>
                            <span class="decrease-flukes" data-pid="<?php echo $aPlayer['PlayerID'];?>">-</span>
                        </td>
                    </tr>
                <?  }
            } ?>
            </tbody>
        </table>

Finally, we married the HTML with JQuery like so:

JAVASCRIPT: JQUERY

        <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.4/jquery.min.js"></script>
        <script type="text/javascript">
            ;                           // CLOSE OFF ANY UNCLOSED JS TAGS... IF ANY ;-)
            jQuery.noConflict();        // ADD THE noConflict() METHOD, IN CASE YOU ARE USING OTHER LIBRARIES THAT MAKE USE OF THE $ SYMBOL
            (function ($) {
                $(document).ready(function(e) {
                    // CREATE VARIABLES TO USE IN THE PROGRAM
                    var flukesCell  = $("td.flukes-cell");
                    flukesCell.each(function(data){
                        var decreaseFluke   = $(this).find(".decrease-flukes");
                        var increaseFluke   = $(this).find(".increase-flukes");
                        var flukeContent    = $(this).find(".flukes");

                        decreaseFluke.on("click", function(evt){
                            var flcVal  = flukeContent.text();
                            var id      = $(this).attr("data-id");
                            if(flcVal != 0){
                                flcVal--;
                                flukeContent.text(flcVal);
                                performAjaxSave(flcVal, id);
                            }
                        });

                        increaseFluke.on("click", function(evt){
                            var flcVal  = flukeContent.text();
                            var id      = $(this).attr("data-id");
                            flcVal++;
                            flukeContent.text(flcVal);
                            performAjaxSave(flcVal, id);
                        });
                    });

                    function performAjaxSave(flcVal, id){
                        var objData = {"id":id, "fluke":flcVal};
                        $.ajax({
                            url         : "ajax-processor.php",      //<== THIS SHOULD POINT TO THE PHP FILE THAT PROCESSES THE AJAX REQUEST
                            dataType    : "json",
                            cache       : false,
                            type        : "POST",
                            data        : (objData),

                            success: function (data, textStatus, jqXHR){
                                if(data){
                                    alert(data.message);        // THIS COULD BE VERY ANNOYING, REALLY... BUT COULD BE HANDY DURING DEVELOPMENT... DELETE IT!!!
                                    console.log(data.message);
                                }
                            },

                            error: function (jqXHR, textStatus, errorThrown) {
                                console.log('The following error occurred: ' + textStatus, errorThrown);
                            },

                            complete: function (jqXHR, textStatus) {
                            }
                        });
                    }
                });
            })(jQuery);

        </script>

And now to save the Fluke back to the Database, Create a PHP-File in the same Directory as the Main File (probably your index.php file). In this case, we called it ajax-processor.php but you can as well call it anything but if you change the name, please make sure this new name is reflected in AJAX Section of your Javascript. Here are the Contents of the ajax-processor.php File:

    <?php
        // FILENAME: ajax-processor.php

        //DATABASE CONNECTION CONFIGURATION:
        defined("HOST")     or define("HOST", "localhost");           //REPLACE WITH YOUR DB-HOST
        defined("DBASE")    or define("DBASE", "sim");                //REPLACE WITH YOUR DB NAME
        defined("USER")     or define("USER", "root");                //REPLACE WITH YOUR DB-USER
        defined("PASS")     or define("PASS", "root");                //REPLACE WITH YOUR DB-PASS

        // GET THE POSTED VALUES OF THE FLUKE AND THE PLAYER ID FROM THE AJAX REQUEST...
        $playerID   = isset($_POST['id'])       ? htmlspecialchars(trim($_POST['id']))      : null;
        $fluke      = isset($_POST['fluke'])    ? htmlspecialchars(trim($_POST['fluke']))   : null;

        $response   = ['message'=>'Error updating Fluke for the Player with ID: ' . $playerID];

        try {
            $dbh = new PDO('mysql:host=' . HOST . ';dbname=' . DBASE, USER, PASS);
            $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

            // NOW UPDATE THE FLUKES ONLY IN THE tPlayers TABLE USING THE DATA ABOVE
            $sql    = "UPDATE tPlayers SET rFlukes=:RFL WHERE pPlayerID=:PID";
            $stmt   = $dbh->prepare($sql);
            $stmt->bindParam(':RFL', $fluke);
            $stmt->bindParam(':PID', $playerID);
            $status = $stmt->execute();

            if($status){
                $response["message"]    = "Update was Successful!!";
            }

            //GARBAGE COLLECTION
            $dbh = NULL;
        } catch (PDOException $e) {
            $response["message"]    = $e->getMessage();
        }

        json_encode($response);

And the Result is that you can now click the Plus and minus Signs to increase or decrease the value of the Flukes plus save the value to the Database with each click of the Plus or Minus Buttons, respectively. However, feel Free to change the Plus & Minus Signs using perhaps classes from Bootstrap or Font-Awesome. It is hoped that this would help get you started, my Friend. Good-Luck ;-)

IMPORTANT

You've got to make changes to the game.php File on Line: 90 to also SELECET p.pPlayerID as shown below. Notice that we added p.pPlayerID AS PlayerID.

    <?php
        $sSQL = 'SELECT p.pPlayerID AS PlayerID, p.sName AS PlayerName, p.rFlukes AS lFlukes, COUNT(pGameID) AS lGameCount , ps.lScore, gt.sName AS GameName, pPlayerID, gt.sFavicon, gt.sLogo ';
Comments