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' ");

you tagged as php; use a form with a post method and a submit type of buttons with named attributes and use those in a conditional statement. JS/jQuery/ajax is also an option — Funk Forty Niner, May 31 '16 at 01:46
Did you try UPDATE users SET Access = "1" WHERE id in ('27','453','YourValue3','YourValue') or you want to update Access= "1", Access= "0" in one query ? — Sami, May 31 '16 at 01:47
Cheers fred, that sounds like that'd do the trick. submit and form — , May 31 '16 at 01:48
@SheaLavington Yep. want me to write up something in an answer, just something a little more elaborate? — Funk Forty Niner, May 31 '16 at 01:50
Sure thing :) That'd be great, i'm sure it'll help others too — , May 31 '16 at 01:51

Funk Forty Niner · Accepted Answer · 2016-05-31T02:01:12.047

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.

score 0 · Answer 2 · edited May 23 '17 at 12:24

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:

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.
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).
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.
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.

Hey, just saw this, seems like a long way to go round it, it seems a lot more for advanced people using minimal code, a form seems to be an easier way. — , May 31 '16 at 15:26
@SheaLavington Its a bit more to type, but once you're using it (I haven't used a form for hardly anything in a very long time) regularly its not any more complex than a form. It also has a great deal more flexibility in what it can do. — Cryptic, May 31 '16 at 20:25

Update a table of information on a button click

2 Answers2

Linked