duanliaoyin3171 2014-04-08 22:15
浏览 47
已采纳

如何编写更新SQL语句来更新多个记录

I have this code so far, which reads a simple table with 3 varchar fields:

<?php
//db connection code...

// select database 
mysql_select_db($db) or die ("Unable to select database!"); 

// create query 
$query = "SELECT * FROM Sheet1"; 

// execute query 
$result = mysql_query($query) or die ("Error in query: $query. ".mysql_error());

// see if any rows were returned 
if (mysql_num_rows($result) > 0) { 
// yes

// see if any rows were returned 
if (mysql_num_rows($result) > 0) { 
// yes 
// print them one after another 
echo "<html><body><table cellpadding=10 border=1>"; 
while($row = mysql_fetch_assoc($result)) { 
    echo "<tr>"; 
    echo "<td>".$row['stickerID']."</td>"; 
    echo "<td>" .$row['stickerName']."</td>"; 
    echo "<td>".$row['stickerSection']."</td>"; 
    echo "<td>"?>
             <form name="some form" action="editform.php" method="post">
             <input type="checkbox" name="<?php echo $row['stickerID'] ?>" value=" <?php echo $row['stickerStatus'] ?> ">
             <?php "</td>";
    echo "</tr>"; 
}
echo "</table></body></html>";
echo " " ?>
             <input type="submit" name="editWish" value="Edit">
             </form>
             <?php " ";
} else {  
// no 
// print status message 
echo "No rows found!"; 
}  

// free result set memory 
mysql_free_result($result); 

// close connection 
mysql_close($connection);
?>

The database has 4 fields, 3 varchar and 1 int with current value of 0. I checked the page source code and confirmed each checkbox name is the stickerID. Now I will post this to the editform.php which I must create. What Im wondering is how should I write the update sql so that it takes into account each new value selected by the user in the form?

This is my idea, but how to I do it for every checkbox?

editform.php

<?php

//update multiple records

//UPDATE user_items SET stickerStatus = $_POST["stickerStatus"] WHERE stickerID = $_POST["stickerID"];

?>
  • 写回答

2条回答 默认 最新

  • douna3367 2014-04-08 22:22
    关注

    First question: use mysql_fetch_assoc() instead of mysql_fetch_row(). That will return an associative array instead of an enumerated one.

    Second question: read up on HTML forms and form handling.

    The answer to the question in the comments:

    // The <form> tag should only be echoed once.
echo '<form name="some form" action="editform.php" method="post">';
while($row = mysql_fetch_assoc($result)) { 
    echo "<tr>"; 
    echo "<td>".$row['stickerID']."</td>"; 
    echo "<td>" .$row['stickerName']."</td>"; 
    echo "<td>".$row['stickerSection']."</td>"; 
    echo "<td>"?>
             <input type="hidden" name="status_<?php echo $row['stickerID"; ?>" value="0">
             <input type="checkbox" name="status_<?php echo $row['stickerID'] ?>" value="<?php echo $row['stickerStatus'] ?> ">
             <?php "</td>";
    echo "</tr>"; 
}
// You need a submit button to send the form
echo '<input type="submit">';
// Close the <form> tag
echo '</form>';

    Using a hidden input with the same name as the checkbox makes sure a value for the given input name is sent to the server. The value of a checkbox that's not checked will not be sent. In that case the hidden input will be used.

    You can get the submitted values in editform.php as follows:

    <?php
foreach ($_POST as $field => $value) {
    if (strpos($field, 'status_')) {
        // Using (int) makes sure it's cast to an integer, preventing SQL injections
        $stickerID = (int) str_replace('status_', '', $field);
        // Again, preventing SQL injections. If the status could be a string, then use mysql_real_escape_string()
        $stickerStatus = (int) $value;

        // Do something with the results
    }
}
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题？

悬赏问题

  • ¥15 如何让企业微信机器人实现消息汇总整合
  • ¥50 关于#ui#的问题：做yolov8的ui界面出现的问题
  • ¥15 如何用Python爬取各高校教师公开的教育和工作经历
  • ¥15 TLE9879QXA40 电机驱动
  • ¥20 对于工程问题的非线性数学模型进行线性化
  • ¥15 Mirare PLUS 进行密钥认证？（详解）
  • ¥15 物体双站RCS和其组成阵列后的双站RCS关系验证
  • ¥20 想用ollama做一个自己的AI数据库
  • ¥15 关于qualoth编辑及缝合服装领子的问题解决方案探寻
  • ¥15 请问怎么才能复现这样的图呀