PHP/MySQL: 更新 id 列等于 column 的整数?

huangapple go评论75阅读模式
英文:

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:

&lt;?php
    require_once(&#39;../system/config.php&#39;);
    require_once(&#39;../system/server.php&#39;);

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

?&gt; 

[...]

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

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(&#39;xxx&#39;, &#39;xxx&#39;, &#39;xxx&#39;, &#39;xx&#39;);    

//Item add
if (isset($_POST[&#39;plus&#39;])) {
    
    $count = mysqli_real_escape_string($db, $_POST[&#39;count&#39;]);

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

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:

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

</table>

On server side:

if (isset($_POST[&#39;plus&#39;])) {

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

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

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

huangapple
  • 本文由 发表于 2020年1月3日 23:37:30
  • 转载请务必保留本文链接:https://go.coder-hub.com/59581317.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定