Update a table of information on a button click

Question

I have a table which will select information from a database and display it, I'm wondering if anyone can provide a code for me to update a column on the click of a button?

Example Table: (Access=Boolean)

ID   -   Name   -   Access
---------------------------
1   -   John    -    1
---------------------------
2   -   Ben     -    1
---------------------------
3   -   Terry   -    0
---------------------------

My exisiting button is based on bootstrap,

    <button type=\"button\" id=\"passed\" class=\"btn btn-success btn-flat\"><i class=\"fa fa-check\"></i></button>
    <button type=\"button\" id=\"insufficient\" class=\"btn btn-danger btn-flat\"><i class=\"fa fa-times\"></i></button>

I was hoping for something like this, ONCLICK button1 Run $Allowed SQL ONCLICK button2 Run $NotAllowed SQL

$allowed = mysqli_query($conn," UPDATE users SET Access = "1" WHERE id = '27' ");     
$notallowed = mysqli_query($conn," UPDATE users SET Access = "0" WHERE id = '453' ");

Answer 1

You can use a form with a post method and a submit type of buttons with named attributes and use those in a conditional statement.

I.e.:

Sidenote: I removed the escaped quotes, as I was not sure if those were already set inside an echo.

HTML form:

<form method="post" action="handler.php">

    <button type="submit" name="passed" id="passed" class="btn btn-success btn-flat"><i class="fa fa-check"></i></button>
    <button type="submit" name="insufficient" id="insufficient" class="btn btn-danger btn-flat"><i class="fa fa-times"></i></button>

</form>

PHP:

<?php 

// db connection

if(isset($_POST['passed'])){

    $allowed = mysqli_query($conn," UPDATE users SET Access = "1" WHERE id = '27' ");

}

if(isset($_POST['insufficient'])){

    $notallowed = mysqli_query($conn," UPDATE users SET Access = "0" WHERE id = '453' ");

}

• Be careful if this has any user interaction at any given point.

• Use a prepared statement, or PDO with prepared statements, they're much safer.

---

Footnotes:

When running an UPDATE query, it's best to use mysqli_affected_rows() for absolute truthness.

• http://php.net/manual/en/mysqli.affected-rows.php

Otherwise, you may get a false positive.

Answer 2

The buttons are running on the front-end (javascript) but the SQL to update your database is running on the back-end (php) so we need to connect these two together.

function updateUserAccess(user_id, access) {
    var d = { user_id: user_id, access: access };
    $.post('update.php', d);
}

And then each button would look like this:

<button type="button" id="passed" class="btn btn-success btn-flat" onclick="updateUserAccess(<?= $user->id ?>, 1)"><i class="fa fa-check"></i></button>
<button type="button" id="passed" class="btn btn-success btn-flat" onclick="updateUserAcces(<?= $user->id ?>, 0)"><i class="fa fa-check"></i></button>

Your query would then go in update.php and the javascript data you're passing in in d would be available in $_POST.

Now, with all of this being said, a few things:

1. I'm not sure why your quotes are escaped, that's a little unusual to say the least. Almost certainly not necessary unless you're mixing your logic and markup which isn't a good habit.
2. While the code I've given you will work, its also not optimal as we typically want to see event binding separated from the display markup, here it is in raw javascript and here in jquery (with some explanation).
3. You don't have to use AJAX to submit the change, as Fred has shown you can do it with a form. AJAX will not cause the page to refresh (unless you specifically tell it to) where a form typically does cause a refresh.
4. Finally, depending on your needs/use-case, I would recommend you learn a bit about modern frameworks in PHP and what they provide. For professional web development (and "modern" php code) the patterns they use/encourage are far superior to the old file-by-file style used for creating web applications. Check out the "MVC Pattern" and some frameworks which use it are CodeIgniter, CakePHP, and Laravel. All are fairly straightforward to enter into and get started with.