Updating Database and Page Content at the Same Time

Question

This is a tough one for me. a total of five documents involved in this process. I can't help but feel that I am over complicating this issue. I asked about this previously and THOUGHT I understood the issue, but then I tried to add a simple "loading" modal to the equation, and it broke. What's worse I can't get it working anymore. I have changed too much. Yes I know I should have backed it up, let's get past that. The one language I cannot change at all in this whole element is my DB language, which is MySql.

What I Want to Happen

Page loads all non-archived submissions. The data is structured so that some but not all data is displayed for each row. At least not until the user clicks the "more info" button. NOTE: THIS IS NOT THE PROBLEM BUT ONLY HERE BECAUSE I HAVEN'T BUILT THIS YET, I WILL FOCUS ON THIS LATER.

After the user has finished using the data from one row, I would like the user to be able to archive the data into the database by changing the "archived" field from 0 to 1. After that is accomplished, I would like the row to disappear. If there is a lag and more than a second or two is needed to accomplish this, then a loading modal should appear that will indicate that the page has received the request and prevents the user from pressing the "archive" button multiple times.

What is Happening Now

When the page loads, all non-archived data is displayed in rows that show some but not all information for each record in a table. When the user clicks the "more info" button nothing happens. Note: Again I am not focusing on this issue I know how to fix this. When the user clicks on the "archive" button, it does nothing, but if they click if multiple times it eventually will bring up the "loading" modal and then refresh the page. The row that should have disappeared is still there, and the record still shows a "0" instead of a "1" as it should.

Final Comments Before Code Is Given

I am open to using other languages as I am a fast learner, I just don't know how to integrate them. But if you do respond with that, please also explain why my way is inferior and what I would have to do to make this work. I am still learning AJAX (very much beginner) and PHP (intermediate . . . I think).

The Code

index.php - abridged without head

<div class="container">
    <h1><span class="hidden">Locate My Pet</span></h1>
    <h2>Administration</h2>
    <p class="lead alert alert-info">Hello There! You can review and archive submitted requests here.</p>

    <div class="panel panel-default">
        <div class="panel-heading">
            <h2 class="text-center">Submissions</h2>
        </div><!--/.panel-heading-->
        <div id="results"></div><!--To be populated by script-->
    </div><!--/.panel .panel-default-->
</div><!-- /.container -->
<footer class="footer">
    <div class="container">
    <p class="text-muted text-center">&copy; 2016 TL Web Development and Design</p>
    </div><!--/.container-->
</footer><!--/.footer-->
<div class="modal fade" id="archiveMessage" tabindex="-1" role="dialog">
    <div class="modal-dialog">
        <div class="modal-content">
            <div class="modal-header">
                <button type="button" class="close" data-dismiss="modal" aria-label="Close"><span aria-hidden="true">&times;</span></button>
                <h4 class="modal-title">Archiving Submission</h4>
            </div><!--/.modal-header-->
            <div class="modal-body">
                <p>Please wait . . .</p>
            </div><!--/.modal-body-->
        </div><!--/.modal-content-->
    </div><!--/.modal-dialog-->
</div><!--/.modal-->

submission.php

<table class="table table-responsive table-striped">
<tr>
    <th>Customer Name</th>
    <th>Address</th>
    <th>Contact</th>
    <th>Pet Info</th>
    <th>Tools</th>
</tr>
<?php
    require "../_php/connect.php";

    $get_all = "SELECT request_id, fName, lName, address, city, state, zip, pPhone, cPhone, email, pName, gender, spayedNeutered, howLost, comments, timeEntered, archived FROM requests";
    $result = $conn->query($get_all);

    if ($result->num_rows > 0) {
        while($row = $result->fetch_assoc()) {
            if (!$row['archived']) {
                echo "<tr id='" . $row['request_id'] . "' class='fade in'>
                        <td>
                            " . $row['fName'] . " " . $row['lName'] . "</br>
                            <strong>Sent: </strong>" . $row['timeEntered'] . "
                        </td>
                        <td>" . $row['address'] . "</br>" . $row['city'] . " " . $row['state'] . ", " . $row['zip'] ."</td>
                        <td>
                            <strong>Primary Phone:</strong> <a href='tel:" . $row['pPhone'] . "'>" . $row['pPhone'] ."</a></br>
                            <strong>Cell Phone:</strong> <a href='tel:" . $row['cPhone'] . "'> " . $row['cPhone'] . "</a></br>
                            <strong>Email:</strong> <a href='mailto:" . $row['email'] . "'>" . $row['email'] . "</a></td>
                        <td>
                            <strong>Pet Name:</strong> " . $row['pName'] . "</br>
                            <strong>Gender:</strong> " . $row['gender'] . "</br>
                            <strong>Spayed or Neutered?:</strong> ";
                if ($row['spayedNeutered'] = 0) {
                    echo "No</td>";
                } else {
                    echo "Yes</td>";
                }
                echo    "<td>
                            <button class='btn btn-info'>More info</button></br>
                            <form action='../_php/archive.php' method='get'><input type='hidden' value='" . $row['request_id'] . "' id='row_id'><button type='submit' class='btn btn-warning archive'>Archive</button></form>
                        </td>
                    </tr>";
            }
        }
    } else if ($conn->connect_error != NULL) {
        echo "<tr><td colspan='5'><div class='alert alert-danger' role='alert'>Error: " . $conn->error . "</div></td></tr>";
    } else {
        echo "<tr><td colspan='5'><div class='alert alert-info' role='alert'>No Records were found.</div></td></tr>";   
    }
?>
<script type="text/javascript" src="../_js/query.js"></script>
</table>

connect.php - some content no included for security reasons

// Create connection
$conn = new mysqli($servername, $username, $password, $dbName);

// Check connection
if ($conn->connect_error) {
    die("<tr><td colspan='5'><div class='alert alert-danger' role='alert'>Error: " . $conn->error . "</div></td></tr>)");
}

query.js

$(document).ready(function() {
    "use strict";
    $('#results').load('../_php/submission.php');
    $(".archive").click(function() {
        $('#archiveMessage').modal('show');
        var id = $(this).parent().parent().attr('id');
        $.ajax({
            type: 'POST',
            url: '../_php/functions.php',
            data: {'archive': id},
        });
    });
});

functions.php

<?php

require "connect.php"; // Connect to database       

    function archive($id) {
        require "connect.php";
        $archive = "UPDATE requests SET archived='1' WHERE request_id='$id'";
        if ($conn->query($archive) === TRUE) {
            echo "Record " . $id . " has been archived.";
        } else {
            echo "Error: " . $conn->error;
        }
    }


    if (isset($_POST['callArchive'])) {
        archive($_POST['callArchive']); 
    } else {
        archive(1);
    }
?>

Note that `$_POST['callArchive']` should be equal to the passed `'archive' : id` parameter in your ajax call. So both 'archive' or both 'callArchive' — Sam Segers, Jan 05 '16 at 04:28
Your ajax call in your functions.php file would never call archive function since your ajax call data contains post 'archive' in which the value is id. — jameshwart lopez, Jan 05 '16 at 04:38
Sam, thank you for pointing that out, and confirming what I had thought a little.It HAD worked with that in place before but I never understood why, which is why I couldn't fix it. Jamesshwart I am a little confused by *"the value is id"* could you explain further? — Tyler Lazenby, Jan 05 '16 at 17:21
This is why you should never revisit your old questions . . . you start yelling at yourself — Tyler Lazenby, Dec 04 '18 at 06:57

score 1 · Accepted Answer · edited May 23 '17 at 12:31

Since archive button is dynamically loaded, its the best choice to use .on('click') rather than .click() that does not fires on a dynamically loaded element. Try to read the question and answeres here, specially the selected correct answer.

query.js

$(document).ready(function() {
    "use strict";
    $('#results').load('../_php/submission.php');
    $("#results .archive").on("click",function() {
        $('#archiveMessage').modal('show');
        var id = $(this).parent().parent().attr('id');
        $.ajax({
            type: 'POST',
            url: '../_php/functions.php',
            data: {'archive': id},
            //If you dont want to change your functions.php file use the commented line below instead of the above code
            //data: {'callArchive':id},
        });
    });
});

When the user clicks on the "archive" button, it does nothing, but if they click if multiple times it eventually will bring up the "loading" modal and then refresh the page. The row that should have disappeared is still there, and the record still shows a "0" instead of a "1" as it should.

Since your ajax call data contains post 'archive' in which the value is id and you want to update some data of your request table but you are checking the wrong index of the
POST data(if (isset($_POST['callArchive'])) )
rather change it to if (isset($_POST['callArchive']))

<?php   
    function archive($id) {
        require "connect.php";// Connect to database    
        $archive = "UPDATE requests SET archived='1' WHERE request_id='$id'";
        if ($conn->query($archive) === TRUE) {
            echo "Record " . $id . " has been archived.";
        } else {
            echo "Error: " . $conn->error;
        }
    }


    if (isset($_POST['archive'])) {
        archive($_POST['archive']); 
    } else {
        archive(1);
    }
?>

Hope that helps :D

Could you explain why the .on() is better than .onclick() just so that I can also avoid similar problems in the future? I was also wondering why I would say "callArchive" but for some reason it used to work that way, so I was hesitant to change that. Thank you. — Tyler Lazenby, Jan 05 '16 at 17:20
If you are hesitant to change 'callArchive' just change your ajax code to `data: {'callArchive':id},` see my updated answer — jameshwart lopez, Jan 06 '16 at 01:50
Ok as I have been working on this, I realized that my submission.php still had the archive button in a form (it was something I was trying to do to fix this issue). I have made the changes you suggested and also removed the surrounding form element from submission.php. The problem I am still having is that it takes multiple clicks to get this work (its like it is constantly refreshing it) and the element doesn't go away after the modal fades off, it just sits there). I have tried adding $('#' + id).remove(); — Tyler Lazenby, Jan 06 '16 at 05:04