PHP/MySQL: Update int where id = column?

Question

I want to list some products out of an SQL-DB, which works perfectly. Now the user should click on two different buttons to add +1 to the amount of this specific product and subtract -1 of this.

This is how my front-end looks like:

<?php
    require_once('../system/config.php');
    require_once('../system/server.php');

// Create connection
$conn = new mysqli($db_host, $db_user, $db_pass, $db_name);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
$sql = "SELECT item, count, date FROM shop";
$result = $conn->query($sql);

?>

[...]

<form>
    <table>
        <form method="post" action="frontend.php">
            <tr>
                <th></th>
                <th></th>
                <th>Amount</th>
                <th>Item</th>
                <th>Date</th>
                <th></th>
            </tr>
            <?php while($row = mysqli_fetch_array($result)):?>
            <tr>
                <td><button class="delete" name="delete">x</button></td>
                <td><button class="minus" name="minus">-</button></td>
                <td><?php echo $row['count'];?></td>
                <td><?php echo $row['item'];?></td>
                <td><?php echo $row['date'];?></td>
                <td><button class="plus" name="plus">+</button></td> 
            </tr>
        </form>
        <?php endwhile;?>
    </table>
</form>

Here works everything so far, it lists the datas out of the DB.

For my backend-code I thought I will work with 'UPDATE'. I post only one function, the two others are quiet similar.

$db = mysqli_connect('xxx', 'xxx', 'xxx', 'xx');    

//Item add
if (isset($_POST['plus'])) {

    $count = mysqli_real_escape_string($db, $_POST['count']);

    $query = "UPDATE `shop` SET `count` = `count` + 1 WHERE `id` = '51'";
    mysqli_query($db, $query) or die(mysqli_error($db));
    header('location: frontend.php');
};

It works if I give a specific ID-number. What can I do if I want the ID who should be changed is the ID the column where the button is clicked?

It is a very bad idea to use `die(mysqli_error($conn));` in your code, because it could potentially leak sensitive information. See this post for more explanation: [mysqli or die, does it have to die?](https://stackoverflow.com/a/15320411/1839439) — Dharman, Jan 03 '20 at 15:42
You should add a hidden field to your form to pass along the id that you want to perform the action. You should also change your select to include the said id. — Dimitris Filippou, Jan 03 '20 at 15:43
It would be simpler to manage if you created one form per fetch, with a hidden `id` field so everything you need gets posted all in one simple click. Then your PHP can just check for `plus` or `minus` or `delete` to do the right thing — RiggsFolly, Jan 03 '20 at 15:44
Your buttons don't do anything. Maybe with JS but not with PHP. — Funk Forty Niner, Jan 03 '20 at 15:51
@Dharman *"I don't think form can be a child table"* No need to "think", you would be 100% correct. — Funk Forty Niner, Jan 03 '20 at 15:54
Please read [How to get the error message in MySQLi?](https://stackoverflow.com/a/22662582/1839439) and [Should we ever check for mysqli_connect() errors manually?](https://stackoverflow.com/q/58808332/1839439) — Dharman, Jan 03 '20 at 16:18

u_mulder · Accepted Answer · 2020-01-03T16:11:49.303

4

What really should be done is:

<!-- NOTE: no <form> tags around and inside <table> -->
<table>
    <tr>
        <th></th>
        <th></th>
        <th>Amount</th>
        <th>Item</th>
        <th>Date</th>
        <th></th>
    </tr>
    <?php while($row = mysqli_fetch_array($result)):?>
    <tr>
        <td>
            <!-- form tag appears here -->
            <form method="post" action="frontend.php">
                <button type="submit" class="delete" name="delete">x</button>
                <input type="hidden" name="item_id" value="<?php echo $row['id'];?>" />
            </form>
        </td>
        <td>
            <!-- form tag appears here -->
            <form method="post" action="frontend.php">
                <button type="submit" class="minus" name="minus">-</button>
                <input type="hidden" name="item_id" value="<?php echo $row['id'];?>" />
            </form>
        </td>
        <td><?php echo $row['count'];?></td>
        <td><?php echo $row['item'];?></td>
        <td><?php echo $row['date'];?></td>
        <td>
            <!-- form tag appears here -->
            <form method="post" action="frontend.php">
                <button type="submit" class="plus" name="plus">+</button>
                <!-- also `input` with type hidden appears, which holds 
                 the ID of current value (I assume it is `id` column) -->
                <input type="hidden" name="item_id" value="<?php echo $row['id'];?>" />
            </form>
        </td> 
    </tr>
    <?php endwhile;?>

On server side:

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

    // you don't need $count
    //$count = mysqli_real_escape_string($db, $_POST['count']);

    $query = "UPDATE `shop` SET `count` = `count` + 1 WHERE `id` = ?";
    // As we receive data from user input, it should be considered 
    // not safe that's why we use prepared statements
    $stmt = $db->prepare($query);
    $stmt->bind_param('s', $_POST['item_id']);
    $stmt->execute();

    header('location: frontend.php');
};
// similar code can be used to `delete`/`minus` actions

edited Jan 03 '20 at 16:11

answered Jan 03 '20 at 15:55

u_mulder

54,101
5
48
64

Have you seen [what I wrote above about the buttons?](https://stackoverflow.com/questions/59581317/php-mysql-update-int-where-id-column#comment105329140_59581317) Also [this one...](https://stackoverflow.com/questions/59581317/php-mysql-update-int-where-id-column#comment105329185_59581317) – Funk Forty Niner Jan 03 '20 at 15:57
@FunkFortyNiner maybe I should add `type="submit"`, thanks. – u_mulder Jan 03 '20 at 15:59
*"we receive data from user input, it should be considered not safe"*. Input should always be considered unsafe, no matter where it came from. – Dharman Jan 03 '20 at 15:59
Adding `type="submit"` to the button should fix it. :) – tftd Jan 03 '20 at 15:59
@u_mulder Aye, for sure mate :) – Funk Forty Niner Jan 03 '20 at 15:59
@FunkFortyNiner Can someone explain why type is needed. What am I missing? – Dharman Jan 03 '20 at 16:00
@Dharman `button` has `type="button"` __by default__. This type doesn't submit the form to the server. – u_mulder Jan 03 '20 at 16:02
@Dharman Because. Buttons without a "submit" type only works with JS. See [this Q&A](https://stackoverflow.com/q/469059/1415724). – Funk Forty Niner Jan 03 '20 at 16:02
@u_mulder Could you support this with some link to a documentation? The default is `submit` as far as I aware. It says so here: https://developer.mozilla.org/en-US/docs/Web/HTML/Element/button – Dharman Jan 03 '20 at 16:03
@Dharman looks like something has changed. Maybe `type="submit"` even not needed now. Thanks for the link. – u_mulder Jan 03 '20 at 16:04
@FunkFortyNiner [One of the answers](https://stackoverflow.com/a/47031164/1839439) claims that you can use it with or without forms – Dharman Jan 03 '20 at 16:05
Related to `type` attribute https://stackoverflow.com/questions/31644723/what-is-the-default-button-type As always old MSIE behaves not as all others) – u_mulder Jan 03 '20 at 16:07
1

@u_mulder Hence, my confusion. I even hacked a small form to test it and it submitted it without `type` attribute. – Dharman Jan 03 '20 at 16:09

PHP/MySQL: Update int where id = column?

1 Answers1