jQuery to help update a table from a database

Question

TheFearful

8 Years Ago

Hey everyone, I have a question. I created a page that pop-ups when a user clicks a link and that page displays a table of the information that it pulls from the database that I have created.

I want to be able to have a user click an update button next to the entry, so I created a button to appear next to each entry on each row so that they can choose to update that one user if they need to. I am also going to implement a delete button that will ask for confirmation if they want to delete that user.

At the very end of the page, I am going to add an add user button so that you can create a new entry into the database table if a new user is created. I can create buttons for each entry and have a button for the add user and have those buttons that will go to a new page, but I want it to be more real-time so that you don't have to refresh or go to a new page. Does anyone have an idea about what I would need to do? I'll show you what I have for now.

<?php

        include_once("sqlConnect.php");
        $sql = "SELECT * FROM engineers ORDER BY LastName";
        $result = mysqli_query($conn, $sql);
        echo "<table style='width:85%'>";
        echo "<tr>";
        echo "<td>FirstName</td>";
        echo "<td>LastName</td>";
        echo "<td>Extension</td>";
        echo "<td>Office</td>";
        echo "<td>Mobile</td>";
        echo "<td>Email</td>";
        echo "<td>PersonalPhone</td>";
        echo "<td>TelephoneAgent</td>";
        echo "<td>UserID</td>";
        echo "<td>EmployeeID</td>";
        while($row = mysqli_fetch_assoc($result))
        {
                echo "<tr>";
                echo "<td>". $row["FirstName"]          . "</td>";
                echo "<td>". $row["LastName"]           . "</td>";
                echo "<td>". $row["Extension"]          . "</td>";
                echo "<td>". $row["Telephone"]          . "</td>";
                echo "<td>". $row["Mobile"]             . "</td>";
                echo "<td>". $row["Email"]              . "</td>";
                echo "<td>". $row["PersonalPhone"]      . "</td>";
                echo "<td>". $row["TelephoneAgent"]     . "</td>";
                echo "<td>". $row["UserID"]             . "</td>";
                echo "<td>". $row["EmployeeID"]         . "</td>";
                echo "<td><button type='button' id='button'>Update</button></td>";
                echo "</tr>";
        }
        echo "</table>";

?>

<html>
<head>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.2/jquery.min.js"> </script>
<script>
$(document).ready(function()
{
        $('#button').on('click', function()
                {
                        $('#button').hide();
                });
});
</script>
</head>
</html>

I did a test to see if I can make the update buttons hide as a start to see if I can get familiar with using jQuery, but I'm only able to make the first update button disappear when I click it and the others remain even after I click them. So I don't know how I can do an individual update for each person if I can't make all the buttons work. Any guidelines or tips would be very helpful. Thank you!

javascript jquery mysql php

2 Contributors
8 Replies
359 Views
3 Hours Discussion Span
Latest Post 8 Years Ago Latest Post by TheFearful

hielo 65 Veteran Poster

8 Years Ago

In HTML, an elements id attribute must be unique. Since you have <button id='button'>Update</button> within the while construct, all the buttons are getting the same id value. By contrast, your $('#button') works only on the first button because it was expecting to find only one to begin with! What you need to do is change id to class, and the # to .

<html>
<head>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.2/jquery.min.js"> </script>
<script>
$(document).ready(function()
{
        $('.button').on('click', function()
                {
                        $(this).hide();
                });
});
</script>
</head>
<body>
<?php
        include_once("sqlConnect.php");
        $sql = "SELECT * FROM engineers ORDER BY LastName";
        $result = mysqli_query($conn, $sql);
        echo "<table style='width:85%'>";
        echo "<tr>";
        echo "<td>FirstName</td>";
        echo "<td>LastName</td>";
        echo "<td>Extension</td>";
        echo "<td>Office</td>";
        echo "<td>Mobile</td>";
        echo "<td>Email</td>";
        echo "<td>PersonalPhone</td>";
        echo "<td>TelephoneAgent</td>";
        echo "<td>UserID</td>";
        echo "<td>EmployeeID</td>";
        while($row = mysqli_fetch_assoc($result))
        {
                echo "<tr>";
                echo "<td>". $row["FirstName"]          . "</td>";
                echo "<td>". $row["LastName"]           . "</td>";
                echo "<td>". $row["Extension"]          . "</td>";
                echo "<td>". $row["Telephone"]          . "</td>";
                echo "<td>". $row["Mobile"]             . "</td>";
                echo "<td>". $row["Email"]              . "</td>";
                echo "<td>". $row["PersonalPhone"]      . "</td>";
                echo "<td>". $row["TelephoneAgent"]     . "</td>";
                echo "<td>". $row["UserID"]             . "</td>";
                echo "<td>". $row["EmployeeID"]         . "</td>";
                echo "<td><button type='button' class='button'>Update</button></td>";
                echo "</tr>";
        }
        echo "</table>";
?>
</body>
</html>

Edited 8 Years Ago by hielo

hielo 65 Veteran Poster

8 Years Ago

I want to know if I can use the event listener to help trigger a php code that will make a query to update that specific user

Yes. Give all your update buttons a common class, and your delete buttons a common class (see sample code below). Then you bind an event listener for the update buttons, and one for the delete buttons. If you give your <tr> an id that references the EmployeeID (assuming that EmployeeID is unique on that query result), then you can determine which record to UPDATE/DELETE.

<html>
<head>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.2/jquery.min.js"> </script>
<script>
$(document).ready(function()
{

        $('.update-button').on('click', function(){
                        // go to jquery's site and read about the closest() method
                        var tr = $(this).closest("tr");

                        // you will need to "collect" the data in the text fields
                        // since this is what the user has changed
                        var fields={};
                        $("input",tr).each(function(){
                            //here I am dynamically creating:
                            //  fields.firstname and assigning it its corresponding text field value
                            //  fields.lastname and assigning it its corresponding text field value
                            fields[$(this).attr("name")] = $(this).val();
                        });

                        fields.action_requested="update";

                        // if you open your page in chrome and do right-click > inspect > console
                        // you should see the contents of fields
                        if( typeof(console)!='undefined')
                        {
                            console.log( 'fields=',fields );
                        }

                        //send the data to process.php
                        $.ajax({ method: "POST",
                                  url: "process.php",
                                  data: fields
                                })
                               .done(function( msg ) {
                                    alert( "Data Saved: " + msg );
                                });
                });

        $('.delete-button').on('click', function(){
                        // go to jquery's site and read about the closest() method
                        var tr = $(this).closest("tr");
                        var fields={'action_requested':'delete'};
                        // there is no need to collect data to do a deletion. You just
                        // need to know the unique record id.  So get the "id=XXX" from the
                        // <TR>
                        fields.id = tr.attr("id").replace("employee-","");

                        // if you open your page in chrome and do right-click > inspect > console
                        // you should see the contents of data
                        if( typeof(console)!='undefined')
                        {
                            console.log( 'fields=',fields );
                        }

                        $.ajax({ method: "POST",
                                  url: "process.php",
                                  data: fields
                                })
                               .done(function( msg ) {
                                    alert( "Data Saved: " + msg );
                                });
        });
});
</script>
</head>
<body>
        <table style='width:85%'>
        <tr>
        <td>FirstName</td>
        <td>LastName</td>
        <td>Extension</td>
        <td>Office</td>
        <td>Mobile</td>
        <td>Email</td>
        <td>PersonalPhone</td>
        <td>TelephoneAgent</td>
        <td>UserID</td>
        <td>EmployeeID</td>
                <tr id="employee-23"><!-- HERE, 23 is supposed to be a unique record id for John Smith -->
                    <td class="firstname"><input type="text" name="firstname" value="John"/></td>
                    <td class="lastname"><input type="text" name="lastname" value="Smith"/></td>
                    <td><button type='button' class='update-button'>Update</button><button type='button' class='delete-button'>Delete</button></td>
                </tr>
                <tr id="employee-97"><!-- HERE, 97 is supposed to be a unique record id for Sally Jones -->
                    <td class="firstname"><input type="text" name="firstname" value="Sally"/></td>
                    <td class="lastname"><input type="text" name="lastname" value="Jones"/></td>
                    <td><button type='button' class='update-button'>Update</button><button type='button' class='delete-button'>Delete</button></td>
                </tr>
        </table>
</body>
</html>

On process.php you would just need to check if you need to do an update or delete:

<?php
if( array_key_exists('action_requested', $_POST) )
{
     if('update'==$_POST['action_requested'])
     {
         //code to update record
     }
     elseif('delete'==$_POST['action_requested'])
     {
        //code to delete record
     }
}

Edited 8 Years Ago by hielo

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

TheFearful · Answer 1 · 2016-05-27T16:20:59+00:00

But wouldn't that simultaneously remove all the buttons?

hielo 65 Veteran Poster · Answer 2 · 2016-05-27T16:23:48+00:00

If you noticed carefully, I used $(this).hide();. Within that function this refers to the element that triggered the action. So it only applies to the clicked button.

TheFearful · Answer 3 · 2016-05-27T16:34:45+00:00

Oh okay! I didn't notice that you use $(this).

So now that I can individually hide the buttons, I want to know if I can use the event listener to help trigger a php code that will make a query to update that specific user. Is that possible to do? Would I have to create another php file with the query? And if I do, would i have to make an individual php file for each type of action such as delete, update, add, etc?

TheFearful · Answer 4 · 2016-05-27T17:10:12+00:00

Thank you! That is very very very helpful! I guess for the update though, the user would have to change information manually. Is it possible to be able to have those printed out results turn into a textbox where you, as the user, can edit them? I figured that once they are edited, I can have an event listener that will update the information once the user hits the enter button or something like that.

Your code and comments are made to seem that the information has already been edited by the user. I think.

hielo 65 Veteran Poster · Answer 5 · 2016-05-27T17:33:20+00:00

Is it possible to be able to have those printed out results turn into a textbox where you, as the user, can edit them?

That is what you need to on the server when you first generate the page. Intead of

echo "<td>". $row["FirstName"] . "</td>";

you need

echo "<td><input type='text' name='firstname' value='". htmlentities($row["FirstName"],ENT_QUOTES) . "'/></td>";

The same goes for the other fields that you wish to edit. Refer to http://php.net/manual/en/function.htmlentities.php if you are not familar with htmlentities().

TheFearful · Answer 6 · 2016-05-27T19:05:21+00:00

Okay, I made those changes. Thank you for the amazing help!