?
Solved

update multiple columns and rows using mysql for PHP Page

Posted on 2011-02-26
11
Medium Priority
?
552 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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 

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

Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

Question has a verified solution.

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

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

771 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