mysql中更新多行

qpgpyjmq 于 2021-06-20 发布在 Mysql

关注(0)|答案(3)|浏览(360)

我试图在一个查询中更新多行。我的代码中没有更新数据。我想把这两张table合起来。当用户输入“否”时，将显示通过外键连接的两个表中的数据。表1中的数据将更新。其中，表2中的列不会得到更新。我需要根据唯一id更新第二个表

if($_REQUEST["profile"] == "profile") 
{
    $Id = $_REQUEST["id"];
    $firstname  = mysql_real_escape_string($_REQUEST["firstname"]);
    $serial  = mysql_real_escape_string($_REQUEST["serial"]);
    $dom  = mysql_real_escape_string($_REQUEST["dom"]);
    $idno = $_REQUEST["idno"];
    $pow = mysql_real_escape_string(stripslashes($_REQUEST["pow"]));
    $address = mysql_real_escape_string(stripslashes($_REQUEST["address"]));
    $bookno = mysql_real_escape_string(stripslashes($_REQUEST["bookno"]));
    $zone = mysql_real_escape_string(stripslashes($_REQUEST["zone"]));
    $mobile = mysql_real_escape_string(stripslashes($_REQUEST["phone"]));
    $phone = mysql_real_escape_string(stripslashes($_REQUEST["mobile"]));
    $mothertongue=mysql_real_escape_string(stripslashes($_REQUEST["mothertongue"]));
    $nof=mysql_real_escape_string(stripslashes($_REQUEST["nof"]));
    $email=mysql_real_escape_string(stripslashes($_REQUEST["email"]));
    $nom=$_REQUEST["nom"];
    $nofemale=$_REQUEST["nofemale"];
mysql_query("UPDATE profile SET  firstname='".$firstname."',serial='".$serial."',dom='".$dom."',idno='".$idno."',pow='".$pow."',address='".$address."',bookno='".$bookno."',
zone='".$zone."',phone='".$mobile."',mobile='".$phone."',mothertongue='".$mothertongue."',email='".$email."',nof='".$nof."',nom='".$nom."',nofemale='".$nofemale."' WHERE id = '".$_POST['id']."' " ) or die(mysql_error());

    for($i=0;$i<count($_REQUEST['slno1']);$i++)
    {
        $mid=$_REQUEST['mid'][$i];
        $slno1 = mysql_real_escape_string(stripslashes($_REQUEST["slno1"][$i]));
    $name1 =  mysql_real_escape_string(stripslashes($_REQUEST["name1"][$i]));
    $rhof1 =  mysql_real_escape_string(stripslashes($_REQUEST["rhof1"][$i]));
    $dob1 =  mysql_real_escape_string(stripslashes($_REQUEST["dob1"][$i]));
    $dobapt1 =  mysql_real_escape_string(stripslashes($_REQUEST["dobapt1"][$i]));
    $doc1 =  mysql_real_escape_string(stripslashes($_REQUEST["doc1"][$i]));
    $doconf1 =  mysql_real_escape_string(stripslashes($_REQUEST["doconf1"][$i]));
    $qualification1 =  mysql_real_escape_string(stripslashes($_REQUEST["qualification1"][$i]));
    $school1 =  mysql_real_escape_string(stripslashes($_REQUEST["school1"][$i]));
    $occupation1 = mysql_real_escape_string(stripslashes($_REQUEST["occupation1"][$i]));

$run=mysql_query("UPDATE member SET
slno1='".$slno1."',name1='".$name1."',rhof1='".$rhof1."',dob1='".$dob1."',dobapt1='".$dobapt1."',doc1='".$doc1."',doconf1='".$doconf1."',qualification1='".$qualification1."' WHERE mid = '".$mid."' " ) or die(mysql_error()); 

}

}

mysql php

来源：https://stackoverflow.com/questions/50445906/update-multiple-rows-in-single-query-php-mysql

3条答案

按热度按时间

eh57zj3b1#

在您的查询中，您使用的是$\u post['mid']，而不是您应该使用的$mid

$mid=$_REQUEST['mid'][$i];

赞(0）回复(0）举报 2021-06-20

ioekq8ef2#

据我所知，如果使用where条件，update查询用于更新有限数量的记录。所以我能想到的唯一方法就是使用insert查询和on duplicate key update子句。请尝试以下代码：

for($i=0;$i<count($_REQUEST['mid']);$i++) {
    $mid[] = $_REQUEST['mid'][$i];
    $slno1[] = mysql_real_escape_string(stripslashes($_REQUEST["slno1"][$i]));
    $name1[] =  mysql_real_escape_string(stripslashes($_REQUEST["name1"][$i]));
    $rhof1[] =  mysql_real_escape_string(stripslashes($_REQUEST["rhof1"][$i]));
    $dob1[] =  mysql_real_escape_string(stripslashes($_REQUEST["dob1"][$i]));
    $dobapt1[] =  mysql_real_escape_string(stripslashes($_REQUEST["dobapt1"][$i]));
    $doc1[] =  mysql_real_escape_string(stripslashes($_REQUEST["doc1"][$i]));
    $doconf1[] =  mysql_real_escape_string(stripslashes($_REQUEST["doconf1"][$i]));
    $qualification1[] =  mysql_real_escape_string(stripslashes($_REQUEST["qualification1"][$i]));
    $school1[] =  mysql_real_escape_string(stripslashes($_REQUEST["school1"][$i]));
    $occupation1[] = mysql_real_escape_string(stripslashes($_REQUEST["occupation1"][$i]));
}

$query = "INSERT INTO `member` (`mid`,`slno1`,`name1`,`rhof1`,`dob1`,`dobapt1`,`doc1`,`doconf1`,`qualification1`) VALUES ";

for ($i = 0; $i < count($mid); $i++) {

    $query .= "('".$mid[$i]."','".$slno1[$i]."','".$name1[$i]."','".$rhof1[$i]."','".$dob1[$i]."','".$dobapt1[$i]."','".$doc1[$i]."','".$doconf1[$i]."','".$qualification1[$i]."')";

    if ($i != (count($mid) - 1)) {
        $query .= ',';
    }
}

$query .= ' ON DUPLICATE KEY UPDATE `slno1` = VALUES(`slno1`), `name1` = VALUES(`name1`), `rhof1` = VALUES(`rhof1`), `dob1` = VALUES(`dob1`), `dobapt1` = VALUES(`dobapt1`), `doc1` = VALUES(`doc1`), `doconf1` = VALUES(`doconf1`), `qualification1` = VALUES(`qualification1`);';

$run=mysql_query($query) or die(mysql_error());

希望这有帮助。

赞(0）回复(0）举报 2021-06-20

ukxgm1gy3#

请使用pdo，这样您就不必转义字符串，这样您的代码就更易于阅读。您的查询使用了太多引号，仅此一项就很容易失败。请用下面的例子，这将帮助你成功。
基本pdo更新：https://www.w3schools.com/php/php_mysql_update.asp
绑定参数：https://www.w3schools.com/php/php_mysql_prepared_statements.asp

赞(0）回复(0）举报 2021-06-20

我来回答

mysql中更新多行

3条答案

相关问题

热门标签

最新问答