Search code examples
phptextboxupdates

Update the value from table in textbox BUT not updated the value in db?

I have listed all the data(Item, Category, Job, Hole(Hole is evaluating marks)) and I display the Hole(mark) in textbox filed.

I want to update the Hole(marks) after user change.

I list all the data using php

<?php
try{
$con = new PDO("mysql:host=localhost;dbname=gcmes", "root", "");
$sql = $con->query("SELECT * FROM details");

echo"<table class='info' align='center'>";
echo"<tr><td width='10'><b>No</b></td>
<td width='30'><b>Category</b></td>
<td width='50'><b>Job</b></td>
<td width='40'><b>Evaluate</b></td><tr>";
foreach($sql as $row) {
$Item = $row["Item"];
$Category = $row["Category"];
$Job = $row["Job"];
$Evaluate = $row["Hole 1"];

echo'
    <tr>
        <td>' . $Item . '</td>
        <td>' . $Category . '</td>
        <td>' . $Job . '</td>
        <td><input type="input" name="Evaluate" id="Evaluate" value="' . $Evaluate . '">
        </td>
    </tr>
';
}
echo"</table></form>";

if(isset($_POST['Update_btn'])){
$Evaluate = $_POST["Hole 1"];

if(empty(Evaluate)){
echo "<script type='text/javascript'>alert('Please fill in the required fields to update!')</script>";
}

else{
$insert=$con->prepare("UPDATE details SET Evaluate=:Hole 1 WHERE Item=:Item");
$insert->bindParam(':Hole1',$Evaluate);
$insert->bindParam(":Item",$Item);
$insert->execute();

echo "<script type='text/javascript'>alert('Successful Updated ! ');
window.location.href = 'Hole 1.php';
</script>";
}//else
}//if add button
}//try
catch(PDOException $e)
{
echo "error".$e->getMessage();
}
?>

The html code i just use to display button

<form id="form1" name="Hole 1" method="post" action="Hole 1.php">
<input name="Update_btn" type="image" id="Update_btn" onmouseover="this.src='UpdateO.png'" onmouseout="this.src='UpdateD.png'" value="submit" src="UpdateD.png" alt="submit Button" align="right"> 
</form>

The problem is will alert message successful updated BUT the value not update in my db. Why? what is the problem?

enter image description here this is my interface i want update the marks in textbox filed

I need to change the hole as a selection give the user choose which hole that need to update only, i set the hole have a drop-down menu list. How to dectect which hole?

enter image description here

i just add the code after <td>{$rowData['Frequency']}</td> (dn Fer answer)

<td><select name="hole">
    <option value="Hole1">1</option>
    <option value="Hole2">2</option>
    <option value="Hole3">3</option>
    <option value="Hole4">4</option>
    <option value="Hole5">5</option>
    <option value="Hole6">6</option>
    <option value="Hole7">7</option>
    <option value="Hole8">8</option>
    <option value="Hole9">9</option>
    <option value="Hole10">10</option>
    <option value="Hole11">11</option>
    <option value="Hole12">12</option>
    <option value="Hole13">13</option>
    <option value="Hole14">14</option>
    <option value="Hole15">15</option>
    <option value="Hole16">16</option>
    <option value="Hole17">17</option>
    <option value="Hole18">18</option>
  </select>
Solution

  • Keep the following in mind:

    • I don't have the same environment as yours, so it might not work one on one.
    • Spaces in database fieldNames and arrayKeys, etc. are discouraged. I prefer to use lowerCamelCase, check if dB fieldNames match the code below!
    • Read the comments I've placed in the code.
    • I didn't take psr coding, value validation or safety (sql injection), etc. in consideration. The code is to guide you, you should take these things in consideration yourself.

    Hopefully getting you closer to your goals...

    Update: The values of the Evaluate field of each row is now validated to be an integer in the range from 1 to 5.

    <?php

//Initialize variables
$result     = '';
$doUpdate   = isset($_POST['updateButton_x']);

//Because button type is 'image', we get parameters buttonName_x and buttonName_y
//from the browsers POST request when the form is sent.
if ($doUpdate) { 
    //Update button pressed.
    //Initialize variables
    $stmtSetParams = $stmtInParams = array();
    $validationOptions = array('options' => array('min_range' => 1, 'max_range' => 5));
    //Define statement and parameters (Assuming dB field 'item' is the primary key).
    $set = '`hole1` = CASE `item` ';
    foreach ($_POST['evaluate'] as $item => $hole1) {
        //Get input value of each table row
        if (filter_var($hole1, FILTER_VALIDATE_INT, $validationOptions) !== false) {
            //Field is not blank
            $set .= 'WHEN ? THEN ? ';
            $stmtSetParams[] = $stmtInParams[] = $item;
            $stmtSetParams[] = $hole1;
        } else {
            //Field is not an integer from 1 to 5
            $result .= "Field \'Evaluate\' of item \'$item\' with a value of \'$hole1\' is not from 1 to 5 and skipped while saving!\\n";
        }
    }
    $set .= 'END';
    //Define query placeholders
    $placeHolders = implode(', ', array_fill(0, count($stmtInParams), '?'));
    $query = <<<SQL
UPDATE `details` SET $set WHERE `item` IN ($placeHolders)
SQL;
}

//Query the dB.
try {
    $dbh = new PDO('mysql:host=localhost;dbname=gcmes', 'root');
    if ($doUpdate) {
        //Update requested. Prepare and execute update query
        $stmt = $dbh->prepare($query);
        $stmt->execute(array_merge($stmtSetParams, $stmtInParams));
        $result .= 'Update Completed!';
    }
    //Query for en fetch (updated) table data
    $stmt = $dbh->query("SELECT * FROM `details`");
    $tableData = $stmt->fetchAll(PDO::FETCH_ASSOC);
} catch (PDOException $e) {
    //A PDO exception is raised
    $result = 'Error: ' . addslashes($e->getMessage());
}

//Alert results of database operations
if ($result != '') {
    echo "<script>alert('$result')</script>";
}
?>

<form id="form1" name="chooseFormNameIfNeeded" method="post" action="test.php">
    <!--  attribute align is deprecated, define and use a class instead -->
    <table class="info align-center">
        <tr>
            <!--  use th instead of td for table header -->
            <!--  using <b> tag is discouraged -->
            <th width="10"><b>No</b></th>
            <th width="30"><b>Category</b></th>
            <th width="50"><b>Job</b></th>
            <th width="40"><b>Evaluate</b></th>
        </tr>
        <?php
            foreach ($tableData as $rowData) {
                //Print a table row for each of the fetched records
                echo <<<HTML
<tr>
    <td>{$rowData['item']}</td>
    <td>{$rowData['category']}</td>
    <td>{$rowData['job']}</td>
    <td>
        <!-- Assuming dB field 'item' is the primary key. -->
        <input type="number" name="evaluate[{$rowData['item']}]" id="Evaluate" value="{$rowData['hole1']}"
               min=1 max=5
        >
    </td>
</tr>
HTML;
            }
        ?>
    </table>
    <!--  Attribute align is deprecated, define and use a class instead -->
    <!--  Value attribute should not be specified -->
    <input name="updateButton" type="image" id="Update_btn" src="http://via.placeholder.com/100x50/0000FF?text=Update" 
           alt="submit Button" class="align-right" 
           onmouseover="this.src='http://via.placeholder.com/100x50/00FF00?text=Update'"
           onmouseout="this.src='http://via.placeholder.com/100x50/0000FF?text=Update'"
    >
</form>