2

I display the list(Item, Category, and Job) from database in table and user can click the list on the table and the data will display in the text box. The user can add, update and delete the list in the text box. After that click the button that wants to function. In this php I decided do 2 functions which add dan update the list(Item, Category, and Job).

Finally i successful to update.

This is Updated code:

First I display 3 textboxes on the table. User can add the new list(Item, Category, Job) on the textbox. after that, user also can click the list on the another that I list all the data from my database on the 3 textbox as I show at the top position and change the list(Category and Job) on the textbox that wants to update.

<form id="form1" name="Checklist" method="post" action="Checklist2.php">
    <table width="305" height="116" align="center" border="0">
        <tr>
            <td width="37%">Item : <br></td>
            <td width="63%"><input type="text" name="Item" id="Item"></td>
        </tr>
        <tr>
            <td>Category : </td>
            <td>
                <input type="text" name="Category" id="Category">
            </td>
        </tr>
        <tr>
            <td>Job : </td>
            <td> 
                <input type="text" name="Job" id="Job">
            </td>
         </tr>
     </table>
      <div align="center">
        <input type="image" value="submit" src="AddD.png" alt="submit Button" onmouseover="this.src='AddO.png'" onmouseout="this.src='AddD.png'" name="Add_btn" id="Add_btn">
        <input type="image" value="submit" src="UpdateD.png" alt="submit Button" onmouseover="this.src='UpdateO.png'" onmouseout="this.src='UpdateD.png'" name="Update_btn" id="Update_btn">
&nbsp;
        <a href="DeleteChecklist2.php" onMouseOut="MM_swapImgRestore()" onMouseOver="MM_swapImage('Image3','','Delete1O.png',1)">
        <img src="Delete1D.png" name="Image3" width="145px" height="50px" border="0" name="Delete_btn" id="Delete_btn">
    </a>
     </div>
 </form>

//This is the PHP code of add and delete button
<?php
    try {
        $con = new PDO("mysql:host=localhost;dbname=gcmes", "root", "");
        if(isset($_POST['Add_btn'])) {
            $Item = $_POST["Item"];
            $Category = $_POST["Category"];
            $Job = $_POST["Job"];

            if(empty($Item)||empty($Category)||empty($Job)) {
                echo "<script type='text/javascript'>alert('Please fill in the required fields to add!')</script>";
            }
            else {
                $insert=$con->prepare("INSERT INTO details(Item,Category,Job) VALUES(:Item,:Category,:Job)");
                $insert->bindParam(':Item',$Item);
                $insert->bindParam(':Category',$Category);
                $insert->bindParam(':Job',$Job);
                $insert->execute();
                echo "<script type='text/javascript'>alert('Successful Added ! '); window.location.href = 'Checklist2.php';</script>";
            }//else
        }//if addbutton

        if(isset($_GET['Update_btn'])) {
            $Item = $_GET['Item'];
            $Category = $_GET['Category'];
            $Job = $_GET['Job'];

            if(empty($Category)||empty($Job)) {
                echo "<script type='text/javascript'>alert('Please fill in the required fields to update!')</script>";
            }
            else {
                $st=$con->prepare("UPDATE details SET Category = :Category, Job = :Job WHERE Item = :Item");
                $st->bindParam(":Category",$Category);
                $st->bindParam(":Job",$Job);
                $st->bindParam(":Item",$Item);
                $st->execute();
            }//else
        }//if updatebutton
    }//try
    catch(PDOException $e) {
        echo "error".$e->getMessage();
    }
?>


//This is the table list all the data from database
<table id="table" border="0" align="center">
    <tr>
        <th>No</th>
        <th>Category</th>
        <th>Job</th>
    </tr>

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

        foreach($sql as $row) {
            $Item = $row["Item"];
            $Category = $row["Category"];
            $Job = $row["Job"];

            echo'
                <tr>
                    <td>' . $Item . '</td>
                    <td>' . $Category . '</td>
                    <td>' . $Job . '</td>
                </tr>
            ';
        }
        echo"</table>";
    }
    catch(PDOException $e) {
        echo "error".$e->getMessage();
    }
?>

This is a script when user click the data at table(above code) will displayed in textbox :

<script>
    var table = document.getElementById('table');
        for(var i = 1; i < table.rows.length; i++)
        {
            table.rows[i].onclick = function()
            {
                //rIndex = this.rowIndex;
                document.getElementById("Item").value = this.cells[0].innerHTML;
                document.getElementById("Category").value = this.cells[1].innerHTML;
                document.getElementById("Job").value = this.cells[2].innerHTML;
            };
        }
</script>

Thank you all!

J. Lee
  • 35
  • 6
  • Remember, you can always drop javascript lines of `console.log('this happened: ' + varname);` to help debug why something is slipping through certain spots in js. – IncredibleHat Dec 13 '17 at 14:50
  • For making item number unchangeable, put it into a hidden form input, and then only 'display' the item number to them. – IncredibleHat Dec 13 '17 at 14:52
  • And when you say "*all the category and job will update to be same*" ... do you mean the `UPDATE` in sql? Or something else. – IncredibleHat Dec 13 '17 at 14:52
  • 1
    Yes. I mean when the user changes the data and click update button the data will be updated to be same in sql – J. Lee Dec 13 '17 at 15:00

2 Answers2

0

A few things to cover here.

Issue 1:

Your INSERT setup is incorrect. You have:

$insert=$con->prepare("INSERT INTO details(Item,Category,Job) VALUES(:Item,:Category,:Job)");
$insert->bindParam('Item',$Item);
$insert->bindParam('Category',$Category);
$insert->bindParam('Job',$Job);
$insert->execute();

It should be like this (note the : additions in bindParam):

$insert=$con->prepare("INSERT INTO details(Item,Category,Job) 
                       VALUES(:Item,:Category,:Job)");
$insert->bindParam(':Item',$Item);
$insert->bindParam(':Category',$Category);
$insert->bindParam(':Job',$Job);
$insert->execute();

Issue 2:

The second is your UPDATE is both incorrect, and not using prepare properly. You have this:

$st=$con->prepare("UPDATE details SET Category='$Category', Job='$Job'");
$st->bindParam(1,$Category);
$st->bindParam(2,$Job);
$st->execute();

Where you are injecting the variables into the query itself, negating the use of prepare entirely. Then you try to bindParam to non-existent placeholders. Lastly, you are updating the ENTIRE table with the same information because you forgot a WHERE clause.

So try this instead:

$st=$con->prepare("UPDATE details SET Category = :Category, Job = :Job 
                   WHERE Item = :Item");
$st->bindParam(":Category",$Category);
$st->bindParam(":Job",$Job);
$st->bindParam(":Item",$Item);
$st->execute();

Issue 3:

And lastly, as I mentioned in comments... you can pass the Item as a hidden form element so the user cannot easily change it:

<tr>
    <td width="37%">Item : <br></td>
    <td width="63%">
        <input type="hidden" name="Item" id="Item">
        <span id="ItemDisplay"><!-- Item will be visible here --></span>
    </td>
</tr>    

// js addition:
    document.getElementById("Item").value = this.cells[0].innerHTML;
    document.getElementById("ItemDisplay").innerHTML = this.cells[0].innerHTML;

Issue 4:

Before you have your html of the update button, you have a close form tag, which is breaking your html form submission parameters:

</form><!-- THIS shouldnt be here -->
<?PHP
     // big block of php
?>
<div align="center">
    <input type="image" value="submit" src="AddD.png" alt="submit Button" onmouseover="this.src='AddO.png'" onmouseout="this.src='AddD.png'" name="Add_btn" id="Add_btn">
    <input type="image" value="submit" src="UpdateD.png" alt="submit Button" onmouseover="this.src='UpdateO.png'" onmouseout="this.src='UpdateD.png'" name="Update_btn" id="Update_btn">
</div>
</form><!-- this one is the CORRECT one to keep -->

Remove that early </form> tag.

IncredibleHat
  • 4,000
  • 4
  • 15
  • 27
  • First, very thankful fix my incorrect part. However, I implement the code that you correct it the update part still doesn't function it – J. Lee Dec 13 '17 at 18:11
  • Maybe because you are prematurely ending your `` before the `name="Update_btn"` button you have that activates the update operation? Other than that, I cannot see beyond what you have presented, so I really do not know what other problems you are having without more information added to your question (like error outputs, and debugging attempts you've made). – IncredibleHat Dec 13 '17 at 18:29
  • Hi, I have updated my code and add more detail than what are I doing. Can you help me see it? Thank you – J. Lee Dec 14 '17 at 09:14
  • Hi, I faced other problem can you help me to see it? https://stackoverflow.com/questions/47828847/how-to-update-the-value-from-table-in-textbox-form?noredirect=1#comment82643869_47828847 – J. Lee Dec 16 '17 at 02:30
-1

The problem lies here, your update query updates the whole table as you did not specify a row to be updated

$st=$con->prepare("UPDATE details SET Category='$Category', Job='$Job'");
        $st->bindParam(1,$Category);
        $st->bindParam(2,$Job);
        $st->execute();
        }

try this instead

$st=$con->prepare("UPDATE details SET Category='$Category', Job='$Job' where id='$id'");

You should pass the row id in a hidden input to the form so that it can be passed back to your script when submited

<input type="hidden" name="db_id" value="1"><!-- the value should be the primary key id from your database-->

you can pick the value up as usual

if(isset($_GET["db_id"])){$id = $_GET["db_id"];}//Kindly make sure you sanitize this input to prevent SQL injection attacks
Tobi
  • 1
  • 3
  • db_id is Item, right? Item my primary key. `if(isset($_GET["db_id"])){$id = $_GET["db_id"];}` where that i change this? I replace this code also not work to update `$st=$con->prepare("UPDATE details SET Category='$Category', Job='$Job' where id='$id'");` – J. Lee Dec 13 '17 at 15:43
  • Why are you using prepare, but inserting the variables straight into the query? This negates sql injection prevention of the prepared statement. – IncredibleHat Dec 13 '17 at 16:46