asked on

update multiple columns and rows using mysql for PHP Page

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

raulggonzalez

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!

SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Ovid Burke

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

AymanDasa

ASKER

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

AymanDasa

ASKER

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

raulggonzalez

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!

ASKER CERTIFIED SOLUTION

Ovid Burke

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

AymanDasa

ASKER

(maybe in a loop???)

sorry its not clear Mr raulggonzalez
what is the code ?

Ovid Burke

There is one question I have, and that is: Where do you define your 221 $mark(n) variables?

raulggonzalez

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

AymanDasa

ASKER

Thanks All