Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

update multiple columns and rows using mysql for PHP Page

Posted on 2011-02-26
11
Medium Priority
?
555 Views
Last Modified: 2012-05-11
I have table "t_task" I need to update 221 row , with 221 variable (mark1 ..... mark221) but is very default to write 221 row ,

and its not working well ?

please write the command to me. to update all 221 row.

<?php
$sql="
UPDATE t_task SET remarksm='$mark1' ,checkm=1 WHERE sn =1;
UPDATE t_task SET remarksm='$mark2' ,checkm=1 WHERE sn =2;
UPDATE t_task SET remarksm='$mark3' ,checkm=1 WHERE sn =3;
UPDATE t_task SET remarksm='$mark4' ,checkm=1 WHERE sn =4;
UPDATE t_task SET remarksm='$mark2' ,checkm=1 WHERE sn =2;
			.
			.
			.
			.
UPDATE t_task SET remarksm='$mark221' ,checkm=1 WHERE sn =221;
";
?>

Open in new window

0
Comment
Question by:AymanDasa
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
  • +1
11 Comments
 
LVL 8

Expert Comment

by:raulggonzalez
ID: 34986776
Hi,

Have you tried to execute the UPDATES one by one?...

Because if I'm not wrong.. you create $sql with 221 UPDATE statements, right?

I've noticed as well that you reference the vbles inside the string instead of concatenating them.

Maybe you can try


$sql=" UPDATE t_task SET remarksm='" . $mark1 . "' ,checkm=1 WHERE sn =1" ;
// Here you execute it

$sql=" UPDATE t_task SET remarksm='" . $mark2 . "' ,checkm=1 WHERE sn =2" ;
// Here you execute it

** Note that the string have sigle quote for the Mysql statement (remarksm VARCHAR???), double quote for the $sql string, concat the vble concat double quote, single quote... etc...


And having in mind that you have to do this 221 times, you should look for a loop not to repeat your code that many

Good luck!

0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 300 total points
ID: 34986897
error message is? ...

anyhow, your posted code does not show the "execution part" of the sql ...

you could try to change the SQL to 1 single UPDATE:
 $sql = " UPDATE t_task SET remarksm = CASE WHEN sn = 1 THEN '" .$mark1. "' WHEN sn = 2 THEN '" . $mark2 ."' end WHERE sn in (1,2)"; 

Open in new window


and you can add/modify the code to put all 221 cases/sn values as needed.
0
 
LVL 11

Assisted Solution

by:Ovid Burke
Ovid Burke earned 1700 total points
ID: 34986993
Try this:
<?php

$rows = 221;

for($i = 1; $i <= $rows; $i++) {
	// ${'mark' . $i} = "Remark number " . $i;
	$sql = "UPDATE t_task_EE SET remarksm = '" . ${'mark' . $i} ."' ,checkm = 1 WHERE sn = $i;";
	mysql_query($sql, $db_conn) or die("ERROR updating " . $i);
}
?>

Open in new window

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:AymanDasa
ID: 34987063
Dear raulggonzalez
I need to execute all of them in one time !!!
 
angelIII
yes its what I am talking ... but

checkm=1 in all should be update.
it is update 2 line only !! how if i need to update 221 line

0
 

Author Comment

by:AymanDasa
ID: 34987268
Haaay experts

I try this One

BUT its more than 221 line !!!!!!
There is no short way to do it?



$sql = " UPDATE t_task SET checkm=1, mark = 
 CASE 
 WHEN sn = 1 THEN '" .$mark1. "'
 WHEN sn = 2 THEN '" .$mark2. "'
 WHEN sn = 3 THEN '" .$mark3. "'
 WHEN sn = 4 THEN '" .$mark4. "'
 WHEN sn = 5 THEN '" .$mark5. "'
 WHEN sn = 6 THEN '" .$mark6. "'
 WHEN sn = 7 THEN '" .$mark7. "'
 WHEN sn = 8 THEN '" .$mark8. "'
 WHEN sn = 9 THEN '" .$mark9. "'
 WHEN sn = 10 THEN '" .$mark10. "'
 WHEN sn = 11 THEN '" .$mark11. "'
 WHEN sn = 12 THEN '" .$mark12. "'
 WHEN sn = 13 THEN '" .$mark13. "'
 WHEN sn = 14 THEN '" .$mark14. "'
 WHEN sn = 15 THEN '" .$mark15. "'
 ....
  WHEN sn = 221 THEN '" .$mark221. "'

 end 
 WHERE sn in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,.......,221) ";

Open in new window

0
 
LVL 8

Expert Comment

by:raulggonzalez
ID: 34987293
Hi,

can you use a temporary table??

If so, create a temp table with columns 'sn' and 'remarksm ' and insert the values 1, $mark1, 2, $mark2 and so on... (maybe in a loop???)


and the update would look like

UPDATE t_task_EE a, YourTempTable b
SET a.remarksm = b.resmarksm
where a.sn = b.sn


If you want to you for Angellll's  solution include the 221 cases and the values in the IN CLAUSE

 WHERE sn in (1,2, ...  , 221)";



Good luck!


0
 
LVL 11

Accepted Solution

by:
Ovid Burke earned 1700 total points
ID: 34987358
I am not sure whether you have tried this. It may have thrown an error because i had renamed the table for my tests, but here i is again,
<?php

$rows = 221;

for($i = 1; $i <= $rows; $i++) {
        // ${'mark' . $i} = "Remark number " . $i;
        $sql = "UPDATE t_task SET remarksm = '" . ${'mark' . $i} . "' ,checkm = 1 WHERE sn = $i;";
        mysql_query($sql, $db_conn) or die("ERROR updating " . $i);
}
?>

Open in new window

0
 

Author Comment

by:AymanDasa
ID: 34987363
(maybe in a loop???)


sorry its not clear Mr raulggonzalez
what is the code ?
0
 
LVL 11

Expert Comment

by:Ovid Burke
ID: 34987365
There is one question I have, and that is: Where do you define your 221 $mark(n) variables?
0
 
LVL 8

Expert Comment

by:raulggonzalez
ID: 34987393
well, taking the loop from madaboutasp you can do ...

more or less...

if you need the syntax to create a temp table

http://dev.mysql.com/doc/refman/5.1/en/create-table.html 


cheers
<?php

$rows = 221;

for($i = 1; $i <= $rows; $i++) {
   $sql = "INSERT INTO yourTempTable(sn, remarksm) " .
          " VALUES (" $i . ", '". ${'mark' . $i} . "')";
        mysql_query($sql, $db_conn) or die("ERROR inserting " . $i);
}
?>

Open in new window

0
 

Author Closing Comment

by:AymanDasa
ID: 34990331
Thanks All
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this article, we’ll look at how to deploy ProxySQL.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
The viewer will learn how to count occurrences of each item in an array.
Suggested Courses

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question