Update multiple rows in single query php mysql

Question

I am trying to update multiple rows in a single query. Data doesnt get updated in my code. I am trying to join the two tables. When user enters a no. The data from the 2 tables will be displayed which is connected through the foreign key.The data from the table1 gets updated. Where as the columns from the table 2 doesnt get updated. I need to update the second table based on unique 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()); 

}



}

If you are sending id in mid then you don't need to write for loop, as it will always be a single value and the count will be always 1. Also try printing query and run it in mysql and check if it is working fine or not — Sambhaji Katrajkar
– Sambhaji Katrajkar, Commented May 21, 2018 at 9:44

Jan Myszkier · Accepted Answer · 2018-05-21 09:50:24Z

2

Please use PDO so you won't have to escape strings and so your code gets simpler to read. Your query has too many quotes used and this alone can make it easy to fail. Please use following examples and this should help you succeed.

Basic PDO update: https://www.w3schools.com/php/php_mysql_update.asp

Bind Params: https://www.w3schools.com/php/php_mysql_prepared_statements.asp

answered May 21, 2018 at 9:50

Jan Myszkier

2,7541 gold badge18 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Vrushali · Accepted Answer · 2018-05-21 09:45:21Z

1

In your query you are using $_POST['mid'] instead of that you should use $mid which you are already reading as

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

answered May 21, 2018 at 9:45

Vrushali

1213 silver badges11 bronze badges

2 Comments

user3168637 Over a year ago

I tried using $mid. Still the Rows doesnt get updated

Vrushali Over a year ago

Try printing $_REQUEST array before 'for' loop to check what values you are getting

Kishen Nagaraju · Accepted Answer · 2018-05-21 09:52:24Z

As per my understanding UPDATE query is used to update a limited number of records if using the where condition. So the only way that I can think of is using an INSERT query with ON DUPLICATE KEY UPDATE clause. Try the below code:

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());

Hope This Helps.

Collectives™ on Stack Overflow

Update multiple rows in single query php mysql

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related