Update MySQL table from PHP table

Question

This is only my second project so very new so excuse my scrappy code. I’m trying to generate a table from MySQL with an additional input column. This column is where I want to be able to input a number which will be added to the original MySQL table ‘score’ if that makes sense. The table is DROPPED and CREATED every time a new game starts (not hand) due to different players participating. The inputs for each hand could happen up to 50 times before the table is dropped. See the code might make more sense or not. Should be simple I just can’t get my head round it at the moment.

<?php // defined variables to remove errors
$submit = 'add_scores';
$name = $message = "";
$hand = 0;
$array = $index = "";
$datatable = "cards_players"; // MySQL table name
?>

<?php 
if (isset($_POST[$submit])) {
//this is where my insert statement will go 
}

?>
   <form method="post" action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]);?>" class="formAcc">
    <ul style= "text-align: center">

    <article>
        <li>
            <label>Current Standing</label>
<?php   
$datatable = "cards_data"; // MySQL table name

$sql = "SELECT * FROM ".$datatable." WHERE player IS not NULL ORDER BY id ASC";
$rs_result = $connection->query($sql);

// Check connection
if ($connection->connect_error) {
    die("Connection failed: " . $connection->connect_error);
}            
?>

<?php
  echo '<table id="db_results">
  <colgroup>
    <col class="col15" />
    <col class="col15" />
    <col class="col15" />
    <col class="col20" />
  </colgroup>
<tr>
<th>Name</th>
<th>Previous Score</th>
<th>Current Score</th>
<th>This Hand</th>
</tr>';

while($row = $rs_result->fetch_assoc()) {
    echo "<tr class=\"center\">
        <td>". $row["player"] . "</td>
        <td>". $row["previous_score"] . "</td>
        <td>". $row["score"] . "</td>
        <td>"//. retain_fill ('text','hand','',$hand,'0') ."
        . '<input type="text" name="'.$row["player"].'" placeholder="';
            if (!empty($hand)) {echo $hand. '" value="'.$hand.'"/>';
        }
            else {echo 0 . '" value=""/>';}
        "</td>
        </tr>"; 
    }
echo "</table>";            
?>            
    </li>
    <li>
        <input type="submit" name="<?php echo $submit?>" value="Input Scores" style="margin-top: 5px;"/>

At the moment I'm not even sure how to begin the insert statement for each user as I know this will be a loop of some sort. Any help appreciated :-)

I'm not quite sure I understand your question. You have an input field, which leads to nowhere. You will need to process it somehow. If you want to remain on this page, I would suggest an AJAX call. If it's ok to leave this page, you could make an another PHP file, either called by a form in each line, or make a large form surrounding the table, and processing them as a whole. — kry
– kry, Commented Jan 30, 2019 at 14:14
It seems you are identifying each row by it's player. So whatever you do, you need an another query which gets the players from your database, and iterate over it for each "name" identifier, similar way you are doing the while cycle right now. — kry
– kry, Commented Jan 30, 2019 at 14:16
Thanks @kry yes i'm identifying each row by its player which generates the table perfectly and creates the input box in the last column. I have a submit button which then activates the insert query and the page will refresh to the new data from the database for the next set up updates (rinse and repeat). Right now i can make this work by for example making the $hand the variable holding the data but it makes all the players have the same number of course so it would update everyone with a single input (not the different ones for each player) — JohnM
– JohnM, Commented Jan 30, 2019 at 14:27
Is your submit button linked to a JS AJAX call, or a PHP one? Personally, instead giving the 'name="$row['player']"' (messed up the quotes, I know...), I would do it like name="player['.$row['player'].']", so the it would send an array named player, where the key->value pairs are the $row['player'] and the value="'.$hand.'". — kry
– kry, Commented Jan 30, 2019 at 14:30
This way not just easier to see as a human, but the keys would also be unique identifiers, which can be iterated over with a foreach loop. — kry
– kry, Commented Jan 30, 2019 at 14:31

JohnM · Accepted Answer · 2019-01-30 17:26:24Z

1

Thanks to @kry for the guidance and well answer really. There was 1 correction needed and a final query statement as follows: First correction =

while($row = $rs_result->fetch_assoc()) {
    echo "<tr class=\"center\">
        <td>". $row["player"] . "</td>
        <td>". $row["previous_score"] . "</td>
        <td>". $row["score"] . "</td>
        <td>"//. retain_fill ('text','hand','',$hand,'0') ."
        . '<input type="text" name="'.$row["player"].'" placeholder="';
            if (!empty($hand)) {echo $hand. '" value="'.$hand.'"/>';
        }
            else {echo 0 . '" value=""/>';}
        "</td>
        </tr>"; 
    }

Should be

while($row = $rs_result->fetch_assoc()) {

    echo "<tr class=\"center\">
        <td>". $row["player"] . "</td>
        <td>". $row["previous_score"] . "</td>
        <td>". $row["score"] . "</td>
        <td>". '<input type="text" name="player['.$row['player'].']" placeholder="0" value=""/>';
        }

Then the database query worked out to be:

foreach ($_POST['player'] as $player=>$hand){   
    $query  = "Update cards_data SET player = '$player', previous_score = '$hand' WHERE player = '$player' LIMIT 1;"; 
$result = mysqli_query($connection, $query);
    }

answered Jan 30, 2019 at 17:26

JohnM

717 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

kry Over a year ago

"Update cards_data SET player = '$player' [...] WHERE player = '$player'" Contemplating the universe.

Collectives™ on Stack Overflow

Update MySQL table from PHP table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related