1

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()); } }
Improve this question
3
  • What is the value of $_REQUEST['mid']?? Commented May 21, 2018 at 9:40
  • It is the id of the row which as to be updated. Commented May 21, 2018 at 9:41
  • 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 Commented May 21, 2018 at 9:44

3 Answers 3

Reset to default
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

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

Comments

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];
Improve this answer

2 Comments

I tried using $mid. Still the Rows doesnt get updated
Try printing $_REQUEST array before 'for' loop to check what values you are getting
1

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.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.