英文:
PHP/MySQL: Update int where id = column?
问题
以下是您要翻译的内容:
我想从SQL数据库中列出一些产品,这部分运行得很完美。现在用户应该单击两个不同的按钮,以增加该特定产品的数量+1,并减少-1。
这是我的前端界面外观:
<?php
    require_once('../system/config.php');
    require_once('../system/server.php');
// 创建连接
$conn = new mysqli($db_host, $db_user, $db_pass, $db_name);
// 检查连接
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>
关于后端代码,我只提供了一个函数,其他两个函数相似。以下是该函数的翻译:
$db = mysqli_connect('xxx', 'xxx', 'xxx', 'xx');    
// 项目添加
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');
};
如果您想更改与单击按钮所在列相关的ID,您可以使用JavaScript来处理此操作。您可以为每个按钮添加一个JavaScript函数,以获取所需的ID并将其传递给后端代码进行更新。这需要一些前端和后端的协作来实现。
英文:
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?
答案1
得分: 4
以下是您要翻译的内容:
在服务器端:
如果(isset($ _POST ['plus'])){
//您不需要$ count
//$ count = mysqli_real_escape_string($ db,$ _POST ['count']);
$ query = "UPDATE 'shop' SET 'count' = 'count' + 1 WHERE 'id' =?";
//由于我们从用户输入接收数据,所以应该考虑到不安全,这就是为什么我们使用准备好的语句
$ stmt = $ db-> prepare($ query);
$ stmt-> bind_param('s',$ _POST ['item_id']);
$ stmt-> execute();
header('位置:frontend.php');
};
//类似的代码可以用于delete/minus操作
英文:
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;?>
</table>
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。


评论