MySQL: Changing a value in a column

Question

The title nearly explains itself to be honest. I am trying to change my Delete column from 0 to 1, depending on what player has been selected from my listplayers drop down.

My listPlayers.php file is working 100%. The players are showing up fine in the dropdown. I should add that the error is happening within my $sql variable.

<?php
include 'dbconnect.php';
$sql = "UPDATE players SET Delete = 1 WHERE playersID = $_POST[playersIDHidden]";

if(! mysql_query($sql, $conn))
{
    echo "Error " . mysql_error();
}

else
{
    if(mysql_affected_rows() != 0)
    {
        echo $_POST[pName] . " has been deleted <br>";
        echo "Person ID: " . $_POST ['playersID'] . "<br>";
        echo "Players Name: " . $_POST ['pName'] . "<br>";

        echo "Deletion complete";
    }

    else
    {
        echo "No records were changed";
    }
}

mysql_close($conn);
?>

<form action = "viewPlayers.php" method = "POST" />
    <input type = "submit" value = "View Players">
</form>

And finally my listPlayers.php that is showing my players names in the drop down.

<?php
include "dbconnect.php"; //Opening Database Connection

$sql = "SELECT * FROM `players` WHERE `Delete` = 0;";

if (!$result = mysql_query($sql, $conn))
{
    die('Error in querying the database' . mysql_error());
}

echo "<br><select name = 'listPersons' id = 'listPersons' onclick = 'populate()'>";

while ($row = mysql_fetch_array($result))
{
    $id = $row['playersID'];
    $pName = $row['playersName'];
    $dob = $row['playersDateOfBirth'];
    $dob = date  ("d-m-Y", strtotime($dob));

    $allText = "$id, $pName, $dob";
    echo "<option value = '$allText'> $pName </option>";
}

echo "</select>";
mysql_close($conn);
?>

Sorry for the relatively long post. I am generally able to fix 99% of undefined errors. This one has me lost.

Cheers!

Answer 1

Use your disabled textbox to display it for the user.

Use another hidden field to hold the playersID.

Populate both of them with your script and then you can use the value in the hidden field when you need to delete.

For clarity I'm showing you what your HTML might look like - here's the script:

<script>
    function populate()
    {
        var select = document.getElementById("listPersons"); 
        var result = select.options[select.selectedIndex].value;

        var personDetails = result.split(", ");
        document.getElementById("playersID_display").value = personDetails[0];
        document.getElementById("playersID").value = personDetails[0];
        document.getElementById("pName_display").value = personDetails[1];
        document.getElementById("pName").value = personDetails[1];
....

and the html:

<form name = "myForm" action = "deletePlayer.php" method = "POST">    
    <p>
        <label for> Players ID:
            <input type = "text" name = "playersID_display" id = "playersID_display" disabled/>
            <input type = "hidden" name = "playersID" id = "playersID" />
        </label>
    </p>

    <p>
        <label for> Players Name:
            <input type = "text" name = "pName_display" id = "pName_display" disabled/>
            <input type = "hidden" name = "pName" id = "pName" />
        </label>
    </p>

    <p>
        <label for> Date of Birth:
            <input type = "date" name = "DoB_display" id = "DoB_display" disabled/>
            <input type = "hidden" name = "DoB" id = "DoB" />
        </label>
    </p>

    <input type = "Submit" value = "Delete" class = "button" onClick = "return confirm('Are you sure you want to delete this player?')"/>

</form>     

Note that I have maintained unique names for the fields and unique IDs for the HTML input elements. I also prefer the type="hidden" rather than putting hidden at the end of the <input ... hidden /> element - I'm not familiar with that syntax so I'm using my preferred way.

Obviously your script will have to update EACH of these fields individually.